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