mysql复制同步1062错误修复

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

show slave status;

lave_IO_State Waiting for master to send event
Master_Host 192.168.0.99
Master_User xxx
Master_Port 3306
Connect_Retry 60
Master_Log_File mysql-bin.000059
Read_Master_Log_Pos 63986019
Relay_Log_File C1g-relay-bin.000417
Relay_Log_Pos 48985722
Relay_Master_Log_File mysql-bin.000059
Slave_IO_Running Yes
Slave_SQL_Running No
Replicate_Do_DB discuzx
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table discuzx.pre_common_admincp_session,discuzx.pre_common_session
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno 1062
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”
Skip_Counter 0
Exec_Master_Log_Pos 48985576
Relay_Log_Space 64152580
Until_Condition None
Until_Log_File

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

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”
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”
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无效

stop slave;
set global sql_slave_skip_counter =1 ;
start slave;

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

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

slave-skip-errors = 1062

重启mysql

/opt/mysql/bin/mysql.server restart

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

Last_Errno 1594
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;

Connect_Retry 60
Master_Log_File mysql-bin.000059
Read_Master_Log_Pos 89775799
Relay_Log_File C1g-relay-bin.000417
Relay_Log_Pos 49139383
Relay_Master_Log_File mysql-bin.000059
Slave_IO_Running Yes
Slave_SQL_Running No
Replicate_Do_DB discuzx
Replicate_Ig

Skip_Counter 1
Exec_Master_Log_Pos 49139237
Relay_Log_Space 89942788
Until_Condition None
Until_Log_File
Until_Log_Pos 0
Master_SSL_Allowed

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

Relay_Master_Log_File mysql-bin.000059
Exec_Master_Log_Pos 49139237

stop slave;
change master to Master_Log_File=’mysql-bin.000059′, Master_Log_Pos=49139237;
start slave;

show slave status;

Slave_IO_Running Yes
Slave_SQL_Running Yes

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

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