1、初始化和启动

mysql/bin/mysqld --initialize-insecure --basedir=/mgr/mysql --datadir=/mgr/data/s1
mysql/bin/mysqld --initialize-insecure --basedir=/mgr/mysql --datadir=/mgr/data/s2
mysql/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 configuration

datadir=/mgr/data/s1 basedir=/mgr/mysql/ user=mysql port=24801 socket=/mgr/data/s1/s1.sock

#disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#Replication Framework server_id=1 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog_format=ROW

#Group Replication Settings

transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="5c7975ec-0000-11e9-a8c9-0800273906ff" loose-group_replication_start_on_boot=off loose-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=off

s2:

[mysqld]

server configuration

datadir=/mgr/data/s2 basedir=/mgr/mysql/ user=mysql port=24802 socket=/mgr/data/s2/s2.sock

#disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#Replication Framework server_id=2 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog_format=ROW

#Group Replication Settings

transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="5c7975ec-0000-11e9-a8c9-0800273906ff" loose-group_replication_start_on_boot=off loose-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=off

s3: [mysqld]

server configuration

datadir=/mgr/data/s3 basedir=/mgr/mysql/ user=mysql port=24803 socket=/mgr/data/s3/s3.sock

#disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#Replication Framework server_id=3 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog_format=ROW

#Group Replication Settings

transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="5c7975ec-0000-11e9-a8c9-0800273906ff" loose-group_replication_start_on_boot=off loose-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> exit

s2必要过程

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=off
group_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=off
loose-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; 新加入节点显示offline mysql> 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)