Last_Errno: 1062，Last_Error: Error 'Duplicate entry '...' for key 'PRIMARY'' on query.的详细分析过程(二)
|
GELOG | 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
[ |