MySQL Slave Relay_log Damage Repair Process

Time: 2013-04-18 11:32

Failure: The database server restart without cause, when the slave started, the report relay_log damaged, SQL thread cannot proceed.

Cause of failure: OS reboot may cause Relay_log to not sync properly

The solution at this point is to restart the change master to, but Master log POS uses the Exec_master_log_pos (show slave status\g variable output) to begin to accept the data from where slave has been executed.

This thought everything OK, but slave on the duplicate key error, then imagine is set Sql_slave_skip_counter = 1 constantly skipped.

Explanation of duplicate key reason for MySQL itself problem

Slave Commit transactions and update replication information are not atomic operations:

I/O thread synchronization: For the relay log flush to disk, and update master.info is Fsync call respectively.

You may receive a duplicate event when the following occurs:

When the server refreshes Relay_log and is about to update the master read location of the Master.info file, server downtime, Master.info not updated, causing the event to repeat when the replication is started.

SQL thread synchronization: SQL thread commits the transaction to the storage engine before updating the Relay_log.info file

The event has been applied on the database, but Relay-log.info has not been updated. When recovering, SQL repeats certain events.

This article is from the "Technology Achievement Dream" blog, please be sure to keep this source http://weipengfei.blog.51cto.com/1511707/1180913

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/