Mysql – How to create a slave replica of a MySQL server without stopping the master

MySQLreplication

To ensure the data on the slave and master are in the same place for replication, the new master should be stopped and data transferred to the new slave before starting the master again, at least this is one option.

How can I start replication of a MySQL server without stopping the master?

Best Answer

easy option - if you have small amount of data use

mysqldump --master-data -uyouruser -p.... -a > dump.sql

this will lock all tables and generate dump with info about current master position. dump will lock all tables - probably not something you want to do when you have busy website and 20gb data to dump.

more advanced option. take your data on lvm partition and use lvm snapshot. that is in one process lock all tables FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; in another proces run sync few times for a good measure and create lvm snapshot of your data / innodb logs. just after creating snapshot [ that takes 2-3 seconds for me ] unlock all tables.

now you have consistent snapshot and your sql server still running. mount snapshot and 'ship it' asap to another server. there run mysql on this data, let innodb reover all data from it's logs and run mysqldump which output put to slave.

start slave using information from SHOW MASTER STATUS that you've run just before taking LVM snapshot.

mylvmbackup does exactly that. read about it here.