Realization Method of MySQL Bidirectional Backup

MySQL bi-directional backup is also called master-master backup, that is, both MySQL services are Master, and any one service is Slave of the other.

Prepare

Server

MySQL服务器	版本	IP地址
masterA	5.6.41	192.168.1.201
masterB	5.6.41	192.168.1.202

Note: The backup version of MySQL server should be kept as 1 as possible. Different versions may have incompatible binary log formats.

Specific operation

Attention

Pay attention to the data on both sides in the operation process! ! !

masterA Configuration

my.cnf

[mysqld]
#  Server only 1 Identification 
server-id=1
# 2 Binary log file name 
log-bin=mysql-bin

#  Database that needs to be backed up, and multiple databases use  ,  Separation 
binlog-do-db=piumnl
#  Database that needs to be replicated, and multiple databases use  ,  Separation 
replicate-do-db=piumnl
#  Relay log file name 
relay_log=mysqld-relay-bin
#  Start synchronization service manually to avoid data log asynchronization caused by sudden downtime 
skip-slave-start=ON
#  Mutual master and slave need to join this 1 Row 
log-slave-updates=ON
#  Disable symbolic links to prevent security risks, but do not add 
symbolic-links=0

#  Don't add 
# 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

#  Don't add 
#  Disable  dns  Parsing will invalidate the domain name used when authorizing 
skip-host-cache
skip-name-resolve

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

masterB Configuration

my.cnf

#  Individual configuration items are no longer explained 
[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 users

masterA & masterB creates backup users:

create user 'rep'@'%' identified by 'rep';  #  Create 1 Account 
grant replication slave on *.* to 'rep'@'%'; #  Grant this account master-slave backup rights to any table in any database 

Remarks:

Under Linux, MySQL has closed grant_priv permission for root @% user, so if it is remote login, authorization will fail Here, backup user account and password can not be 1, here in order to simplify the operation, use 1 kind of account and password

Restart the server

Restart the server

Turn on backup

masterA

View masterB status

show master status\G;
#  Attention needs to be paid here  File  And  Position  Value 

Turn on backup

stop slave;

# master_log_file  Is the first 1 Step-operated  File  Value 
# master_log_pos  Is the first 1 Step-operated  Position  Value 
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;
#  View the two most important items, both of which must be  Yes  , there are 1 A Wei  No  Check the error log file to see where there is a problem 
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes

masterB

Repeat the operation of masterA in reverse

Test

Insert data in masterA and masterB, respectively, and see if 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. Try the following

stop slave;
flush logs;
start slave;

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

When you pull the log from the main library, you find that the first file in the mysql_bin. index file of the main library does not exist.

#  Reset by doing the following 
#  If 2 Binary log or relay log has other functions. Please do not do the following operations 
reset master;
reset slave;
flush logs;

< database > . < table >

Use < database > . < table > Do insert, update and delete operations, and there will be no backup (this is a giant pit)! ! !