14

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.

John Gardeniers
  • 27,262
  • 12
  • 53
  • 108
Patrick
  • 455
  • 1
  • 7
  • 10

3 Answers3

17

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.

nhahtdh
  • 115
  • 7
pQd
  • 29,561
  • 5
  • 64
  • 106
  • 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 Aug 20 '13 at 09:15
  • if some tables dont use innodb the backup might be inconsistent. see here: http://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 Aug 22 '13 at 05:24
  • Ok, I converted my remaining myisam tables to innodb and made sure that only the databases I want are replicated/backed-up. – mathieu Aug 22 '13 at 08:58
3

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.

Aleksandar Ivanisevic
  • 3,327
  • 19
  • 24
1

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.

hellomynameisjoel
  • 2,170
  • 1
  • 18
  • 23