mysql 恢复同步_mysql 从数据库同步异常后，恢复同步

本人主要分享在mysql主从同步过程中，从数据库因为SQL命令执行异常导致该异常之后的sql都无法进行同步了。

先看从库同步状态

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: #主库ip

Master_User: th2class

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000017

Read_Master_Log_Pos: 88974903

Relay_Log_File: 1-relay-bin.000002

Relay_Log_Pos: 88344166

Relay_Master_Log_File: mysql-bin.000017

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1062

Last_Error: Error 'xxx错误信息xxxx'

Skip_Counter: 0

Exec_Master_Log_Pos: 88610079

Relay_Log_Space: 88709562

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1062

Last_SQL_Error: Error 'xxx错误信息xxxx'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 5

Master_UUID: e9013703-6e44-11ea-ac83-fa163e2c1732

Master_Info_File: /usr/local/mysql/var/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp: 200921 16:08:30

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

这时看到 Slave_SQL_Running=No 说明sql是不再执行同步的了，然后发现了一个error，具体问题需要具体分析，这个时候恢复同步可以尝试两种方案

方案1

先停止slave

mysql>stop slave;

跳过slave上的1个错误

mysql>set global sql_slave_skip_counter=1;

3.在slave上手工插入一条数据(如果不是插入错误，例如是更新错误等自行执行)

mysql>insert into ...

4.启动slave

mysql>start slave;

既可恢复同步

方案2

方案2就比较粗暴了，在数据量跟用户量不大的时候使用比较合适，毕竟对数据库杀伤力比较大

首先去到主库执行

FLUSH TABLES WITH READ LOCK;

对库进行FTWRL，这个时候主库上了全局读锁

查看主库状态以及导出主库数据

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000017

Position: 88970639

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

mysqldump --uroot -p -A -B --master-data=2 --single-transaction >> /home/1.sql

#把导出来的sql文件通过scp或者其他路径传输到从服务器中

将刚导出来的sql导入到从库中

#注意这里已经是在从库的shell端口中了

source /home/1.sql;

stop slave;

reset slave;

#重新同步

change master to master_host='主机IP',master_user='master',master_password='master_pwd',master_log_file='mysql-bin.45451',master_log_pos=23415631;

slave start;

然后查看下同步状态 show slave status\G

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: #主库ip

Master_User: th2class

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000017

Read_Master_Log_Pos: 88980289

Relay_Log_File: 1-relay-bin.000002

Relay_Log_Pos: 1552

Relay_Master_Log_File: mysql-bin.000017

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 88980289

Relay_Log_Space: 1755

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 5

Master_UUID: e9013703-6e44-11ea-ac83-fa163e2c1732

Master_Info_File: /usr/local/mysql/var/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

看到

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

则表明重新同步生效

思路就是 在主库查看主库的状态 ，查看当前的 File: mysql-bin 与 Position ，然后马上导出sql文件，并将sql文件传输到从服务器，让从服务器的数据库导入该文件，然后执行重新同步操作(即停止同步，重置同步，执行重置同步命令，开启同步即可)