How to implement MySQL bidirectional backup

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:

  1. MySQL in Linux disables the grant_priv permission for the root@% user, so authorization fails if you log in remotely.
  2. 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:
  • How to implement MySQL bidirectional synchronous backup under Windows

<<:  Implementing a simple Gobang game with native JavaScript

>>:  Detailed installation and use of docker-compose

Recommend

HTML table tag tutorial (26): cell tag

The attributes of the <TD> tag are used to ...

How to use vite to build vue3 application

1. Installation Tip: There is currently no offici...

Solve the installation problem of mysql8.0.19 winx64 version

MySQL is an open source, small relational databas...

Difference between HTML ReadOnly and Enabled

The TextBox with the ReadOnly attribute will be di...

How to install Windows Server 2008 R2 on Dell R720 server

Note: All pictures in this article are collected ...

Simple analysis of EffectList in React

Table of contents EffectList Collection EffectLis...

How to remove spaces or specified characters in a string in Shell

There are many methods on the Internet that, alth...

How to make your browser talk with JavaScript

Table of contents 1. The simplest example 2. Cust...

Docker Detailed Illustrations

1. Introduction to Docker 1.1 Virtualization 1.1....

Detailed explanation of two points to note in vue3: setup

Table of contents In vue2 In vue3 Notes on setup ...

CentOS 6.5 configuration ssh key-free login to execute pssh command explanation

1. Check and install pssh, yum list pssh 2. Becau...

Solution to Incorrect string value in MySQL

Many friends will report the following error when...

JavaScript array merging case study

Method 1: var a = [1,2,3]; var b=[4,5] a = a.conc...

How to set up automatic daily database backup in Linux

This article takes Centos7.6 system and Oracle11g...