MySQL in a Nutshell, 2nd Edition

Backups with Replication

With replication running, it’s an easy task to make a backup of the data. You just need to temporarily stop the slave server from replicating by entering the following SQL statement while logged onto the slave server as root or as a user with SUPER privileges:

STOP SLAVE;

The slave server knows the position where it left off in the binary log of the master server and will record that information in the master.info file. So, you can take your time making a backup of the replicated databases on the slave server. You can use any backup utility or method you prefer. The only complication is if the slave also assists in handling user requests for load balancing, in which case STOP SLAVE throws the burden back on the master or on other slaves.

If the slave is used only for backups and has no users accessing the data, you could simply copy the data directory. I prefer to use mysqldump because it’s fairly straightforward and works with all table types. To make a backup with mysqldump, enter something like the following:

mysqldump --user=root --password=my_pwd --lock-all-tables \
 --all-databases > /backups/mysql/backup.sql

When the backup is finished, enter the following SQL statement as root on the slave server to restart replication:

START SLAVE;

After entering this statement, there should be a flurry of activity on the slave as it executes the SQL statements that occurred while it was down. After a very short period of time, though, it should be current.