MySQL Replication is the key enabler of â€œScale Outâ€ discussed in the previous post. Scale Out is leveraged extensively by Web 2.0 sites and applications. MySQL natively supports one-way, asynchronous replication. Replication works by simply having one server act as a master, while one or more servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster.
Asynchronous data replication means that data is copied from one machine to another, with a resultant delay. Often this delay is determined by networking bandwidth, resource availability or a predetermined time interval set by the administrator. However, with the correct components and tuning, replication itself can appear to be almost instantaneous to most applications. Synchronous data replication implies that data is committed to one or more machines at the same time, usually via what is commonly known as a â€œtwo-phase commitâ€.
In standard MySQL Replication, the master server writes updates to its binary log files and maintains an index of those files in order to keep track of the log rotation. The binary log files serve as a record of updates to be sent to slave servers. When a slave connects to its master, it determines the last position it has read in the logs on its last successful update. The slave then receives any updates which have taken place since that time. The slave subsequently blocks and waits for the master to notify it of new updates.
Below is an illustration of a basic Scale Out implementation using MySQL Replication.
Replication offers the benefits of reliability, performance, and ease of use:
- In the event the master fails, the application can be designed to switch to the slave.
- Better response time for clients can be achieved by splitting the load for processing client queries between the master and slave servers. Queries which simply â€œreadâ€ data, such as SELECTs, may be sent to the slave in order to reduce the query processing load on the master. Statements that modify data should be sent to the master so that the data on the master and slave do not get out of synch. This load-balancing strategy is effective if non-updating queries dominate.
- Another benefit of using replication is that database backups can be performed using a slave server without impacting the resources on the master. The master continues to process updates while the backup is being made.