Introduction to MySQL two-way backup method (with code)
- 不言forward
- 2019-04-11 11:52:333022browse
This article brings you an introduction to the MySQL two-way backup method (with code). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.
MySQL two-way backup is also called master-master backup, that is, both MySQL services are Master, and any one of them is the Slave of another service.
Preparation
Server
| MySQL Server | Version | IP Address |
|---|---|---|
| masterA | 5.6.41 | 192.168.1.201 |
| 5.6.41 | 192.168.1.202 |
The backup MySQL server version should be kept consistent as much as possible. Different versions may have binary log formats. Not compatible.
Specific operation
AttentionDuring the operation, pay attention to the consistency of the data on both sides! ! ! masterA configurationmy.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_TABLESmasterB configurationmy.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_TABLESCreate backup usermasterA & masterB both need to create a backup user:
create user 'rep'@'%' identified by 'rep'; # 创建一个账户 grant replication slave on *.* to 'rep'@'%'; # 授予该账户对任意数据库任意表的主从备份权限Note:
- MySQL under Linux is closed for the
- root@%
usergrant_privPermissions, so if it is a remote login, authorization failure will occurThe backup user account and password here may not be consistent, here to simplify the operation, use the same account and password
show master status\G; # 此处需要关注 File 和 Position 值Enable backup
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;View the results
show slave status\G; # 查看最重要的两项，两个都必须为 Yes ，有一个为 No 都要去查看错误日志文件，看看什么地方存在问题 # Slave_IO_Running: Yes # Slave_SQL_Running: YesmasterB
Reverse the operation of masterA
TestInsert data into masterA and masterB respectively, and check whether the expected data appears on the other server in time ProblemMySQL Slave Failed to Open the Relay LogThis should be a problem with the relay log. You can try the following operationsstop slave; flush logs; start slave;Got fatal error 1236 from master when reading data from binary logWhen pulling the log from the main library, it is found that the first file in the mysql_bin.index file of the main library does not exist.
# 进行如下操作重置 # 如果二进制日志或中继日志有其他作用，请勿进行如下操作 reset master; reset slave; flush logs;