CentOS と MariaDB で 双方向レプリケーション + 1 Slave 構成を作って見るテスト
構成
host1
key | value |
---|---|
hostname | radius0 |
ip address | 192.168.100.102 |
role | radius primary |
db role | Primary Replica |
server_id | 101 |
auto_increment_increment | 2 |
auto_increment_offset | 1 |
host2
key | value |
---|---|
hostname | radius1 |
ip address | 192.168.100.103 |
role | radius secondary |
db role | Replica Primary |
server_id | 102 |
auto_increment_increment | 2 |
auto_increment_offset | 2 |
host3
key | value |
---|---|
hostname | slave-db0 |
ip address | 192.168.100.110 |
role | database backup |
db role | Replica Replica |
server_id | 103 |
auto_increment_increment | 2 |
想定環境
┌─────────────┐ Con1 ┌─────────────┐ master │ ├────────────►│ │ slave │ host 1 │ │ host 2 │ slave │ radius0 │◄────────────┤ radius1 │ master └─────┬───────┘ Con2 └───────┬─────┘ │ │ │ │ │ │ │ │ Con1 │ │ Con2 │ │ │ │ │ │ │ ┌────────────────┐ │ │ │ │ │ └─────►│ host 3 │◄────┘ │ slave-db0 │ slave └────────────────┘ slave
前回と違うところ
レプリケーションするときにコネクション名をつける
connection name | default master host | default slave hosts |
---|---|---|
Con1 | host1 | host2, host3 |
Con2 | host2 | host1, host3 |
前回
前回を参考に組み立てていく
firewall 設定 radius[01], slave-db0
sudo firewall-cmd --list-all sudo firewall-cmd --add-port=3306/tcp --permanent sudo firewall-cmd --add-port=4567/tcp --permanent sudo firewall-cmd --add-port=4444/tcp --permanent sudo firewall-cmd --reload sudo firewall-cmd --list-all
replication 準備 radius0
server.cnf の設定編集
sudo cp -aiv /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf.$(date +%F_%H-%M-%S) sudo vim /etc/my.cnf.d/server.cnf # [mysqld] 配下に書き込む log-bin log-basename=radius0 binlog-format=mixed server_id=101 # 2 づつ増やす auto_increment_increment=2 # 1 からカウントし始める auto_increment_offset=1 # [galera] 配下 bind-address=0.0.0.0 のコメントアウトを外す
sample
[ope@radius0 ~]$ sudo diff -uprN /etc/my.cnf.d/server.cnf.2021-07-17_16-11-27 /etc/my.cnf.d/server.cnf --- /etc/my.cnf.d/server.cnf.2021-07-17_16-11-27 2021-07-03 17:42:58.021731556 +0900 +++ /etc/my.cnf.d/server.cnf 2021-07-17 16:11:58.703418875 +0900 @@ -11,6 +11,14 @@ # this is only for the mysqld standalone daemon [mysqld] character-set-server=utf8 +log-bin +log-basename=radius0 +binlog-format=mixed +server_id=101 +# 2 づつ増やす +auto_increment_increment=2 +# 1 からカウントし始める +auto_increment_offset=1 # # * Galera-related settings @@ -26,7 +34,7 @@ character-set-server=utf8 # # Allow server to accept connections on all interfaces. # -#bind-address=0.0.0.0 +bind-address=0.0.0.0 # # Optional setting #wsrep_slave_threads=1 [ope@radius0 ~]$
mariadb restart
sudo systemctl status mariadb.service | grep -E "Active|PID" sudo systemctl restart mariadb.service sudo systemctl status mariadb.service | grep -E "Active|PID"
replication user を作る
mysql --defaults-extra-file=mariadb_root.cnf SELECT host, user FROM mysql.user WHERE user='replication_user'; CREATE USER 'replication_user'@'%' IDENTIFIED BY 'bigs3cret'; GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%'; FLUSH PRIVILEGES; SELECT host, user FROM mysql.user WHERE user='replication_user'; QUIT;
replication の準備 radius1
server.cnf の設定
sudo cp -av /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf.$(date +%F_%H-%M-%S) sudo vim /etc/my.cnf.d/server.cnf # [mysqld] 配下に以下を追記する log-bin log-basename=radius1 binlog-format=mixed server_id=102 # 2 づつカウントする auto_increment_increment=2 # 2 からカウントを始める auto_increment_offset=2 # [galera] 配下 bind-address=0.0.0.0 のコメントアウトを外す
sample
[ope@radius1 ~]$ sudo diff -uprN /etc/my.cnf.d/server.cnf.2021-07-17_16-15-51 /etc/my.cnf.d/server.cnf --- /etc/my.cnf.d/server.cnf.2021-07-17_16-15-51 2021-04-11 17:04:35.057898325 +0900 +++ /etc/my.cnf.d/server.cnf 2021-07-17 16:16:16.203070071 +0900 @@ -11,6 +11,14 @@ # this is only for the mysqld standalone daemon [mysqld] character-set-server=utf8 +log-bin +log-basename=radius1 +binlog-format=mixed +server_id=102 +# 2 づつカウントする +auto_increment_increment=2 +# 2 からカウントを始める +auto_increment_offset=2 # # * Galera-related settings @@ -26,7 +34,7 @@ character-set-server=utf8 # # Allow server to accept connections on all interfaces. # -#bind-address=0.0.0.0 +bind-address=0.0.0.0 # # Optional setting #wsrep_slave_threads=1 [ope@radius1 ~]$
mariadb restart
sudo systemctl status mariadb | grep -E "Active|PID" sudo systemctl restart mariadb sudo systemctl status mariadb | grep -E "Active|PID"
replication の準備 slave-db0
server.cnf の設定
sudo cp -av /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf.$(date +%F_%H-%M-%S) sudo vim /etc/my.cnf.d/server.cnf # [mysqld] 配下に以下を追記する log-bin log-basename=slave-db0 binlog-format=mixed server_id=103 # 2 づつカウントする # auto_increment_increment=2 # 2 からカウントを始める # auto_increment_offset=2 # [galera] 配下 bind-address=0.0.0.0 のコメントアウトを外す
[ope@slave-db0 ~]$ diff -uprN /etc/my.cnf.d/server.cnf.2021-08-11_11-21-32 /etc/my.cnf.d/server.cnf --- /etc/my.cnf.d/server.cnf.2021-08-11_11-21-32 2021-04-11 17:04:35.057898325 +0900 +++ /etc/my.cnf.d/server.cnf 2021-08-11 11:22:17.658896135 +0900 @@ -11,6 +11,14 @@ # this is only for the mysqld standalone daemon [mysqld] character-set-server=utf8 +log-bin +log-basename=slave-db0 +binlog-format=mixed +server_id=103 +# 2 づつカウントする +# auto_increment_increment=2 +# 2 からカウントを始める +# auto_increment_offset=2 # # * Galera-related settings @@ -26,7 +34,7 @@ character-set-server=utf8 # # Allow server to accept connections on all interfaces. # -#bind-address=0.0.0.0 +bind-address=0.0.0.0 # # Optional setting #wsrep_slave_threads=1 [ope@slave-db0 ~]$
restart mariadb
sudo systemctl status mariadb | grep -E "Active|PID" sudo systemctl restart mariadb sudo systemctl status mariadb | grep -E "Active|PID"
replication user は作らないでおく
radius0 の書き込みを止めてバックアップ
mysql --defaults-extra-file=mariadb_root.cnf FLUSH TABLES WITH READ LOCK;
READ LOCK
したターミナルは起動しっぱなしにする
別ターミナルを開いてバックアップをする
mysqldump --defaults-extra-file=mariadb_root.cnf --all-databases \ --flush-logs --single-transaction --master-data=2 > \ all_databases_on_master_$(date +%F_%H-%M-%S).sql
radius1 にバックアップデータの転送
sftp <username>@<ip address> lls # ls in localhost put <file name> ls # ls in remote host exit
[ope@radius0 ~]$ sftp ope@192.168.100.103 Connected to ope@192.168.100.103. sftp> lls all_databases_on_master_2021-08-11_14-36-18.sql mariadb_root.cnf mariadb_radius.cnf sftp> put all_databases_on_master_2021-08-11_14-36-18.sql Uploading all_databases_on_master_2021-08-11_14-36-18.sql to /home/ope/all_databases_on_master_2021-08-11_14-36-18.sql all_databases_on_master_2021-08-11_14- 100% 1365KB 23.3MB/s 00:00 sftp> ls all_databases_on_master_2021-08-11_14-36-18.sql mariadb_radius.cnf mariadb_root.cnf sftp> exit [ope@radius0 ~]$
radius1 でバックアップデータをリストア
mysql --defaults-extra-file=mariadb_root.cnf < <file name>
[ope@radius1 ~]$ mysql --defaults-extra-file=mariadb_root.cnf < all_databases_on_master_2021-08-11_14-36-18.sql [ope@radius1 ~]$
一応 FLUSH PRIVILEGES;
する
mysql --defaults-extra-file=mariadb_root.cnf FLUSH PRIVILEGES; QUIT;
radius0 で RESET MASTER
する
RESET MASTER;
radius0 の情報を確認する
SHOW MASTER STATUS;
SELECT @@global.gtid_binlog_pos;
MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | radius0-bin.000001 | 330 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.001 sec) MariaDB [(none)]> SELECT @@global.gtid_binlog_pos; +--------------------------+ | @@global.gtid_binlog_pos | +--------------------------+ | | +--------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]>
bin-log の File と Position を覚えておく
radius0 で UNLOCK TABLES
を実行する
backup が終わった時点で UNLOCK してもいいだろう
UNLOCK TABLES;
radius1 で RESET SLAVE
しておく
mysql --defaults-extra-file=mariadb_root.cnf
RESET SLAVE;
radius1 で CHANGE MASTER TO
する
MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.1.2.7 レプリカでのソース構成の設定
の資料によると CHANGE REPLICATION SOURCE TO
でも良いらしい
昨今の MASTER
SLAVE
発言やめよう運動にちなんだものなのだろう
CHANGE MASTER TO - MariaDB Knowledge Base
ここのトップにもコメントが書かれている
ただ、ドキュメントに反映されていないだけなのか、実装がまだなのか、 mariadb で使えるのか不明
要検証?
CHANGE REPLICATION SOURCE 'Con1' TO MASTER_HOST='192.168.100.102', MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='radius0-bin.000001', MASTER_LOG_POS=330, MASTER_CONNECT_RETRY=10, MASTER_USE_GTID=slave_pos;
やってみる
MariaDB [(none)]> CHANGE REPLICATION SOURCE 'Con1' TO -> MASTER_HOST='192.168.100.102', -> MASTER_USER='replication_user', -> MASTER_PASSWORD='bigs3cret', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='radius0-bin.000001', -> MASTER_LOG_POS=330, -> MASTER_CONNECT_RETRY=10, -> MASTER_USE_GTID=slave_pos; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'REPLICATION SOURCE 'Con1' TO MASTER_HOST='192.168.100.102', MASTER_USER='...' at line 1 MariaDB [(none)]>
ちなみに、バージョンは
MariaDB [(none)]> SELECT VERSION(); +--------------------+ | VERSION() | +--------------------+ | 10.5.9-MariaDB-log | +--------------------+ 1 row in set (0.001 sec) MariaDB [(none)]>
うまく動かなかったので、 CHANGE MASTER TO
で実行
CHANGE MASTER 'Con1' TO MASTER_HOST='192.168.100.102', MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='radius0-bin.000001', MASTER_LOG_POS=330, MASTER_CONNECT_RETRY=10, MASTER_USE_GTID=slave_pos;
MariaDB [(none)]> CHANGE MASTER 'Con1' TO -> MASTER_HOST='192.168.100.102', -> MASTER_USER='replication_user', -> MASTER_PASSWORD='bigs3cret', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='radius0-bin.000001', -> MASTER_LOG_POS=330, -> MASTER_CONNECT_RETRY=10, -> MASTER_USE_GTID=slave_pos; Query OK, 0 rows affected (0.116 sec) MariaDB [(none)]>
radius1 で START SLAVE
する
START SLAVE - MariaDB Knowledge Base
によると、こちらは START REPLICA
が出来るようだ
また、今回は CONNECTION NAME
を指定しているので、単純な START SLAVE
では駄目かも?
START SLAVE 'Con1' ; START REPLICA 'Con1' ;
SHOW SLAVE STATUS - MariaDB Knowledge Base
によると、こちらも SLAVE
を REPLICA
に変えても動くようだ
CONNECTION NAME
もちゃんと入れてあげよう
全 CONNECTION
を見たいならまたコマンドがあるようだ
SHOW SLAVE 'Con1' STATUS \G SHOW REPLICA 'Con1' STATUS \G SHOW ALL SLAVES STATUS \G SHOW ALL REPLICA STATUS \G
早速使ってみよう
MariaDB [(none)]> START REPLICA 'Con1' ; Query OK, 0 rows affected (0.058 sec) MariaDB [(none)]> SHOW REPLICA 'Con1' STATUS \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.102 Master_User: replication_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: radius0-bin.000001 Read_Master_Log_Pos: 330 Relay_Log_File: radius1-relay-bin-con1.000002 Relay_Log_Pos: 631 Relay_Master_Log_File: radius0-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 330 Relay_Log_Space: 947 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 101 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.000 sec) MariaDB [(none)]>
コネクション名を表示するカラムがないことが分かった。チョット残念
ただ
Relay_Log_File: radius1-relay-bin-con1.000002
これを見れば、コネクション名が分かるっぽい
Slave_IO_Running: Yes Slave_SQL_Running: Yes
どちらも Yes
であり、問題ない
これで radius0 -> radius1 への片方向レプリケーションは出来た
後に分かったことだが、 SHOW ALL REPLICAS STATUS
で CONNECTION NAME
が分かる
SHOW ALL REPLICAS STATUS \G
MariaDB [(none)]> SHOW ALL REPLICAS STATUS \G *************************** 1. row *************************** Connection_name: Con1 ...
1 行目に表示される
radius1 で SHOW MASTER STATUS
する
SHOW MASTER STATUS - MariaDB Knowledge Base
によると、 MASTER
部分は BINLOG
でも使えるようだ
MASTER
は BINLOG
にリネームされたが、互換性維持のため残すらしい
SHOW MASTER STATUS;
SHOW BINLOG STATUS;
SELECT @@global.gtid_binlog_pos;
MariaDB [(none)]> SHOW BINLOG STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | radius1-bin.000001 | 1401770 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.001 sec) MariaDB [(none)]> SELECT @@global.gtid_binlog_pos; +--------------------------+ | @@global.gtid_binlog_pos | +--------------------------+ | 0-102-153 | +--------------------------+ 1 row in set (0.001 sec) MariaDB [(none)]>
使えた
bin-log の File と Position の値をメモっておく
radius0 で CHANGE MASTER TO
する
パラメータはちゃんと確認しよう
mysql --defaults-extra-file=mariadb_root.cnf CHANGE MASTER TO MASTER_HOST='192.168.100.103', MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='radius1-bin.000001', MASTER_LOG_POS=1401770, MASTER_CONNECT_RETRY=10, MASTER_USE_GTID=slave_pos;
MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.100.103', -> MASTER_USER='replication_user', -> MASTER_PASSWORD='bigs3cret', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='radius1-bin.000001', -> MASTER_LOG_POS=1401770, -> MASTER_CONNECT_RETRY=10, -> MASTER_USE_GTID=slave_pos; Query OK, 0 rows affected (0.088 sec) MariaDB [(none)]>
あ。コネクション名を付け忘れた
CHANGE MASTER 'Con2' TO MASTER_HOST='192.168.100.103', MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='radius1-bin.000001', MASTER_LOG_POS=1401770, MASTER_CONNECT_RETRY=10, MASTER_USE_GTID=slave_pos;
MariaDB [(none)]> CHANGE MASTER 'Con2' TO -> MASTER_HOST='192.168.100.103', -> MASTER_USER='replication_user', -> MASTER_PASSWORD='bigs3cret', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='radius1-bin.000001', -> MASTER_LOG_POS=1401770, -> MASTER_CONNECT_RETRY=10, -> MASTER_USE_GTID=slave_pos; ERROR 1934 (HY000): Connection 'Con2' conflicts with existing connection '' MariaDB [(none)]>
コンフリクトを起こした
とりあえずステータス確認してみる
MariaDB [(none)]> SHOW REPLICA STATUS\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.100.103 Master_User: replication_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: radius1-bin.000001 Read_Master_Log_Pos: 1401770 Relay_Log_File: radius0-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: radius1-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No ... MariaDB [(none)]>
Relay log 名を見ると
Relay_Log_File: radius0-relay-bin.000001
コネクション名がはいっていない
どうしたものか
RESET REPLICA してみる
RESET SLAVE - MariaDB Knowledge Base
RESET REPLICA ['Connection Name'] [ALL] ; RESET REPLICA;
MariaDB [(none)]> RESET REPLICA; Query OK, 0 rows affected (0.081 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW REPLICA STATUS \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.100.103 Master_User: replication_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No ... 1 row in set (0.000 sec) MariaDB [(none)]>
relay log とかなくなった
もう一度 CHANGE MASTER TO
する
CHANGE MASTER 'Con2' TO MASTER_HOST='192.168.100.103', MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='radius1-bin.000001', MASTER_LOG_POS=1401770, MASTER_CONNECT_RETRY=10, MASTER_USE_GTID=slave_pos;
MariaDB [(none)]> CHANGE MASTER 'Con2' TO -> MASTER_HOST='192.168.100.103', -> MASTER_USER='replication_user', -> MASTER_PASSWORD='bigs3cret', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='radius1-bin.000001', -> MASTER_LOG_POS=1401770, -> MASTER_CONNECT_RETRY=10, -> MASTER_USE_GTID=slave_pos; ERROR 1934 (HY000): Connection 'Con2' conflicts with existing connection '' MariaDB [(none)]>
STOP REPLICA
してみる
MariaDB [(none)]> STOP REPLICA; Query OK, 0 rows affected, 1 warning (0.001 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW REPLICA STATUS\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.100.103 Master_User: replication_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB:
ホスト情報とか残ったまま
たぶんまたやっても、もう一度コンフリクトするだけ
もう一度ドキュメントを見る
RESET SLAVE - MariaDB Knowledge Base
The ALL also resets the PORT, HOST, USER and PASSWORD parameters for the slave. If you are using a connection name, it will permanently delete it and it will not show up anymore in SHOW ALL SLAVES STATUS.
ALL したら良かったっぽいな
やる
RESET REPLICA ALL ;
MariaDB [(none)]> RESET REPLICA ALL ; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]>
エラーなし
MariaDB [(none)]> SHOW REPLICA STATUS \G Empty set (0.001 sec) MariaDB [(none)]>
きれいに消えた
CHANGE MASTER TO
Retry
CHANGE MASTER 'Con2' TO MASTER_HOST='192.168.100.103', MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='radius1-bin.000001', MASTER_LOG_POS=1401770, MASTER_CONNECT_RETRY=10, MASTER_USE_GTID=slave_pos;
MariaDB [(none)]> CHANGE MASTER 'Con2' TO -> MASTER_HOST='192.168.100.103', -> MASTER_USER='replication_user', -> MASTER_PASSWORD='bigs3cret', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='radius1-bin.000001', -> MASTER_LOG_POS=1401770, -> MASTER_CONNECT_RETRY=10, -> MASTER_USE_GTID=slave_pos; Query OK, 0 rows affected (0.130 sec) MariaDB [(none)]>
radius0 で START REPLICA
する
START REPLICA 'Con2' ;
MariaDB [(none)]> START REPLICA 'Con2' ; Query OK, 0 rows affected (0.211 sec) MariaDB [(none)]>
SHOW REPLICA 'Con2' STATUS \G SHOW ALL REPLICAS STATUS \G
MariaDB [(none)]> SHOW REPLICA 'Con2' STATUS \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.103 Master_User: replication_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: radius1-bin.000001 Read_Master_Log_Pos: 1401770 Relay_Log_File: radius0-relay-bin-con2.000002 Relay_Log_Pos: 1402071 Relay_Master_Log_File: radius1-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1401770 Relay_Log_Space: 1402387 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 102 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-102-153 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 142 Slave_Non_Transactional_Groups: 11 Slave_Transactional_Groups: 0 1 row in set (0.000 sec) MariaDB [(none)]>
Slave_IO_Running: Yes Slave_SQL_Running: Yes
OK
MariaDB [(none)]> SHOW ALL REPLICAS STATUS \G *************************** 1. row *************************** Connection_name: Con2
コネクション名も確認できた
これでお互いに同期出来たはず
slave-db0 を Con1
の REPLICA にする
おそらく、 radius1 を radius0 の REPLICA にしたときと同じ手順を踏む必要がありそう
なぜ?
slave-db0 は もととなるデータを持っていないから
というわけで、radius1 のためにバックアップしたデータを radius0 から slave-db0 に送る
sftp user@ip lls put ls exit
[ope@radius0 ~]$ sftp ope@192.168.100.110 Connected to ope@192.168.100.110. sftp> lls all_databases_on_master_2021-08-11_14-36-18.sql mariadb_root.cnf mariadb_radius.cnf sftp> put all_databases_on_master_2021-08-11_14-36-18.sql Uploading all_databases_on_master_2021-08-11_14-36-18.sql to /home/ope/all_databases_on_master_2021-08-11_14-36-18.sql all_databases_on_master_2021-08-11_14- 100% 1365KB 25.5MB/s 00:00 sftp> ls all_databases_on_master_2021-08-11_14-36-18.sql mariadb_radius.cnf mariadb_root.cnf sftp> exit [ope@radius0 ~]$
slave-db0 でデータをリストア
mysql --defaults-extra-file=mariadb_root.cnf < <file name>
[ope@slave-db0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf < all_databases_on_master_2021-08-11_14-36-18.sql
[ope@slave-db0 ~]$
一応 FLUSH PRIVILEGES;
をしておく
mysql --defaults-extra-file=mariadb_root.cnf FLUSH PRIVILEGES;
radius0 の情報を確認する
mysql --defaults-extra-file=mariadb_root.cnf SHOW BINLOG STATUS; SELECT @@global.gtid_binlog_pos;
MariaDB [(none)]> SHOW BINLOG STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | radius0-bin.000001 | 330 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT @@global.gtid_binlog_pos; +--------------------------+ | @@global.gtid_binlog_pos | +--------------------------+ | | +--------------------------+ 1 row in set (0.001 sec) MariaDB [(none)]>
slave-db0 で CHANGE MASTER TO
する
CONNECTION NAME
を忘れない
mysql --defaults-extra-file=mariadb_root.cnf CHANGE MASTER 'Con1' TO MASTER_HOST='192.168.100.102', MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='radius0-bin.000001', MASTER_LOG_POS=330, MASTER_CONNECT_RETRY=10, MASTER_USE_GTID=slave_pos;
MariaDB [(none)]> CHANGE MASTER 'Con1' TO -> MASTER_HOST='192.168.100.102', -> MASTER_USER='replication_user', -> MASTER_PASSWORD='bigs3cret', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='radius0-bin.000001', -> MASTER_LOG_POS=330, -> MASTER_CONNECT_RETRY=10, -> MASTER_USE_GTID=slave_pos; Query OK, 0 rows affected (0.133 sec) MariaDB [(none)]>
slave-db0 の Con1 を START REPLICA
する
START REPLICA 'Con1' ;
MariaDB [(none)]> START REPLICA 'Con1' ; Query OK, 0 rows affected (0.051 sec) MariaDB [(none)]>
no error
SHOW REPLICA 'Con1' STATUS \G SHOW ALL REPLICAS STATUS \G
MariaDB [(none)]> SHOW REPLICA 'Con1' STATUS \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.102 Master_User: replication_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: radius0-bin.000001 Read_Master_Log_Pos: 330 Relay_Log_File: slave-db0-relay-bin-con1.000002 Relay_Log_Pos: 631 Relay_Master_Log_File: radius0-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 330 Relay_Log_Space: 949 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 101 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.001 sec) MariaDB [(none)]>
Slave_IO_Running: Yes Slave_SQL_Running: Yes
OK
MariaDB [(none)]> SHOW ALL REPLICAS STATUS \G *************************** 1. row *************************** Connection_name: Con1
コネクション名も確認できた
radius1 の情報を確認する
mysql --defaults-extra-file=mariadb_root.cnf SHOW BINLOG STATUS; SELECT @@global.gtid_binlog_pos;
MariaDB [(none)]> SHOW BINLOG STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | radius1-bin.000001 | 1401770 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.001 sec) MariaDB [(none)]> SELECT @@global.gtid_binlog_pos; +--------------------------+ | @@global.gtid_binlog_pos | +--------------------------+ | 0-102-153 | +--------------------------+ 1 row in set (0.001 sec) MariaDB [(none)]>
slave-db0 で 'Con2' の CHANGE MASTER TO
をする
CONNECTION NAME
を忘れない
mysql --defaults-extra-file=mariadb_root.cnf CHANGE MASTER 'Con2' TO MASTER_HOST='192.168.100.103', MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='radius1-bin.000001', MASTER_LOG_POS=1401770, MASTER_CONNECT_RETRY=10, MASTER_USE_GTID=slave_pos;
MariaDB [(none)]> CHANGE MASTER 'Con2' TO -> MASTER_HOST='192.168.100.103', -> MASTER_USER='replication_user', -> MASTER_PASSWORD='bigs3cret', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='radius1-bin.000001', -> MASTER_LOG_POS=1401770, -> MASTER_CONNECT_RETRY=10, -> MASTER_USE_GTID=slave_pos; Query OK, 0 rows affected (0.109 sec) MariaDB [(none)]>
slave-db0 の 'Con2' を START REPLICA
する
START REPLICA 'Con2' ;
MariaDB [(none)]> START REPLICA 'Con2' ; Query OK, 0 rows affected (0.052 sec) MariaDB [(none)]>
no error
SHOW REPLICA 'Con2' STATUS \G SHOW ALL REPLICAS STATUS \G
MariaDB [(none)]> SHOW REPLICA 'Con2' STATUS \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.103 Master_User: replication_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: radius1-bin.000001 Read_Master_Log_Pos: 1401770 Relay_Log_File: slave-db0-relay-bin-con2.000002 Relay_Log_Pos: 1402071 Relay_Master_Log_File: radius1-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1401770 Relay_Log_Space: 1402389 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 102 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-102-153 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 142 Slave_Non_Transactional_Groups: 11 Slave_Transactional_Groups: 0 1 row in set (0.000 sec) MariaDB [(none)]>
Slave_IO_Running: Yes Slave_SQL_Running: Yes
OK
MariaDB [(none)]> SHOW ALL REPLICAS STATUS \G *************************** 1. row *************************** Connection_name: Con1 ... *************************** 2. row *************************** Connection_name: Con2
コネクション名も確認できた
動作確認
とりあえず、 radius[01]
での更新が slave-db0 に反映されるかを見る
現状確認
mysql --defaults-extra-file=mariadb_root.cnf radius SELECT * FROM radcheck;
radius0
MariaDB [radius]> SELECT * FROM radcheck; Empty set (0.001 sec) MariaDB [radius]>
radius1
MariaDB [radius]> SELECT * FROM radcheck; Empty set (0.001 sec) MariaDB [radius]>
slave-db0
MariaDB [radius]> SELECT * FROM radcheck; Empty set (0.001 sec) MariaDB [radius]>
radius0 で INSERT
SHOW VARIABLES LIKE 'hostname'; INSERT INTO radcheck (username, attribute, op, value) VALUES ('foo1', 'Cleartext-Password', ':=', 'foo1pass') ; SELECT * FROM radcheck;
MariaDB [radius]> SHOW VARIABLES LIKE 'hostname'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | hostname | radius0 | +---------------+---------+ 1 row in set (0.003 sec) MariaDB [radius]> INSERT INTO radcheck -> (username, attribute, op, value) -> VALUES -> ('foo1', 'Cleartext-Password', ':=', 'foo1pass') -> ; Query OK, 1 row affected (0.008 sec) MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | +----+----------+--------------------+----+----------+ 1 row in set (0.001 sec) MariaDB [radius]>
check in radius[01], slave-db0
radius0
MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | +----+----------+--------------------+----+----------+ 1 row in set (0.001 sec) MariaDB [radius]>
radius1
MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | +----+----------+--------------------+----+----------+ 1 row in set (0.001 sec) MariaDB [radius]>
slave-db0
MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | +----+----------+--------------------+----+----------+ 1 row in set (0.001 sec) MariaDB [radius]>
全てに反映されているのが分かる
radius1 で INSERT
SHOW VARIABLES LIKE 'hostname'; INSERT INTO radcheck (username, attribute, op, value) VALUES ('foo2', 'Cleartext-Password', ':=', 'foo2pass') ; SELECT * FROM radcheck;
MariaDB [radius]> SHOW VARIABLES LIKE 'hostname'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | hostname | radius1 | +---------------+---------+ 1 row in set (0.003 sec) MariaDB [radius]> INSERT INTO radcheck -> (username, attribute, op, value) -> VALUES -> ('foo2', 'Cleartext-Password', ':=', 'foo2pass') -> ; Query OK, 1 row affected (0.010 sec) MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | | 2 | foo2 | Cleartext-Password | := | foo2pass | +----+----------+--------------------+----+----------+ 2 rows in set (0.001 sec) MariaDB [radius]>
check in radius[01], slave-db0
radius0
MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | | 2 | foo2 | Cleartext-Password | := | foo2pass | +----+----------+--------------------+----+----------+ 2 rows in set (0.001 sec) MariaDB [radius]>
radius1
MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | | 2 | foo2 | Cleartext-Password | := | foo2pass | +----+----------+--------------------+----+----------+ 2 rows in set (0.001 sec) MariaDB [radius]>
slave-db0
MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | | 2 | foo2 | Cleartext-Password | := | foo2pass | +----+----------+--------------------+----+----------+ 2 rows in set (0.001 sec) MariaDB [radius]>
radius1 からの書き込みについても全体に反映されたことが分かる
relay log の場所とか
[ope@slave-db0 ~]$ ls -l /var/lib/mysql/ | grep relay -rw-rw----. 1 mysql mysql 63 8月 11 16:10 relay-log-con1.info -rw-rw----. 1 mysql mysql 67 8月 11 16:15 relay-log-con2.info -rw-rw----. 1 mysql mysql 318 8月 11 16:10 slave-db0-relay-bin-con1.000001 -rw-rw----. 1 mysql mysql 920 8月 11 16:40 slave-db0-relay-bin-con1.000002 -rw-rw----. 1 mysql mysql 68 8月 11 16:10 slave-db0-relay-bin-con1.index -rw-rw----. 1 mysql mysql 318 8月 11 16:15 slave-db0-relay-bin-con2.000001 -rw-rw----. 1 mysql mysql 1402360 8月 11 16:43 slave-db0-relay-bin-con2.000002 -rw-rw----. 1 mysql mysql 68 8月 11 16:15 slave-db0-relay-bin-con2.index [ope@slave-db0 ~]$
なかも、とりあえず見てみる
[ope@slave-db0 ~]$ sudo strings /var/lib/mysql/slave-db0-relay-bin-con1.000001 10.5.9-MariaDB-log slave-db0-relay-bin-con1.000002 [ope@slave-db0 ~]$ [ope@slave-db0 ~]$ sudo strings /var/lib/mysql/slave-db0-relay-bin-con1.000002 10.5.9-MariaDB-log radius0-bin.000001& 10.5.9-MariaDB-log radius0-bin.000001, radius INSERT INTO radcheck (username, attribute, op, value) VALUES ('foo1', 'Cleartext-Password', ':=', 'foo1pass')M [ope@slave-db0 ~]$
Fail over
どうなるんだ?
双方向同期レプリケーションなので、基本的に同期している
片方向レプリケーションなら Fail over でもう片方に Master を移動させる必要があるが、双方向なので、別コネクションで Master が生きている
例えば、radius0 が落ちても radius1 が Master で頑張ってくれる
でも
- radius0 down
- radius1 でクエリ受付
- radius1, slave-db0 が更新され続ける
- radius0 再起動
- radius0 Master として復活
というシナリオを用意したときに、radius0 のデータが radius1, slave-db0 よりも古いことになる
無難なのは、
- radius0 down
- radius1 でクエリ受付
- radius1, slave-db0 が更新され続ける
- radius1 を con0 の Master にする
- radius1 に READ LOCK かける
- radius1 のデータをバックアップする
- radius1 の READ LOCK を UNLOCK する
- radius1 のバックアップデータを radius0 にリストア
- radius0 を Slave として起動
- radius0 を Slave として同期
- radius0 を con1 の Master に Fail over
という流れかと思う
長いな
続きは後日