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

I have MySQL replication set up in production as follows:

DB1 -> DB2
DB1 -> BAK

Where DB2 and BAK are slaves to DB1. All 3 servers are in sync (0 seconds behind the master) and have 30+ GB of data.

I'd like to put the servers in a new master-slave configuration as follows:

DB1 -> DB2 -> BAK

What is the best way to change the master host on BAK?

Is there a way to avoid having to stop the slave thread on DB2 and getting a mysqldump for BAK (a 5-6 hour processes) ?

share|improve this question

2 Answers 2

They call that a master-slave-slave cascading replication. You need to enable log-bin and log-slave-updates on DB2, see here:

http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_log-slave-updates

Once that's running on BD2, on BAK you need to change the master and reset the slave to flush it's log info, ala:

mysql> stop slave;
mysql> change master to MASTER_HOST="BAK name or IP", .....
mysql> reset slave;
mysql> start slave

Where '...' are the options as usual from: http://dev.mysql.com/doc/refman/5.0/en/change-master-to.html

In theory that should do it for you without having to make a new dump and import it -- I'd make sure to run a flush logs on the master and make backups of BAK if you want in case something goes wrong in getting your log positions wrong or somesuch...

share|improve this answer

As you say, dump and import can take forever. A much better option is to use xtrabackup, which will do a restore about as fast as it can copy the files into place. I blogged about it a while ago. I find it's a perfect way of setting up slaves, really quick and easy. The backups take a long time, but restores are really fast. I also strongly recommend you use pigz instead of gzip if you're compressing your backups - it easily made a factor of 4 difference to my backup times.

If you can arrange to stop both slaves at the same time (so they are both pointing at the same log and position on db1), for example by stopping DB1 momentarily, then you can safely repoint BAK at DB2 using its master info with no need to do a lengthy restore. As troyengel says, most important is that you need log-slave-updates on to do the relaying, otherwise only statements that originate on DB2 will get sent to BAK.

I'd also recommend you set DB2 to read-only. This won't affect replication, but is safer as you won't end up with data on DB2 and BAK that's not on DB1.

share|improve this answer

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.