Basically we have a large MySQL database and we're looking to do replication to a slave (master slave setup). Do you guys have any step-by-step guide on how to do this with MINIMAL downtime on the database? Backing up the database and transferring the backup to the slave server (even through a private network) takes like 40 minutes or so. Quite a long downtime.

3 Answers 3


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.

  • what would happen if some tables are not using innodb ? And would the above backup be still valid if I use --databases to dump a subset of the databases instead of -A ?
    – mathieu
    Commented Aug 20, 2013 at 9:15
  • if some tables dont use innodb the backup might be inconsistent. see here: stackoverflow.com/a/7461466/104398 ; backup will not contain those skipped databases. if you set up replication based on such backup and dont filter what's replicated - your replication will break as soon as there's some operation on omitted databases.
    – pQd
    Commented Aug 22, 2013 at 5:24
  • Ok, I converted my remaining myisam tables to innodb and made sure that only the databases I want are replicated/backed-up.
    – mathieu
    Commented Aug 22, 2013 at 8:58

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.


In order to setup the slave you will need to take a data snapshot and record the binary log position. Backing up the database should not cause downtime although it will cause reduced performance, but your already backing up the database anyway right? You may find the backup needs to lock some tables and this may cause lockups on your application, really need to look at how it is implemented for that.

Can you snapshot the disk? That would reduce downtime considerably if you can do a hotbackup.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .