MGR组复制实战
MGR原理介绍
基于传统异步复制和半同步复制的缺陷——数据的一致性问题无法保证 MySQL Group Replication（简称MGR）是MySQL官方于2016年12月12日推出的一款高可用与高扩展的解决方案，它提供了高可用、高扩展、高可靠的MySQL集群服务 MySQL官方在5.7.17版本正式推出组复制（MySQL Group Replication，简称MGR）。由若干个节点共同组成一个复制组，一个事务的提交，必须经过组内大多数节点（N / 2 + 1）决议并通 过，才能得以提交。由3个节点组成一个复制组，在事务提交过程中，发生组间通讯，由2个节点决议(certify)通过这个事务，事务才能够最终得以提交并响应。引入组复制， 主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题。组复制依靠分布式一致性协议(Paxos协议的变体)，实现了分布式下数据的最终一致性， 提供了真正的数据高可用方案(是否真正高可用还有待商榷)。其提供的多写方案，给我们实现多活方案带来了希望。组复制脱离了传统的主从模式结构，是一个具有容错功能的集群架构， 在组复制的架构中，有多个server成员构成，并且每个成员都可以独立执行事务，也就意味着多写的功能，但是所有的读写事务必须在冲突校验完成后才能提交， 如果是只读型的事务那么会直接提交。当某个节点上发出一个读写的事务准备提交时，那么这个节点就会向整个集群开始广播这次读写的变更和对应的一个校验标识符， 然后会针对这个事务产生一个全局的顺序号，由于是有顺序号的，所以集群中的每个成员都会按照顺序去执行事务的变更从而保证了数据的一致性。 如果在不同的 server 上执行了相同的操作，并且产生了事务冲突，那么校验机制就会做成相应的判断，通常先提交的事务先执行，后提交的回滚。所以从某种程度上来说， 组复制是一种伪同步复制模式。
一、环境准备
192.168.247.131 mgr-node-01 2vcpu 2G 100G rocky8.6 mysql8.0.26 192.168.247.132 mgr-node-02 2vcpu 2G 100G rocky8.6 mysql8.0.26 192.168.247.133 mgr-node-03 2vcpu 2G 100G rocky8.6 mysql8.0.26
配置主机名 hostnamectl set-hostname mgr-node-01 hostnamectl set-hostname mgr-node-02 hostnamectl set-hostname mgr-node-03
新增hosts cat >> /etc/hosts <<EOF 192.168.247.131 mgr-node-01 192.168.247.132 mgr-node-02 192.168.247.133 mgr-node-03 EOF
二、安装mysql服务并配置my.cnf文件
yum install -y mysql-server
# rpm -qa |grep mysql mysql-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64 mysql-common-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64 mysql-errmsg-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64 mysql-server-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64
/etc/my.cnf 配置说明
#服务器编号 server_id=1 #开启binlog的GTID模式，默认为OFF gtid_mode=ON #开启后MysQL只允许能够保障事务安全，并且能够被日志记录的sQL语句被执行，默认为OFF enforce_gtid_consistency=ON #加载组复制模块 plugin_load_add = 'group_replication.so' #复制组的名称，必须是有效格式的UUID，可以使用命令uuidgen生成，所有节点必须相同。默认为空 loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee" #确定服务器是否应该在服务器启动期间启动组复制。默认为ON loose-group_replication_start_on_boot=OFF #为复制组中其他的成员提供的网络地址，指定为“主机:端口”的格式化字符串。很多人想当然认为端口应该是3306，起始不然，MGR需要开启新端口24901同步交换 loose-group_replication_local_address="10.0.0.101:24901" #用于建立新成员到组的连接组成员列表。这个列表指定为由分隔号间隔的组成员网络地址列表，类似host1:port1、host2:port2的格式。同样采用ne~n2的主机名替代 loose-group_replication_group_seeds="10.0.0.101:24901,10.0.0.102:24901,10.0.0.103:24901" #配置此服务器为引导组，这个选项必须仅在一台服务器上设置，并且仅当第一次启动组或者重新启动整个组时。成功引导组启动后，将此选项设置为关闭。 loose-group_replication_bootstrap_group=off #使用mysql_native_password密码策略 default_authentication_plugin=mysql_native_password #关闭binlog校验，默认值为CRC32，此项可选 binlog_checksum=NONE #定义用于事务期间哈希写入提取的算法，组复制模式下必须设置为XXHASH64。此为默认值，可不设置 transaction_write_set_extraction=XXHASH64 #确定组复制恢复时是否应该应用SSL，通常设置为“开”，但默认设置为“关”。此项可选 loose-group_replication_recovery_use_ssl=ON
mgr-node-01配置
[root@mgr-node-01 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] default_authentication_plugin=mysql_native_password binlog_checksum=NONE server-id=131 gtid_mode=ON enforce_gtid_consistency=ON loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee" loose-group_replication_start_on_boot=OFF loose-group_replication_local_address="192.168.247.131:24901" loose-group_replication_group_seeds="192.168.247.131:24901,192.168.247.132:24901,192.168.247.133:24901" loose-group_replication_bootstrap_group=OFF loose-group_replication_recovery_use_ssl=ON [root@mgr-node-01 ~]#
mgr-node-02配置
[root@mgr-node-02 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] default_authentication_plugin=mysql_native_password binlog_checksum=NONE server-id=132 gtid_mode=ON enforce_gtid_consistency=ON loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee" loose-group_replication_start_on_boot=OFF loose-group_replication_local_address="192.168.247.132:24901" loose-group_replication_group_seeds="192.168.247.131:24901,192.168.247.132:24901,192.168.247.133:24901" loose-group_replication_bootstrap_group=OFF loose-group_replication_recovery_use_ssl=ON [root@mgr-node-02 ~]#
mgr-node-03配置
[root@mgr-node-03 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] default_authentication_plugin=mysql_native_password binlog_checksum=NONE server-id=133 gtid_mode=ON enforce_gtid_consistency=ON loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee" loose-group_replication_start_on_boot=OFF loose-group_replication_local_address="192.168.247.133:24901" loose-group_replication_group_seeds="192.168.247.131:24901,192.168.247.132:24901,192.168.247.133:24901" loose-group_replication_bootstrap_group=OFF loose-group_replication_recovery_use_ssl=ON [root@mgr-node-03 ~]#
三、配置 MGR集群服务
3.1、在所有主机节点创建复制用户
SET SQL_LOG_BIN=0;
CREATE USER repluser@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO repluser@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
3.2、在所有机器安装插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'group_replication'\G
show plugins;
[root@mgr-node-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repluser@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@'%'; Query OK, 0 rows affected (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.04 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'group_replication'\G *************************** 1. row *************************** PLUGIN_NAME: group_replication PLUGIN_VERSION: 1.1 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: GROUP REPLICATION PLUGIN_TYPE_VERSION: 1.4 PLUGIN_LIBRARY: group_replication.so PLUGIN_LIBRARY_VERSION: 1.10 PLUGIN_AUTHOR: Oracle Corporation PLUGIN_DESCRIPTION: Group Replication (1.1.0) PLUGIN_LICENSE: GPL LOAD_OPTION: ON 1 row in set (0.00 sec) mysql> show plugins; +---------------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +---------------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | mysqlx | ACTIVE | DAEMON | NULL | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +---------------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec) mysql>
[root@mgr-node-02 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repluser@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.02 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@'%'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.04 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'group_replication'\G *************************** 1. row *************************** PLUGIN_NAME: group_replication PLUGIN_VERSION: 1.1 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: GROUP REPLICATION PLUGIN_TYPE_VERSION: 1.4 PLUGIN_LIBRARY: group_replication.so PLUGIN_LIBRARY_VERSION: 1.10 PLUGIN_AUTHOR: Oracle Corporation PLUGIN_DESCRIPTION: Group Replication (1.1.0) PLUGIN_LICENSE: GPL LOAD_OPTION: ON 1 row in set (0.00 sec) mysql> show plugins; +---------------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +---------------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | mysqlx | ACTIVE | DAEMON | NULL | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +---------------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec) mysql>
[root@mgr-node-03 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repluser@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.02 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@'%'; Query OK, 0 rows affected (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.04 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'group_replication'\G *************************** 1. row *************************** PLUGIN_NAME: group_replication PLUGIN_VERSION: 1.1 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: GROUP REPLICATION PLUGIN_TYPE_VERSION: 1.4 PLUGIN_LIBRARY: group_replication.so PLUGIN_LIBRARY_VERSION: 1.10 PLUGIN_AUTHOR: Oracle Corporation PLUGIN_DESCRIPTION: Group Replication (1.1.0) PLUGIN_LICENSE: GPL LOAD_OPTION: ON 1 row in set (0.00 sec) mysql> show plugins; +---------------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +---------------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | mysqlx | ACTIVE | DAEMON | NULL | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +---------------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec) mysql>
3.3、启用第一个节点Primary
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF; #验证结果
3.4、启用剩余的所有节点Secondary
#设置复制用户信息
CHANGE MASTER TO MASTER_USER='repluser', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
#新版命令代替上面命令
CHANGE REPLICATION SOURCE TO SOURCE_USER='repluser', SOURCE_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
#启动复制
START GROUP_REPLICATION;
验证结果
验证结果
四、测试复制
4.1、在primary节点，创建一个新的数据库，看其他的2个台是否会同步此创建的数据库
4.2、验证当primary节点宕机了，是否会选举出新的primary
当旧主节点恢复，查看是否会自动加入
此时旧的primary变成Secondary
此时机器加入集群，但节点变RECOVERING，大概30s后状态自动变ERROR
4.3、如果要正常加入集群使用需要将进行如下操作
停掉mysql服务，然后删除/var/lib/mysql下相关数据目录
在把该节点当一个新的从节点加入集群
[root@mgr-node-01 ~]# systemctl stop mysqld [root@mgr-node-01 ~]# rm -fr /var/lib/mysql/* [root@mgr-node-01 ~]# systemctl start mysqld [root@mgr-node-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repluser@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@'%'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'group_replication'\G *************************** 1. row *************************** PLUGIN_NAME: group_replication PLUGIN_VERSION: 1.1 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: GROUP REPLICATION PLUGIN_TYPE_VERSION: 1.4 PLUGIN_LIBRARY: group_replication.so PLUGIN_LIBRARY_VERSION: 1.10 PLUGIN_AUTHOR: Oracle Corporation PLUGIN_DESCRIPTION: Group Replication (1.1.0) PLUGIN_LICENSE: GPL LOAD_OPTION: ON 1 row in set (0.00 sec) mysql> show plugins; +---------------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +---------------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | mysqlx | ACTIVE | DAEMON | NULL | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +---------------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec) mysql> mysql> mysql> mysql> CHANGE MASTER TO MASTER_USER='repluser', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 5 warnings (0.01 sec) mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='repluser', SOURCE_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (3.13 sec) mysql> mysql>
五、单主、多主切换设置
5.1、单主切换多主
#切换命令
select group_replication_switch_to_multi_primary_mode();
#首先观察当前集群的模式和各个节点的读写模式
select * from performance_schema.replication_group_members;
show variables like '%read_only%';
方法二：
#所有节点 STOP GROUP_REPLICATION; #是否启用单主模式，默认ON，OFF代表多主 SET global group_replication_single_primary_mode=off; #是否开启条件检查，因为多主的约束更为严格，不符合要求的直接拒绝,不支持外键的级联操作,不支持隔离级别Serializable set global group_replication_enforce_update_everywhere_checks=ON; #主节点 SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF; SELECT * FROM performance_schema.replication_group_members; #所有从节点 START GROUP_REPLICATION;
5.2、多主切换为单主
select group_replication_switch_to_single_primary_mode();
方法二：
#所有节点 STOP GROUP_REPLICATION; set global group_replication_enforce_update_everywhere_checks=OFF; SET global group_replication_single_primary_mode=ON; #主节点 SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF; SELECT * FROM performance_schema.replication_group_members; #所有从节点 START GROUP_REPLICATION;
5.3、指定单主切换到新的主
select group_replication_set_as_primary("server_uuid");
其他节点查看验证结果
