mysql复制同步1062错误修复

Percona-Server-5.5.22-rel25.2
其中一台从库同步discuzx时出错,Slave_SQL_Running为no

show slave status;

  1. lave_IO_State Waiting for master to send event
  2. Master_Host 192.168.0.99
  3. Master_User xxx
  4. Master_Port 3306
  5. Connect_Retry 60
  6. Master_Log_File mysql-bin.000059
  7. Read_Master_Log_Pos 63986019
  8. Relay_Log_File C1g-relay-bin.000417
  9. Relay_Log_Pos 48985722
  10. Relay_Master_Log_File mysql-bin.000059
  11. Slave_IO_Running Yes
  12. Slave_SQL_Running No
  13. Replicate_Do_DB discuzx
  14. Replicate_Ignore_DB
  15. Replicate_Do_Table
  16. Replicate_Ignore_Table discuzx.pre_common_admincp_session,discuzx.pre_common_session
  17. Replicate_Wild_Do_Table
  18. Replicate_Wild_Ignore_Table
  19. Last_Errno 1062
  20. Last_Error Error ‘Duplicate entry ‘1503760-25’ for key ‘PRIMARY” on query. Default database: ‘discuzx’. Query: ‘INSERT INTO pre_forum_post SET `fid`=’456′ , `tid`=’1553760′ , `first`=’0′ , `author`=’xxx’ , `authorid`=’4018205′ , `subject`=” , `dateline`=’1349931502′ , `message`=’xxx’ , `useip`=’61.165.2.2′ , `invisible`=’0′ , `anonymous`=’0′ , `usesig`=’1′ , `htmlon`=’0′ , `bbcodeoff`=0 , `smileyoff`=’-1′ , `parseurloff`=0 , `attachment`=’0′ , `status`=’0′ , `pid`=’12202748”
  21. Skip_Counter 0
  22. Exec_Master_Log_Pos 48985576
  23. Relay_Log_Space 64152580
  24. Until_Condition None
  25. Until_Log_File

先尝试修复表pre_forum_post,再删除1503760-25主键记录,start slave;
依然有很表错误

  1. Last_Error Error ‘Duplicate entry ‘3064092’ for key ‘PRIMARY” on query. Default database: ‘discuzx’. Query: ‘INSERT INTO pre_common_member_action_log SET `uid`=’4818285′ , `action`=’1’ , `dateline`=’1349931502”
  2. Last_Error Error ‘Duplicate entry ‘2327049’ for key ‘PRIMARY” on query. Default database: ‘discuzx’. Query: ‘INSERT INTO pre_home_notification SET `uid`=’4047895′ , `type`=’post’ , `new`=’1′ , `authorid`=’4818285′ , `author`=’xxx’ , `note`=’xxx’ , `dateline`=’1349931502′ , `from_id`=’12002748′ , `from_idtype`=’quote’ , `from_num`=’1”
  3. last_Error Error ‘Duplicate entry ‘3409334’ for key ‘PRIMARY” on query. Default database: ‘discuzx’. Query: ‘INSERT INTO pre_common_credit_rule_log SET `uid`=’4082252′ , `rid`=’5′ , `fid`=’0′ , `total`=’1′ , `cyclenum`=’1′ , `dateline`=’1349931503′ , `extcredits2`=’-2”

尝试sql_slave_skip_counter无效

  1. stop slave;
  2. set global sql_slave_skip_counter =1 ;
  3. start slave;

使用命令批量修复数据表
不停机 mysqlcheck -A -o -r -p
或停机用myisamchk

在my.cnf中忽略所有1062错误

  1. slave-skip-errors = 1062

重启mysql

  1. /opt/mysql/bin/mysql.server restart

会出现找不到Relay log日志的错误

  1. Last_Errno 1594
  2. Last_Error Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave.

show slave status;

  1. Connect_Retry 60
  2. Master_Log_File mysql-bin.000059
  3. Read_Master_Log_Pos 89775799
  4. Relay_Log_File C1g-relay-bin.000417
  5. Relay_Log_Pos 49139383
  6. Relay_Master_Log_File mysql-bin.000059
  7. Slave_IO_Running Yes
  8. Slave_SQL_Running No
  9. Replicate_Do_DB discuzx
  10. Replicate_Ig
  11. Skip_Counter 1
  12. Exec_Master_Log_Pos 49139237
  13. Relay_Log_Space 89942788
  14. Until_Condition None
  15. Until_Log_File
  16. Until_Log_Pos 0
  17. Master_SSL_Allowed

记录下面两个参数,重新设定记录点

  1. Relay_Master_Log_File mysql-bin.000059
  2. Exec_Master_Log_Pos 49139237
  1. stop slave;
  2. change master to Master_Log_File=’mysql-bin.000059′, Master_Log_Pos=49139237;
  3. start slave;

show slave status;

  1. Slave_IO_Running Yes
  2. Slave_SQL_Running Yes

再观察其它从库和master的记录点,没有问题,修复成功

参考:

http://www.cnblogs.com/niniwzw/archive/2010/02/04/1663685.html

此条目发表在服务器相关分类目录,贴了标签。将固定链接加入收藏夹。