Two ways to solve the problem of MySQL master-slave database not being synchronized

Two ways to solve the problem of MySQL master-slave database not being synchronized

During the work process, it was found that the master and slave databases of Mysql were not synchronized

1. Asynchronous situation

Master library:

mysql>show processlist;

Check whether the process sleeps too much. Found it normal.

show master status;

That's normal.

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)

Then check on Slave

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

It can be seen that the Slave is out of sync

2. Solution

Here are two solutions

Method 1: Ignore the error and continue syncing

This method is suitable for situations where the data of the master and slave databases are not very different, or the data can be not completely unified, and the data requirements are not strict.

solve:

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

Then use mysql> show slave status\G to view

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

OK, now the master-slave synchronization status is normal. . .

Method 2: Re-do master-slave and fully synchronize

This method is suitable for situations where the data of the master and slave databases differ greatly, or when the data needs to be completely unified.

The solution steps are as follows:

1. Enter the main database first and lock the table to prevent data from being written

Use command:

mysql> flush tables with read lock;

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

2. Perform data backup

#Back up the data to the mysql.bak.sql file

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

One thing to note here: database backup must be performed regularly. You can use shell scripts or python scripts, which are more convenient and 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 status

mysql> stop slave;

6. Then execute the mysql command from the database to import the data backup

mysql> source /tmp/mysql.bak.sql

7. Set up slave synchronization

When setting up slave synchronization, pay attention to the synchronization point, which is the | File | Position item in the show master status information of the master database.

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. Check the synchronization status

mysql> show slave status\G  

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

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

UNLOCK TABLES;

The above are the details of two methods to solve the problem of master-slave synchronization of MySQL database. For more information on solving the problem of master-slave database synchronization of MySQL, please pay attention to other related articles on 123WORDPRESS.COM!