MySQL Group Replication mgr 安装关键过程

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

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

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%';

问题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)

相关阅读