MySQL8.0.12设置主主同步

配置MySQL主主同步,可以做到在A服务器上修改数据同步到B服务器上,同样,在B服务器上修改数据同步到服务器A上。主主同步与主从同步类似,只不过主主同步,两个服务器同时具有主、从服务器的身份,互相同步数据。
本文中,MySQL服务器版本为8.0.12,所有数据库的引擎均为InnoDB。为保证同步成功,两个服务器需使用同一个版本的MySQL。

一、环境描述

数据库版本:8.0.12,系统版本CentOS 7
服务器A:192.168.100.1,开放端口3306,同步账号repl
服务器B:192.168.100.2,开放端口3306,同步账号repl

二、my.cnf配置文件

[mysqld]
user				= mysql #mysql使用的用户名
basedir				= /usr/local/mysql #mysql安装目录
datadir				= /usr/local/mysql/data #mysql数据目录
port				= 3306 #监听端口
socket				= /usr/local/mysql/mysql.sock #sock文件位置
bind_address			= 0.0.0.0 #监听IP,此处不能用127.0.0.1因为需要外部机器访问
default_authentication_plugin 	= mysql_native_password

server-id = 2 #server-id,必须是大于0的一个整数,两台服务器不能设置一样,一般一个设1,另一个设2就可以
log_bin				= mysql-bin #二进制日志名,最终会在/usr/local/mysql/data/中以mysql-bin.000001这种文件名形式存在,扩展名是一个编号
binlog_format			= mixed #混合型日志格式,具体含义可自行查询
binlog_expire_logs_seconds	= 604800 #日志过期时间
sync-binlog			= 1
innodb_flush_log_at_trx_commit	= 1

# Master DB #
#需要同步的数据库用多个binlog-do-db,不要写在一行用逗号分隔,这种情况可能会导致数据库同步状态一切正常,就是同步不了,日志也没有任何问题,根据实验发现,如果写成binlog-do-db = database1,database2在使用show master status以及show slave status时与下面设置方式完全一样,但就是无法同步
binlog-do-db			= database1 #需要同步的数据库
binlog-do-db			= database2 #需要同步的数据库
#由于两个服务器都可以写数据,为了防止自增的key冲突,需要设置下面两个,increment是每次增长数,两台服务器设为2即可,offset是起始偏移,一个服务器设置为1,另一个设置为2,这样自增id在服务器A上是1、3、5这样,在服务器B上就是2、4、6不会产生冲突
auto-increment-increment	= 2
auto-increment-offset		= 2

# Slave DB #
replicate-do-db			= database1 #需要从其他服务器同步过来的数据库名
replicate-do-db			= database2
relay_log			= relay-bin #中继日志名称

配置完两个服务器后,分别用service mysql restart重启服务器

三、新增同步账号

这一步不是必须的,但建议为同步数据库的账号启用一个专门的账号,这里使用repl
首先在A服务器上创建账号并添加权限:

mysql> CREATE USER 'repl'@'192.168.100.2' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.100.2';

B服务器上类似,只是ip不同:

mysql> CREATE USER 'repl'@'192.168.100.1' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.100.1';

四、添加iptables规则
服务器A:

iptables -A INPUT -s 192.168.100.2/32 -p tcp -m tcp --dport 3306 -j ACCEPT

服务器B:

iptables -A INPUT -s 192.168.100.1/32 -p tcp -m tcp --dport 3306 -j ACCEPT

五、配置主从服务器信息

服务器A中执行:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS\G

结果类似下面:

*********************** 1. row ***************************
             File: mysql-bin.000001
         Position: 64353552
     Binlog_Do_DB: database1,database2
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

记下结果中的file和position,到服务器B上设置:

mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.1',MASTER_USER='repl',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=64353552;

其中MASTER为主服务器的IP,这里就是对方服务器的IP,MASTER_USER和MASTER_PASSWORD为刚刚创建的同步账号用户名和密码,MASTER_LOG_FILE为对方服务器上查到的日志文件名,MASTER_LOG_POS为对方服务器上查到的日志位置。
同理,在B服务器上执行 SHOW MASTER STATUS 将查询结果中的文件名,和当前位置信息配置到服务器A上

六、同步初始化数据库

假设服务器A是原始服务器,一般在双向同步前,会将两个数据库手动复制一次,否则两个库会出现不一致。

服务器A上执行导出操作,以下是导出所有的数据库,导出单个库将–all-databases改为–databases 数据库1 数据库2 …

mysqldump -p --all-databases > backup.sql

将backup.sql复制到服务器B上,并执行:

mysql -p < backup.sql

输入B服务器的数据库root账户的密码后即可导入备份的数据库

七、开启slave
两个服务器分别执行:

mysql> start slave;

八、测试开启状态
两服务器分别执行:

mysql> show slave status\G

观察以下两个值是否为Yes:Slave_IO_Running Slave_SQL_Running
在服务器A的指定数据库内,更改数据观察服务器B上是否同步;同理,B上更改数据也会同步到A上

九、注意事项
1. 注意需要开放防火墙,以免服务器无法互相通信,建议只为对应的服务器IP开放端口访问,以提高安全性
2. 需要同步多个数据库,请设置多个binlog-do-db,否则可能会出现数据库日志已同步,但数据库内容不同步的问题(之前遇到过多个库用逗号分隔,写在一个binlog-do-db字段中,结果既没有报错也不会同步的问题,怀疑是将一行当做了一个整体)
3. 处于安全考虑,建议为同步账号创建独立的账号,并使用不同的密码,因为同步账号的密码会以明文的形式保存在mysql数据库的slave_master_info表中

官方文档:https://dev.mysql.com/doc/refman/8.0/en/replication.html