mysql+5.7+error+2000,MySQL 5.7.18 主从复制 Error1205

最新推荐文章于 2024-04-18 16:55:39 发布

豆小娘

最新推荐文章于 2024-04-18 16:55:39 发布

阅读量186

点赞数

文章标签： mysql+5.7+error+2000

从库报 error 1205

1.mysql报错信息

[[email protected](35.102) ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 17236634

Server version: 5.7.18-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[email protected] 09:11: [(none)]> show slave status \G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.35.101

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: slave1-bin.000032

Read_Master_Log_Pos: 239784353

Relay_Log_File: slave2-relay-bin.090191

Relay_Log_Pos: 1931045

Relay_Master_Log_File: slave1-bin.000032

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: 1205

Last_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.

Skip_Counter: 0

Exec_Master_Log_Pos: 218623234

Relay_Log_Space: 23099318

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: 1205

Last_SQL_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: c6bf07ac-45f9-11e7-a93f-b8ca3a60c558

Master_Info_File: /r2/mysqldata/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: 180330 15:26:09

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: c6bf07ac-45f9-11e7-a93f-b8ca3a60c558:2594841-4737183

Executed_Gtid_Set: a975c1f6-45f9-11e7-8385-b8ca3a60cc28:1-71460,

c6bf07ac-45f9-11e7-a93f-b8ca3a60c558:1-4707081

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

2.日志

[[email protected](35.102) ~]# tail -150 /r2/mysqldata/error.log

2018-03-30T15:24:34.877355+08:00 15454325 [Warning] Slave SQL for channel '': Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'homed_iusm'. Query: 'UPDATE account_info set last_login_time=FROM_UNIXTIME(1522394668), last_used_device=1400357731, f_extend='{"EXTEND":"","LIP":"192.168.15.164","WORK_EXTEND":{"areaid":null,"departmentname":"粤BZ2000-a1523-13685926554_粤BZ2298-a1523-13685926554_粤B91278-a1523-15012993006_粤BZ2000-a1523-15012993006_粤BZ2000-a1523-15012993006_粤BZ2000-a1523-15012993006"},"monitor_areaid":"440300"}

' WHERE DA=50000106', Error_code: 1205

2018-03-30T15:24:40.878439+08:00 15454325 [Warning] Slave SQL for channel '': Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'homed_iusm'. Query: 'UPDATE account_info set last_login_time=FROM_UNIXTIME(1522394668), last_used_device=1400357731, f_extend='{"EXTEND":"","LIP":"192.168.15.164","WORK_EXTEND":{"areaid":null,"departmentname":"粤BZ2000-a1523-13685926554_粤BZ2298-a1523-13685926554_粤B91278-a1523-15012993006_粤BZ2000-a1523-15012993006_粤BZ2000-a1523-15012993006_粤BZ2000-a1523-15012993006"},"monitor_areaid":"440300"}

' WHERE DA=50000106', Error_code: 1205

......

2018-03-30T15:26:09.912743+08:00 15454325 [ERROR] Slave SQL for channel '': Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable. Error_code: 1205

2018-03-30T15:26:09.912755+08:00 15454325 [Warning] Slave: Lock wait timeout exceeded; try restarting transaction Error_code: 1205

2018-03-30T15:26:09.912763+08:00 15454325 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'slave1-bin.000032' position 218623234

3,排错

参数innodb_lock_wait_time 设置的为15秒，单个事务在等待15秒后开始报1025错：因为锁执行超时并重启事务

参数slave_transaction_retries 设置的为10次，如果事务重试次数超过10次，复制中断

问题SQL

Default database: 'homed_iusm'. Query: 'UPDATE account_info set last_login_time=FROM_UNIXTIME(1522394668), last_used_device=1400357731, f_extend='{"EXTEND":"","LIP":"192.168.15.164","WORK_EXTEND":{"areaid":null,"departmentname":"粤BZ2000-a1523-13685926554_粤BZ2298-a1523-13685926554_粤B91278-a1523-15012993006_粤BZ2000-a1523-15012993006_粤BZ2000-a1523-15012993006_粤BZ2000-a1523-15012993006"},"monitor_areaid":"440300"}

解决 start slave

2018-04-02T09:15:23.733243+08:00 17236884 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'slave1-bin.000032' atposition 218623234, relay log './slave2-relay-bin.090191' position: 1931045

[[email protected](35.102) ~]#