背景
MySQLでレプリケーション構成を組んだことがなかったので検証メモです。目的
・シングルマスターレプリケーションの動作確認 ・マルチマスターレプリケーションの動作確認 ・MySQL Routerの動作確認環境
Azure環境で検証 CentOS:7.7 MySQL:8.0.19サーバ：
10.0.0.4
10.0.0.5
10.0.0.6
MySQLユーザ：
root
rpl_user(レプリケーション用ユーザ)
手順
事前準備
SELinuxを一時的に無効
[sqlroot@sql1 ~]$sudo setenforce 0
MySQLをインストール
[sqlroot@sql1 ~]$sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
MySQLのrootパスワード確認、変更
[sqlroot@sql1 ~]$sudo systemctl start mysqld
[sqlroot@sql1 ~]$sudo cat /var/log/myslqld.log | grep root
2020-04-21T02:06:36.551226Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: mqbJiz:tl73B
[sqlroot@sql1 ~]$mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@ssword1';
Query OK, 0 rows affected (0.02 sec)
mysql> quit
Bye
/etc/my.cnf を編集(マスター用)
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "10.0.0.4:33061"
group_replication_group_seeds= "10.0.0.4:33061,10.0.0.5:33061,10.0.0.6:33061"
group_replication_bootstrap_group=off
report-host = "10.0.0.4"
MySQLの再起動
[sqlroot@sql1 ~]$ sudo systemctl restart mysql
グループレプリケーション設定
[sqlroot@sql1 ~]$mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'P@ssword2';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER USER rpl_user@'%' IDENTIFIED WITH mysql_native_password BY 'P@ssword2';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='P@ssword2' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.13 sec)
mysql> SHOW PLUGINS;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
---------------------------省略---------------------------------------
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+---------------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.00 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> RESET MASTER;
Query OK, 0 rows affected (0.11 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.42 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | def5169c-8460-11ea-9ce3-000d3afd2ecd | 10.0.0.4 | 3306 | ONLINE | PRIMARY | 8.0.19 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)
2台目、3台目の構築
1台目と同じ手順で構築して、/etc/my.cnf を編集(スレーブ用)
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=任意の一意の数字
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "2台目 or 3台目のIP"
group_replication_group_seeds= "10.0.0.4:33061,10.0.0.5:33061,10.0.0.6:33061"
group_replication_bootstrap_group=off
report-host = "2台目 or 3台目のIP"
グループレプリケーション設定(2台目、3台目)
[sqlroot@sql3 ~]$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'P@ssword2';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.08 sec)
mysql> ALTER USER rpl_user@'%' IDENTIFIED WITH mysql_native_password BY 'P@ssword2';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='P@ssword2' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.13 sec)
mysql> RESTRET MASTER;
Query OK, 0 rows affected (0.07 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (4.49 sec)
レプリケーションの確認
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | bad8ec6b-8374-11ea-9eaa-000d3a6e2ea8 | 10.0.0.4 | 3306 | ONLINE | PRIMARY | 8.0.19 |
| group_replication_applier | bda782c4-8374-11ea-b070-000d3a6da918 | 10.0.0.6 | 3306 | ONLINE | SECONDARY | 8.0.19 |
| group_replication_applier | bdef8cb5-8374-11ea-b5d7-000d3a6e6af1 | 10.0.0.5 | 3306 | ONLINE | SECONDARY | 8.0.19 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
レプリケーションが失敗するとき。、マスター、スレーブで以下のコマンドを実行
mysql> STOP GROUP_REPLICATION;
mysql> RESET MASTER;
mysql> START GROUP_REPLICATION;
レプリケーションの動作確認
PRIMARYで作成したデータがSECONDARYで同期しているか確認
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.05 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO t1 VALUES (1, 'hoge');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | hoge |
+----+------+
1 row in set (0.00 sec)
#以下SECONDARYで確認
mysql> SELECT * FROM test.t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | hoge |
+----+------+
1 row in set (0.00 sec)
マルチマスターモードに変更して戻す
mysql> SELECT group_replication_switch_to_multi_primary_mode();
+--------------------------------------------------+
| group_replication_switch_to_multi_primary_mode() |
+--------------------------------------------------+
| Mode switched to multi-primary successfully. |
+--------------------------------------------------+
1 row in set (1.01 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | bad8ec6b-8374-11ea-9eaa-000d3a6e2ea8 | 10.0.0.4 | 3306 | ONLINE | PRIMARY | 8.0.19 |
| group_replication_applier | bda782c4-8374-11ea-b070-000d3a6da918 | 10.0.0.6 | 3306 | ONLINE | PRIMARY | 8.0.19 |
| group_replication_applier | bdef8cb5-8374-11ea-b5d7-000d3a6e6af1 | 10.0.0.5 | 3306 | ONLINE | PRIMARY | 8.0.19 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT group_replication_switch_to_single_primary_mode();
+---------------------------------------------------+
| group_replication_switch_to_single_primary_mode() |
+---------------------------------------------------+
| Mode switched to single-primary successfully. |
+---------------------------------------------------+
1 row in set (1.02 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | bad8ec6b-8374-11ea-9eaa-000d3a6e2ea8 | 10.0.0.4 | 3306 | ONLINE | PRIMARY | 8.0.19 |
| group_replication_applier | bda782c4-8374-11ea-b070-000d3a6da918 | 10.0.0.6 | 3306 | ONLINE | SECONDARY | 8.0.19 |
| group_replication_applier | bdef8cb5-8374-11ea-b5d7-000d3a6e6af1 | 10.0.0.5 | 3306 | ONLINE | SECONDARY | 8.0.19 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)
MySQL Routerのインストール
[sqlroot@sql2 ~]$ sudo yum install mysql-router
MySQL Routerの設定変更(/etc/mysqlrouter.conf )
下記設定を追加
[routing]
bind_address = 10.0.0.4
bind_port = 3307
destinations = 10.0.0.4:3306,10.0.0.5:3306,10.0.0.6:3306
routing_strategy = first-available
MySQL Routerの設定適用
[sqlroot@sql2 ~]$ sudo systemctl restart myslqlrouter
リモートで接続出来るようMySQLに許可追加
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.03 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
MySQL route経由で接続
[sqlroot@sql2 ~]$ mysql -u root -h 10.0.0.5 -P 3307 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> @@hostname;
+------------+
| @@hostname |
+------------+
| sql1 |
+------------+
1 row in set (0.00 sec)