Online environment I have never encountered 1062 of the problem, the test environment development environment is constantly similar problems, in the past in order to catch the time is skip or directly redo, this will have time, just to check the problem
1 error message from library:
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 ', ' FI Lename ') 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 ', ' FI Lename ') 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 View Table Structure
Mysql> Show CREATE TABLE Databasechangelog;
+-------------------+------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------+
| table | Create table |
+-------------------+------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------+
| Databasechangelog | CREATE TABLE ' Databasechangelog ' (
' ID ' varchar () not NULL,
' AUTHOR ' varchar not NULL,
' FILENAME ' varchar not NULL,
' dateexecuted ' datetime not NULL,
' md5sum ' varchar DEFAULT NULL,
' DESCRIPTION ' varchar (255) DEFAULT NULL,
' COMMENTS ' varchar (255) DEFAULT NULL,
' TAG ' varchar (255) DEFAULT NULL,
' xxxx ' varchar (a) DEFAULT NULL,
PRIMARY key (' ID ', ' AUTHOR ', ' FILENAME ') the table design structure of the pit Dad, not my favorite style
) Engine=innodb DEFAULT Charset=utf8 |
+-------------------+------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------+
1 row in Set (0.00 sec)
3 Look at the data that already exists
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 See Binlog, in relay_master_log_file:mysql-bin.000152, go to the main library to find this Binlog parse out
Analytical
[root@xxxx0402 tmp]# mysqlbinlog mysql-bin.000152 > A152.log
Search for statements containing ' rding ' strings because this is one of the primary key fields, so it should be easier to retrieve them.
[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 ')
There is only one insert SQL statement above the main library.
5 to see the relay log logs from the library 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 ')
Strange, 2 sides of the same son. How do you judge this mistake?
6 to see the data recorded by the master and the time of entry.
From the top of the library:
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>
Main Library above:
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>
See dateexecuted Time fields are August 12 input, but my DB server due to limited disk, only save the recent binlog, and now the main library above the first binlog is the wrong one mysql-bin.000152
7 Last move, look at the Binlog from the library, see if someone recently inserts this record
[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 0 Sep 3 17:47 rd1.log
Empty, the first log has no input operation
[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 0 Sep 3 17:48 rd2.log
[Root@eanshlt2mydbc004db002 tmp]#
Empty, the second log has no input operation
[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 0 Se p 3 17:49 rd3.log
[root@eanshlt2mydbc004db002 tmp]#
is empty, and the third log has no input operation
[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 0 Sep 3 17:50 rd4.log
Empty, fourth log no entry operation
Here parse the error, the solution, recorded in this
Http://www.jb51.net/article/47089.htm
[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 0 Sep 3 18:07 rd5.log
[Root@eanshlt2mydbc004db002 tmp]#
Empty, fifth log no entry operation
Parsed from the library 5 logs, did not see this discipline insert operation, the problem to this stuck, why? The brothers who developed this side have been urging, I just skip after the library to redo.
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>