可以参考:https: //www .cnblogs.com /kevingrace/p/10260685 .html 由于之前做了其他测试,这里需要将三个节点的mysql环境抹干净: # systemctl stop mysqld # rm -rf /var/lib/mysql # systemctl start mysqld 然后重启密码 # cat /var/log/mysqld.log|grep 'A temporary password' # mysql -p123456 mysql> set global validate_password_policy=0; mysql> set global validate_password_length=1; mysql> set password=password( "123456" ); mysql> flush privileges; ======================================================= 1) MGR-node1节点操作 [root@MGR-node1 ~] # mysql -p123456 ......... mysql> select uuid(); +--------------------------------------+ | uuid() | +--------------------------------------+ | ae09faae-34bb-11e9-9f91-005056ac6820 | +--------------------------------------+ 1 row in set (0.00 sec) [root@MGR-node1 ~] # cp /etc/my.cnf /etc/my.cnf.bak [root@MGR-node1 ~] # >/etc/my.cnf [root@MGR-node1 ~] # vim /etc/my.cnf [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql .sock symbolic-links = 0 log-error = /var/log/mysqld .log pid- file = /var/run/mysqld/mysqld .pid #GTID: server_id = 1 gtid_mode = on enforce_gtid_consistency = on master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE #binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync -master-info = 1 sync_binlog = 1 #relay log skip_slave_start = 1 transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name= "5db40c3c-180c-11e9-afbf-005056ac6820" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "172.16.60.211:24901" loose-group_replication_group_seeds= "172.16.60.211:24901,172.16.60.212:24901,172.16.60.213:24901" loose-group_replication_bootstrap_group=off loose-group_replication_single_primary_mode=off loose-group_replication_enforce_update_everywhere_checks=on loose-group_replication_ip_whitelist= "172.16.60.0/24,127.0.0.1/8" 重启mysql服务 [root@MGR-node1 ~] # systemctl restart mysqld 登录mysql进行相关设置操作 [root@MGR-node1 ~] # mysql -p123456 ............ mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_slave@ '%' IDENTIFIED BY 'slave@123' ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql> reset master; Query OK, 0 rows affected (0.19 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_USER= 'rpl_slave' , MASTER_PASSWORD= 'slave@123' FOR CHANNEL 'group_replication_recovery' ; Query OK, 0 rows affected, 2 warnings (0.33 sec) mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so' ; Query OK, 0 rows affected (0.03 sec) mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ ............... ............... | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec) mysql> SET GLOBAL group_replication_bootstrap_group=ON; Query OK, 0 rows affected (0.00 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (2.34 sec) mysql> SET GLOBAL group_replication_bootstrap_group=OFF; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 42ca8591-34bb-11e9-8296-005056ac6820 | MGR-node1 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 1 row in set (0.00 sec) 比如要保证上面的group_replication_applier的状态为 "ONLINE" 才对! 创建一个测试库 mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.03 sec) mysql> use kevin; Database changed mysql> create table if not exists haha ( id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL); Query OK, 0 rows affected (0.24 sec) mysql> insert into kevin.haha values(1, "wangshibo" ),(2, "guohuihui" ),(3, "yangyang" ),(4, "shikui" ); Query OK, 4 rows affected (0.07 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | wangshibo | | 2 | guohuihui | | 3 | yangyang | | 4 | shikui | +----+-----------+ 4 rows in set (0.00 sec) ===================================================================== 2) MGR-node2节点操作 [root@MGR-node2 ~] # cp /etc/my.cnf /etc/my.cnf.bak [root@MGR-node2 ~] # >/etc/my.cnf [root@MGR-node2 ~] # vim /etc/my.cnf [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql .sock symbolic-links = 0 log-error = /var/log/mysqld .log pid- file = /var/run/mysqld/mysqld .pid #GTID: server_id = 2 gtid_mode = on enforce_gtid_consistency = on master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE #binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync -master-info = 1 sync_binlog = 1 #relay log skip_slave_start = 1 transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name= "5db40c3c-180c-11e9-afbf-005056ac6820" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "172.16.60.212:24901" loose-group_replication_group_seeds= "172.16.60.211:24901,172.16.60.212:24901,172.16.60.213:24901" loose-group_replication_bootstrap_group=off loose-group_replication_single_primary_mode=off loose-group_replication_enforce_update_everywhere_checks=on loose-group_replication_ip_whitelist= "172.16.60.0/24,127.0.0.1/8" 重启mysql服务 [root@MGR-node2 ~] # systemctl restart mysqld 登录mysql进行相关设置操作 [root@MGR-node2 ~] # mysql -p123456 ......... mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_slave@ '%' IDENTIFIED BY 'slave@123' ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> reset master; Query OK, 0 rows affected (0.17 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_USER= 'rpl_slave' , MASTER_PASSWORD= 'slave@123' FOR CHANNEL 'group_replication_recovery' ; Query OK, 0 rows affected, 2 warnings (0.21 sec) mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so' ; Query OK, 0 rows affected (0.20 sec) mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ ............. ............. | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (6.25 sec) mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 4281f7b7-34bb-11e9-8949-00505688047c | MGR-node2 | 3306 | ONLINE | | group_replication_applier | 42ca8591-34bb-11e9-8296-005056ac6820 | MGR-node1 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 2 rows in set (0.00 sec) 查看下,发现已经将MGR-node1节点添加的数据同步过来了 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | kevin | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | wangshibo | | 2 | guohuihui | | 3 | yangyang | | 4 | shikui | +----+-----------+ 4 rows in set (0.00 sec) ===================================================================== 3) MGR-node3节点操作 [root@MGR-node3 ~] # cp /etc/my.cnf /etc/my.cnf.bak [root@MGR-node3 ~] # >/etc/my.cnf [root@MGR-node3 ~] # vim /etc/my.cnf [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql .sock symbolic-links = 0 log-error = /var/log/mysqld .log pid- file = /var/run/mysqld/mysqld .pid #GTID: server_id = 3 gtid_mode = on enforce_gtid_consistency = on master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE #binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync -master-info = 1 sync_binlog = 1 #relay log skip_slave_start = 1 transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name= "5db40c3c-180c-11e9-afbf-005056ac6820" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "172.16.60.213:24901" loose-group_replication_group_seeds= "172.16.60.211:24901,172.16.60.212:24901,172.16.60.213:24901" loose-group_replication_bootstrap_group=off loose-group_replication_single_primary_mode=off loose-group_replication_enforce_update_everywhere_checks=on loose-group_replication_ip_whitelist= "172.16.60.0/24,127.0.0.1/8" 重启mysql服务 [root@MGR-node3 ~] # systemctl restart mysqld 登录mysql进行相关设置操作 [root@MGR-node3 ~] # mysql -p123456 .......... mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_slave@ '%' IDENTIFIED BY 'slave@123' ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql> reset master; Query OK, 0 rows affected (0.10 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_USER= 'rpl_slave' , MASTER_PASSWORD= 'slave@123' FOR CHANNEL 'group_replication_recovery' ; Query OK, 0 rows affected, 2 warnings (0.27 sec) mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so' ; Query OK, 0 rows affected (0.04 sec) mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ ............. | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (4.54 sec) mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 4281f7b7-34bb-11e9-8949-00505688047c | MGR-node2 | 3306 | ONLINE | | group_replication_applier | 42ca8591-34bb-11e9-8296-005056ac6820 | MGR-node1 | 3306 | ONLINE | | group_replication_applier | 456216bd-34bb-11e9-bbd1-005056880888 | MGR-node3 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec) 查看下,发现已经将在其他节点上添加的数据同步过来了 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | kevin | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | wangshibo | | 2 | guohuihui | | 3 | yangyang | | 4 | shikui | +----+-----------+ 4 rows in set (0.00 sec) ===================================================================== 4) 组复制数据同步测试 在任意一个节点上执行 mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 2658b203-1565-11e9-9f8b-005056880888 | MGR-node3 | 3306 | ONLINE | | group_replication_applier | 2c1efc46-1565-11e9-ab8e-00505688047c | MGR-node2 | 3306 | ONLINE | | group_replication_applier | 317e2aad-1565-11e9-9c2e-005056ac6820 | MGR-node1 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec) 如上,说明已经在MGR-node1、MGR-node2、MGR-node3 三个节点上成功部署了基于GTID的组复制同步环境。 现在在三个节点中的任意一个上面更新数据,那么其他两个节点的数据库都会将新数据同步过去的! 1)在MGR-node1节点数据库更新数据 mysql> delete from kevin.haha where id >2; Query OK, 2 rows affected (0.14 sec) 接着在MGR-node2、MGR-node3节点数据库查看,发现更新后数据已经同步过来了! mysql> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | wangshibo | | 2 | guohuihui | +----+-----------+ 2 rows in set (0.00 sec) 2)在MGR-node2节点数据库更新数据 mysql> insert into kevin.haha values(11, "beijing" ),(12, "shanghai" ),(13, "anhui" ); Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0 接着在MGR-node1、MGR-node3节点数据库查看,发现更新后数据已经同步过来了! mysql> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | wangshibo | | 2 | guohuihui | | 11 | beijing | | 12 | shanghai | | 13 | anhui | +----+-----------+ 5 rows in set (0.00 sec) 3)在MGR-node3节点数据库更新数据 mysql> update kevin.haha set id =100 where name= "anhui" ; Query OK, 1 row affected (0.16 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> delete from kevin.haha where id =12; Query OK, 1 row affected (0.22 sec) 接着在MGR-node1、MGR-node2节点数据库查看,发现更新后数据已经同步过来了! mysql> select * from kevin.haha; +-----+-----------+ | id | name | +-----+-----------+ | 1 | wangshibo | | 2 | guohuihui | | 11 | beijing | | 100 | anhui | +-----+-----------+ 4 rows in set (0.00 sec) |