Mysql Technical Document -Source Mr.Anandha

Anandkumar formally known as Anandha do assortment research on mysql database and he use to read lot of write ups over internet. The below technical document he got from internet


---------- Forwarded message ----------
From: Anand Kumar
Date: Wed, Sep 8, 2010 at 10:10 PM
Subject: Fwd: master-slave replication sync problems.


Hi Guys,

I found it to be a good writeup about the internal funtion of the replication, worth reading....Hope this will clear all your doubts about the mysql replicaiton.


There are two formats for replication (ROW and STATEMENT) but the general process remains the same. Here is a nutshell summary of the process.

** on the master **

m1) The MySQL master is instructed to change some data.

m2) The data is changed and the results are committed to disk. If you rollback the changes before you commit them, then there is nothing to replicate. Only the InnoDB engine supports this type of rollback.

m3) The change committed in step 2 is written to the binary log

(repeat from step m1 until the Master is shutdown)


** on the slave - the IO thread **
(assuming that the slave is already configured with compatible data, a starting position, and the proper credentials to act as a slave)

o1) The SLAVE IO thread requests information from the master's binary logs. This information is identified by a file name and a byte offset from the start of that file

o2) The SLAVE IO thread copies all available information from the master's binary logs into a local copy of those logs known as the relay logs.

(repeat from o1 until the SLAVE IO thread is stopped(by error or by command) or the slave is shutdown)

** on the slave - the SQL thread **

s1) Once an unapplied change has been completely buffered into the relay logs, the SLAVE SQL thread attempts to apply the change to the slave's data.

s2) If LOG SLAVE UPDATES is enabled, copy the applied change (using the correct format) into the slave's binary log.

(repeat from s1 until the SLAVE SQL thread is stopped (by error or by command) or the slave is shutdown)

**
As you can tell by this very simplified process description, there is no attempt to rectify one dataset to the other. Replication operates under the principle that if you perform identical changes to identical sets of data, you will end up with identical end results.

Various replication "filters" can omit certain changes from either being replicated to or processed by the slave instance. Use these WITH EXTREME CAUTION as they can very easily create situations where the master and slave datasets diverge to the point that a rebuild of the slave data is the only effective repair.

**
When you get a replication error, such as DUPLICATE KEY, it is the administrator's responsibility to figure out why and fix it. Maybe some user on the slave added an extra row to a table? Maybe an INSERT ... SELECT added more rows on the slave than it did on the master? Maybe a DELETE on the master removed less rows than it did on the slave?

Simply skipping those problems (and others) without investigation or correction may allow the two datasets (master and slave) to diverge even more. The human administrator is required to make a judgment call on which version of the conflicting row is the "correct" version. Is is the one on the table or the change coming in from the binary log? Maybe the data on the slave is "correct" but it has been offset by a few rows inserted a long time ago.

It's your responsibility to understand and appropriately respond to the errors not just repeat scripted actions until the problems disappear temporarily.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN