MySQL8.0.14 配置主主同步

             

背景:

配置 MySQL 主主同步,可以做到在 A 服务器上修改数据同步到 B 服务器上,同样,在 B 服务器上修改数据同步到服务器 A 上。

主主同步与主从同步类似,只不过主主同步,两个服务器同时具有主、从服务器的身份,互相同步数据。

本文中,MySQL 服务器版本为 8.0.14,所有数据库的引擎均为 InnoDB。为保证同步成功,两个服务器需使用同一个版本的 MySQL。

一、环境描述

数据库版本:8.0.14,系统版本 CentOS 7.2
服务器 A:10.253.114.11,开放端口 3306,同步账号 repl
服务器 B:10.253.114.116,开放端口 3306,同步账号 repl

二、my.cnf 配置文件

# 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
server-id  = 11、116    #必须唯一,一般使用 ip 的后三位

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

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

三、新增同步账号

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

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

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

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

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

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

服务器 B:

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

五、配置主从服务器信息

服务器 A 中执行:(由于我是本地测试服务器,该命令没有执行)

mysql> FLUSH TABLES WITH READ LOCK; 

FLUSH TABLES WITH READ LOCK 简称 (FTWRL),该命令主要用于备份工具获取一致性备份 (数据与 binlog 位点匹配)。由于 FTWRL 总共需要持有两把全局的 MDL 锁,并且还需要关闭所有表对象,因此这个命令的杀伤性很大,执行命令时容易导致库 hang 住。如果是主库,则业务无法正常访问;如果是备库,则会导致 SQL 线程卡住,主备延迟。

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='10.253.114.11',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 表中

Slave_IO_Running 和 Slave_SQL_Running 必须都为 Yes 才表示成功。

首先因为 ip 写错导致失败。失败的原因一般有以下几点:
1、网络不通
2、防火墙
3、用户 密码 ip pos 等错误
4、Slave_IO_Running:NO 可能原因:帐号无权限操作


发表评论 请登录再评论
  • 文章分类
回到顶部