インストール状態
設定ファイル: /etc/my.cnf.d/mariadb-server.cnf
設定ファイル: /etc/my.cnf.d/client.cnf
データベース: datadir=/var/lib/myql
とりあえずこれを, ディスクに余裕がある /local/disk1/SQL に変更する.
# systemctl mariadb.service
# rsync -avr /var/lib/mysql/ /local/disk1/SQL
# chown mysql /local/disk1/SQL
# chgrp mysql /local/disk1/SQL
ここでdatadir=/local/disk1/SQL に変更して再起動.
# mysql -h localhost -u root -p Mariadb> ローカルホストはOK # mysql -h 10.249.229.123 -u root -p Access denied for user 'root'@'h123.229.249.10.1016485.vlan.kuins.net'
外部からの接続許可
ここを参考にすると
MariaDB > select User,Host from mysql.user ;
+-------------+----------------------------------------+
| User | Host |
+-------------+----------------------------------------+
| slurm | h123.229.249.10.1016485.vlan.kuins.net |
| mariadb.sys | localhost |
| mysql | localhost |
| root | localhost |
| slurm | localhost |
+-------------+----------------------------------------+
MaridaDB > show grants;
+-----------------------------------------------------------------
| Grants for root@localhost
+-----------------------------------------------------------------
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA
mysql_native_password USING '*AABFBADF686632CD641B5C0C7E15248DE3E308C5' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
ふむう．ユーザー足さないとね.
MariaDB > create user 'root'@'%' identified by 'いつもの'; アクセスできるユーザー追加
MariaDB > grant all privileges on *.* to root@'%' identified by 'いつもの' with grant option; ユーザーやDBの追加権限
これで，外部から接続できて, テーブルやユーザーの追加も可能である．
だが, Mariabackupなるものを利用すると, 全自動で全部できるらしい.
Mariabackupでバックアップ（失敗）
旧SQLサーバーで実行. ピンクが作業フォルダ
sun0 # mkdir /local/disk0/mariabackup
sun0 # mariabackup --backup --target-dir /local/disk0/mariabackup/ -u root -p いつもの
/local/disk0/mariabackup フォルダーを is2013-1 に転送. 新サーバーで実行：
is2013-1 # systemctl stop mariadb.service
is2013-1 # rm -rf /local/disk1/SQL/*
is2013-1 # mariabackup --prepare --target-dir /local/disk1/mariabackup/ -u root -p いつもの
[ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was created with Backup 10.3.28-MariaDB.
You must start up and shut down MariaDB 10.4 or earlier on the data directory.
あれ？バージョン違いのようであるな. sun0: MariaDB 10.3 で, is2013-1: MariaDB 10.5 なのが, ダメっぽいなあ．どうやら，一度DBをupgradeしないとダメらしいぞ．
MariaDB10.3 → 10.5のバージョンアップ
データを旧サーバーから新サーバーにコピー.
is2013-1 # systemctl stop mariadb.service
is2013-1 # rm -rf /local/disk1/SQL
is2013-1 # rsync -avr sun0:/local/disk1/mysql/ local/disk1/SQL/
ここで, なんと, 起動する:
is2013-1 # systemctl start mariadb.service
Socket file /local/disk1/SQL/mysql.sock exists. あ. コピー元が稼働中ではダメなんだな. 停止してからrsyncして, 再起動だ！
is2013-1 # systemctl start mariadb.service
なんと起動するのだな．で, 生きながらにしてupgrade:
is2013-1 # # mariadb-upgrade -u root -p
Enter password:
Major version upgrade detected from 10.3.17-MariaDB to 10.5.27-MariaDB. Check required!
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.transaction_registry OK
mysql.user OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
SDA
SDA.data OK
SDA.list OK
information_schema
performance_schema
phase_01
phase_01.alc_1 OK
phase_01.cel_1 OK
phase_01.col_1 OK
phase_01.dat_1 OK
phase_01.dclj_1 OK
phase_01.div_1 OK
phase_01.dlg_1 OK
phase_01.dom_1 OK
phase_01.exlj_1 OK
phase_01.gas_1 OK
phase_01.hs_1 OK
phase_01.idx_1 OK
phase_01.ini_1 OK
phase_01.lj_1 OK
phase_01.log_1 OK
phase_01.main_0 OK
phase_01.mcv_1 OK
phase_01.net_1 OK
phase_01.pw_1 OK
phase_01.reg_1 OK
phase_01.sim_1 OK
phase_01.test OK
phase_01.test2 OK
phase_01.tmp_1 OK
phase_01.var_1 OK
phase_01.wal_1 OK
phase_02
phase_02.cart1d_2 OK
phase_02.cel_2 OK
phase_02.data1d_2 OK
phase_02.dclj_2 OK
phase_02.dom_2 OK
phase_02.exlj_2 OK
phase_02.f1_2 OK
phase_02.f2_2 OK
phase_02.gas_2 OK
phase_02.hs_2 OK
phase_02.idx_2 OK
phase_02.ini_2 OK
phase_02.legend1d_2 OK
phase_02.line1d_2 OK
phase_02.lj_2 OK
phase_02.lnk_2 OK
phase_02.log_2 OK
phase_02.lset1d_2 OK
phase_02.net_2 OK
phase_02.parm1d_2 OK
phase_02.reg_2 OK
phase_02.sim_2 OK
phase_02.tmt_2 OK
phase_02.var_2 OK
phase_02.wal_2 OK
phase_03
phase_03.affine_3 OK
phase_03.bin_3 OK
phase_03.bx3_3 OK
phase_03.bxn_3 OK
phase_03.bxt_3 OK
phase_03.cart1d_3 OK
phase_03.cel_3 OK
phase_03.data1d_3 OK
phase_03.dclj_3 OK
phase_03.dom_3 OK
phase_03.exlj_3 OK
phase_03.f1_3 OK
phase_03.f2_3 OK
phase_03.gas_3 OK
phase_03.hs_3 OK
phase_03.idx_3 OK
phase_03.ini_3 OK
phase_03.kid_3 OK
phase_03.legend1d_3 OK
phase_03.line1d_3 OK
phase_03.lj_3 OK
phase_03.lnk_3 OK
phase_03.log_3 OK
phase_03.lset1d_3 OK
phase_03.main_0 OK
phase_03.miki OK
phase_03.net_3 OK
phase_03.parm1d_3 OK
phase_03.reg_3 OK
phase_03.sdadata_3 OK
phase_03.sdalist_3 OK
phase_03.sfr_3 OK
phase_03.sim_3 OK
phase_03.tmt_3 OK
phase_03.var_3 OK
phase_03.vhs_3 OK
phase_03.vlg_3 OK
phase_03.vss_3 OK
phase_03.vss_col_3 OK
phase_03.wal_3 OK
pipenet
pipenet.cell_consts OK
pipenet.cell_series OK
pipenet.cell_vars OK
pipenet.cell_vars_delta OK
pipenet.channel_consts OK
pipenet.channel_series OK
pipenet.channel_series_index OK
pipenet.channel_series_vars OK
pipenet.resources OK
pipenet.simulations OK
shogoDB
shogoDB.Test_1 OK
shogoDB.test_2 OK
slurm_acct_db
test
test.first OK
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK
OKらしいぞ，OKだって.
ほんまに動いてるわ. 笑える〜