MySQL master slave synchronization slave_ sql_ No is running

Time:2020-11-27

background

The master-slave was built before, but the read-write separation was not set, and the slave database can also write data. So I want to test the synchronization caused by writing data from the library. It was found that slave_ sql_ Running is no, Slava_ IO_ Running is still yes

reason

Since the slave database writes data, the master-slave data is inconsistent. If the same data is written in the master database and the slave database, the SQL thread will be terminated. Check the MySQL error log as follows:

2020-08-01T10:58:19.623077Z 135 [ERROR] Slave SQL for channel '': Could not execute Write_rows event on table shy_dep.zp_test; , Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 882496, Error_code: 1062
2020-08-01T10:58:19.623101Z 135 [Warning] Slave: Error_code: 1062
2020-08-01T10:58:19.623110Z 135 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000001' position 882218

Solution 1

  1. Stop the slave synchronization and execute stop slave;
  2. The main database executes show master status and records the values of file and position
  3. The slave database is reconnected according to the position position of the master database for synchronization
CHANGE MASTER TO master_host = '192.168.164.84',
MASTER_PORT = 3306,
master_user = 'root',
master_password = 'root',
master_log_file = 'mysql-bin.000001',
master_ log_ POS = 902262; ා the latest position of master is recorded here
  1. Start synchronization from the database and start slave;

The above steps can be re synchronized from the main step.

Here, it is assumed that the master library is not synchronized when it is restarted. Because if a write operation is performed, the position of the main database just recorded may change.

Therefore, it is generally necessary to temporarily lock the main database to prevent writing.

Solution 2

Execute the following command in the slave Library:

stop slave;

set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

start slave;

SHOW SLAVE STATUS

After testing, the above methods can also be used.

Personal experience

There is a problem with solution one. For example, write a piece of data 11 in the slave database and a piece of data 12 in the master database. We know that the master-slave synchronization will lead to slave_ sql_ Running stopped. If the data 12 is deleted after reconnecting and starting by the first method, the following error will be reported:

2020-08-01T11:00:38.853703Z 17564 [ERROR] Slave SQL for channel '': Could not execute Delete_rows event on table shy_dep.zp_test; Unknown error 1032, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 883098, Error_code: 1032
2020-08-01T11:00:38.853717Z 17564 [Warning] Slave: Unknown error 1032 Error_code: 1032
2020-08-01T11:00:38.853721Z 17564 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000001' position 882828

The slave will be terminated because the record 12 cannot be found for deletion_ sql_ The running thread needs to be reconnected to the latest binlog location of the main database for synchronization.

The second solution is that even if the record 12 is deleted, it will still be synchronized. So the feeling here is that the second way is better.

set global sql_ slave_ skip_ Counter = n ා here n means to skip n events

Official explanation:

This statement skips the next N events from the master. This is useful for recovering from replication stops caused by a statement.

In my understanding, it is to skip the events that cannot be executed from the master

summary

  1. Here are two solutions to master-slave asynchronyThe second solutionBetter
  2. In fact, according to the truth, there is no master-slave synchronization, because the master-slave needs to match the read-write separation. Since the slave library can only read, there is no master-slave asynchronous situation.