Introduction to MySQL two-way backup method (with code)

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

##masterB5.6.41192.168.1.202

MySQL Server	Version	IP Address
masterA	5.6.41	192.168.1.201

Note:

The backup MySQL server version should be kept consistent as much as possible. Different versions may have binary log formats. Not compatible.

Specific operation

Attention

During the operation, pay attention to the consistency of the data on both sides! ! !

masterA configuration

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 configuration

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

Create backup user

masterA & 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
1. root@% usergrant_privPermissions, so if it is a remote login, authorization failure will occur
The backup user account and password here may not be consistent, here to simplify the operation, use the same account and password

Restart the server

Restart the server

Enable backup

masterA

View masterB status

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;</rep_password></master_port></rep_username></master_hostname>

View the results

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

masterB

Reverse the operation of masterA

Test

Insert data into masterA and masterB respectively, and check whether the expected data appears on the other server in time

Problem

MySQL Slave Failed to Open the Relay Log

This should be a problem with the relay log. You can try the following operations

stop slave;
flush logs;
start slave;

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

When 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;

.

Use . for insert, update and delete operations, no backup will be performed (this is a huge pit) )! ! ! [Related recommendations:

MySQL Tutorial]

The above is the detailed content of Introduction to MySQL two-way backup method (with code). For more information, please follow other related articles on the PHP Chinese website!