ちきんめも

背景

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)

感想

グループレプリケーションの切り替えはOSシャットダウン等であればすぐ切り替わった。
PRIMARYのMySQLをシャットダウンしてMySQL Routerの接続はすぐ切り替わった。

参考

https://qiita.com/rutko/items/56a33d1ecd70c0480202
http://kaerugaeru.xyz/index.php/2018/11/17/mysql-8-0-groupreplication/
https://qiita.com/ucan-lab/items/3ae911b7e13287a5b917
https://dev.mysql.com/doc/refman/8.0/en/group-replication.html
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0118
https://dev.mysql.com/doc/mysql-router/8.0/en/

ちきんめも

code-prettify

2020年5月23日土曜日

MySQL 8.0でのレプリケーション

背景

目的

環境

手順

感想

参考