MySQL 双向备份的实现方法

mysql 双向备份也被称为 主主备份 ，即两个 mysql 服务都是 master，其中任意一个服务又是另一个服务的 slave。

准备

服务器

mysql服务器	版本	ip地址
mastera	5.6.41	192.168.1.201
masterb	5.6.41	192.168.1.202

注：备份的 mysql 服务器版本尽量保持一致，不同的版本可能二进制日志格式不兼容。

具体操作

注意

操作过程中注意两边数据的一致！！！

mastera 配置

my.cnf

[mysqld]
# 服务器唯一标识
server-id=1
# 二进制日志文件名
log-bin=mysql-bin

# 需要备份的数据库，多个数据库用 , 分隔
binlog-do-db=piumnl
# 需要复制的数据库，多个数据库用 , 分隔
replicate-do-db=piumnl
# 中继日志文件名
relay_log=mysqld-relay-bin
# 手动启动同步服务，避免突然宕机导致的数据日志不同步
skip-slave-start=on
# 互为主从需要加入这一行
log-slave-updates=on
# 禁用符号链接，防止安全风险，可不加
symbolic-links=0

# 可不加
# resolve - [warning] slave sql: if a crash happens this configuration does not guarantee that the relay log info will be consistent, error_code: 0
master-info-repository=table
relay-log-info-repository=table
relay-log-recovery=1

# 可不加
# 禁用 dns 解析，会使授权时使用的域名无效
skip-host-cache
skip-name-resolve

sql_mode=no_engine_substitution,strict_trans_tables

masterb 配置

my.cnf

# 不再解释各个配置项
[mysqld]
server-id=2
log-bin=mysql-bin

binlog-do-db=piumnl
replicate-do-db=piumnl
relay_log=mysql-relay-bin
skip-slave-start=on
log-slave-updates=on
symbolic-links=0

# resolve - [warning] slave sql: if a crash happens this configuration does not guarantee that the relay log info will be consistent, error_code: 0
master-info-repository=table
relay-log-info-repository=table
relay-log-recovery=1

skip-host-cache
skip-name-resolve

sql_mode=no_engine_substitution,strict_trans_tables

创建备份用户

mastera & masterb 都要创建备份用户：

create user 'rep'@'%' identified by 'rep';  # 创建一个账户
grant replication slave on *.* to 'rep'@'%'; # 授予该账户对任意数据库任意表的主从备份权限

备注：

1. linux 下 mysql 对 用户关闭了 grant_priv 权限，所以如果是远程登录会出现授权失败的情况
2. 此处备份用户帐号和密码可不一致，此处为了简化操作使用一样的帐号和密码

重启服务器

重启服务器

开启备份

mastera

查看 masterb 状态

show master status\g;
# 此处需要关注 file 和 position 值

开启备份

stop slave;

# master_log_file 就是第一步操作的 file 值
# master_log_pos 就是第一步操作的 position 值
change master to master_host=<master_hostname>, master_user=<rep_username>, master_port=<master_port>, master_password=<rep_password>, master_log_file='mysql-log.000003', master_log_pos=154;
start slave;

查看结果

show slave status\g;
# 查看最重要的两项，两个都必须为 yes ，有一个为 no 都要去查看错误日志文件，看看什么地方存在问题
# slave_io_running: yes
# slave_sql_running: yes

masterb

反向重复 mastera 的操作

测试

分别在 mastera 和 masterb 中插入数据，并查看另一台服务器是否及时出现预期的数据

问题

mysql slave failed to open the relay log

这应该是中继日志出现问题，可尝试如下操作

stop slave;
flush logs;
start slave;

got fatal error 1236 from master when reading data from binary log

从主库中拉取日志时，发现主库的 mysql_bin.index 文件中的第一个文件不存在。

# 进行如下操作重置
# 如果二进制日志或中继日志有其他作用，请勿进行如下操作
reset master;
reset slave;
flush logs;

<database>.<table>

使用 <database>.<table> 进行插入、更新和删除操作，将不会进行备份（ 这是巨坑 ）！！！

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。