主从MySQL复制过程零星崩溃

我想知道是否有人遇到过这个问题，也许可以提供一些有关这个问题的见解。

我们有一个计划 – 香草MySQL主从复制设置。 这些表是MyISAM和主可以得到相当读/写活跃。 我们使用从实例来执行完整的每日备份，以避免closures主服务器。 备份过程执行以下操作：

STOP SLAVE SQL_THREAD mysqlhotcopy all tables START SLAVE SQL_THREAD 

每隔一段时间（一个月左右一次），复制就会打破各种错误消息，指出损坏的查询或日志文件。 这是昨晚发生的一件事：

 mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: server8 Master_User: nexus8 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000045 Read_Master_Log_Pos: 581644327 Relay_Log_File: relay.000086 Relay_Log_Pos: 94131 Relay_Master_Log_File: bin.000045 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: 1064 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '138070603'£' at line 1' on query. Default database: 'wtsdb'. Query: 'UPDATE fill SET clearing_fee='0.0E id='138070603'£' Skip_Counter: 0 Exec_Master_Log_Pos: 4164743 Relay_Log_Space: 577574251 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 

我按照以下步骤从上面的错误中恢复并恢复复制：

• MySQL转储，在使用–extended-insert的同时在新行上输出每个表行
• 在MySQL中的表别名（或'符号链接'）
• 通过“SSH隧道”访问外部的MySQL服务器 – 任何缺点？
• 如何诊断远程mysql数据库连接？
• IP表修改，允许在CENTOS 5服务器上访问mysql

 stop slave; change master to MASTER_LOG_POS = 4164743, MASTER_LOG_FILE = 'bin.000045'; start slave; 

我们有多个服务器以这种方式设置，他们都零星地停止复制与类似的错误。 任何build议如何解决这将不胜感激。

更新：

阅读由pQd （特别是第3.5节） build议的优秀文章 ，似乎很有可能，我遇到中断日志损坏，可以“使语句不可parsing”。 显然这是可以发生的事情，但会指出MySQL复制代码中的错误。 我不能真正重现这个问题，但是对于我们的生产环境中的每个奴隶来说，每个月至less会发生一次。

这里有两个更多的错误供参考：

 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: server10 Master_User: nexus10 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000008 Read_Master_Log_Pos: 1034654844 Relay_Log_File: relay.001392 Relay_Log_Pos: 109929 Relay_Master_Log_File: bin.000008 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: 1064 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versio n for the right syntax to use near '' at line 1' on query. Default database: 'avidb'. Query: 'DELETE FROM reconciliat' Skip_Counter: 0 Exec_Master_Log_Pos: 1026067551 Relay_Log_Space: 8697758 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 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: server8 Master_User: nexus8 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000019 Read_Master_Log_Pos: 476189428 Relay_Log_File: relay.000163 Relay_Log_Pos: 98576 Relay_Master_Log_File: bin.000019 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: 1064 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: 'wtsdb'. Query: 'UPDATE requestlog SET id='2173589',start_time='2009-08-18 04:59:10',end_time='2009-08-18 04:59:10',ip='XXXX',session='hkacn7d54',lock_wait_time='0',unmarshal_time='0.00057435035705566',unmarshaled_objects='30',data_sync_time='0',page_process_time='0',memory_used='1572864',memory_used_peak='1572864',method='GET',request='/report.php',request_parameters=' groupId = -4 startDate = 2009-08-17 reportType = trades accountId = 2409 endDate = 2009-08-17',progress_percent='0',progress_text='' WHERE id='2173588'' Skip_Counter: 0 Exec_Master_Log_Pos: 186172755 Relay_Log_Space: 290116589 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 

 STOP SLAVE; [correct and execute whatever statement is the problem] SET GLOBAL sql_slave_skip_counter = 1; START SLAVE;