开启binlog，且binlog-format=row

新建一个表

CREATE TABLE `student` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(10) NOT NULL DEFAULT '',   `class` int(10), `score` varchar(10) NOT NULL DEFAULT '', PRIMARY KEY (`id`));

插入测试数据

insert into student(name,class,score) values('a',1,56),('b',1,61),('c',2,78),('d',2,45),('e',3,76),('f',3,89),('g',4,43),('h',4,90);

模拟删除

mysql> delete from student;
Query OK, 8 rows affected (0.00 sec)
 
mysql> select * from student;
Empty set (0.00 sec)

- 阅读剩余部分 -

#!/bin/bash
 
########################################################################
##
##此脚本是用来自动处理同步报错的，默认跳过10次。
##
##只有Last_SQL_Error:Could not execute Delete_rows event on table ...;Can't find record ## in ...;Error_code:1032;handler error HA_ERR_KEY_NOT_FOUND;the event's master log ## bin-log.000001.end_log_pos ...
##这种情况才跳过，其他的情况自行处理，以免丢失数据。
##
#########################################################################
 
export LANG=zh_CN
. /root/.bash_profile
v_dir=/usr/local/mysql/bin/
v_user=root
v_password=
v_log=/home/logs/
v_time=10
 
if [ -d "${v_log}" ];then
    echo "${v_log} has existed before."
else
    mkdir ${v_log}
fi
 
echo "" > ${v_log}/slave_status.log
echo "" > ${v_log}/slave_status_error.log
count=1
while true
do
    Seconds_Behind_Master=$(${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" | awk -F':' '/Seconds_Behind_Master/{print $2}')
    if [ "$Seconds_Behind_Master" != "NULL" ];then
        echo "slave is ok!"
        ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" >> ${v_log}/slave_status.log
        break
    else
        echo "" >> ${v_log}/slave_status_error.log
        date >> ${v_log}/slave_status_error.log
        echo "" >> ${v_log}/slave_status_error.log
 
        ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" >> ${v_log}/slave_status_error.log
        ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" | egrep 'Delete_rows' > /dev/null 2>&1
        if [ $? = 0 ];then
            ${v_dir}/mysql -u${v_user} -p${v_password} -e "stop slave;set global sql_slave_skip_counter=1;start slave;"
        else
            ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" | grep 'Last_SQL_Error'
            break
        fi
 
        let count++
 
        if [ $count -gt "${v_times}" ];then
            break
        else
            ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" >> ${v_log}/slave_status_error.log
            sleep 2
            continue
        fi
    fi
done

主从数据不一致时，slave上已经有该条记录，但我们又在master上插入了同一条记录，此时就会报错，报错信息如下：

Last_SQL_Error:Could not execute Write_rows event on table ...;
Duplicater entry '2' for key 'PRIMARY',
Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;
the event's master log bin-log.000006, end_log_pos 924

解决方法：在slave上使用命令“desc abc.student;”先查看一下表结构，如下所示：

mysql> desc abc.student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     |         |                |
| class | int(10)          | YES  |     | NULL    |                |
| score | varchar(10)      | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

得到主键的字段名id，接着删除重复的主键，命令如下：

delete from student where id=2;

开启主从复制：

start slave;

show slave status\G;

主从数据不一致时，master上已经有该条记录，但slave上没有这条记录，之后若在master上又更新了这条记录，此时就会报错，报错信息如下：

Last_SQL_Error: Could not execure Update_rows event on table abc.student; Can't find record in 'student', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log bin-log.000006, end_log_pos 794

解决方法：在master上，用mysqlbinlog分析一下出错的binlog日志在干什么，如下所示：

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS bin-log.000006 | grep -A '10' 794

# at 794
#160323 17:10:36 server id 1  end_log_pos 8515214 CRC32 0x90b5f50c     Update_rows: table id 151 flags: STMT_END_F
### UPDATE `abc`.`student`
### WHERE
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
###   @2='h' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=4 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### SET
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
###   @2='h' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=4 /* INT meta=0 nullable=1 is_null=0 */
###   @4='90' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */

- 阅读剩余部分 -

当slave意外宕机，有可能会损坏中继日志relay-log，再次开启同步复制时，报错信息如下：

Last_SQL_Error: Error initializing relay log position: I/O error reading the header from the binary log
Last_SQL_Error: Error initializing relay log position: Binlog has had magic number;
It's not a binary log file that can be used by this version of mysql

解决方法：找到同步的binlog日志的POS，然后重新进行同步，这样就可以有新的中继日志了。

下面看个例子：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.110
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin-log.000006
          Read_Master_Log_Pos: 8517413
               Relay_Log_File: relay-bin.000009
                Relay_Log_Pos: 281
        Relay_Master_Log_File: bin-log.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: abc
          Replicate_Ignore_DB: information_schema,performance_schema,mysql,test
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1593
                   Last_Error: Last_SQL_Error: Error initializing relay log position: I/O error reading the header from the binary log
                 Skip_Counter: 1
          Exec_Master_Log_Pos: 8517121

主要关注的参数是：

Master_Log_File: bin-log.000006
Exec_Master_Log_Pos: 8517121

恢复命令如下：

mysql> change master to master_LOG_FILE='bin-log.000006', master_LOG_POS=8517121;

通过这种方法我们已经修复了中继日志。其实MySQL5.5已经考虑到了这个问题，新特性已经加入了自修复特性，在my.cnf加入 relay_log-recovery=1