Mysql – Do database servers need load balancing

You can do master-master replication, especially if there is a natural partition of your data. We have customers that do this for their users based on the user's region. That way most users don't have to deal with the effects of replication lag on their application while both databases serve as backup for each other. On the other hand, if you don't have a way to partition data, you will end up having to deal with replication errors where both servers locally update the same row, report commit success to the client, breaking replication, and which will have to be resolved manually.

Another option that we see is to have a writable master and one or more read-only slaves. The upside to this approach is that you don't have to find a partition for your data and it's a simpler setup (less likely to get you up in the middle of the night). Downside is that database reads will always be slightly stale. For queries that this staleness is unacceptable, you can always query the master so long as its able to keep up.

There are also a variety of clustered database solutions that may meet your need with less application jiggering.

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.