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

<h2>
背景
</h2>MySQLでレプリケーション構成を組んだことがなかったので検証メモです。
<h2>
目的
</h2>
　・シングルマスターレプリケーションの動作確認
　・マルチマスターレプリケーションの動作確認
　・MySQL Routerの動作確認
<h2>環境
</h2>Azure環境で検証
CentOS:7.7
MySQL:8.0.19

サーバ：
10.0.0.4
10.0.0.5
10.0.0.6

MySQLユーザ：
root
rpl_user(レプリケーション用ユーザ)
<h2>
手順</h2>

事前準備</div>
SELinuxを一時的に無効
<pre class="prettyprint linenums">[sqlroot@sql1 ~]$sudo setenforce 0
</pre>
MySQLをインストール
<pre class="prettyprint linenums">[sqlroot@sql1 ~]$sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
</pre>
MySQLのrootパスワード確認、変更
<pre class="prettyprint linenums">[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&gt; ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@ssword1';
Query OK, 0 rows affected (0.02 sec)

mysql&gt; quit
Bye
</pre>
/etc/my.cnf を編集(マスター用)

<pre class="prettyprint linenums">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"
</pre>
MySQLの再起動

<pre class="prettyprint linenums">[sqlroot@sql1 ~]$ sudo systemctl restart mysql
</pre>
グループレプリケーション設定

<pre class="prettyprint linenums">[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&gt; SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; CREATE USER rpl_user@'%' IDENTIFIED BY 'P@ssword2';
Query OK, 0 rows affected (0.02 sec)

mysql&gt; GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 sec)

mysql&gt; GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 sec)

mysql&gt; ALTER USER rpl_user@'%' IDENTIFIED WITH mysql_native_password BY 'P@ssword2';
Query OK, 0 rows affected (0.01 sec)

mysql&gt; FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; 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&gt; 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&gt; SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; RESET MASTER;
Query OK, 0 rows affected (0.11 sec)

mysql&gt;  START GROUP_REPLICATION;
Query OK, 0 rows affected (3.42 sec)

mysql&gt;  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)

</pre>
2台目、3台目の構築
1台目と同じ手順で構築して、/etc/my.cnf を編集(スレーブ用)

<pre class="prettyprint linenums">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"
</pre>
グループレプリケーション設定(2台目、3台目)

<pre class="prettyprint linenums">[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&gt; SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; CREATE USER rpl_user@'%' IDENTIFIED BY 'P@ssword2';
Query OK, 0 rows affected (0.01 sec)

mysql&gt; GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 sec)

mysql&gt; GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.08 sec)

mysql&gt; ALTER USER rpl_user@'%' IDENTIFIED WITH mysql_native_password BY 'P@ssword2';
Query OK, 0 rows affected (0.01 sec)

mysql&gt; FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; 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&gt; RESTRET MASTER;
Query OK, 0 rows affected (0.07 sec)

mysql&gt; START GROUP_REPLICATION;
Query OK, 0 rows affected (4.49 sec)

</pre>
レプリケーションの確認

<pre class="prettyprint linenums">mysql&gt; 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)

</pre>
レプリケーションが失敗するとき。、マスター、スレーブで以下のコマンドを実行

<pre class="prettyprint linenums">mysql&gt; STOP GROUP_REPLICATION;
mysql&gt; RESET MASTER;
mysql&gt; START GROUP_REPLICATION;
</pre>
レプリケーションの動作確認
PRIMARYで作成したデータがSECONDARYで同期しているか確認

<pre class="prettyprint linenums">mysql&gt; CREATE DATABASE test;
Query OK, 1 row affected (0.05 sec)

mysql&gt; USE test;
Database changed
mysql&gt; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.10 sec)

mysql&gt; INSERT INTO t1 VALUES (1, 'hoge');
Query OK, 1 row affected (0.05 sec)

mysql&gt; SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | hoge |
+----+------+
1 row in set (0.00 sec)

#以下SECONDARYで確認
mysql&gt;  SELECT * FROM test.t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | hoge |
+----+------+
1 row in set (0.00 sec)

</pre>
マルチマスターモードに変更して戻す

<pre class="prettyprint linenums">mysql&gt; 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&gt; 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&gt; 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&gt; 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)

</pre>
MySQL Routerのインストール

<pre class="prettyprint linenums">[sqlroot@sql2 ~]$ sudo yum install mysql-router
</pre>
MySQL Routerの設定変更(/etc/mysqlrouter.conf )

<pre class="prettyprint linenums">下記設定を追加
[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
</pre>
MySQL Routerの設定適用

<pre class="prettyprint linenums">[sqlroot@sql2 ~]$ sudo systemctl restart myslqlrouter
</pre>
リモートで接続出来るようMySQLに許可追加

<pre class="prettyprint linenums">mysql&gt; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.03 sec)

mysql&gt; flush privileges;
Query OK, 0 rows affected (0.04 sec)
</pre>
MySQL route経由で接続

<pre class="prettyprint linenums">[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&gt; @@hostname;
+------------+
| @@hostname |
+------------+
| sql1       |
+------------+
1 row in set (0.00 sec)

</pre>

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

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