mysql 高可用之组复制 (MGR)

MySQL Group Replication(简称 MGR )是 MySQL 官方于 2016 年 12 月推出的一个全新的高可用与高扩展的解决方案

组复制是 MySQL 5.7.17 版本出现的新特性，它提供了高可用、高扩展、高可靠的 MySQL 集群服务

MySQL 组复制分单主模式和多主模式，传统的 mysql 复制技术仅解决了数据同步的问题，

MGR 对属于同一组的服务器自动进行协调。对于要提交的事务，组成员必须就全局事务序列中给定事务的顺序达成一致

提交或回滚事务由每个服务器单独完成，但所有服务器都必须做出相同的决定

如果存在网络分区，导致成员无法达成事先定义的分割策略，则在解决此问题之前系统不会继续进行，这是一种内置的自动裂脑保护机制

MGR 由组通信系统(Group Communication System，GCS ) 协议支持

该系统提供故障检测机制、组成员服务以及安全且有序的消息传递

组复制流程

首先我们将多个节点共同组成一个复制组，在执行读写（RW）事务的时候，需要通过一致性协议层（Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节点）的同意，大多数指的是同意的节点数量需要大于 （N/2+1），这样才可以进行提交，而不是原发起方一个说了算。而针对只读（RO）事务则不需要经过组内同意，直接 提交 即可

注意：节点数量不能超过 9 台

组复制单主和多主模式

single-primary mode(单写或单主模式)

单写模式 group 内只有一台节点可写可读，其他节点只可以读。当主服务器失败时，会自动选择新的主服务器，

multi-primary mode(多写或多主模式)

组内的所有机器都是 primary 节点，同时可以进行读写操作，并且数据是最终一致的。

一.还原mysql所有节点

利用ansible还原所有节点

#利用ansible还原所有节点
[root@mha ~]# cat > /etc/yum.repos.d/epel.repo <<EOF
> [epel]
> name = epel
> baseurl = https://mirrors.aliyun.com/epel-archive/9.6/Everything/x86_64/
> gpgcheck = 0
> EOF
​
[root@mha ~]# dnf install ansible -y
​
[root@mha ~]# ansible --version
ansible [core 2.14.18]
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.9/site-packages/ansible
  ansible collection location = /root/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/bin/ansible
  python version = 3.9.21 (main, Feb 10 2025, 00:00:00) [GCC 11.5.0 20240719 (Red Hat 11.5.0-5)] (/usr/bin/python3)
  jinja version = 3.1.2
  libyaml = True
​
​
[root@mha ~]# useradd  devops
[root@mha ~]# echo lee | passwd --stdin devops
[root@mha ~]# su - devops
[devops@mha ~]$ mkdir  ansible
​
[devops@mha ansible]$ cat >ansible.cfg <<EOF
[defaults]
inventory=./inventory
remote_user=root
host_key_checking=false
[privilege_escalation]
become=False
EOF
​
[devops@mha ansible]$ ansible mysql -m user -a 'name=devops'
[devops@mha ansible]$  ansible mysql -m shell -a 'echo devops | passwd --stdin devops'
[devops@mha ansible]$ ansible mysql -m shell -a 'echo "devops   ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers'
[devops@mha ansible]$ ansible all -m file -a 'path=/home/devops/.ssh owner=devops group=devops mode="0700" state=directory'
[devops@mha ansible]$ ansible all -m copy -a 'src=/home/devops/.ssh/authorized_keys dest=/home/devops/.ssh/authorized_keys owner=devops group=devops mode='0600''
​
[devops@mha ansible]$ cat >ansible.cfg <<EOF
[defaults]
inventory=./inventory
remote_user=devops
host_key_checking=false
​
[privilege_escalation]
become=True
become_ask_pass=False
become_method=sudo
become_user=root
EOF
​
[devops@mha ansible]$ ansible all -m shell -a 'whoami'
172.25.254.20 | CHANGED | rc=0 >>
root
172.25.254.30 | CHANGED | rc=0 >>
root
172.25.254.10 | CHANGED | rc=0 >>
root
​
[devops@mha ansible]$ vim clear_mysql.yml
- name: reset mysql
  hosts: mysql
  tasks:
  - name: stop mysql
    shell: '/etc/init.d/mysqld stop'
    ignore_errors: yes
