组复制
MySQL组复制(MySQL Group Replication,MGR)是官方在5.7.17推出的一个基于Paxos协议的高可用方案。在MGR之前,MySQL仅支持主从复制。传统的主从复制包括异步复制和半同步复制。相对于传统的主从复制,组复制主要新增了两个模块。
Consensus:共识。共识是基于Paxos实现的,可以保证消息的全局有序和消息会被半数以上的节点确认并接收。
Certify:认证。认证可以保证所有的节点会以确定行为来处理同一个事务,要么全部认证通过,要么全部认证失败。
从原理上来,组复制能百分之一百确保各个在线节点的数据一致性。
组复制规划
IP | 主机名 | 角色 | MySQL版本 | server-id |
192.168.6.101 | node01 | Primary | 8.0.33 | 1001 |
192.168.6.102 | node02 | Secondary | 8.0.33 | 1002 |
192.168.6.103 | node03 | Secondary | 8.0.33 | 1003 |
部署组复制
1、准备安装环境
关闭防火墙
[root@node01 ~]# systemctl stop firewalld
[root@node01 ~]# systemctl disablefirewalld
关闭selinux
[root@node01 ~]# sed -n '/SELINUX=enforcing/c SELINUX=disabled' /etc/sysconfig/selinux
SELINUX=disabled
2、准备安装包
[root@node01 ~]# id mysql
uid=54321(mysql) gid=54321(mysql) groups=54321(mysql)
[root@node01 ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
[root@node01 ~]# ln -s /usr/local/cluster/mysql-8.0.33-linux-glibc2.12-x86_64/ /usr/local/mysql
3、创建数据目录
[root@node01 ~]# mkdir -p /data/mysql/3306/data
[root@node01 ~]# chown -R mysql:mysql /data/mysql/3306/data
4、修改配置文件
[root@node01 ~]# vim /etc/my.cnf
[client]
port=3306
socket=/data/mysql/3306/data/mysql.sock
[mysql]
prompt="\\u@\\h [\\d]> "
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/3306/data
user=mysql
port=3306
pid-file=/data/mysql/3306/data/mysql.pid
socket=/data/mysql/3306/data/mysql.sock
log-error=/data/mysql/3306/data/mysql.err
mysqlx_port=33060
mysqlx_socket=/data/mysql/3306/data/mysqlx.sock
log-timestamps=system
skip-name-resolve
report-host="192.168.6.101"
disabled-storage-engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
#gtid
server-id=1001
log-bin=mysql-bin
binlog-format=row
log-slave-updates=on
gtid-mode=on
enforce-gtid-consistency=on
master-info-repository=table
relay-log-info-repository=table
super-read-only=on
binlog-transaction-dependency-tracking=writeset
transaction-write-set-extraction=xxhash64
#multi-thread repl
slave-parallel-type=logical_clock
slave-preserve-commit-order=on
slave-parallel-workers=4
#group repl
plugin-load-add="group_replication.so"
loose-group-replication-group-name="326b7a2d-458b-11ee-839e-000c29004cdf"
loose-group-replication-start-on-boot=off
loose-group-replication-local-address="192.168.6.101:33061"
loose-group-replication-group-seeds="192.168.6.101:33061,192.168.6.102:33061,192.168.6.103:33061"
loose-group-replication-bootstrap-group=off
loose-group-replication-recovery-get-public-key=on
node02的配置文件修改的部分
report-host="192.168.6.102"
server-id=1002
loose-group-replication-local-address="192.168.6.102:33061"
node03的配置文件修改的部分
report-host="192.168.6.103"
server-id=1003
loose-group-replication-local-address="192.168.6.103:33061"
5、初始化实例
[root@node01 ~]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --initialize-insecure
6、启动实例
[root@node01 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
启动组复制
1、查看插件是否加载成功
root@localhost [(none)]> show plugins;
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
2、初始化组复制,只在node01节点执行
root@localhost [(none)]> set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)]> start group_replication;
Query OK, 0 rows affected (1.39 sec)
root@localhost [(none)]> set global group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+--------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | member_role |
+--------------------------------------+--------------+-------------+--------------+-------------+
| baec364d-458b-11ee-829b-000c29004cdf | 192.16.6.101 | 3306 | ONLINE | PRIMARY |
+--------------------------------------+--------------+-------------+--------------+-------------+
1 row in set (0.02 sec)
3、创建组复制用户
root@localhost [(none)]> create user 'repl_group'@'%' identified by 'oracle';
Query OK, 0 rows affected (0.02 sec)
root@localhost [(none)]> grant replication slave on *.* to 'repl_group'@'%';
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)]> grant backup_admin on *.* to 'repl_group'@'%';
Query OK, 0 rows affected (0.00 sec)
4、配置恢复通道
change master to
master_user='repl_group',
master_password='oracle'
for channel 'group_replication_recovery';
root@localhost [(none)]> change master to
-> master_user='repl_group',
-> master_password='oracle'
-> for channel 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.03 sec)
5、创建测试数据
root@localhost [(none)]> create database hr;
Query OK, 1 row affected (0.00 sec)
root@localhost [(none)]> create table hr.employees (id int primary key,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
root@localhost [(none)]> insert into hr.employees values('1001','张三'),(1002,'李四');
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@localhost [(none)]> select * from hr.employees;
+------+--------+
| id | name |
+------+--------+
| 1001 | 张三 |
| 1002 | 李四 |
+------+--------+
2 rows in set (0.00 sec)
添加节点
1、配置恢复通道
change master to
master_user='repl_group',
master_password='oracle'
for channel 'group_replication_recovery';
分别在node02和node03上执行
[root@node02 ~]# mysql -uroot -p
root@localhost [(none)]> change master to
-> master_user='repl_group',
-> master_password='oracle'
-> for channel 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.04 sec)
[root@node03 ~]# mysql -uroot -p
root@localhost [(none)]> change master to
-> master_user='repl_group',
-> master_password='oracle'
-> for channel 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.04 sec)
2、启动组复制
root@localhost [(none)]> start group_replication;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
[root@node02 ~]# tail -100f /data/mysql/3306/data/mysql.err
2023-08-29T12:55:50.945748+08:00 11 [ERROR] [MY-011685] [Repl] Plugin group_replication reported: 'The group_replication_group_name option is mandatory'
变量group_replication_group_name必须在每个MGR成员中设置,并且保持一致
root@localhost [(none)]> select @@group_replication_group_name;
+--------------------------------------+
| @@group_replication_group_name |
+--------------------------------------+
| 326b7a2d-458b-11ee-839e-000c29004cdf |
+--------------------------------------+
1 row in set (0.00 sec)
root@localhost [(none)]> select @@group_replication_group_name;
+--------------------------------+
| @@group_replication_group_name |
+--------------------------------+
| NULL |
+--------------------------------+
1 row in set (0.00 sec)
修改node02和node03参数group_replication_group_name,并在配置文件中更新
root@localhost [(none)]> set global group_replication_group_name="326b7a2d-458b-11ee-839e-000c29004cdf";
Query OK, 0 rows affected (0.02 sec)
root@localhost [(none)]> set global group_replication_group_name="326b7a2d-458b-11ee-839e-000c29004cdf";
Query OK, 0 rows affected (0.03 sec)
[root@node02 ~]# vim /etc/my.cnf
loose-group-replication-group-name="326b7a2d-458b-11ee-839e-000c29004cdf"
[root@node03 ~]# vim /etc/my.cnf
loose-group-replication-group-name="326b7a2d-458b-11ee-839e-000c29004cdf"
node02启动组复制
root@localhost [(none)]> start group_replication;
Query OK, 0 rows affected (2.19 sec)
启动复制
root@localhost [(none)]> start group_replication;
Query OK, 0 rows affected (2.51 sec)
root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+--------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | member_role |
+--------------------------------------+--------------+-------------+--------------+-------------+
| 874cb511-461e-11ee-87b6-000c296e813f | 192.16.6.103 | 3306 | RECOVERING | SECONDARY |
| 9d10fb5e-461d-11ee-bcb8-000c297a8cee | 192.16.6.102 | 3306 | RECOVERING | SECONDARY |
| baec364d-458b-11ee-829b-000c29004cdf | 192.16.6.101 | 3306 | ONLINE | PRIMARY |
+--------------------------------------+--------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)
日志
[root@node02 ~]# tail -100f /data/mysql/3306/data/mysql.err
2023-08-29T13:56:18.552167+08:00 76 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
2023-08-29T13:56:39.554719+08:00 76 [ERROR] [MY-010584] [Repl] Replica I/O for channel 'group_replication_recovery': Error connecting to source 'repl_group@192.16.6.101:3306'. This was attempt 1/1, with a delay of 60 seconds between attempts. Message: Can't connect to MySQL server on '192.16.6.101:3306' (111), Error_code: MY-002003
2023-08-29T13:56:39.555636+08:00 24 [ERROR] [MY-011582] [Repl] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.'
2023-08-29T13:56:39.555775+08:00 24 [ERROR] [MY-011583] [Repl] Plugin group_replication reported: 'For details please check performance_schema.replication_connection_status table and error log messages of Replica I/O for channel group_replication_recovery.'
参数值配置错误
report-host="192.16.6.101"
改为
report-host="192.168.6.101"
重启mysql
[root@node01 ~]# /usr/local/mysql/support-files/mysql.server restart
[root@node02 ~]# /usr/local/mysql/support-files/mysql.server restart
[root@node03 ~]# /usr/local/mysql/support-files/mysql.server restart
node01启动组复制
root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+-----------+-------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | member_role |
+-----------+-------------+-------------+--------------+-------------+
| | | NULL | OFFLINE | |
+-----------+-------------+-------------+--------------+-------------+
root@localhost [(none)]> set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)]> start group_replication;
Query OK, 0 rows affected (1.10 sec)
root@localhost [(none)]> set global group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)]> change master to
-> master_user='repl_group',
-> master_password='oracle'
-> for channel 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.00 sec)
root@localhost [(none)]> change master to master_user='repl_group',master_password='oracle' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.02 sec)
root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+---------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | member_role |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 66d72c0c-4634-11ee-a0bc-000c29004cdf | 192.168.6.101 | 3306 | ONLINE | PRIMARY |
+--------------------------------------+---------------+-------------+--------------+-------------+
1 row in set (0.00 sec)
change master to master_user='repl_group',master_password='oracle' for channel 'group_replication_recovery';
重新添加节点node02、node03
root@localhost [(none)]> change master to master_user='repl_group',master_password='oracle' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.01 sec)
root@localhost [(none)]> start group_replication;
Query OK, 0 rows affected (1.69 sec)
root@localhost [(none)]> change master to master_user='repl_group',master_password='oracle' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.01 sec)
root@localhost [(none)]> start group_replication;
Query OK, 0 rows affected (1.87 sec)
root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+---------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | member_role |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 66d72c0c-4634-11ee-a0bc-000c29004cdf | 192.168.6.101 | 3306 | ONLINE | PRIMARY |
| 9844cd81-4634-11ee-87f2-000c297a8cee | 192.168.6.102 | 3306 | ONLINE | SECONDARY |
| a3dac279-4634-11ee-9220-000c296e813f | 192.168.6.103 | 3306 | ONLINE | SECONDARY |
+--------------------------------------+---------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)
单主模式和多主模式的区别
1、单主模式
只在Primary节点读写,其他Secondary节点只读
root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+---------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | member_role |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 66d72c0c-4634-11ee-a0bc-000c29004cdf | 192.168.6.101 | 3306 | ONLINE | PRIMARY |
| 9844cd81-4634-11ee-87f2-000c297a8cee | 192.168.6.102 | 3306 | ONLINE | SECONDARY |
| a3dac279-4634-11ee-9220-000c296e813f | 192.168.6.103 | 3306 | ONLINE | SECONDARY |
+--------------------------------------+---------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)
2、多主模式
所有节点都是primary,可读写
3、部署方式
多主模式部署多两个参数
group_replication_single_primary_mode=off
group_replication_enforce_undate_everywhere_checks=on
4、read_only设置
单主模式,组复制会自动将secondary节点的super_read_only和read_only设置为on
root@localhost [(none)]> show variables like 'super_read_only';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| super_read_only | ON |
+-----------------+-------+
1 row in set (0.03 sec)
root@localhost [(none)]> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.00 sec)
5、自增主键
单主默认为1
root@localhost [(none)]> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
多主模式
auto_increment_offset=@@server_id
auto_increment_increment=group_replication_auto_increment_increment
root@localhost [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 1001 |
+-------------+
1 row in set (0.00 sec)
root@localhost [(none)]> show variables like 'group_replication_auto_increment%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| group_replication_auto_increment_increment | 7 |
+--------------------------------------------+-------+
1 row in set (0.00 sec)
单主模式和多主模式在线切换
MySQL 8.0.13之后可以在线切换模式
1、单主切换为多主模式
select group_replication_switch_to_multi_primary_mode();
node01节点执行
root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+---------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | member_role |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 66d72c0c-4634-11ee-a0bc-000c29004cdf | 192.168.6.101 | 3306 | ONLINE | PRIMARY |
| 9844cd81-4634-11ee-87f2-000c297a8cee | 192.168.6.102 | 3306 | ONLINE | SECONDARY |
| a3dac279-4634-11ee-9220-000c296e813f | 192.168.6.103 | 3306 | ONLINE | SECONDARY |
+--------------------------------------+---------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)
root@localhost [(none)]> select group_replication_switch_to_multi_primary_mode();
+--------------------------------------------------+
| group_replication_switch_to_multi_primary_mode() |
+--------------------------------------------------+
| Mode switched to multi-primary successfully. |
+--------------------------------------------------+
1 row in set (0.27 sec)
root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+---------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | member_role |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 66d72c0c-4634-11ee-a0bc-000c29004cdf | 192.168.6.101 | 3306 | ONLINE | PRIMARY |
| 9844cd81-4634-11ee-87f2-000c297a8cee | 192.168.6.102 | 3306 | ONLINE | PRIMARY |
| a3dac279-4634-11ee-9220-000c296e813f | 192.168.6.103 | 3306 | ONLINE | PRIMARY |
+--------------------------------------+---------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)
[root@node02 ~]# tail -50f /data/mysql/3306/data/mysql.err
2023-08-29T16:26:02.741021+08:00 66 [System] [MY-013214] [Repl] Plugin group_replication reported: 'Starting group operation local execution: Multi primary mode migration'
2023-08-29T16:26:02.752165+08:00 66 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'
2023-08-29T16:26:02.876809+08:00 0 [System] [MY-013213] [Repl] Plugin group_replication reported: 'Configuration operation 'Multi primary mode migration' terminated. Mode switched to multi-primary successfully.'
2、多主切换为单主模式
select group_replication_switch_to_single_primary_mode(member_id);
将node01切换为primary
root@localhost [(none)]> select group_replication_switch_to_single_primary_mode('66d72c0c-4634-11ee-a0bc-000c29004cdf');
+-----------------------------------------------------------------------------------------+
| group_replication_switch_to_single_primary_mode('66d72c0c-4634-11ee-a0bc-000c29004cdf') |
+-----------------------------------------------------------------------------------------+
| Mode switched to single-primary successfully. |
+-----------------------------------------------------------------------------------------+
1 row in set (0.20 sec)
root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+---------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | member_role |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 66d72c0c-4634-11ee-a0bc-000c29004cdf | 192.168.6.101 | 3306 | ONLINE | PRIMARY |
| 9844cd81-4634-11ee-87f2-000c297a8cee | 192.168.6.102 | 3306 | ONLINE | SECONDARY |
| a3dac279-4634-11ee-9220-000c296e813f | 192.168.6.103 | 3306 | ONLINE | SECONDARY |
+--------------------------------------+---------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)
[root@node02 ~]# tail -50f /data/mysql/3306/data/mysql.err
2023-08-29T16:29:24.515905+08:00 67 [System] [MY-013214] [Repl] Plugin group_replication reported: 'Starting group operation local execution: Change to single primary mode'
2023-08-29T16:29:24.559660+08:00 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address 192.168.6.101:3306 was elected. Enabling conflict detection until the new primary applies all relay logs.'
2023-08-29T16:29:24.562625+08:00 68 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'
2023-08-29T16:29:24.617273+08:00 68 [System] [MY-011511] [Repl] Plugin group_replication reported: 'This server is working as secondary member with primary member address 192.168.6.101:3306.'
2023-08-29T16:29:24.686305+08:00 0 [System] [MY-013213] [Repl] Plugin group_replication reported: 'Configuration operation 'Change to single primary mode' terminated. Mode switched to single-primary successfully.'
[root@node03 ~]# tail -50f /data/mysql/3306/data/mysql.err
2023-08-29T16:29:24.535924+08:00 64 [System] [MY-013214] [Repl] Plugin group_replication reported: 'Starting group operation local execution: Change to single primary mode'
2023-08-29T16:29:24.559379+08:00 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address 192.168.6.101:3306 was elected. Enabling conflict detection until the new primary applies all relay logs.'
2023-08-29T16:29:24.562133+08:00 65 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'
2023-08-29T16:29:24.634945+08:00 65 [System] [MY-011511] [Repl] Plugin group_replication reported: 'This server is working as secondary member with primary member address 192.168.6.101:3306.'
2023-08-29T16:29:24.686225+08:00 0 [System] [MY-013213] [Repl] Plugin group_replication reported: 'Configuration operation 'Change to single primary mode' terminated. Mode switched to single-primary successfully.'