MySQL Cookbook, 4th Edition

Chapter 3. MySQL Replication

3.0 Introduction

MySQL replication provides a way to set up a copy (replica) server of the active (source) database, then automatically continuously update such a copy applying all of the changes the source server receives.

Replica is useful in many situations, particularly the following:

Hot Standby

A server, normally idle, will replace an active one in case of a failure.

Read scale

Multiple servers, replicating from the same source, can process more parallel read requests than a single machine.

Geographical distribution

When an application serves users in different regions, having a local database server can help users retrieve data faster.

Analytics server

Complicated analytics queries may take hours to run, set plenty of locks, and use a lot of resources. Running them on the replica minimizes the impact on other parts of the application.

Backup server

Taking backups from a live database involves high-IO resource usage and locking, which is necessary to avoid data inconsistencies between the backup and active dataset. Taking backups from the dedicated replica reduces the impact on production.

Delayed copy

A replica, applying updates with a delay, configured by the SOURCE_DELAY (MASTER_DELAY) option, allows for rolling back human errors, such as the removal of an important table.

Note

Historically, the source server was called a “master,” and the replica server was called a “slave.” Recently, it was discovered that the terminology master and slave do not correctly ...