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