MySQL组复制

组复制

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 're