Last_Errno: 1062，Last_Error: Error Duplicate entry

线上环境我从来没有碰到过1062的问题，测试环境开发环境不停的出现类似问题，以往为了赶时间都是skip或者直接重新做，这会有时间，就好好去查查问题所在

1 从库报错信息：

mysql> show slave status\g
*************************** 1. row ***************************
               slave_io_state: waiting for master to send event
                  master_host: xxxx0402.china.online.ea.com
                  master_user: replication
                  master_port: 3306
                connect_retry: 60
              master_log_file: mysql-bin.000154
          read_master_log_pos: 56680675
               relay_log_file: mysql-relay-bin.000455
                relay_log_pos: 33013454
        relay_master_log_file: mysql-bin.000152
             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: 1062
                   last_error: error 'duplicate entry '250.1.1-rding-changelogs/myidentity/250/xxxx.xml' for key 'primary'' on query. default database: 'identity'. query: 'insert into `databasechangelog` (`dateexecuted`, `author`, `xxxx`, `description`, `comments`, `md5sum`, `id`, `filename`) values (now(), 'rding', '1.9.3', 'custom sql', '', '4ac9fbf5222bc344362ccdecbc072', '250.1.1', 'changelogs/myidentity/250/xxxx.xml')'
                 skip_counter: 0
          exec_master_log_pos: 33013308
              relay_log_space: 33020134
              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
master_ssl_verify_server_cert: no
                last_io_errno: 0
                last_io_error:
               last_sql_errno: 1062
               last_sql_error: error 'duplicate entry '250.1.1-rding-changelogs/myidentity/250/xxxx.xml' for key 'primary'' on query. default database: 'identity'. query: 'insert into `databasechangelog` (`dateexecuted`, `author`, `xxxx`, `description`, `comments`, `md5sum`, `id`, `filename`) values (now(), 'rding', '1.9.3', 'custom sql', '', '4ac9fbf5222bc344362ccdecbc072', '250.1.1', 'changelogs/myidentity/250/xxxx.xml')'
  replicate_ignore_server_ids:
             master_server_id: 1
1 row in set (0.00 sec)

2 看表结构

mysql> show create table databasechangelog;
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table             | create table                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| databasechangelog | create table `databasechangelog` (
  `id` varchar(63) not null,
  `author` varchar(63) not null,
  `filename` varchar(200) not null,
  `dateexecuted` datetime not null,
  `md5sum` varchar(32) default null,
  `description` varchar(255) default null,
  `comments` varchar(255) default null,
  `tag` varchar(255) default null,
  `xxxx` varchar(10) default null,
  primary key (`id`,`author`,`filename`) 坑爹的表设计结构，不是我喜欢的风格
) engine=innodb default charset=utf8 |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3 看已经存在的数据

mysql> select * from databasechangelog  where author ='rding';
+---------+--------+-----------------------------------------+---------------------+----------------------------------+-------------+------------------------------------------+------+-----------+
| id      | author | filename                                | dateexecuted        | md5sum                           | description | comments                                 | tag  | xxxx |
+---------+--------+-----------------------------------------+---------------------+----------------------------------+-------------+------------------------------------------+------+-----------+
| 250.1.1 | rding  | changelogs/myidentity/250/xxxx.xml | 2013-08-12 20:41:22 | 4ac9fbf5222bc344362ccdecbc072    | custom sql  |                                          | null | 1.9.3     |
| 250.1.2 | rding  | changelogs/myidentity/250/xxxx.xml | 2013-08-12 20:41:22 | 8463e1cf4ba029e3ace675d3e69a71d2 | custom sql  | create new table for email change record | null | 1.9.3     |
+---------+--------+-----------------------------------------+---------------------+----------------------------------+-------------+------------------------------------------+------+-----------+
2 rows in set (0.00 sec)

4 看binlog，在relay_master_log_file: mysql-bin.000152，去主库找这个binlog解析出来

