双方向レプリケーションテスト
CentOS8
MariaDB10.5
を使うよ
Ref
この記事をもとに、レプリケーションしていくよ
この記事も参考にさせていただくよ
前提
環境は 元記事 と同じ
チョットだけ書き出す
| key | value |
|---|---|
| IP | 192.168.100.102 |
| HOSTNAME | radius0 |
| server_id | 101 |
| auto_increment_increment | 2 |
| auto_increment_offset | 1 |
| key | value |
|---|---|
| IP | 192.168.100.103 |
| HOSTNAME | radius1 |
| server_id | 102 |
| auto_increment_increment | 2 |
| auto_increment_offset | 2 |
Firewall 設定 (radius[01])
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 のコメントアウトを外す
[ope@radius0 ~]$ sudo diff -uprN /etc/my.cnf.d/server.cnf.2021-07-04_22-23-26 /etc/my.cnf.d/server.cnf --- /etc/my.cnf.d/server.cnf.2021-07-04_22-23-26 2021-07-03 17:42:58.021731556 +0900 +++ /etc/my.cnf.d/server.cnf 2021-07-04 22:26:38.234451404 +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 の再起動
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;
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 のコメントアウトを外す
[ope@radius1 ~]$ sudo diff -uprN /etc/my.cnf.d/server.cnf.2021-07-04_22-29-11 /etc/my.cnf.d/server.cnf --- /etc/my.cnf.d/server.cnf.2021-07-04_22-29-11 2021-04-11 17:04:35.057898325 +0900 +++ /etc/my.cnf.d/server.cnf 2021-07-04 22:29:42.376541317 +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 ~]$
restart mariadb
sudo systemctl status mariadb | grep -E "Active|PID" sudo systemctl restart mariadb sudo systemctl status mariadb | 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;
radius0 の書き込みを止めてバックアップ
mysql --defaults-extra-file=mariadb_root.cnf FLUSH TABLES WITH READ LOCK;
log
[ope@radius0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf ... MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.110 sec) MariaDB [(none)]>
FLUSH TABLES をしたターミナルは起動しっぱなしにする！
別ターミナルを開いてバックアップをする
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
log(sample)
[ope@radius0 ~]$ sftp ope@192.168.100.103 Connected to ope@192.168.100.103. sftp> lls all_databases_on_master_2021-07-04_15-36-07.sql mariadb_radius.cnf mariadb_root.cnf sftp> put all_databases_on_master_2021-07-04_15-36-07.sql Uploading all_databases_on_master_2021-07-04_15-36-07.sql to /home/ope/all_databases_on_master_2021-07-04_15-36-07.sql all_databases_on_master_2021-07-04_15-36-07.sql 100% 1365KB 29.6MB/s 00:00 sftp> ls all_databases_on_master_2021-07-04_15-36-07.sql mariadb_radius.cnf mariadb_root.cnf sftp> exit [ope@radius0 ~]$
radius1 でデータをリストア
mysql --defaults-extra-file=mariadb_root.cnf < <file name>
log (sample)
[ope@radius1 ~]$ ls all_databases_on_master_2021-07-04_15-36-07.sql mariadb_root.cnf mariadb_radius.cnf [ope@radius1 ~]$ [ope@radius1 ~]$ mysql --defaults-extra-file=mariadb_root.cnf < all_databases_on_master_2021-07-04_15-36-07.sql [ope@radius1 ~]$
一応 FLUSH PRIVILEGES; しておく
mysql --defaults-extra-file=mariadb_root.cnf FLUSH PRIVILEGES;
radius0 で RESET MASTER する
mysql --defaults-extra-file=mariadb_root.cnf
RESET MASTER
radius0 のデータを確認する
SHOW MASTER STATUS; SELECT @@global.gtid_binlog_pos;
log
MariaDB [(none)]> SHOW MASTER 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.000 sec) MariaDB [(none)]>
radius0 で UNLOCK TABLES を実行する
UNLOCK TABLES;
radius1 で RESET SLAVE しておく
mysql --defaults-extra-file=mariadb_root.cnf
RESET SLAVE;
radius1 で CHANGE MASTER TO を実行する
これで、 master の設定をさせた上で自分が slave となる準備が整うはず
参考コマンド
mysql --defaults-extra-file=mariadb_root.cnf CHANGE MASTER 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;
log
MariaDB [(none)]> CHANGE MASTER 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.235 sec) MariaDB [(none)]>
radius1 で START SLAVE する
START SLAVE;
SHOW SLAVE STATUS\G
以下のようになっているか確認
Slave_IO_Running: Yes Slave_SQL_Running: Yes
log (sample)
MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.072 sec) MariaDB [(none)]> SHOW SLAVE 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.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: 942 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)]>
OK これで片方向レプリケーションはできた
radius1 で SHOW MASTER STATUS する
SHOW MASTER STATUS; SELECT @@global.gtid_binlog_pos;
log (sample)
MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | radius1-bin.000001 | 1401770 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT @@global.gtid_binlog_pos; +--------------------------+ | @@global.gtid_binlog_pos | +--------------------------+ | 0-102-153 | +--------------------------+ 1 row in set (0.001 sec) MariaDB [(none)]>
radius0 で CHANGE MASTER TO する
IP アドレスなどのパラメータを radius1 向けにするので今一度確認すること
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;
log (sample)
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.102 sec) MariaDB [(none)]>
radius0 で START SLAVE する
START SLAVE;
SHOW SLAVE STATUS\G
以下のようになっているか確認
Slave_IO_Running: Yes Slave_SQL_Running: Yes
log (sample)
MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.067 sec) MariaDB [(none)]> SHOW SLAVE 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.000002 Relay_Log_Pos: 6255 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: 5954 Relay_Log_Space: 1402382 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: 1017 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: closing tables Slave_DDL_Groups: 14 Slave_Non_Transactional_Groups: 1 Slave_Transactional_Groups: 0 1 row in set (0.001 sec) MariaDB [(none)]>
OK
動作確認
現状確認
radius0, radius1
mysql --defaults-extra-file=mariadb_root.cnf radius SELECT * FROM radcheck;
radius0 log
[ope@radius0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf radius ... MariaDB [radius]> MariaDB [radius]> SELECT * FROM radcheck; Empty set (0.001 sec) MariaDB [radius]>
radius1 log
[ope@radius1 ~]$ mysql --defaults-extra-file=mariadb_root.cnf radius ... MariaDB [radius]> MariaDB [radius]> SELECT * FROM radcheck; Empty set (0.001 sec) MariaDB [radius]>
radius0 で INSERT
mysql --defaults-extra-file=mariadb_root.cnf radius -e "\ INSERT INTO radcheck (username, attribute, op, value) VALUES ('foo1', 'Cleartext-Password', ':=', 'foo1pass') ; "
log
[ope@radius0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf radius -e "\ > INSERT INTO radcheck > (username, attribute, op, value) > VALUES > ('foo1', 'Cleartext-Password', ':=', 'foo1pass') > ; > " [ope@radius0 ~]$
radius0, radius1 で状態確認 1
mysql --defaults-extra-file=mariadb_root.cnf radius -e "\ SELECT * FROM radcheck;"
radius0 log
[ope@radius0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf radius -e "\ > SELECT * FROM radcheck;" +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | +----+----------+--------------------+----+----------+ [ope@radius0 ~]$
radius1 log
[ope@radius1 ~]$ mysql --defaults-extra-file=mariadb_root.cnf radius -e "\ > SELECT * FROM radcheck;" +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | +----+----------+--------------------+----+----------+ [ope@radius1 ~]$
反映されている
radius1 で INSERT
mysql --defaults-extra-file=mariadb_root.cnf radius -e "\ INSERT INTO radcheck (username, attribute, op, value) VALUES ('foo2', 'Cleartext-Password', ':=', 'foo2pass') ; "
log
[ope@radius1 ~]$ mysql --defaults-extra-file=mariadb_root.cnf radius -e "\ > INSERT INTO radcheck > (username, attribute, op, value) > VALUES > ('foo2', 'Cleartext-Password', ':=', 'foo2pass') > ; > " [ope@radius1 ~]$
radius0, radius1 で状態確認 2
mysql --defaults-extra-file=mariadb_root.cnf radius -e "\ SELECT * FROM radcheck;"
radius1 log
[ope@radius1 ~]$ mysql --defaults-extra-file=mariadb_root.cnf radius -e "\ > SELECT * FROM radcheck;" +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | | 2 | foo2 | Cleartext-Password | := | foo2pass | +----+----------+--------------------+----+----------+ [ope@radius1 ~]$
radius0 log
[ope@radius0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf radius -e "\ > SELECT * FROM radcheck;" +----+----------+--------------------+----+----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------+ | 1 | foo1 | Cleartext-Password | := | foo1pass | | 2 | foo2 | Cleartext-Password | := | foo2pass | +----+----------+--------------------+----+----------+ [ope@radius0 ~]$
ちゃんと反映されている 双方向でレプリケーションされていることを確認できた