​
  - name: delete mysql data
    file:
      path: /data/mysql
      state: absent
​
  - name: crate data directroy
    file:
      path: /data/mysql
      state: directory
      owner: mysql
      group: mysql
​
  - name: initialize mysql
    shell: '/usr/local/mysql/bin/mysqld --initialize --user=mysql'
​
​
[devops@mha ansible]$ ansible-playbook  clear_mysql.yml  -vv | grep password

手动还原方式

#所有节点初始化数据
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
[root@mysql-node1 ~]# rm -rf /data/mysql/*
​
[root@mysql-node1 ~]# cat > /etc/my.cnf <<EOF
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
​
#不同主机server-id一定要根据实际情况做相应改变
server-id=10|20|30
log-bin=mysql-bin
​
gtid_mode=ON   #启用全局事件标识
enforce-gtid-consistency=ON    #强制gtid一致
default_authentication_plugin=mysql_native_password
log_slave_updates=ON     #打开数据库中继，  #当slave中sql线程读取日志后也会写入到自己的binlog中
binlog_format=ROW      #使用行日志格式 
binlog_checksum=NONE    #禁止对二进制日志校验
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"   #禁用指定存储引擎
EOF
​
[root@mysql-node1 ~]# mysqld --user=mysql --initialize

二.部署组复制

#设置所有mysql节点的解析
[root@mysql-node1 ~]# cat > /etc/hosts <<EOF
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
172.25.254.10     mysql-node1
172.25.254.20     mysql-node2
172.25.254.30     mysql-node3
EOF
​
[root@mysql-node1 ~]# cat  >> /etc/my.cnf <<EOF
plugin_load_add='group_replication.so'   #加载组复制插件
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"    #通知插件正式加入或创建的组名    名称为uuid格式
group_replication_start_on_boot=off  #在server启动时不自动启动组复制
group_replication_local_address="172.25.254.10:33061"   #其他两台主机一定要根据ip进行修改
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061"   #本地地址允许访问成员列表
group_replication_bootstrap_group=off   #不随系统自启而启动
group_replication_single_primary_mode=OFF  #使用多主模式
EOF
[root@mysql-node1 ~]# /etc/init.d/mysqld start
​
#配置组复制-在首台主机中
[root@mysql-node1 ~]# mysql -uroot -p'lsyVh+etR1ht'
mysql> alter user root@localhost identified   by 'lee';
Query OK, 0 rows affected (0.04 sec)
​
mysql> SET SQL_LOG_BIN=0;  #关闭bin_log日志
Query OK, 0 rows affected (0.00 sec)
​
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'lee';
Query OK, 0 rows affected (0.00 sec)
​
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
​
mysql> GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
​
mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
​
mysql>  GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
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> CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='lee' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
​
mysql> SHOW PLUGINS;     #查看组复制插件是否激活
| group_replication               | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
​
mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='lee';
Query OK, 0 rows affected (1.10 sec)
​
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
​
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | ac3d6eaf-1a0a-11f1-9efa-000c29f4a60c | mysql-node1 |        3306 | ONLINE       | PRIMARY     | 8.3.0          | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)
​
​
#配置组复制在其余主机中
[root@mysql-node2 ~]# /etc/init.d/mysqld start
[root@mysql-node2 ~]# mysql -uroot -p'XkP<Uaa:9so5'
mysql> alter user root@localhost identified   by 'lee';
Query OK, 0 rows affected (0.00 sec)
​
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
​
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'lee';
Query OK, 0 rows affected (0.00 sec)
​
mysql>  GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
​
mysql> GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
​
mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
​
mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
​
mysql>  SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
​
mysql>  CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user',SOURCE_PASSWORD='lee' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
​
mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='lee';
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.            #出现此处报错可以初始化下master
mysql> reset master;            #用过此命令解决以上报错
Query OK, 0 rows affected, 1 warning (0.04 sec)
​
mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='lee';
Query OK, 0 rows affected (7.94 sec)
​
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | ac3d6eaf-1a0a-11f1-9efa-000c29f4a60c | mysql-node1 |        3306 | ONLINE       | PRIMARY     | 8.3.0          | XCom                       |
| group_replication_applier | e0b37b20-1a0b-11f1-a62c-000c29e84b64 | mysql-node2 |        3306 | ONLINE       | PRIMARY     | 8.3.0          | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)
​
#看到主机online表示成功
​
#重新启动时
#master
#-- 在 mysql-node1 上执行
#-- 确保组复制插件已加载（您的显示已加载）
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
​
#-- 验证组状态
SELECT * FROM performance_schema.replication_group_members;
​
#salve
#-- 在 mysql-node2 和 mysql-node3 上分别执行
START GROUP_REPLICATION;
​
#-- 验证组状态
SELECT * FROM performance_schema.replication_group_members;

