How to implement MySQL bidirectional backup

MySQL bidirectional backup is also called master-master backup, that is, both MySQL services are Master, and either service is a Slave of the other service.

Prepare

server

MySQL Server	Version	IP address
masterA	5.6.41	192.168.1.201
masterB	5.6.41	192.168.1.202

Note: The backed-up MySQL server version should be kept consistent as different versions may have incompatible binary log formats.

Specific operations

Notice

Pay attention to the consistency of data on both sides during the operation! ! !

masterA configuration

my.cnf

[mysqld]
# Server unique identifier server-id=1
# Binary log file name log-bin=mysql-bin

# Databases that need to be backed up, multiple databases are separated by , binlog-do-db=piumnl
# Databases that need to be replicated, multiple databases are separated by replicate-do-db=piumnl
# Relay log file name relay_log=mysqld-relay-bin
# Manually start the synchronization service to avoid data log asynchronization caused by sudden downtime skip-slave-start=ON
# For mutual master-slave, you need to add this line log-slave-updates=ON
# Disable symbolic links to prevent security risks. You don't need to add symbolic-links=0

# Optional# 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

#Optional# Disabling DNS resolution will invalidate the domain name used during authorization skip-host-cache
skip-name-resolve

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

masterB configuration

my.cnf

# No longer explain each configuration item [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 a backup user

Both masterA and masterB need to create a backup user:

create user 'rep'@'%' identified by 'rep'; # Create an account grant replication slave on *.* to 'rep'@'%'; # Grant the account master-slave backup permissions for any table in any database

Remark:

MySQL in Linux disables the grant_priv permission for the root@% user, so authorization fails if you log in remotely.
The backup user account and password here can be inconsistent. In order to simplify the operation, the same account and password are used here.

Restart the server

Restart the server

Enable backup

masterA

Check the status of masterB

show master status\G;
# Here you need to pay attention to the File and Position values

Enable backup

stop slave;

# master_log_file is the File value of the first step operation # master_log_pos is the Position value of the first step operation 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 Results

show slave status\G;
# Check the two most important items. Both must be Yes. If one is No, check the error log file to see where the problem is. # Slave_IO_Running: Yes
# Slave_SQL_Running: Yes

masterB

Repeat the operation of masterA in reverse

test

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

question

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 logs from the main database, it was found that the first file in the mysql_bin.index file of the main database did not exist.

# Perform the following operations to reset # If the binary log or relay log has other functions, do not perform the following operations reset master;
reset slave;
flush logs;

<database>.<table>

Using <database>.<table> for insert, update and delete operations will not be backed up (this is a huge pitfall)! ! !

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in: