What if the master and slave databases of MySQL are not synchronized?

Time:2021-3-6

problem

Today, we found that the master-slave database of MySQL is not synchronized

Go to the master library first:

mysql>show processlist;

Check whether the next process has too many sleep. It’s normal.

show master status;

It’s normal, too.

mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 |     3260 |              | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

Check it out on slave

mysql> show slave status\G                                                
 
Slave_IO_Running: Yes
Slave_SQL_Running: No

It can be seen that the slave is not synchronized

Solution

Here are two solutions

Method 1: ignore the error and continue to synchronize

This method is suitable for the situation that the data of master and slave databases are not different, or the data can not be completely unified, and the data requirements are not strict

solve:

stop slave;
 
#Indicates a skip step error, the following number is variable
set global sql_slave_skip_counter =1;
start slave;

Then, use MySQL > show slave status to view it

mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

OK, now the master-slave synchronization state is normal…

Mode 2: to be master and slave again, completely synchronous

This method is suitable for the situation that the data of master and slave databases are quite different, or the data is required to be completely unified

The solution steps are as follows:

1. Enter the main database first and lock the table to prevent data writing

Use the command:

mysql> flush tables with read lock;

Note: This is locked as read-only, and the statement is not case sensitive

2. Data backup

\#Backup data to mysql.bak.sql file

mysqldump -uroot -p -hlocalhost > mysql.bak.sql

Here’s a point to note: database backup must be carried out on a regular basis. You can use shell script or Python script, which is more convenient to ensure that the data is safe.

3. Check the master status

mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 |     3260 |              | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

4. Transfer the MySQL backup file to the slave machine for data recovery

scp mysql.bak.sql [email protected]:/tmp/

5. Stop the slave library

mysql> stop slave;

6. Then go to the slave library and execute the MySQL command to import the data backup

mysql> source /tmp/mysql.bak.sql

7. Set the synchronization of the slave library. Pay attention to the synchronization point, which is the | file | position in the show master status information of the master library

change master to master_host = '192.168.128.100', master_user = 'rsync',  master_port=3306, master_password='', master_log_file =  'mysqld-bin.000001', master_log_pos=3260;

8. Restart the slave synchronization

mysql> start slave;

9. View synchronization status

mysql> show slave status\G  

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

10. Return to the main library and execute the following command to unlock the table.

UNLOCK TABLES;

Heavyweight benefits

WeChat search official account official, WeChat’s official account, pay attention to this deep programmer, read hard core technology dry cargo every day, reply to PDF, and I have prepared a big factory interview information and my original PDF technology document, and I have prepared many resume templates for you (constantly updated). I hope you can find your favorite job. Learning is a way of sometimes depressed and sometimes laughing. Come on. If you succeed in entering the company you like, don’t slack off. Career growth is the same as new technology learning. If you are lucky, I’ll see you in the world!

In addition, I open source PDF, I will continue to update and maintain, thank you for your long-term support to Glacier!!

Write at the end

If you think glacier‘s writing is good, please search on wechat and follow “Glacier TechnologyThe official account of WeChat is learning high concurrent, distributed, micro services, big data, Internet and cloud native technology with glaciers.Glacier TechnologyThe official account of WeChat has updated a lot of technical topics, and every technical article is full of dry cargo. Many readers have read theGlacier TechnologyThe official account of WeChat, who has been interlocking with the interviewer, has successfully switched to the big factory. Many readers have achieved technological leaps and become the backbone of the company. If you want to improve your ability, realize the leap of technical ability, enter a large factory, get a promotion and raise salary, then pay attention toGlacier TechnologyWeChat official account, updating the hard core technology dry cargo every day, so that you can’t lose your knowledge of how to improve the technical capability.

Recommended Today

Third party calls wechat payment interface

Step one: preparation 1. Wechat payment interface can only be called if the developer qualification has been authenticated on wechat open platform, so the first thing is to authenticate. It’s very simple, but wechat will charge 300 yuan for audit 2. Set payment directory Login wechat payment merchant platform( pay.weixin.qq . com) — > Product […]