Chapter 16. Replicating Your Database

One of the advanced features included in MySQL is replication. Using this feature, you can have multiple servers storing the same data. You might do this for performance reasons, for reliability, or for ease of backups . Additionally, you might choose to make use of replication simply to spread your database load across multiple servers you already own, instead of one (new) large server.

Replicating a database can provide fault tolerance. If your master server fails, a slave can be treated as a hot backup and immediately be turned into the master.

In a system in which most operations involve reading data, but not writing data, replication can improve performance by routing queries to a number of machines. You might be trying to reduce load by performing queries on lightly loaded machines, or you might be trying to reduce network transmissions by sending queries to a geographically nearby machine. If your database queries are mainly fairly simple, even a simple rotating load-balancing algorithm can spread the load effectively. If some of your queries require extensive processing, you will probably have to investigate more sophisticated application-level load balancing.

A related use of replication to assist performance is in making backups. As mentioned in Chapter 14, "Backup and Disaster Recovery," backups can take a long time for a large database. Using replication, you can stop a slave and generate a backup of its data without affecting performance for other users.