About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/Articles/Can the CLONE PLUGIN be used for backup operations in the latest MySQL 8.0?

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

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

Current issues with using XtraBackup

Current backup operations

For the services 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

We extract the full backup taken with XtraBackup, check the binlog position at the time the backup was taken, and restore the data from that point onwards by applying the data from the binlog backup.
 

Issues with XtraBackup

Basically, this operation worked without any problems even with MySQL 8.0.
MySQL8.0.29Until it arrives...
 
ALTER TABLE … ALGORITHM=INSTANT
ALGORITHM=INSTANT
ALGORITHM=INPLACE/COPY;
As an aside, there was a critical bug in this function, and 8.0.29 was discontinued immediately after its release. It was then officially announced that users should use 8.0.30 or later.
 
ALGORITHM=INSTANT
 
If there is even one applied table, the following error will be output when running XtraBackup.
ALTER TABLE ALGORITHM=COPY
When the size of the target table is small, it is sufficient to run OPTIMIZE etc. each time before performing a backup, but for huge tables, this may take a very long time.
ALGORITHM=INSTANT
 

New features of MySQL 8.0 CLONE PLUGIN

In order to avoid the problems with XtraBackup mentioned above, I wondered if I could use the MySQL feature CLONE PLUGIN.
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 remote copy on the data provider's (donor's) server.
For example, if you want to add a replica, you can run the following command to copy all data from the data provider (donor) server.
 
*In practice, the following preparations are required when using CLONE from a donor.
MySQL on the data provider side
 
MySQL on the destination (receiver) side
 

How to use CLONE PLUGIN instead of XtraBackup

We mentioned earlier that there are problems with the operation of XtraBackup with MySQL 8.0.30 and later.
In this chapter, we will consider backup operations in MySQL 8.0.30 and later.
 
With the following configuration, run the CLONE PLUGIN on the replica server to obtain a backup.
It is assumed that you have uploaded the file 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 the replication of the replica, record the position, and then execute CLONE.
 
Stop replication.
 
Record the position.
 
Run the 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. Changes made after a full backup are extracted 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 the binlog files from the time the full backup was taken onwards.
        1. Start MySQL.
        1. Run a recovery query to the started MySQL.
          1. This will restore the full backup plus any differences since then.
           

          Conclusion

          As in this case, the CLONE PLUGIN can be used in a similar way to 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 as described here.
           
          Although I did not introduce it this time, the CLONE PLUGIN makes adding replicas much easier than the conventional replica adding 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 are interested, please contact us here.