MySQL 双向备份的实现方法

数据库  /  管理员 发布于 6年前   193

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: 0master-info-repository=tablerelay-log-info-repository=tablerelay-log-recovery=1# 可不加# 禁用 dns 解析，会使授权时使用的域名无效skip-host-cacheskip-name-resolvesql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

masterB 配置

my.cnf

# 不再解释各个配置项[mysqld]server-id=2log-bin=mysql-binbinlog-do-db=piumnlreplicate-do-db=piumnlrelay_log=mysql-relay-binskip-slave-start=ONlog-slave-updates=ONsymbolic-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: 0master-info-repository=tablerelay-log-info-repository=tablerelay-log-recovery=1skip-host-cacheskip-name-resolvesql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

创建备份用户

masterA & masterB 都要创建备份用户：

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

备注：

1. Linux 下 MySQL 对 root@% 用户关闭了 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> 进行插入、更新和删除操作，将不会进行备份（ 这是巨坑 ）！！！

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