MySQL MGR 集群搭建（单主模式&多主模式）

最新推荐文章于 2024-09-06 21:09:11 发布

hbhe0316

最新推荐文章于 2024-09-06 21:09:11 发布

阅读量391

点赞数

文章标签：分布式 mysql java linux 数据库

本文链接：https://blog.csdn.net/hbhe0316/article/details/122701348

版权

1.背景
MySQL Group Replication(MGR)是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案，以插件形式提供，实现了分布式下数据的最终一致性，总结MGR特点如下：
高一致性：基于分布式paxos协议实现组复制，保证数据一致性；
高容错性：自动检测机制，只要不是大多数节点都宕机就可以继续工作，内置防脑裂保护机制；
高扩展性：节点的增加与移除会自动更新组成员信息，新节点加入后，自动从其他节点同步增量数据，直到与其他节点数据一致；
高灵活性：提供单主模式和多主模式，单主模式在主库宕机后能够自动选主，所有写入都在主节点进行，多主模式支持多节点写入。

2.环境：
3台服务器搭建3节点MGR集群，MySQL版本8.0.16，操作系统版本CentOS 7.6。

https://www.cndba.cn/hbhe0316/article/4840

[root@mysql-master home]# cat /etc/hosts
192.168.2.119 mysql-master
192.168.2.183 mysql-slave1
192.168.2.148 mysql-slave2

3.安装步骤
3.1 安装MySQL8.0
MySQL官方版8.0.16，采用Linux Generic安装方式，直接下载压缩包，解压到目录/mysql目录下
3.2 修改配置文件
编辑配置文件 /home/mysql/etc/my_mgr_8.0.cnf，3个节点除了server_id、loose-group_replication_local_address、report_host 三个参数不一样外，其他保持一致。

https://www.cndba.cn/hbhe0316/article/4840

#/etc/my_mgr_8.0.cnf
[mysqld]
port=3306
basedir=/usr/local/mysql8.0
datadir=/mysql/data_mgr_8.0/
socket=/mysql/data_mgr_8.0/mysql.sock
pid_file=/mysql/data_mgr_8.0/mysql.pid

server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "192.168.56.101:33061"
loose-group_replication_group_seeds= "192.168.56.101:33061,192.168.56.102:33061,192.168.56.103:33061"
loose-group_replication_bootstrap_group=OFF

report_host=192.168.2.119
report_port=3306

3.3 初始化数据库(所有节点执行)https://www.cndba.cn/hbhe0316/article/4840

useradd mysql
mkdir -p /mysql/data_mgr_8.0
sudo chown -R mysql.mysql /mysql/data_mgr_8.0/
sudo chown -R mysql.mysql /home/mysql/etc/
/usr/local/mysql8.0/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql8.0 --datadir=/mysql/data_mgr_8.0 --user=mysql

3.4 启动数据库，安装MGR插件，设置复制账号(所有节点执行)
启动数据库

su - mysql -c "/usr/local/mysql8.0/bin/mysqld_safe --defaults-file=/home/mysql/etc/my_mgr_8.0.cnf &"

登录数据库

/usr/local/mysql8.0/bin/mysql -S /mysql/data_mgr_8.0/mysql.sock

安装MGR插件https://www.cndba.cn/hbhe0316/article/4840

mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';

设置复制账号

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl';
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

3.5 启动MGR单主模式
启动MGR，在主库(192.168.56.101)上执行

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

查看MGR组信息https://www.cndba.cn/hbhe0316/article/4840

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8cb3f19b-8414-11e8-9d34-fa163eda7360 | 192.168.56.101 |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
+---------------------------+--------------------------------------+------------------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)

其他节点加入MGR，在从库(192.168.56.102，192.168.56.103)上执行

mysql> START GROUP_REPLICATION;

查看MGR组信息

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 5254cf46-8415-11e8-af09-fa163eab3dcf | 192.168.56.102 |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
| group_replication_applier | 601a4025-8415-11e8-b2b6-fa163e767b9a | 192.168.56.103 |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
| group_replication_applier | 8cb3f19b-8414-11e8-9d34-fa163eda7360 | 192.168.56.101 |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

可以看到，3个节点状态为online，并且主节点为192.168.56.101，只有主节点可以写入，其他节点只读，MGR单主模式搭建成功。
3.6 切换到多主模式
MGR切换模式需要重新启动组复制，因些需要在所有节点上先关闭组复制，设置 group_replication_single_primary_mode=OFF 等参数，再启动组复制。
停止组复制(所有节点执行)：https://www.cndba.cn/hbhe0316/article/4840

mysql> stop group_replication;
mysql> set global group_replication_single_primary_mode=OFF;
mysql> set global group_replication_enforce_update_everywhere_checks=ON;

随便选择某个节点执行

mysql> SET GLOBAL group_replication_bootstrap_group=ON; 
mysql> START GROUP_REPLICATION; 
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

其他节点执行

mysql> START GROUP_REPLICATION;

查看组信息，所有节点的 MEMBER_ROLE 都为 PRIMARY

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 5254cf46-8415-11e8-af09-fa163eab3dcf | 192.168.56.102 |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | 601a4025-8415-11e8-b2b6-fa163e767b9a | 192.168.56.103 |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | 8cb3f19b-8414-11e8-9d34-fa163eda7360 | 192.168.56.101 |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

可以看到所有节点状态都是online，角色都是PRIMARY，MGR多主模式搭建成功。

3.7 切回单主模式
所有节点执行

mysql> stop group_replication;
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
mysql> set global group_replication_single_primary_mode=ON;

主节点(192.168.56.101)执行https://www.cndba.cn/hbhe0316/article/4840https://www.cndba.cn/hbhe0316/article/4840

SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;

从节点(192.168.56.102、192.168.56.103)执行

START GROUP_REPLICATION;

查看MGR组信息

https://www.cndba.cn/hbhe0316/article/4840

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 5254cf46-8415-11e8-af09-fa163eab3dcf | 192.168.56.102 |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
| group_replication_applier | 601a4025-8415-11e8-b2b6-fa163e767b9a | 192.168.56.103 |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
| group_replication_applier | 8cb3f19b-8414-11e8-9d34-fa163eda7360 | 192.168.56.101 |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

MYSQL