收到报警,mysql的从数据库在同步的过程出现问题,已停止同步。
ERROR] Slave SQL: Error "Lock wait timeout exceeded; try restarting transaction" on query. Default database: "yang". Query: "UPDATE workitem SET status = state, modify_time=1329640301 where workitem_id = 800", Error_cod
Slave sql not running: result is No|slave_sql_running=0
root@127.0.0.1 : yang 16:48:34> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.249.119.52
Master_Log_File: mysql-bin.001156
Read_Master_Log_Pos: 49021182
Relay_Log_File: slave-relay.118531
Relay_Master_Log_File: mysql-bin.001156
Last_Error: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'yang'. Query: 'UPDATE workitem SET status = state, modify_time=1329640301 where workitem_id = 800'
Exec_Master_Log_Pos: 47702497
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_SQL_Error: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'yang'. Query: 'UPDATE work SET status = state, modify_time=1329640301 where work_id = 800'
从库由于 Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'yang'. Query: 'UPDATE work SET status = state, modify_time=1329640301 where work_id = 800'; 锁等待超时导致事务失败,多次重试不成功。 Slave_SQL_进程中断! mysql> show master status;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.001156 | 48291554 | | |
+------------------+-----------+--------------+------------------+
记录File和Position对应的值 mysql-bin.001156,48291554
> master_host='master_ip',
> master_log_file='mysql-bin.001156',
> master_log_pos=48291554;
注:这种办法可能会导致从服务器上的数据不完整,如从服务器一直出错,但主服务器日志文件一直在增加,过好长时间,再直接从主服务器上取日志位置,可能会造成错误期间的数据无法更新到从服务器中.这里建议采用下面的这种办法(将错误语句直接跳过).
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=N,用来跳过备机的一条或N条出错的复制语句。然后重新start slave即可。
由于我们的是双master 架构,所以为了避免重复执行变更的语句,实际操作:
root@127.0.0.1 : (none) 16:56:00> SET SQL_LOG_bin=0;
Query OK, 0 rows affected (0.00 sec)
root@127.0.0.1 : (none) 16:56:06> use yang;
root@127.0.0.1 : yang 16:56:10> UPDATE workitem SET status = state, modify_time=1329640301 where workitem_id = 800;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@127.0.0.1 : yang 16:56:45>stop slave;
Query OK, 0 rows affected (0.00 sec)
root@127.0.0.1 : yang 16:56:46>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
Query OK, 0 rows affected (0.00 sec)
root@127.0.0.1 : yang 16:56:46>start slave;
Query OK, 0 rows affected (0.00 sec)
root@127.0.0.1 : (none) 16:59:40> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.249.119.52
Master_Log_File: mysql-bin.001156
Read_Master_Log_Pos: 49846932
Relay_Log_File: slave-relay.118532
Relay_Master_Log_File: mysql-bin.001156
Exec_Master_Log_Pos: 49846932
Master_SSL_Verify_Server_Cert: No