.测试

#测试所有节点是否可以执行读写并数据是否同步
#node1中
mysql> create database ;
Query OK, 1 row affected (0.00 sec)
​
mysql> create table hjw.userlist (
    -> username VARCHAR(10) PRIMARY KEY NOT NULL,
    -> password VARCHAR(50) NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)
​
mysql> INSERT INTO hjw.userlist VALUES ('user1','111');
Query OK, 1 row affected (0.01 sec)
​
#在node2中查看并插入新的数据
mysql> select * from hjw.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.00 sec)
​
mysql> insert into hjw.userlist values ('user2','222');
Query OK, 1 row affected (0.01 sec)
​
mysql>select * from hjw.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
+----------+----------+
2 rows in set (0.01 sec)
​
#在node3中查看并插入数据
mysql> select * from hjw.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
+----------+----------+
2 rows in set (0.00 sec)
​
​
mysql> insert into hjw.userlist values ('user3','333');
Query OK, 1 row affected (0.01 sec)
​
mysql> select * from hjw.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
| user3    | 333      |
+----------+----------+
3 rows in set (0.00 sec)
​
mysql>
​
​
#在node1和2中也可以看到以上数据

三.Mysqlrouter软件下载

[root@mysqlrouter ~]# wget https://downloads.mysql.com/archives/get/p/41/file/mysql-router-community-8.4.7-1.el9.x86_64.rpm

安装mysqlrouter

[root@mysqlrouter ~]# dnf install mysql-router-community-8.4.7-1.el9.x86_64.rpm -y

mysqlrouter配置文件

[root@mysqlrouter ~]# rpm -qc mysql-router-community
/etc/logrotate.d/mysqlrouter                #日志轮询及日志截断策略
/etc/mysqlrouter/mysqlrouter.conf           #主配置文件
​
[root@mysqlrouter ~]# systemctl status mysqlrouter.service      #启动脚本

配置mysqlrouter

[root@mysqlrouter ~]# vim /etc/mysqlrouter/mysqlrouter.conf
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.254.10:3306,172.25.254.20:3306,172.25.254.30:3306
routing_strategy = round-robin
​
​
[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.254.30:3306,172.25.254.20:3306,172.25.254.10:3306
routing_strategy = first-available
​
[root@mysqlrouter ~]# systemctl enable --now mysqlrouter.service
Created symlink /etc/systemd/system/multi-user.target.wants/mysqlrouter.service → /usr/lib/systemd/system/mysqlrouter.service.
​
[root@mysqlrouter ~]# netstat -antlupe | grep mysql
tcp        0      0 0.0.0.0:7001            0.0.0.0:*               LISTEN      991        176991     39587/mysqlrouter
tcp        0      0 0.0.0.0:7002            0.0.0.0:*               LISTEN      991        176009     39587/mysqlrouter

测试

#在mysql节点的任意主机中添加root远程登录
[root@mysql-node1 ~]# mysql -uroot -plee

mysql> CREATE USER root@'%' identified by 'lee';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON *.* TO root@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| rpl_user         | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

mysql> quit
Bye
[root@mysql-node1 ~]# mysql -uroot -plee -h172.25.254.10

mysql> quit
Bye
[root@mysql-node1 ~]# mysql -uroot -plee -h172.25.254.20


mysql> quit
Bye
[root@mysql-node1 ~]# mysql -uroot -plee -h172.25.254.30

mysql> quit


#查看调度效果
[root@mysql-node10 & 20 & 30 ~]# watch -n1 lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  9879 mysql   22u  IPv6  56697      0t0  TCP *:mysql (LISTEN)

#测试效果
[root@mysql-node1 ~]#  mysql -uroot -plee -h172.25.254.40 -P7002

#通过多次登录查看响应变化