A case-solving method for the asynchronous failure of Mysql master-slave library _mysql

So:

1. Create a temporary library in the main library and copy the table files that need to be imported

2. Implementation

Create Database tmpdb;

CREATE TABLE tmptable;

CP mysql_date_file master_data_file//shell command copies the datasheet file to the master Data_dir

INSERT INTO master.tmptable select * from Tmpdb.tmptable;

After execution, the data in the main library is imported normally

And look at slave status.

show slave status;

Error found: Not found tmpdb.tmptable (roughly meaning this, the original error message is not recorded)

In a hurry, watch show Master status Master_log_pos marked as $master_log_pos

Then change the MASTER to master_log_pos= $Master on the slave _log_pos

Then look at Show master status and see that there are 1162 errors

Now we're finding that the data on both sides can't sync.

。。。。。。

Brooding, do not redo the master library?

Mysqlbinlog, I suddenly thought of it.

So Mysqlbinlog--start-position=190000000--stop-position=200000000 xxx.binlog|grep tmptable

The SQL that executed the error on the slave was found

Mysqlbinlog--start-position=190000000--stop-position=200000000 xxx.binlog|grep tmptable >/tmp/tmpbinlog

Vi/tmp/tmpbinlog (Find tmptable)

Error SQL found next # at (a string of digits) marked as $next_pos

Change the MASTER to master_log_pos= $NEXT _pos on slave

Show slave status shows:

Slave_io_running:yes
Slave_sql_running:yes

Haha, complete the sync.

If you encounter 1062 errors in the middle of the slave configuration file set slave-skip-errors=1062, restart slave