Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

4 Answers 4

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 changing my.cnf. it is the only downtime, after it 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

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

share|improve this answer
    
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 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 Aug 22 '13 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 Aug 22 '13 at 8:58
add comment

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.

share|improve this answer
add comment

Get a look at this, its a no downtime solution

http://plusbryan.com/mysql-replication-without-downtime

share|improve this answer
2  
In the ServerFault tradition, please reproduce (or at least outline) the solution here, in case the link goes dead someday. –  200_success Jun 25 '13 at 11:11
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.