MySQL per-database replication?

Question

So, my problem is interesting: we want to migrate from one server to another. We made a master-slave replication, but my boss came with the idea to make migration one database at a time.

So he asked me to setup at the new server another MySQL instance, let the slave almost as-is and make the new instance be the new master incrementally, one database at a time. Is it possible, that is, can I transfer the database 'x' from old master to new master and just tell slave to synchronize 'x' at the new master from now on?

I've read at this old thread ( Mysql Replication - are per-database threads possible? ) that this was not possible at that time. This can be done now?

Thanks!

Lucas Bracher.

Answer 1

Yes, you can do this. MySQL's replication allows you to specify which schemas to replicate[1] or not, look for the replicate_wild_do_table parameter.

However, it is also very silly to do this, as you will need some downtime for each migration to make sure there is no data loss, and I also can't think of a good reason not to simply replicate all the databases.

[1] The slave will still fetch the complete binlog, but only execute statements that match the filter(s)

Answer 2

You could do something like this, but there's no benefit.

The thing you can't do is "tell slave to synchronize 'x'" That's not how replication works. Replication just replays events from the master starting from the position you tell it to. So you are required to make sure you take a snapshot of the data from the master at a consistent point in time, and copy that data to the slave before you start replication.

There's no real advantage to doing it one database at a time from the master to the slave. In fact, it's much more difficult.

The easiest way to set up a slave is to make a full backup of your master, making use to record the binlog position, then restore that backup to the slave, and start replication from the recorded binlog position.

But lets step back and take a look at what you're really trying to do:

You want to migrate to a new server, and let the slave be "master" for one database at a time.

This is simple.

So here is your replication: Master -> Slave

You set up the slave to be a complete copy of the master, and replicate changes so they stay in sync.

What does it mean for the slave to be master now? It just means you tell your application to use the slave to do all the reading and writing. That's it. Once you have replication set up, there's no other mysql changes to make.

Promoting a slave to a master is an application configuration change only (in your case where you have a master mysql server with one slave replicating from it.)

So if you can change your application config to use a different mysql database for each database it's trying to use, then you can migrate on db at a time.

Simple.

Answer 3

Nope -- MySQL replication is all-or-nothing(ish). You can't slave one MySQL server from two separate masters.