Live Backups of MySQL Using Replication
Subject:   Replication Problems
Date:   2005-12-22 07:51:58
From:   aquariusrick
Response to: Replication Problems

It works really nicely the way the article describes if you create the replicant user without a specific host name, instead create it as 'replicant'@'%'. If you change your root account to 'root'@'localhost' you should be able to replicate the mysql database to the slave without causing problems when you overwrite it with the master dump file.

(for those that MAY not know that @'%' and @'localhost' means, '%' is for ANY host and 'localhost' is for the logging in from the box itself only)

If you think you may ever have more than one slave, I'd recommend doing it this way, that way all slaves could become the master with no required changes to the user table. The only command needed to change master on the slaves would be 'CHANGE MASTER TO'.

This way if you have more than one slave you can promote any of the slaves to become a master and all remaining slaves can be pointed to replicate from the new master easily.

If you really want to have a different set of user accounts on the slave than on the master then I'd recommend using the --ignore-table=mysql.xxxx for each table you want to ignore replication.

Remember the whole point of this article is to set up replication for a working backup solution. So if you have different user accounts on the slave when you perform the backup, you'll need to have some way of restoring the user accounts associated with the master before you can call it a complete backup solution.