About SRGTechnical ArticlesRecruitment Information
About SRGTechnical ArticlesRecruitment Information
HOME/Technical Articles/Can the CLONE PLUGIN be used for backup operations with the latest MySQL 8.0?

Can the CLONE PLUGIN be used for backup operations with the latest MySQL 8.0?

This is Oniumi (@fat47) from the Service Reliability Group (SRG) of the Technology Headquarters.
#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
This article is located in SRGDBWGWe will publish materials regarding the database provided by the DB Working Group for the entire company.
I hope this helps in some way.
 

Current issues with using XtraBackup

Current backup operations

In the service that our team is responsible for, we use Percona's XtraBackup to back up MySQL, and we also back up the binlog separately every few minutes.
For example, the operation is as follows:
Backup Typefrequencymeans
Full BackupOnce a dayRun XtraBackup with cron
binlog backupEvery 5 minutesRun s3 sync with cron

Current restore flow

The full backup taken with XtraBackup is extracted, the binlog position at the time the backup was taken is checked, and data from that point onwards is restored by applying the data from the binlog backup.
 

Problems with XtraBackup

Basically, this operation worked without any problems even with MySQL 8.0.
MySQL8.0.29Until it comes...
 
ALTER TABLE … ALGORITHM=INSTANT
ALGORITHM=INSTANT
ALGORITHM=INPLACE/COPY;
As an aside, there was a critical bug related to this feature, and 8.0.29 was discontinued shortly after its release. It was then officially announced that users should use 8.0.30 or later.
 
ALGORITHM=INSTANT
 
If there is even one table that has been applied, the following error will be output when XtraBackup is executed.
ALTER TABLE ALGORITHM=COPY
If the target table is small, you can simply run OPTIMIZE or similar before each backup, but if the table is huge, this may take a very long time.
ALGORITHM=INSTANT
 

MySQL 8.0 New Features CLONE PLUGIN

To avoid the problems with XtraBackup mentioned above, I wondered if I could use the CLONE PLUGIN feature of MySQL.
Let me briefly introduce the CLONE PLUGIN again.
The Clone Plugin is a new physical backup feature for MySQL that was released in MySQL 8.0.17 in 2019.
You can save it locally as a file, or you can create a copy of the data on the donor server remotely.
For example, if you want to add one replica, you can copy all data from the data provider (donor) server by executing the following command.
 
*In practice, the following preparations are required when using CLONE from a donor.
MySQL on the data provider (donor) side
 
MySQL on the destination (receiver) side
 

How to use the Clone Plugin instead of XtraBackup

We mentioned earlier that there are problems with XtraBackup's operation with MySQL 8.0.30 and later.
In this chapter, we will consider backup operations for MySQL 8.0.30 and later.
 
Run the CLONE PLUGIN on the replica server with the following configuration to obtain a backup.
It is assumed that you have uploaded it to object storage such as S3.
 
💡
The CLONE PLUGIN has some limitations, such as only allowing the InnoDB storage engine. Please refer to the official documentation for details. https://dev.mysql.com/doc/refman/8.0/ja/clone-plugin-limitations.html
This timeNon-GTID environmentWe will skip the S3 upload part and show an example where the file is stored locally.

Full Backup

(First time only) Create a backup execution user
 
The CLONE PLUGIN does not have the ability to back up binlog files or my.cnf.
Since there is no way to know the MASTER POSITION at the moment the backup was taken, we stop replication of the replica, record the position, and then execute CLONE.
 
Stop replication.
 
Record the position.
 
Perform a backup.
 
Resume replication.
 

Binlog backup

Use cron to periodically copy the binlog file to another directory using rsync or similar.
 

Restore flow

  1. Stop MySQL.
  1. Extract the full backup taken with CLONE to the MySQL directory.
    1. Extracts changes made after a full backup from the binlog file.
      1. Check the position file recorded earlier
        1. Check the binlog files updated since the full backup
          1. Generate the recovery query.
              2. For start-position, specify the position shown above, and for the binlog files to be read, specify all binlog files from the time the full backup was taken.
        1. Start MySQL.
        1. Run a recovery query on the started MySQL.
          1. This will restore the full backup plus any differences since then.
           

          Conclusion

          As in this case, the CLONE PLUGIN can also be used in the same way as XtraBackup.
          If you are using MySQL 8.0.29 or later, we recommend that you consider using XtraBackup+OPTIMIZE TABLE or using the CLONE PLUGIN with the steps outlined here.
           
          Although I didn't introduce it this time, the CLONE PLUGIN makes adding replicas much easier than the conventional replica addition procedure and is very convenient, so I would like to make use of it.
           
          SRG is looking for people to work with us. If you're interested, please contact us here.