Дата публикации: 28-07-2026 09:40:00

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
General: The Clone Plugin
There are a few minor limitations for the clone backup, which are described here: Clone Plugin Limitations.
Monitoring the clone backup is described here: Monitoring Cloning Operations.
Tuning the clone backup is described here: Clone System Variable Reference
Backups Using the MySQL Clone Operation appeared first on MariaDB.org

Основное содержимое страницы с новостью.

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        |
+-------------+---------------+

[mysqld]
plugin_load_add = mysql_clone.so
clone           = FORCE_PLUS_PERMANENT

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

$ cp ${datadir}/*auto.cnf ${datadir}/mysql_upgrade_history ${datadir}/*.pem /mnt/backup/mysql_clone/

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

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

[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.

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

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

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

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

$ 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/

Схожие новости