数据库守护者：揭秘MySQL组复制的高可用魔法

mysql高可用之组复制(MGR)(数据库守护者：揭秘MySQL组复制的高可用魔法)

什么是MySQL Group Replication？

MySQL Group Replication是一个基于组通信的复制解决方案，它允许将多个MySQL实例组织成一个组，在该组内进行事务的一致性复制。这样可以确保即使某个实例发生故障，其他实例仍然可以继续提供服务，从而提高系统的整体可用性。

主要特性

• 多主复制：允许任何节点生成更新，并将这些更新传播到组中的所有其他成员。
• 自动故障检测和恢复：系统可以自动检测并隔离失败的成员，而不会影响到整个复制组的运行。
• 一致性保证：通过使用Paxos算法变体来确保事务的一致性，即使在网络分区的情况下也能保持数据的完整性。
• 动态组管理：可以在不停机的情况下添加或移除组成员。
• 可配置的同步级别：可以根据业务需求调整同步模式，以权衡性能和数据一致性之间的关系。
• 读负载均衡：客户端可以通过连接到不同的只读副本上，从而分散读操作的压力。

工作原理

• 事务提交：在一个成员上提交的事务会被记录为一个变更集（Change Set），然后广播给组内的其他成员。
• 变更应用：每个成员接收到变更集后，会按照相同的顺序应用这些变更，从而保持数据的一致性。
• 冲突解决：如果同一事务在不同成员上执行了不同的操作，系统会自动检测并解决这些冲突。
• 组通信：利用GCS（Group Communication System）来实现成员间的高效通信。

应用场景

• 高可用性：通过自动故障切换和数据冗余来提高数据库的可用性。
• 容错性：即使部分节点失效，也可以继续提供服务。
• 扩展性：支持水平扩展，通过增加更多的只读副本提升读取性能。

配置与部署

部署MySQL Group Replication需要设置一个包含多个成员的复制组，并且需要配置每个实例的角色以及它们之间如何通信。此外，还需要监控组的状态和健康状况，以确保正常运行。

通过这些特性，MySQL Group Replication为需要高度可靠和高性能数据库服务的应用程序提供了一个强大的工具。

具体实现

master主机操作

# 删除掉mysql目录下的所有文件，进行重新初始化
[root@master ~]# rm -fr /data/mysql/*

# 编写主配置文件
[root@master ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server_id=10
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.254.10:33061"
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061"
group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1

[root@master mysql]# /etc/init.d/mysqld start
[root@master mysql]# mysqld --user=mysql --initialize
2024-09-02T12:01:06.435315Z 1 [Note] A temporary password is generated for root@localhost: Clut10dN-hHP

[root@master mysql]# mysql -uroot -p'Clut10dN-hHP'
mysql> alter user root@localhost identified by '123456';
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> 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 MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='lee' FOR CHANNEL \
    -> 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql>  SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.06 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 |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | 08beab6b-6923-11ef-af94-000c29d8aa47 | master.shanxin.org |        3306 | ONLINE       |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
1 row in set (0.01 sec)

# 将主配置文件传输至slave1和slave2主机
[root@master mysql]# scp /etc/my.cnf [email protected]:/etc/my.cnf
my.cnf                                                                100%  915     1.4MB/s   00:00    
[root@master mysql]#  scp /etc/my.cnf [email protected]:/etc/my.cnf
my.cnf                                                                100%  915     1.2MB/s   00:00    

# 将三台主机的域名解析都做好，因为之后的数据同步是通过域名来同步的				
[root@master mysql]# vim /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
172.25.254.10 master.shanxin.org
172.25.254.20 slave1.shanxin.org
172.25.254.30 slave2.shanxin.org

[root@master mysql]# scp /etc/hosts [email protected]:/etc/hosts
hosts                                                                 100%  259   329.6KB/s   00:00    
[root@master mysql]# scp /etc/hosts [email protected]:/etc/hosts
hosts                                                                 100%  259   414.7KB/s   00:00    

slave1主机操作

[root@slave1 ~]# rm -fr /data/mysql/*

[root@slave1 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server_id=20 # 这里修改
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.254.20:33061" # 将这里修改为slave1主机的IP
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061"
group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1

[root@slave1 ~]# /etc/init.d/mysqld start

[root@slave1 ~]# mysqld --user=mysql --initialize
2024-09-02T12:11:03.221575Z 1 [Note] A temporary password is generated for root@localhost: zh!s%p0_hH%w

[root@slave1 ~]# mysql -uroot -p'zh!s%p0_hH%w'
mysql> alter user root@localhost identified by '123456';
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> 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 MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='lee' FOR CHANNEL \
    -> 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST        | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | 08beab6b-6923-11ef-af94-000c29d8aa47 | master.shanxin.org |        3306 | ONLINE       |
| group_replication_applier | 6c70230d-6924-11ef-9068-000c29d56bdc | slave1.shanxin.org |        3306 | RECOVERING   |
+---------------------------+--------------------------------------+-----------------------+
2 rows in set (0.00 sec)

slave2主机操作

[root@slave2 ~]# rm -fr /data/mysql/*
[root@slave2 ~]# vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server_id=30
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.254.30:33061"
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061"
group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1

[root@slave2 ~]# mysqld --user=mysql --initialize
2024-09-02T12:25:43.389278Z 1 [Note] A temporary password is generated for root@localhost: =lD7E?ijReAP

[root@slave2 ~]# /etc/init.d/mysqld start

[root@slave2 ~]# mysql -uroot -p'=lD7E?ijReAP'
mysql> alter user root@localhost identified by '123456';
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>  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 MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='lee' FOR CHANNEL \
    -> 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (3.18 sec)

mysql>  SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST        | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | 08beab6b-6923-11ef-af94-000c29d8aa47 | master.shanxin.org |        3306 | ONLINE       |
| group_replication_applier | 6c70230d-6924-11ef-9068-000c29d56bdc | slave1.shanxin.org |        3306 | ONLINE       |
| group_replication_applier | 790c40f0-6926-11ef-a6bf-000c290abe65 | slave2.shanxin.org |        3306 | ONLINE       |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
3 rows in set (0.00 sec)

在三台主机中写入数据，看数据是否可以同步

# master主机
[root@master mysql]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>  CREATE DATABASE lee;
Query OK, 1 row affected (0.00 sec)

mysql>  CREATE TABLE lee.userlist(
    -> username VARCHAR(10) PRIMARY KEY NOT NULL,
    ->  password VARCHAR(50) NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO lee.userlist VALUES ('user1','111');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.00 sec)

# slave1主机
mysql> INSERT INTO lee.userlist values ('user2','222');
Query OK, 1 row affected (0.00 sec)

mysql> select * from lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
+----------+----------+
2 rows in set (0.00 sec)

# slave2主机操作

mysql> INSERT INTO lee.userlist values ('user3','333');
Query OK, 1 row affected (0.00 sec)

mysql> select * from lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
| user3    | 333      |
+----------+----------+
3 rows in set (0.00 sec)