MySQL Replication – How to Resynchronize for a Single Database

MySQLreplication

I have two MySQL 5.5 servers in a master/slave replication setup. The server hosts about 10,000 databases, but the replication is messed up for only a couple of databases.

To fix the replication I could do a complete resynchronization by copying all the databases over to the slave server, but there are many databases and so much data that this would entail a long downtime for all the databases.

A better solution would be to re-synchronize just the two or three databases whose replication is broken. Can I do this in such a way that the only downtime would be for those two or three databases that have replication problems?

Best Answer

It depends a bit on your setup, there are different ways to archive what you want. I will describe a very basic one which only uses MySQL tools (and some standart Linux tools).

First of all you have to make sure a few things:

  1. It can be guaranteed that the databases which you want ro resync will have no writes or other DML or DDL statements issued against while you perform the resync. E.g. you are able to revoke privileges for these database users.
  2. You are familiar with backup and restore using mysql tools :)

Now the procedure:

  1. Ensure that there will be no access on the databases which you want to resync, easiest way to do this is to revoke all priviliges for users which have access to this database. A possible way to find those users would be querying mysql.db table.
  2. Now you have your three schemas and there will be no changes to it, it means it will be on a consistent state until you have finished your operations on it. You can now safely dump only there required databases using mysqldump. Please refer here to the mysqldump docs. If you are unsure just ask.
  3. Copy your dumps to your slave machine and restore your three databases there using these dumps. Beforehand make sure that all binary logs are applied to the slave which might contain changes to these databases (make sure replication has catched up and there is no lag or something).
  4. After the restore your databases will be identical on the master and the slave. It is now safe to grant back the permissions to the users on the master, as soon as there are changes on the master they will be replicated to the slave.

Keep in mind that there are different approaches for restoring failed slave databases, above is one of them, so maybe some additional research would be a good idea. If you have questions to particular points of my description don´t hesitate to ask, I will then edit my answer.

Related Question