Backup of MySQL Replication Slaves

From The Open Source Backup Wiki (Amanda, MySQL Backup, BackupPC)

Jump to: navigation, search

The main advantage of using replication servers is that the backup can happen without disturbing the production server.

The typical configuration for replication is to setup the production server as the master server. Another mysql server is then configured as a slave server. See diagram below.

Please see the section on replication in the MySQL Reference Manual for details on how to setup replication between MySQL servers.

This will ensure that any database event on the master is automatically replicated to the slave server. This movement happens after the database event has been logged in the master server. MySQL version 4 or higher to support this feature. Hence while backup is being performed, the master replication server can continue to update its tables as the backup is being done from the slave replication server.

Configuration Parameters

The only difference between backing up a regular MySQL server and a replication slave server is that on the slave the replication related files should also be backed up.

To configure ZRM for MySQL to backup replication related files the option

replication=1

should be specified in either the global mysql-zrm.conf file or the backup-set specific mysql-zrm.conf file.

If the replication option is specified and if the mysql server is a slave server, then the utility will also backup all replication related files.

Please note, ZRM for MySQL does not set up MySQL replication slave for backups. This needs to be done before configuring ZRM for MySQL to backup the replication slave.

Advantages of Using Replication Slaves for Backup

  • Backup can happen without disturbing the production server.
  • Can be used as disaster recovery solution also.
  • Replication slave can be used for load balancing and high availability.
  • Hot backup.

Disadvantages of Using Replication Slaves for Backup

  • The replication slave is always behind the master server. This backup method may not be suitable for databases with high transaction rates.
  • Replication as a backup method is expensive (additional server, more network bandwidth). If replication is being used for load balancing and high-availability reasons, this method can be used for database backups also.
Personal tools