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
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:
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:
|
<<: Implementing a simple Gobang game with native JavaScript
>>: Detailed installation and use of docker-compose
Recommend
Java imports data from excel into mysql
Sometimes in our actual work, we need to import d...
Detailed explanation of creating, calling and managing MySQL stored procedures
Table of contents Introduction to stored procedur...
MySQL scheduled task implementation and usage examples
This article uses examples to illustrate the impl...
Example of how to display a default image when an image does not exist in HTML
An image link <img src="" /> I wa...
Docker View Process, Memory, and Cup Consumption
Docker view process, memory, cup consumption Star...
CSS to achieve scrolling image bar example code
On some websites, you can often see some pictures...
Index in MySQL
Preface Let's get straight to the point. The ...
Example of using swiper plugin to implement carousel in Vue
Table of contents vue - Use swiper plugin to impl...
CentOs7 64-bit MySQL 5.6.40 source code installation process
1. Install the dependency packages first to avoid...
How to view and set the mysql time zone
1. Check the database time zone show variables li...
Use pure CSS to achieve scroll shadow effect
To get straight to the point, there is a very com...
Vue ElementUI Form form validation
Form validation is one of the most commonly used ...
How to implement https with nginx and openssl
If the server data is not encrypted and authentic...
Detailed explanation of the differences between SQL joint query inner join, outer join and cross join
When developing applications that use a database,...
Web Design: Web Music Implementation Techniques
<br />When inserting music into a web page, ...