企业运维--mysql多组复制和主从切换_三台机器,一主两从,同时开启组复制,为什么都变成primary了-CSDN博客

本文链接：https://blog.csdn.net/m0_62885194/article/details/121983698

本文介绍了MySQL多组复制的配置，确保在一台服务器故障时，其他服务器仍能保持数据同步。详细阐述了MHA（Master High Availability）高可用架构的搭建，包括手动和自动主从切换过程，确保在数据库故障时实现无缝切换，维持业务连续性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql多组复制：

组复制必须保证三台数据库完全一致，否则会失败
server1\server2\server3三台主机进行多组复制，当其中一台down掉后，其余两台依旧可以正常同步数据

server1：


[root@server1 ~]# cd /data/mysql/
[root@server1 mysql]# rm -fr *        ###删除初始mysql数据
[root@server1 mysql]# vim /etc/my.cnf    ###编辑配置文件
***********************
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
symbolic-links=0
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid         ###pid之前不用改

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server-id=1      ###主机id为1（server2和server3和1的不一样）
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="server1:33061"    ###主机名和端口
group_replication_group_seeds="server1:33061,server2:33061,server3:33061"   ###组员
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.8.0/24,127.0.0.1/8"     ###通讯网段
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON    ###不检测
**********************

[root@server1 mysql]# mysqld --initialize --user=mysql     ###初始化
[root@server1 mysql]# cat mysqld.log     ###查看日志中的默认密码
[root@server1 mysql]# /etc/init.d/mysqld start    ##开启mysql
[root@server1 mysql]# mysql -p
Enter password: 
mysql> alter user root@localhost identified by 'westos';    ###修改密码为westos
mysql> show databases;
mysql> SET SQL_LOG_BIN=0;     ###关闭日志同步

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';    ###建立用户
 
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';    ###设置权限

mysql> FLUSH PRIVILEGES;    ###刷新

mysql> SET SQL_LOG_BIN=1;   ###开启日志同步

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';     

mysql> SET GLOBAL group_replication_bootstrap_group=ON;    ##设置组    关于组设置只有server1需要

mysql> START GROUP_REPLICATION;       ###开启组加入

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;   ###关闭组复制
 
mysql> SELECT * FROM performance_schema.replication_group_members;--->ONLINE  ###查看组成员及状态为ONLINE

server2:


[root@server2 ~]# cd /data/mysql/
[root@server2 mysql]# rm -fr *
[root@server2 mysql]# vim /etc/my.cnf
********************************
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server-id=2       ###server2的id为2
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="server2:33061"     ###server2
group_replication_group_seeds="server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.8.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON

group_replication_allow_local_disjoint_gtids_join=1    ###如果加入组时数据不一致，则删除多余部分，强制加入
****************************