MySQL自动同步

1、MySQL 双向备份的实现方法

这篇文章主要介绍了MySQL 双向备份的实现方法，即两个 MySQL 服务都是 Master，其中任意一个服务又是另一个服务的 Slave，感兴趣的可以了解一下

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

1.1、准备

服务器

MySQL服务器	版本	IP地址
masterA	5.6 ~ 8.2	192.168.1.201
masterB	5.6 ~ 8.2	192.168.1.202

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

1.2、具体操作

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

1.2.1、配置文件修改

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

1.2.2、创建备份用户

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

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

备注：

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

1.2.3、重启服务器

重启服务器

1.2.3、开启备份

1.2.3.1、masterA

查看 masterB 状态

show master status;
# 此处需要关注 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

全部代码示例（我用到的代码拷贝过来的）：

show master status;
stop slave;

# master_log_file 就是第一步操作的 File 值
# master_log_pos 就是第一步操作的 Position 值
change master to master_host='192.168.31.142', master_user='root', master_port=3306, master_password='123456', master_log_file='mysql-bin.000003', master_log_pos=2329;
start slave;

show slave status;
# 查看最重要的两项，两个都必须为 Yes ，有一个为 No 都要去查看错误日志文件，看看什么地方存在问题
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes

1.2.3.2、masterB

反向重复 masterA 的操作

1.2.4、测试

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

2、常见错误

2.1、Mysql主从同步时Slave_IO_Running：Connecting ; Slave_SQL_Running：Yes的情况故障排除

前几天在测试主从服务器Mysql同步时遇到了从数据库显示Slave_IO_Running：Connecting； Slave_SQL_Running：Yes的问题。

下面列举几种可能的错误原因：
1.网络不通
2.账户密码错误
3.防火墙
4.mysql配置文件问题
5.连接服务器时语法
6.主服务器mysql权限

我的服务器ip：
主服务器ubuntu：192.168.16.105
从服务器ubuntu：192.168.16.115

逐项排除：

2.1.1、因为从服务器是虚拟机，网卡选择了桥接模式，ip地址确认在同一网段中，且互ping能通，排除网络问题。

2.1.2、主服务器创建了账号slave密码slave的权限账号，在主服务器可以登录slave帐号，排除帐号密码问题。

2.1.3、终端输入 sudo ufw disable 关闭防火墙

sudo ufw status查看防火墙，确认已关闭，排除防火墙原因。（sudo ufw enable打开防火墙）

2.1.4、配置文件

（1）查看主服务器配置文件：sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf，看第83行：确认log_bin和server-id已经取消注释，设置无错误。
在这里插入图片描述
（2）查看从服务器：
在这里插入图片描述
此处server-id只需要与主服务器不同即可，我设置为ip的末尾数字115。确认配置无错误，排除。

2.1.5、语法：

（1）查看主服务器二进制日志信息show master status;
在这里插入图片描述
（2）设置从服务器链接到master主服务器，确认语句无错误，排除。
注：
master_host：主服务器Ubuntu的ip地址
master_log_file: 前面查询到的主服务器日志文件名
master_log_pos: 前面查询到的主服务器日志文件位置

change master to master_host=‘192.168.16.105’, master_user=‘slave’, master_password=‘slave’,master_log_file=‘mysql-bin.000011’, master_log_pos=154;

2.1.6、权限：

(1)主服务器查看slave帐号，已设置登录ip为%
在这里插入图片描述
(2)在从服务器上登录主服务器slave帐号测试：
在这里插入图片描述
无法登录主服务器！打开主服务器配置文件发现43行有一条绑定主机ip的语句，问题出在这里！！！给它注释掉！保存退出。重启主服务器数据库：sudo service mysql restart
在这里插入图片描述
(3)重新测试从服务器上登录主服务器slave帐号：登录成功！
(4)重新进入从服务器链接master主服务器，start slave开启主从同步，输入show slave status \G查看从服务器状态，若开启不成功先stop slave，再start slave，可以看到Slave_IO_Runninghe和Slave_SQL_Running状态都是Yes，同步成功。

2.2、MySQL Slave Failed to Open the Relay Log

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

stop slave;
flush logs;
start slave;

2.3、Got fatal error 1236 from master when reading data from binary log

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

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

2.4、 <database>.<table>

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

3、可能感兴趣的文章:

windows下mysql双向同步备份实现方法