How to setup MySQL replication with minimal downtime

Solution 1:

I assume you use InnoDB as a storage engine. If so, you need to turn on bin-logging. If it's not on now, you need to restart MySQL after modifying my.cnf. It is the only downtime, after which you can take dump of the database with binlog position without blocking the database:

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A

Based on this backup, restore data on the slave. After this, you can follow any MySQL replication tutorial and let slave catch up/run together with the master.

Solution 2:

to start a replication you need a consistent copy of your database on the slave

are your tables myisam or innodb backed? to find out issue

show table status

and look at the 'Engine' column

if all the tables are innodb than you can do zero downtime setup using mysqldump --single-transaction and importing that into the slave

if the tables are myisam there will have to be downtime as you have to lock the tables for writing while they are being copied. mysqlhotcopy is the tool to help you with that.

alternatively, if you are using LVM (logical volume manager), you can stop the database, take a LVM snapshot in a few seconds and start the db again. Then you can make a consistent copy from the snapshot.