17.3.1.3 Backing Up a Master or Slave by Making It Read Only

It is possible to back up either master or slave servers in a
replication setup by acquiring a global read lock and
manipulating the read_only
system variable to change the read-only state of the server to
be backed up:

Make the server read-only, so that it processes only
retrievals and blocks updates.

Perform the backup.

Change the server back to its normal read/write state.

Note

The instructions in this section place the server to be backed
up in a state that is safe for backup methods that get the
data from the server, such as mysqldump
(see Section 4.5.4, “mysqldump — A Database Backup Program”). You should not attempt to
use these instructions to make a binary backup by copying
files directly because the server may still have modified data
cached in memory and not flushed to disk.

The following instructions describe how to do this for a master
server and for a slave server. For both scenarios discussed
here, suppose that you have the following replication setup:

A master server M1

A slave server S1 that has M1 as its master

A client C1 connected to M1

A client C2 connected to S1

In either scenario, the statements to acquire the global read
lock and manipulate the
read_only variable are
performed on the server to be backed up and do not propagate to
any slaves of that server.

Scenario 1: Backup with a Read-Only
Master

Put the master M1 in a read-only state by executing these
statements on it:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

While M1 is in a read-only state, the following properties are
true:

Requests for updates sent by C1 to M1 will block because the
server is in read-only mode.

Requests for query results sent by C1 to M1 will succeed.

Making a backup on M1 is safe.

Making a backup on S1 is not safe. This server is still
running, and might be processing the binary log or update
requests coming from client C2

While M1 is read only, perform the backup. For example, you can
use mysqldump.

After the backup operation on M1 completes, restore M1 to its
normal operational state by executing these statements:

mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

Although performing the backup on M1 is safe (as far as the
backup is concerned), it is not optimal for performance because
clients of M1 are blocked from executing updates.

This strategy applies to backing up a master server in a
replication setup, but can also be used for a single server in a
nonreplication setting.

Scenario 2: Backup with a Read-Only
Slave

Put the slave S1 in a read-only state by executing these
statements on it:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

While S1 is in a read-only state, the following properties are
true:

The master M1 will continue to operate, so making a backup
on the master is not safe.

The slave S1 is stopped, so making a backup on the slave S1
is safe.

These properties provide the basis for a popular backup
scenario: Having one slave busy performing a backup for a while
is not a problem because it does not affect the entire network,
and the system is still running during the backup. In
particular, clients can still perform updates on the master
server, which remains unaffected by backup activity on the
slave.

While S1 is read only, perform the backup. For example, you can
use mysqldump.

After the backup operation on S1 completes, restore S1 to its
normal operational state by executing these statements:

mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

After the slave is restored to normal operation, it again
synchronizes to the master by catching up with any outstanding
updates from the binary log of the master.