解析
[root@xxxx0402 tmp]# mysqlbinlog mysql-bin.000152 > a152.log
搜索包含'rding'字符串的语句，因为这个是主键字段之一，所以检索起来应该比较容易。
[root@xxxx0402 tmp]# grep a152.log 'rding' > rd.log
grep: rding: no such file or directory
[root@xxxx0402 tmp]# grep  'rding' a152.log > rd.log
[root@xxxx0402 tmp]# ll

[root@xxxx0402 tmp]# more rd.log
insert into `databasechangelog` (`dateexecuted`, `author`, `xxxx`, `description`, `comments`, `md5sum`, `id`, `filename`) values (now(), 'rding', '1.9.3', 'custom
 sql', '', '4ac9fbf5222bc344362ccdecbc072', '250.1.1', 'changelogs/myidentity/250/xxxx.xml')
insert into `databasechangelog` (`dateexecuted`, `author`, `xxxx`, `description`, `comments`, `md5sum`, `id`, `filename`) values (now(), 'rding', '1.9.3', 'custom
 sql', 'create new table for email change record', '8463e1cf4ba029e3ace675d3e69a71d2', '250.1.2', 'changelogs/myidentity/250/xxxx.xml')
主库上面只有一条insert sql语句。

5 再去看从库的relay log日志relay_log_file: mysql-relay-bin.000455

[root@eanshlt2mydbc004db002 data]# cp mysql-relay-bin.000455 /tmp
[root@eanshlt2mydbc004db002 data]# cd /tmp
[root@eanshlt2mydbc004db002 tmp]# mysqlbinlog mysql-relay-bin.000455 > relay.log
[root@eanshlt2mydbc004db002 tmp]#
[root@eanshlt2mydbc004db002 tmp]# grep  'rding' relay.log > rd.log
[root@eanshlt2mydbc004db002 tmp]# more rd.log
insert into `databasechangelog` (`dateexecuted`, `author`, `xxxx`, `description`, `comments`, `md5sum`, `id`, `filename`) values (now(), 'rding', '1.9.3', 'custom
 sql', '', '4ac9fbf5222bc344362ccdecbc072', '250.1.1', 'changelogs/myidentity/250/xxxx.xml')
insert into `databasechangelog` (`dateexecuted`, `author`, `xxxx`, `description`, `comments`, `md5sum`, `id`, `filename`) values (now(), 'rding', '1.9.3', 'custom
 sql', 'create new table for email change record', '8463e1cf4ba029e3ace675d3e69a71d2', '250.1.2', 'changelogs/myidentity/250/xxxx.xml')

奇怪了，2边的都是一样子的。这个错误怎么判断？

6 去看下主从关于这个数据记录的录入时间。

从库上面：
mysql> select * from databasechangelog where author='rding' and id='250.1.1' and filename='changelogs/myidentity/250/xxxx.xml'\g;
*************************** 1. row ***************************
          id: 250.1.1
      author: rding
    filename: changelogs/myidentity/250/xxxx.xml
dateexecuted: 2013-08-12 20:41:22
      md5sum: 4ac9fbf5222bc344362ccdecbc072
 description: custom sql
    comments:
         tag: null
   xxxx: 1.9.3
1 row in set (0.00 sec)

error:
no query specified
mysql>

主库上面 ：
mysql> select * from databasechangelog where author='rding' and id='250.1.1' and filename='changelogs/myidentity/250/xxxx.xml'\g;
*************************** 1. row ***************************
          id: 250.1.1
      author: rding
    filename: changelogs/myidentity/250/xxxx.xml
dateexecuted: 2013-08-12 19:54:29
      md5sum: 4ac9fbf5222bc344362ccdecbc072
 description: custom sql
    comments:
         tag: null
   xxxx: 1.9.3
1 row in set (0.02 sec)
error:
no query specified
mysql>

看dateexecuted时间字段都是8月12日录入的，可惜我的db server由于磁盘有限，只保存了近期的binlog，而且现在主库上面最早的binlog就是出错的那个mysql-bin.000152

7 最后一招，去看从库的binlog，看是否近期有人insert了这条记录

[root@eanshlt2mydbc004db002 data]# cp mysql-bin.004* /tmp/
[root@eanshlt2mydbc004db002 tmp]# mysqlbinlog mysql-bin.004268 > 1.log
[root@eanshlt2mydbc004db002 tmp]# grep  'rding' 1.log > rd1.log
[root@eanshlt2mydbc004db002 tmp]# ll rd1.log
-rw-r--r-- 1 root root 0 sep  3 17:47 rd1.log
空的，第一个日志没有录入操作

[root@eanshlt2mydbc004db002 tmp]# mysqlbinlog mysql-bin.004269 > 2.log
[root@eanshlt2mydbc004db002 tmp]# grep  'rding' 2.log > rd2.log
[root@eanshlt2mydbc004db002 tmp]# ll rd2.log
-rw-r--r-- 1 root root 0 sep  3 17:48 rd2.log
[root@eanshlt2mydbc004db002 tmp]#
空的，第二个日志没有录入操作

[root@eanshlt2mydbc004db002 tmp]# mysqlbinlog mysql-bin.004270 > 3.log
[root@eanshlt2mydbc004db002 tmp]# grep  'rding'  3.log > rd3.log
[root@eanshlt2mydbc004db002 tmp]# ll rd3.log
-rw-r--r-- 1 root root 0 sep  3 17:49 rd3.log
[root@eanshlt2mydbc004db002 tmp]#
空的，第三个日志没有录入操作

[root@eanshlt2mydbc004db002 tmp]# mysqlbinlog mysql-bin.004271 > 4.log
error: error in log_event::read_log_event(): 'read error', data_len: 438, event_type: 2
[root@eanshlt2mydbc004db002 tmp]# grep  'rding' 4.log > rd4.log
[root@eanshlt2mydbc004db002 tmp]# ll rd4.log
-rw-r--r-- 1 root root 0 sep  3 17:50 rd4.log
空的，第四个日志没有录入操作
这里解析报错了，解决方案，记录在如此


[root@eanshlt2mydbc004db002 tmp]# mysqlbinlog mysql-bin.004272 > 5.log
[root@eanshlt2mydbc004db002 tmp]# grep  'rding' 5.log > rd5.log
[root@eanshlt2mydbc004db002 tmp]# ll rd5.log
-rw-r--r-- 1 root root 0 sep  3 18:07 rd5.log
[root@eanshlt2mydbc004db002 tmp]#
空的，第五个日志没有录入操作

解析了从库的5个日志，都没有看到这条纪律的insert操作，问题到此卡住了，原因何在？这边开发的兄弟们已经在催了，我只要skip之后从库重新做了。

mysql> stop slave;
set global sql_slave_skip_counter=1;
start slave;
show slave status\g
query ok, 0 rows affected (0.09 sec)

mysql> set global sql_slave_skip_counter=1;
query ok, 0 rows affected (0.00 sec)

mysql> start slave;
query ok, 0 rows affected (0.00 sec)
mysql> show slave status\g
*************************** 1. row ***************************
               slave_io_state: waiting for master to send event
                  master_host: xxxx0402.china.online.ea.com
                  master_user: replication
                  master_port: 3306
                connect_retry: 60
              master_log_file: mysql-bin.000184
          read_master_log_pos: 27865900
               relay_log_file: mysql-relay-bin.000495
                relay_log_pos: 253
        relay_master_log_file: mysql-bin.000171
             slave_io_running: yes
            slave_sql_running: yes
              replicate_do_db:
          replicate_ignore_db:
           replicate_do_table:
       replicate_ignore_table:
      replicate_wild_do_table:
  replicate_wild_ignore_table:
                   last_errno: 0
                   last_error:
                 skip_counter: 0
          exec_master_log_pos: 107
              relay_log_space: 8000
              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: 3434734
master_ssl_verify_server_cert: no
                last_io_errno: 0
                last_io_error:
               last_sql_errno: 0
               last_sql_error:
  replicate_ignore_server_ids:
             master_server_id: 1
1 row in set (0.11 sec)
mysql>