code-prettify

2020年5月23日土曜日

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


背景

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

感想

グループレプリケーションの切り替えは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/
投稿者 時刻: 0 件のコメント:
ラベル: ,
登録: 投稿 (Atom)