Replication as a Backup Solution
Replication alone is not sufficient for backup. It assists in protecting against hardware failure on the master server, but does not protect against data loss. An accidental or malicious
DROP DATABASE or
TRUNCATE TABLE statement will be replicated onto the slaves as well. Care needs to be taken to prevent data getting out of sync between the master and the slave.
Replication is most commonly used to support backups as follows:
- A master server replicates to a slave server
- Backups are then run off the slave without any impact on the master.
Backups can have a significant effect on a server, and a high-availability master may not be able to be stopped, locked or simply handle the extra load of a backup. Running the backup from a slave has the advantage of being able to shutdown or lock the slave and perform a backup without any impact on the primary server.
Note that when backing up off a slave server, it is important to ensure that the servers keep the data in sync. See for example Replication and Foreign Keys for a situation when identical statements can result in different data on a slave and a master.