How to setup MySQL replication with minimal downtime

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.

Leave a Comment