Posted by peter

As you probably know MySQL Replication (statement based) works by fetching statements from MASTERs binary log and executing them on the SLAVE. Since MySQL 4.0 this process is a bit more involved having events passing via relay logs on the Slave which also means there are two replication threads “IO Thread” and “SQL Thread” used in the process but idea remains the same.

For replication to work properly you need to have proper “snapshot” - consistent state of the database as it was on master and corresponding position in masters binary logs. There are multiple ways to get the “snapshot” - shutting down MySQL Server and copying data, using LVM, Using Innodb Hot Backup Tool, Using another Slave, using backup image etc but in all the cases you have to be sure the snapshot you’re dealing with corresponds to correct binary log position.
[read more...]