文章目录
MGR单主(Single-Primary)模式部署
1、创建复制账号
在每一个MySQL节点创建复制使用账号
set sql_log_bin=0; #设为0后,在Master数据库上执行的语句都不记录binlog
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.136.*' IDENTIFIED BY 'repl';
CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
set sql_log_bin=1;
2、修改配置参数
修改每一个MySQL节点的配置文件 my.cnf ,增加如下参数,并重启数据库实例
[mysqld]
master-info-repository=TABLE #复制元数据存储在系统表而不是文件
relay-log-info-repository=TABLE #中继日志信息写入到表而不是文件
gtid_mode=on #开启全局事务ID
enforce_gtid_consistency=on #开启全局事务ID强一致性
slave_preserve_commit_order=on #控制从库并行reply时事务提交的顺序
binlog_checksum=NONE #禁用二进制日志时间校验和
transaction_write_set_extraction=XXHASH64 #以便在server收集写集合的同时将其记录到二进制日志。写集合基于每行的主键,并且是行更改后的唯一标识此标识将用于检测冲突。
3、主节点配置
选择一个 MySQL 节点作为主节点,安装组复制插件并配置组复制参数,同时将参数写入 my.cnf 文件。
#开放 23306 端口
firewall-cmd --zone=public --add-port=23306/tcp --permanent
firewall-cmd --reload
#安装组复制插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
#使用SELECT UUID()生成一个UUID。
mysql> select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 3a90016b-afe0-11ea-ab97-00505636b68c |
+--------------------------------------+
1 row in set (0.00 sec)
set global group_replication_group_name='3a90016b-afe0-11ea-ab97-00505636b68c'; #组的名字可以随便起,必须是有效的UUID,但不能用主机的GTID! 所有节点的这个组名必须保持一致!。在二进制日志中为组复制事件设置GTID时,将在内部使用此UUID。使用SELECT UUID()生成一个UUID。
set global group_replication_local_address='192.168.136.136:23306'; #不同节点配置不同节点本身的IP地址和端口,区分MYSQL自身的3306端口
set global group_replication_group_seeds='192.168.136.136:23306,192.168.136.135:23306,192.168.136.137:23306'; #表示当集群有新成员加入时,可以从哪些节点(IP:Port)获取需要的数据进行 recovery
set global group_replication_start_on_boot=off; #表示组复制是否随实例启动而启动
set global group_replication_bootstrap_group=on; #只用于集群初始化的时候,主节点必须执行,其他节点不需要执行
set global group_replication_ip_whitelist='192.168.136.0/24'; #IP白名单,确定可以接受哪个主机的组通信系统连接
注意:写入 my.cnf 文件时,group_replication 开头的参数应该增加 loose- 开头,例如 loose-group_replication_group_name
loose-group_replication_group_name='3a90016b-afe0-11ea-ab97-00505636b68c'
loose-group_replication_local_address='192.168.136.136:23306'
loose-group_replication_group_seeds='192.168.136.136:23306,192.168.136.135:23306,192.168.136.137:23306'
loose-group_replication_start_on_boot=off
#loose-group_replication_bootstrap_group=on
loose-group_replication_ip_whitelist='192.168.136.0/24'
4、在主节点启动组复制并查看日志。
start group_replication;
如果显示:Plugin group_replication reported: ‘This server is working as primary member.’ 则成功启动
2020-06-16T14:49:09.898607Z 17 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"'
2020-06-16T14:49:09.899050Z 17 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.'
2020-06-16T14:49:09.899464Z 17 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'
2020-06-16T14:49:09.899496Z 17 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "3a90016b-afe0-11ea-ab97-00505636b68c"; group_replication_local_address: "192.168.136.136:23306"; group_replication_group_seeds: "192.168.136.136:23306,192.168.136.135:23306,192.168.136.137:23306"; group_replication_bootstrap_group: true; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "192.168.136.0/24"'
2020-06-16T14:49:09.899566Z 17 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0'
2020-06-16T14:49:09.899580Z 17 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds'
2020-06-16T14:49:09.899632Z 17 [Note] Plugin group_replication reported: 'Member configuration: member_id: 1363306; member_uuid: "839d3796-a8a1-11ea-b1db-00505636b68c"; single-primary mode: "true"; group_replication_auto_increment_increment: 7; '
2020-06-16T14:49:09.899961Z 20 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 373, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2020-06-16T14:49:09.914952Z 17 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2020-06-16T14:49:09.915006Z 17 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
2020-06-16T14:49:09.915033Z 17 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 1363306'
2020-06-16T14:49:09.915005Z 23 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './ip136-relay-bin-group_replication_applier.000002' position: 4
2020-06-16T14:49:09.915432Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
2020-06-16T14:49:09.915447Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 23306'
2020-06-16T14:49:10.921514Z 0 [Note] Plugin group_replication reported: 'Group membership changed to ip136:3306 on view 15923189509206964:1.'
2020-06-16T14:49:10.921627Z 30 [Note] Plugin group_replication reported: 'Only one server alive. Declaring this server as online within the replication group'
2020-06-16T14:49:11.231107Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'
2020-06-16T14:49:11.231322Z 0 [Note] Plugin group_replication reported: 'A new primary with address ip136:3306 was elected, enabling conflict detection until the new primary applies all relay logs.'
2020-06-16T14:49:11.231425Z 32 [Note] Plugin group_replication reported: 'This server is working as primary member.'
5、在主节点通过视图查看现在的集群状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 839d3796-a8a1-11ea-b1db-00505636b68c | ip136 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
mysql> select VARIABLE_VALUE from performance_schema.global_status where VARIABLE_NAME='group_replication_primary_member';
+--------------------------------------+
| VARIABLE_VALUE |
+--------------------------------------+
| 839d3796-a8a1-11ea-b1db-00505636b68c |
+--------------------------------------+
1 row in set (0.00 sec)
6、在主节点关闭 group_replication_bootstrap_group
set global group_replication_bootstrap_group=off;
7、配置从节点
在需要加入集群的两个节点分别安装组复制插件,并配置组复制参数,同时写入 my.cnf 文件
#开放 23306 端口
firewall-cmd --zone=public --add-port=23306/tcp --permanent
firewall-cmd --reload
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
#节点1
set global group_replication_group_name='3a90016b-afe0-11ea-ab97-00505636b68c';
set global group_replication_local_address='192.168.136.135:23306';
set global group_replication_group_seeds='192.168.136.136:23306,192.168.136.135:23306,192.168.136.137:23306';
set global group_replication_start_on_boot=off;
set global group_replication_ip_whitelist='192.168.136.0/24';
loose-group_replication_group_name='3a90016b-afe0-11ea-ab97-00505636b68c'
loose-group_replication_local_address='192.168.136.135:23306'
loose-group_replication_group_seeds='192.168.136.136:23306,192.168.136.135:23306,192.168.136.137:23306'
loose-group_replication_start_on_boot=off
loose-group_replication_ip_whitelist='192.168.136.0/24'
#节点2
set global group_replication_group_name='3a90016b-afe0-11ea-ab97-00505636b68c';
set global group_replication_local_address='192.168.136.137:23306';
set global group_replication_group_seeds='192.168.136.136:23306,192.168.136.135:23306,192.168.136.137:23306';
set global group_replication_start_on_boot=off;
set global group_replication_ip_whitelist='192.168.136.0/24';
loose-group_replication_group_name='3a90016b-afe0-11ea-ab97-00505636b68c'
loose-group_replication_local_address='192.168.136.137:23306'
loose-group_replication_group_seeds='192.168.136.136:23306,192.168.136.135:23306,192.168.136.137:23306'
loose-group_replication_start_on_boot=off
loose-group_replication_ip_whitelist='192.168.136.0/24'
8、在两个节点启动组复制
依次在两个节点启动组复制,加入集群环境,可以通过观察日志或者视图确认是否加入成功
#启动组复制
start group_replication;
#查看视图
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 839d3796-a8a1-11ea-b1db-00505636b68c | ip136 | 3306 | ONLINE |
| group_replication_applier | e741120b-a7e9-11ea-9af7-0050563bbd20 | ip135 | 3306 | ONLINE |
| group_replication_applier | f44ecd83-a8d1-11ea-ad97-0050562512fc | ip137 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
#日志打印如下,则启动成功
2020-06-16T14:56:17.265752Z 2 [Note] Plugin group_replication reported: 'This server is working as secondary member with primary member address ip136:3306.'
...
2020-06-16T14:56:17.344919Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'
启动详细日志
2020-06-16T14:56:12.522704Z 2 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"'
2020-06-16T14:56:12.523859Z 2 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.'
2020-06-16T14:56:12.524045Z 2 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'
2020-06-16T14:56:12.524102Z 2 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "3a90016b-afe0-11ea-ab97-00505636b68c"; group_replication_local_address: "192.168.136.135:23306"; group_replication_group_seeds: "192.168.136.136:23306,192.168.136.135:23306,192.168.136.137:23306"; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "192.168.136.0/24"'
2020-06-16T14:56:12.524147Z 2 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0'
2020-06-16T14:56:12.524156Z 2 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds'
2020-06-16T14:56:12.524185Z 2 [Note] Plugin group_replication reported: 'Member configuration: member_id: 1353306; member_uuid: "e741120b-a7e9-11ea-9af7-0050563bbd20"; single-primary mode: "true"; group_replication_auto_increment_increment: 7; '
2020-06-16T14:56:12.530898Z 4 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2020-06-16T14:56:12.557787Z 7 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './ip135-relay-bin-group_replication_applier.000001' position: 4
2020-06-16T14:56:12.557788Z 2 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2020-06-16T14:56:12.557874Z 2 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
2020-06-16T14:56:12.557881Z 2 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 1353306'
2020-06-16T14:56:12.591272Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
2020-06-16T14:56:12.591345Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 23306'
2020-06-16T14:56:17.265752Z 2 [Note] Plugin group_replication reported: 'This server is working as secondary member with primary member address ip136:3306.'
2020-06-16T14:56:17.267305Z 14 [Note] Plugin group_replication reported: 'Establishing group recovery connection with a possible donor. Attempt 1/10'
2020-06-16T14:56:17.267876Z 0 [Note] Plugin group_replication reported: 'Group membership changed to ip136:3306, ip135:3306 on view 15923189509206964:2.'
2020-06-16T14:56:17.283582Z 14 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='ip136', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2020-06-16T14:56:17.292194Z 14 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 839d3796-a8a1-11ea-b1db-00505636b68c at ip136 port: 3306.'
2020-06-16T14:56:17.292856Z 16 [Warning]
2020-06-16T14:56:17.300970Z 16 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@ip136:3306',replication started in log 'FIRST' at position 4
2020-06-16T14:56:17.309664Z 17 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './ip135-relay-bin-group_replication_recovery.000001' position: 4
2020-06-16T14:56:17.322972Z 14 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
2020-06-16T14:56:17.323386Z 17 [Warning] Slave SQL for channel 'group_replication_recovery': Coordinator thread of multi-threaded slave is being stopped in the middle of assigning a group of events; deferring to exit until the group completion ... , Error_code: 0
2020-06-16T14:56:17.324925Z 17 [Note] Slave SQL thread for channel 'group_replication_recovery' exiting, replication stopped in log 'mysql-binlog.000010' at position 1442
2020-06-16T14:56:17.325910Z 16 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery'
2020-06-16T14:56:17.325935Z 16 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'mysql-binlog.000010', position 1442
2020-06-16T14:56:17.337845Z 14 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='ip136', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2020-06-16T14:56:17.344919Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'
9、如何找出 Primary 成员
集群搭建成功之后,可以通过查看视图找出 Primary 成员;或者使用 show variables like ‘%read_only%’; 因为 MGR 默认单主模式,secondary 节点会自动开启 read only 模式
mysql> select VARIABLE_VALUE from performance_schema.global_status where VARIABLE_NAME='group_replication_primary_member';
+--------------------------------------+
| VARIABLE_VALUE |
+--------------------------------------+
| 839d3796-a8a1-11ea-b1db-00505636b68c |
+--------------------------------------+
1 row in set (0.00 sec)
#查看 Primary 成员
mysql> select *from performance_schema.replication_group_members where member_id =(select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member');
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 839d3796-a8a1-11ea-b1db-00505636b68c | ip136 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
10、常见报错
1) 问题一
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
ERROR 1126 (HY000):
原因 :MySQL的配置文件指定的 plugin_dir 中,找不到你要安装的【*.so】文件。
#查看 plugin_dir 路径
mysql> show global variables like 'plugin_dir';
+---------------+-------------------------------+
| Variable_name | Value |
+---------------+-------------------------------+
| plugin_dir | /home/mysql/mysql/lib/plugin/ |
+---------------+-------------------------------+
1 row in set (0.00 sec)
#将【*.so】文件复制到此路径下
cp /home/mysql/mysql/mysql-5.7.30/lib/plugin/group_replication.so /home/mysql/mysql/lib/plugin/
2)问题二
Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.136.136:23306 on local port: 23306.'
Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.136.137:23306 on local port: 23306.'
原因:23306 未放通
centos7 firewalld常用命令
方法一:关闭防火墙
# 启动
systemctl start firewalld
# 关闭
systemctl stop firewalld
# 查看状态
systemctl status firewalld
# 开机禁用
systemctl disable firewalld
# 开机启用
systemctl enable firewalld
# 查看所有打开的端口
firewall-cmd --zone=public --list-ports
方法二:开放 23306 端口
firewall-cmd --zone=public --add-port=23306/tcp --permanent
firewall-cmd --reload
节点接入报错
Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 23306'
Plugin group_replication reported: 'Timeout on wait for view after joining group'
#开放 23306 端口
firewall-cmd --zone=public --add-port=23306/tcp --permanent
firewall-cmd --reload
centos6 防火墙常用命令
# 查看防火墙状态
service iptables status
# 停止防火墙
service iptables stop
# 启动防火墙
service iptables start
# 重启防火墙
service iptables restart
# 永久关闭防火墙
chkconfig iptables off
# 永久关闭后重启
chkconfig iptables on
#开放23306端口
vim /etc/sysconfig/iptables
-A INPUT -m state --state NEW -M tcp -p tcp --dport 23306 -j ACCEPT
service iptables restart
11、集群故障
#主从节点故障,重新加入组复制
stop group_replication;
start group_replication;
#查看 recovery 阶段的执行情况
show slave status for channel 'group_replication_recovery' \G;