We use MySQL master-slave replication to maintain a backup database server as a replica of our master / production database server.

If there are issues with the master slave replication (i.e. it's become corrupted for some reason), we stop the replication, read lock the master, take the replication co-ordinates, take dumps from the master, unlock the master, restore the dumps to the slave

In our database servers, we have several databases, including some which are quite large (100 - 200 GB). As such, taking the dumps can take a long time during which the master database is subject to read locks. Also, restoring the slave can take a long time, which means we're without our backup replication for a long time.

Is it possible to run master-slave replication, or some equivalent, on a per database basis?

That is, for each individual database on the servers, the replication of changes is done separately, to separate files etc. This would allow us to turn replication on/off for individual databases. Then, if the replication gets corrupted for any individual database, we can turn off the replication for that database, fix and restart it, without having to turn off the replication for the other databases.

I'm aware of the ability to limit the master replication or slave replication by databases (replication-do-db or replication-ignore-db), but the data is still bundled in single files.

migrated from stackoverflow.com Feb 9 '17 at 14:26

This question came from our site for professional and enthusiast programmers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.