MySQL Group Replication mgr 安装关键过程

1、初始化和启动

mysql/bin/mysqld --initialize-insecure --basedir=/mgr/mysql --datadir=/mgr/data/s1mysql/bin/mysqld --initialize-insecure --basedir=/mgr/mysql --datadir=/mgr/data/s2mysql/bin/mysqld --initialize-insecure --basedir=/mgr/mysql --datadir=/mgr/data/s3/mgr/mysql/bin/mysqld --defaults-file=/mgr/data/s1/s1.cnf/mgr/mysql/bin/mysqld --defaults-file=/mgr/data/s2/s2.cnf/mgr/mysql/bin/mysqld --defaults-file=/mgr/data/s3/s3.cnf

2、配置文件具体内

s1:[mysqld]# server configurationdatadir=/mgr/data/s1basedir=/mgr/mysql/user=mysqlport=24801socket=/mgr/data/s1/s1.sock#disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"#Replication Frameworkserver_id=1gtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONlog_bin=binlogbinlog_format=ROW#Group Replication Settingstransaction_write_set_extraction=XXHASH64loose-group_replication_group_name="5c7975ec-0000-11e9-a8c9-0800273906ff"loose-group_replication_start_on_boot=offloose-group_replication_local_address= "127.0.0.1:24901"loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"loose-group_replication_bootstrap_group=offs2:[mysqld]# server configurationdatadir=/mgr/data/s2basedir=/mgr/mysql/user=mysqlport=24802socket=/mgr/data/s2/s2.sock#disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"#Replication Frameworkserver_id=2gtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONlog_bin=binlogbinlog_format=ROW#Group Replication Settingstransaction_write_set_extraction=XXHASH64loose-group_replication_group_name="5c7975ec-0000-11e9-a8c9-0800273906ff"loose-group_replication_start_on_boot=offloose-group_replication_local_address= "127.0.0.1:24902"loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"loose-group_replication_bootstrap_group=offs3:[mysqld]# server configurationdatadir=/mgr/data/s3basedir=/mgr/mysql/user=mysqlport=24803socket=/mgr/data/s3/s3.sock#disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"#Replication Frameworkserver_id=3gtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONlog_bin=binlogbinlog_format=ROW#Group Replication Settingstransaction_write_set_extraction=XXHASH64loose-group_replication_group_name="5c7975ec-0000-11e9-a8c9-0800273906ff"loose-group_replication_start_on_boot=offloose-group_replication_local_address= "127.0.0.1:24903"loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"loose-group_replication_bootstrap_group=off

3、登录MySQL执行命令

s1必要过程mysql> SET SQL_LOG_BIN=0;mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';mysql> FLUSH PRIVILEGES;mysql> SET SQL_LOG_BIN=1;mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';mysql> SET GLOBAL group_replication_bootstrap_group=ON;mysql> START GROUP_REPLICATION;mysql> SET GLOBAL group_replication_bootstrap_group=OFF;s1非必要过程mysql> SELECT * FROM performance_schema.replication_group_members;mysql> SHOW PLUGINS; mysql> CREATE DATABASE test;mysql> USE test;mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);mysql> INSERT INTO t1 VALUES (1, 'Luis');mysql> SELECT * FROM t1;mysql> SHOW BINLOG EVENTS;mysql> exits2必要过程mysql> SET SQL_LOG_BIN=0;mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';mysql> FLUSH PRIVILEGES;mysql> SET SQL_LOG_BIN=1;mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';mysql> SELECT * FROM performance_schema.replication_group_members;mysql> START GROUP_REPLICATION;mysql> SELECT * FROM performance_schema.replication_group_members;mysql> show variables like '%read_only%';s3必要过程mysql> SET SQL_LOG_BIN=0;mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';mysql> FLUSH PRIVILEGES;mysql> SET SQL_LOG_BIN=1;mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';mysql> SELECT * FROM performance_schema.replication_group_members;mysql> START GROUP_REPLICATION;mysql> SELECT * FROM performance_schema.replication_group_members;mysql> show variables like '%read_only%';

4、遇到问题

问题1[ERROR] unknown variable 'group_replication_group_name=5c7975ec-0000-11e9-a8c9-0800273906ff'原参数group_replication_group_name="5c7975ec-0000-11e9-a8c9-0800273906ff"group_replication_start_on_boot=offgroup_replication_local_address= "127.0.0.1:24903"group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"group_replication_bootstrap_group=off修改后参数 loose-group_replication_group_name="5c7975ec-0000-11e9-a8c9-0800273906ff"loose-group_replication_start_on_boot=offloose-group_replication_local_address= "127.0.0.1:24903"loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"loose-group_replication_bootstrap_group=off问题2创建的复制用户和使用的复制用户不一致mysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 5c7975ec-a9cd-11e9-a8c9-0800273906ff | mgr1        |       24801 | ONLINE       || group_replication_applier | 69908c35-a9cd-11e9-8b78-0800273906ff | mgr1        |       24802 | RECOVERING   |+---------------------------+--------------------------------------+-------------+-------------+--------------+原：CHANGE MASTER TO MASTER_USER='repli', MASTER_PASSWORD='repli@%' FOR CHANNEL 'group_replication_recovery';修改后，要和创建的复制用户一致CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';问题3 未执行START GROUP_REPLICATION; 新加入节点显示offlinemysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+-----------+-------------+-------------+--------------+| CHANNEL_NAME              | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+-----------+-------------+-------------+--------------+| group_replication_applier |           |             |        NULL | OFFLINE      |+---------------------------+-----------+-------------+-------------+--------------+1 row in set (0.00 sec)执行后mysql> START GROUP_REPLICATION;Query OK, 0 rows affected (3.47 sec)mysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 5c7975ec-a9cd-11e9-a8c9-0800273906ff | mgr1        |       24801 | ONLINE       || group_replication_applier | 69908c35-a9cd-11e9-8b78-0800273906ff | mgr1        |       24802 | ONLINE       || group_replication_applier | 79ca1c48-a9cd-11e9-9526-0800273906ff | mgr1        |       24803 | ONLINE       |+---------------------------+--------------------------------------+-------------+-------------+--------------+3 rows in set (0.00 sec)