MySQL 双向备份的实现方法

时间:2021-05-24

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'@'%'; # 授予该账户对任意数据库任意表的主从备份权限

备注:

  • Linux 下 MySQL 对 root@% 用户关闭了 grant_priv 权限,所以如果是远程登录会出现授权失败的情况
  • 此处备份用户帐号和密码可不一致,此处为了简化操作使用一样的帐号和密码
  • 重启服务器

    重启服务器

    开启备份

    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> 进行插入、更新和删除操作,将不会进行备份( 这是巨坑 )!!!

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

    声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。

    相关文章