here i have 1 master 2 slave, they use the same mysql version(5.5.25a-log percona version).
Then slave 2 break down, i used the bak files from slave 1 (using mysqldump), and then imported to slave 2
when imported complete, then set slave 2 to catch up master, but replication error happened.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.119
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000090
Read_Master_Log_Pos: 246003112
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 267860420
Relay_Master_Log_File: mysql-bin.000087
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: 1032
Last_Error: Could not execute Update_rows event on table kx_info.s_user_logo_7; Can't find record in 's_user_logo_7', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000087, end_log_pos 747388250
Skip_Counter: 0
Exec_Master_Log_Pos: 747387897
Relay_Log_Space: 2987705016
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: 1032
Last_SQL_Error: Could not execute Update_rows event on table kx_info.s_user_logo_7; Can't find record in 's_user_logo_7', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000087, end_log_pos 747388250
Replicate_Ignore_Server_Ids:
Master_Server_Id: 561193306
Then i used mysqlbinlog to check in master machine
/usr/local/mysql/bin/mysqlbinlog mysql-bin.000087 -vv --start-position=747387897 | less
# at 747387968
# at 747388042
#121009 6:45:57 server id 561193306 end_log_pos 747388042 Table_map: `kx_info`.`s_user_logo_7` mapped to number 253
#121009 6:45:57 server id 561193306 end_log_pos 747388250 Update_rows: table id 253 flags: STMT_END_F
BINLOG '
pVdzUBNaIXMhSgAAAIo8jCwAAP0AAAAAAAEAB2t4X2luZm8ADX NfdXNlcl9s b2dvXzcADgMDAwMD
AwQDDwMHBwMPBQQKACAAAAA=
pVdzUBhaIXMh0AAAAFo9jCwAAP0AAAAAAAEADv////8AwIeRFQNgUH01yQAA AC8AAAAAAAAAtAAA
AGZauT5RAQAABGpwZWdYAgAAHANzUBwDc1AAAAAAIDFjMWZhNT diYWQ4ZjJm ZDJlYjUyOWZlNWU0
YWE5ZjM1AMCHkRUDYFB9NckAAAAvAAAAAAAAALQAAABmWrk+UQ EAAARqcGVn WAIAABwDc1ClV3NQ
AQAAACAxYzFmYTU3YmFkOGYyZmQyZWI1MjlmZTVlNGFhOWYzNQ ==
'/*!*/;
### UPDATE kx_info.s_user_logo_7
### WHERE
### @1=51745159 /* INT meta=0 nullable=0 is_null=0 */
### @2=897405024 /* INT meta=0 nullable=0 is_null=0 */
### @3=201 /* INT meta=0 nullable=0 is_null=0 */
### @4=47 /* INT meta=0 nullable=0 is_null=0 */
### @5=0 /* INT meta=0 nullable=0 is_null=0 */
### @6=180 /* INT meta=0 nullable=0 is_null=0 */
### @7=0.362018 /* FLOAT meta=4 nullable=0 is_null=0 */
### @8=337 /* INT meta=0 nullable=0 is_null=0 */
### @9='jpeg' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
### @10=600 /* INT meta=0 nullable=0 is_null=0 */
### @11=1349714716 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### @12=1349714716 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### @13=0 /* INT meta=0 nullable=0 is_null=0 */
### @14='1c1fa57bad8f2fd2eb529fe5e4aa9f35' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
### SET
### @1=51745159 /* INT meta=0 nullable=0 is_null=0 */
### @2=897405024 /* INT meta=0 nullable=0 is_null=0 */
### @3=201 /* INT meta=0 nullable=0 is_null=0 */
### @4=47 /* INT meta=0 nullable=0 is_null=0 */
### @5=0 /* INT meta=0 nullable=0 is_null=0 */
### @6=180 /* INT meta=0 nullable=0 is_null=0 */
### @7=0.362018 /* FLOAT meta=4 nullable=0 is_null=0 */
### @8=337 /* INT meta=0 nullable=0 is_null=0 */
### @9='jpeg' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
### @10=600 /* INT meta=0 nullable=0 is_null=0 */
### @11=1349714716 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### @12=1349736357 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### @13=1 /* INT meta=0 nullable=0 is_null=0 */
### @14='1c1fa57bad8f2fd2eb529fe5e4aa9f35' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
# at 747388250
at the sametime, the data in master/slave1/slave2
mysql> select * from s_user_logo_7 where uid=51745159 and pid=897405024;
+------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+
|HOST | uid | pid | logo | top | lleft | size | zoom | width | format | height | ctime | mtime | commentnum | md5 |
+------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+
|MASTER| 51745159 | 897405024 | 201 | 47 | 0 | 180 | 0.362018 | 337 | jpeg | 600 | 2012-10-09 00:45:16 | 2012-10-09 06:45:57 | 1 | 1c1fa57bad8f2fd2eb529fe5e4aa9f35 |
+------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+
|SLAVE1| 51745159 | 897405024 | 201 | 47 | 0 | 180 | 0.362018 | 337 | jpeg | 600 | 2012-10-09 00:45:16 | 2012-10-09 06:45:57 | 1 | 1c1fa57bad8f2fd2eb529fe5e4aa9f35 |
+------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+
|SLAVE2| 51745159 | 897405024 | 201 | 47 | 0 | 180 | 0.362018 | 337 | jpeg | 600 | 2012-10-09 00:45:16 | 2012-10-09 00:45:16 | 0 | 1c1fa57bad8f2fd2eb529fe5e4aa9f35 |
+------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+
i don't know why the error happened.
When i skip this record, the same error will occur again, and only happened in s_user_logo table.
Reference,
binlog_format=mixed
isolation_level=read_committed
Table: s_user_logo_7
Create Table: CREATE TABLE `s_user_logo_7` (
`uid` int(11) NOT NULL DEFAULT '0' COMMENT 'SPLIT_KEY',
`pid` int(11) NOT NULL DEFAULT '0',
`logo` int(11) NOT NULL DEFAULT '0',
`top` int(11) NOT NULL DEFAULT '0',
`lleft` int(11) NOT NULL DEFAULT '0',
`size` int(11) NOT NULL DEFAULT '0',
`zoom` float NOT NULL DEFAULT '0',
`width` int(11) NOT NULL DEFAULT '0',
`format` varchar(10) NOT NULL DEFAULT '',
`height` int(11) NOT NULL DEFAULT '0',
`ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`commentnum` int(11) NOT NULL DEFAULT '0',
`md5` varchar(32) NOT NULL DEFAULT '',
KEY `uid` (`uid`,`ctime`),
KEY `pid` (`pid`),
KEY `md5` (`md5`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Then slave 2 break down, i used the bak files from slave 1 (using mysqldump), and then imported to slave 2
when imported complete, then set slave 2 to catch up master, but replication error happened.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.119
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000090
Read_Master_Log_Pos: 246003112
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 267860420
Relay_Master_Log_File: mysql-bin.000087
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: 1032
Last_Error: Could not execute Update_rows event on table kx_info.s_user_logo_7; Can't find record in 's_user_logo_7', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000087, end_log_pos 747388250
Skip_Counter: 0
Exec_Master_Log_Pos: 747387897
Relay_Log_Space: 2987705016
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: 1032
Last_SQL_Error: Could not execute Update_rows event on table kx_info.s_user_logo_7; Can't find record in 's_user_logo_7', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000087, end_log_pos 747388250
Replicate_Ignore_Server_Ids:
Master_Server_Id: 561193306
Then i used mysqlbinlog to check in master machine
/usr/local/mysql/bin/mysqlbinlog mysql-bin.000087 -vv --start-position=747387897 | less
# at 747387968
# at 747388042
#121009 6:45:57 server id 561193306 end_log_pos 747388042 Table_map: `kx_info`.`s_user_logo_7` mapped to number 253
#121009 6:45:57 server id 561193306 end_log_pos 747388250 Update_rows: table id 253 flags: STMT_END_F
BINLOG '
pVdzUBNaIXMhSgAAAIo8jCwAAP0AAAAAAAEAB2t4X2luZm8ADX NfdXNlcl9s b2dvXzcADgMDAwMD
AwQDDwMHBwMPBQQKACAAAAA=
pVdzUBhaIXMh0AAAAFo9jCwAAP0AAAAAAAEADv////8AwIeRFQNgUH01yQAA AC8AAAAAAAAAtAAA
AGZauT5RAQAABGpwZWdYAgAAHANzUBwDc1AAAAAAIDFjMWZhNT diYWQ4ZjJm ZDJlYjUyOWZlNWU0
YWE5ZjM1AMCHkRUDYFB9NckAAAAvAAAAAAAAALQAAABmWrk+UQ EAAARqcGVn WAIAABwDc1ClV3NQ
AQAAACAxYzFmYTU3YmFkOGYyZmQyZWI1MjlmZTVlNGFhOWYzNQ ==
'/*!*/;
### UPDATE kx_info.s_user_logo_7
### WHERE
### @1=51745159 /* INT meta=0 nullable=0 is_null=0 */
### @2=897405024 /* INT meta=0 nullable=0 is_null=0 */
### @3=201 /* INT meta=0 nullable=0 is_null=0 */
### @4=47 /* INT meta=0 nullable=0 is_null=0 */
### @5=0 /* INT meta=0 nullable=0 is_null=0 */
### @6=180 /* INT meta=0 nullable=0 is_null=0 */
### @7=0.362018 /* FLOAT meta=4 nullable=0 is_null=0 */
### @8=337 /* INT meta=0 nullable=0 is_null=0 */
### @9='jpeg' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
### @10=600 /* INT meta=0 nullable=0 is_null=0 */
### @11=1349714716 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### @12=1349714716 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### @13=0 /* INT meta=0 nullable=0 is_null=0 */
### @14='1c1fa57bad8f2fd2eb529fe5e4aa9f35' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
### SET
### @1=51745159 /* INT meta=0 nullable=0 is_null=0 */
### @2=897405024 /* INT meta=0 nullable=0 is_null=0 */
### @3=201 /* INT meta=0 nullable=0 is_null=0 */
### @4=47 /* INT meta=0 nullable=0 is_null=0 */
### @5=0 /* INT meta=0 nullable=0 is_null=0 */
### @6=180 /* INT meta=0 nullable=0 is_null=0 */
### @7=0.362018 /* FLOAT meta=4 nullable=0 is_null=0 */
### @8=337 /* INT meta=0 nullable=0 is_null=0 */
### @9='jpeg' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
### @10=600 /* INT meta=0 nullable=0 is_null=0 */
### @11=1349714716 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### @12=1349736357 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### @13=1 /* INT meta=0 nullable=0 is_null=0 */
### @14='1c1fa57bad8f2fd2eb529fe5e4aa9f35' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
# at 747388250
at the sametime, the data in master/slave1/slave2
mysql> select * from s_user_logo_7 where uid=51745159 and pid=897405024;
+------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+
|HOST | uid | pid | logo | top | lleft | size | zoom | width | format | height | ctime | mtime | commentnum | md5 |
+------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+
|MASTER| 51745159 | 897405024 | 201 | 47 | 0 | 180 | 0.362018 | 337 | jpeg | 600 | 2012-10-09 00:45:16 | 2012-10-09 06:45:57 | 1 | 1c1fa57bad8f2fd2eb529fe5e4aa9f35 |
+------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+
|SLAVE1| 51745159 | 897405024 | 201 | 47 | 0 | 180 | 0.362018 | 337 | jpeg | 600 | 2012-10-09 00:45:16 | 2012-10-09 06:45:57 | 1 | 1c1fa57bad8f2fd2eb529fe5e4aa9f35 |
+------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+
|SLAVE2| 51745159 | 897405024 | 201 | 47 | 0 | 180 | 0.362018 | 337 | jpeg | 600 | 2012-10-09 00:45:16 | 2012-10-09 00:45:16 | 0 | 1c1fa57bad8f2fd2eb529fe5e4aa9f35 |
+------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+
i don't know why the error happened.
When i skip this record, the same error will occur again, and only happened in s_user_logo table.
Reference,
binlog_format=mixed
isolation_level=read_committed
Table: s_user_logo_7
Create Table: CREATE TABLE `s_user_logo_7` (
`uid` int(11) NOT NULL DEFAULT '0' COMMENT 'SPLIT_KEY',
`pid` int(11) NOT NULL DEFAULT '0',
`logo` int(11) NOT NULL DEFAULT '0',
`top` int(11) NOT NULL DEFAULT '0',
`lleft` int(11) NOT NULL DEFAULT '0',
`size` int(11) NOT NULL DEFAULT '0',
`zoom` float NOT NULL DEFAULT '0',
`width` int(11) NOT NULL DEFAULT '0',
`format` varchar(10) NOT NULL DEFAULT '',
`height` int(11) NOT NULL DEFAULT '0',
`ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`commentnum` int(11) NOT NULL DEFAULT '0',
`md5` varchar(32) NOT NULL DEFAULT '',
KEY `uid` (`uid`,`ctime`),
KEY `pid` (`pid`),
KEY `md5` (`md5`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Comment