takashi kono's blog

コーヒーとキーボードと共に何かを記録していくブログ

CentOS8 + MariaDB10.5 で 双方向レプリケーション+1 Slave やってみる

CentOSMariaDB で 双方向レプリケーション + 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

前回

takashikono.hatenadiary.jp

前回を参考に組み立てていく

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

によると、こちらも SLAVEREPLICA に変えても動くようだ
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 STATUSCONNECTION 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 でも使えるようだ
MASTERBINLOG にリネームされたが、互換性維持のため残すらしい

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  811 16:10 relay-log-con1.info
-rw-rw----. 1 mysql mysql        67  811 16:15 relay-log-con2.info
-rw-rw----. 1 mysql mysql       318  811 16:10 slave-db0-relay-bin-con1.000001
-rw-rw----. 1 mysql mysql       920  811 16:40 slave-db0-relay-bin-con1.000002
-rw-rw----. 1 mysql mysql        68  811 16:10 slave-db0-relay-bin-con1.index
-rw-rw----. 1 mysql mysql       318  811 16:15 slave-db0-relay-bin-con2.000001
-rw-rw----. 1 mysql mysql   1402360  811 16:43 slave-db0-relay-bin-con2.000002
-rw-rw----. 1 mysql mysql        68  811 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

という流れかと思う
長いな

続きは後日