Master-Slave Replication (MySQL)

A method of data replication in MySQL, which uses two servers, to provide a near-live backup of all data which can be used in the case of complete failure of the primary (or "Master") server.

The first server is called the Master Server, and is the server which clients and applications connect to. The second server is called the Slave Server, and exists solely as a backup of the Master.

Every time a change is made on the master server, the slave is notified, and applies the same change to its own copy of the database.

Although data is replicated immediately, replication is not transactional, so there are certain cases where data on the slave is not guaranteed to be 100% up to date:

  • New data is written to the Master Server, which then fails before it has a chance to replicate to a Slave Server
  • New data is written to the Master Server, but all Slave Servers are down or otherwise not able to communicate with the Master Server.

Master/Slave Replication is different to a normal, periodical backup. Each has it's own advantages and disadvantages, and both types can be used at the same time.

Advantages of Master/Slave Replication:

  • Data on the slave server is more likely to be up to date than on a periodical backup which is only performed periodically (eg: daily). If there is a complete failure of the Master Server, the Slave Server can be made available to take over the functionality, or it can be used to restore data with minimal loss.

Advantages of a Periodical Backup:

  • Periodical backups safely store snapshots of data from a previous point in time. If data is deleted from a Master Server, the changes will immediately replicate to the Slave Server. Therefore the data will be deleted on both servers and cannot be recovered from the slave. However, deleted data can still be recovered from a periodical backup taken the night before.

More Information

metawerx specific

referring pages