Home / server / Questions / 148241
In Process
mmattax
Asked: 2010-06-05 07:31:42 +0800 CST

What is the best way to change the replication scheme of 2 currently replicated slaves?

  • 772

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) ?

mysql mysql-replication

2 Answers

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

    • 1

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

    • 0