Backups Using the MySQL Clone Operation

Tue, 2026-07-28 11:40 — Shinguz

We recently tested the PostgreSQL backup tool pg_basebackup and were very impressed with its remote backup functionality, which allows for both physical local and physical remote backups.

This led us to wonder whether a physical remote backup is also possible using the “new” MySQL Server Clone feature, which was introduced in MySQL 8.0.17 (July 2019).

The MySQL Clone operation can be used to create both a local and a remote copy of the database. The original idea behind this feature was likely to automatically create nodes in an InnoDB Cluster (similar to Percona XtraDB Cluster SST).

Terminology used in the clone operation:

  • Donor (source database)
  • Recipient (destination database)

The clone operation is initiated from the recipient. The data can be cloned to the recipient’s own directory or, alternatively, to a different directory.

Preparations

The plugin must be installed on both the Donor and the Recipient.

SQL> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.01 sec)

SQL> SELECT PLUGIN_NAME, PLUGIN_STATUS
  FROM INFORMATION_SCHEMA.PLUGINS
 WHERE PLUGIN_NAME = 'clone'
;
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| clone       | ACTIVE        |
+-------------+---------------+

If you want to force the plugin to load on restart, it must be configured as follows in the MySQL configuration file (my.cnf):

[mysqld]
plugin_load_add = mysql_clone.so
clone           = FORCE_PLUS_PERMANENT

Local Backup Using Clone

This method can serve as a replacement for a physical backup solution (xtrabackup or MySQL Enterprise Backup (mysql_backup)). On the database acting as the recipient in this case, execute the following command:

SQL> CLONE LOCAL DATA DIRECTORY = '/mnt/backup/mysql_clone';

The following items are still missing for the clone operation:

  • All TLS keys (*.pem files).
  • The auto.cnf file, which contains the server_uuid.
  • The mysqld-auto.cnf file, which contains dynamically modified, persistent server configuration variables.
  • The mysql_upgrade_history.
  • The MySQL configuration file (my.cnf) as well as
  • The binary logs.
$ cp ${datadir}/*auto.cnf ${datadir}/mysql_upgrade_history ${datadir}/*.pem /mnt/backup/mysql_clone/

Restoring the database is quite simple:

$ systemctl stop mysql
$ rm -rf ${datadir}/*
$ cp -a /mnt/backup/mysql_clone/* ${datadir}/
$ chown -R mysql: ${datadir}/*
$ systemctl start mysql

The #clone folder is created by the clone operation and can be ignored, but must not be deleted.

$ ls -lad /mnt/backup/mysql_clone/*
...
drwxr-x--- 2 dba dba     4096 Jul 27 14:52 '#clone'
...

It is automatically removed when the MySQL database is started. If you delete it anyway, you will receive the following error messages:

[System] [MY-013576] [InnoDB] InnoDB initialization has started.
[System] [MY-013577] [InnoDB] InnoDB initialization has ended.
mysqld: Can't create/write to file './performance_schema/clone_status_385.sdi' (OS errno 2 - No such file or directory)
mysqld: Can't create file './performance_schema/clone_status_385.sdi' (errno: 2 - No such file or directory)
[ERROR] [MY-013272] [Clone] Plugin Clone reported: 'Client: PFS table creation failed.'
[ERROR] [MY-010202] [Server] Plugin 'clone' init function returned error.

The binary log position required for point-in-time recovery can be determined as follows:

SQL> SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;
+-------------------------------+-----------------+
| BINLOG_FILE                   | BINLOG_POSITION |
+-------------------------------+-----------------+
| boss_percona-84_binlog.000003 |         1231898 |
+-------------------------------+-----------------+

Remote Backup Using Clone

To create a remote backup using the clone functionality, a minimally functional MySQL database is required on the remote system. Unfortunately, a simple process or tool is not sufficient for this.

On the donor server, you need a user with the following privileges:

SQL> CREATE USER 'backup_user'@'%' IDENTIFIED BY 'secret';
SQL> GRANT BACKUP_ADMIN ON *.* TO 'backup_user'@'%';

In addition, the potential donor must be specified on the recipient server:

SQL> SET GLOBAL clone_valid_donor_list = '192.168.1.129:3306';

The remote backup is then performed as follows:

SQL> CLONE INSTANCE FROM 'backup_user'@'192.168.1.129':3306 IDENTIFIED BY 'secret'
DATA DIRECTORY = '/mnt/backup/mysql_clone';

The missing files described above must now also be copied somehow:

$ scp mysql@192.168.1.129:${datadir}/*auto.cnf /mnt/backup/mysql_clone/
$ scp mysql@192.168.1.129:${datadir}/mysql_upgrade_history /mnt/backup/mysql_clone/
$ scp mysql@192.168.1.129:${datadir}/*.pem /mnt/backup/mysql_clone/

Restoring the database is done in the same way as described above.

Conclusion

The MySQL Clone operation is a cool feature that I neglected for a long time because it never occurred to me that it could also be used for backup purposes.

I wouldn’t be surprised if the MySQL developers took a cue from PostgreSQL’s pg_basebackup when they implemented this feature.

Unfortunately, to my knowledge, this feature is still completely missing in MariaDB. Too bad!

Sources

tags: 
mysql percona point-in-time-recovery pitr restore recovery backup remote clone