日期: 2017 年 3 月 5 日

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