MySQL 8.0 Release Notes
MySQL 8.0 Source Code Documentation
It is possible to back up either source or replica servers in a replication setup by acquiring a global read lock and manipulating theread_onlysystem variable to change the read-only state of the server to be backed up:
使服务器只读,流程ly 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 asmysqldump(seeSection 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 source and for a replica. For both scenarios discussed here, suppose that you have the following replication setup:
A source server S1
A replica server R1 that has S1 as its source
A client C1 connected to S1
A client C2 connected to R1
In either scenario, the statements to acquire the global read lock and manipulate theread_onlyvariable are performed on the server to be backed up and do not propagate to any replicas of that server.
Scenario 1: Backup with a Read-Only Source
Put the source 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:
Requests for updates sent by C1 to S1 block because the server is in read-only mode.
Requests for query results sent by C1 to S1 succeed.
Making a backup on S1 is safe.
Making a backup on R1 is not safe. This server is still running, and might be processing the binary log or update requests coming from client C2.
While S1 is read only, perform the backup. For example, you can usemysqldump.
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;Although performing the backup on S1 is safe (as far as the backup is concerned), it is not optimal for performance because clients of S1 are blocked from executing updates.
This strategy applies to backing up a source in a replication setup, but can also be used for a single server in a nonreplication setting.
Scenario 2: Backup with a Read-Only Replica
Put the replica R1 in a read-only state by executing these statements on it:
mysql> FLUSH TABLES WITH READ LOCK; mysql> SET GLOBAL read_only = ON;While R1 is in a read-only state, the following properties are true:
The source S1 continues to operate, so making a backup on the source is not safe.
The replica R1 is stopped, so making a backup on the replica R1 is safe.
一个流行的b这些属性提供了依据ackup scenario: Having one replica 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 source server, which remains unaffected by backup activity on the replica.
While R1 is read only, perform the backup. For example, you can usemysqldump.
After the backup operation on R1 completes, restore R1 to its normal operational state by executing these statements:
mysql> SET GLOBAL read_only = OFF; mysql> UNLOCK TABLES;After the replca is restored to normal operation, it again synchronizes to the source by catching up with any outstanding updates from the source's binary log.