Home / server / Questions / 220322
In Process
Patrick
Asked: 2011-01-10 03:57:34 +0800 CST

How to setup MySQL replication with minimal downtime

  • 772

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.

mysql-replication master-slave

3 Answers

  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.

    • 17

  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.

    • 3

  3. 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.

    • 1