MySQL master-slave data is inconsistent, prompt: Slave_SQL_Running: No solution

This article uses an example to describe the solution to the inconsistency between MySQL master and slave data, and the prompt: Slave_SQL_Running No. Share with you for your reference, the details are as follows:

On the slave server, run the following command

mysql> show slave status\G;

The following is displayed:

Slave_IO_Running: Yes
Slave_SQL_Running: No

Indicates that the slave is out of sync

Solution 1 (ignore the error and continue syncing):

1. Stop the slave first

mysql> stop slave;

2. Skip the wrong steps, and the following steps can be changed

mysql> set global sql_slave_skip_counter=1;

3. Restart slave

mysql> start slave;

4. Check the synchronization status

mysql> show slave status\G;

Solution 2 (re-do master-slave, complete synchronization):

1. Enter the main database first to lock the table, and be careful not to close the window

mysql> flush table with read lock;

2. Back up your data

> mysqldump -uroot -p --opt -R database > /data/bak.sql

3. Open a new window to view the main database information

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 1158 | | | |
+------------------+----------+--------------+------------------+-------------------+

4. Stop slave on the slave library

mysql> stop slave;

5. Import the backup data file

mysql> source /data/bak.sql

6. Reset Sync

mysql> reset slave;

7. Reset the synchronization node

mysql> CHANGE MASTER TO
MASTER_HOST='192.168.137.233',
MASTER_PORT=3306,
MASTER_USER='sync',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=1158;

Please modify host, port, user, and password according to your master database settings, and modify log_file and log_pos according to the master status in the master database.

8. Enable slave

mysql> start slave;

9. Check slave status

mysql> show slave status\G;

If the following information is displayed, it is normal

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

10. Unlock the master database

mysql> unlock tables;

11. Add or modify data in the master database again to see whether the slave database data is synchronized.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.