MySQL 8, cloning instances for replication using CLONE statement

Introduction

The number of manual steps to setup a replication is high (binary log file position based or GTID based replication) , especially when the source server databases snapshot is performed.

As of MySQL 8.0.17, the new CLONE plugin simplifies the replica provisioning.

In this paper, a MySQL instance is cloned for replication using this plugin.

The following terminology is adopted when using the CLONE plugin :

The source server is called the donor server.
The target server is called the recipient server.

The CLONE operation does not alter donor and recipient configuration files, nor their startup parameters.

Some limitations :

DDL statements (CREATE DROP ALTER) and TRUNCATE statements are not permitted during a cloning operation, only concurrent DML statements are allowed. DDL statements are blocked in the donor server when a cloning operation is undergoing.
MySQL versions must be exactly the same, including minor versions.
Binary logs are not cloned.
Only InnoDB tables are cloned. MyISAM or CSV tables are cloned but only the structures, not the data.

The CLONE operation progress can be monitored.

Let’s explore this new feature : steps, internal mechanism… A remote cloning is performed in this paper.

Installing the CLONE plugin

On both servers (donor and recipient), the CLONE plugin is installed : at server startup or at runtime.

At server startup :

$CFG/vpsfrsqlpac(1|2).cfg

[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

At runtime :

(vpsfrsqlpac1|2) mysql> INSTALL PLUGIN CLONE SONAME 'mysql_clone.so';

If there is an issue while installing, check the variable plugin_dir :

(vpsfrsqlpac1|2) mysql> SHOW VARIABLES LIKE 'plugin_dir';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| plugin_dir    | /opt/mysql/mysql-8.0/lib/plugin/ |
+---------------+----------------------------------+

To check the installation :

(vpsfrsqlpac1|2) mysql> SELECT plugin_name, plugin_status
                           FROM information_schema.plugins
                           WHERE plugin_name='clone';
+-------------+---------------+
| plugin_name | plugin_status |
+-------------+---------------+
| clone       | ACTIVE        |
+-------------+---------------+

Checking prerequisites (optional)

Check some prerequisites if a customization has been setup (innodb_data_file_path, undo files locations and names…). If there is no customization, skip this step.

First check the variables innodb_data_file_path and innodb_page_size, they must be the same :

(vpsfrsqlpac1|2) mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';
(vpsfrsqlpac1|2) mysql> SHOW VARIABLES LIKE 'innodb_page_size';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_page_size      | 16384                  |
+-----------------------+------------------------+

Undo tablespace file names on the donor must be unique because they will be grouped and cloned in the recipient to the innodb_undo_directory location.

(vpsfrsqlpac1) mysql> SELECT tablespace_name, file_name FROM information_schema.files
                           WHERE file_type LIKE 'UNDO LOG';
+-----------------+------------+
| TABLESPACE_NAME | FILE_NAME  |
+-----------------+------------+
| innodb_undo_001 | ./undo_001 |
| innodb_undo_002 | ./undo_002 |
+-----------------+------------+

The max_allowed_packet parameter must be greater than 2MB on the donor and the recipient servers :

(vpsfrsqlpac1|2) mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+

Preparing the donor server

A user with the privilege BACKUP_ADMIN is created in the donor server :

(vpsfrsqlpac1) mysql> CREATE USER clone_user@'%' IDENTIFIED BY '***************';
(vpsfrsqlpac1) mysql> GRANT BACKUP_ADMIN on *.* TO clone_user@'%';
          
(vpsfrsqlpac1) mysql> SHOW GRANTS FOR clone_user@'%';
+-----------------------------------------------+
| Grants for clone_user@%                       |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `clone_user`@`%`        |
| GRANT BACKUP_ADMIN ON *.* TO `clone_user`@`%` |
+-----------------------------------------------+

Preparing the recipient server

Bear in mind that the mysql database is also cloned : the root account properties and password will be those of the donor server. So, backup some informations before cloning if needed :

Users, passwords, authentication plugin (mysql.user).
Grants.
Foreign servers (mysql.servers) …

If the cloning operation is not performed using root account, the account used for cloning must have the privilege CLONE_ADMIN :

(vpsfrsqlpac2) mysql> CREATE USER clone_user@'%' IDENTIFIED BY '***************';
(vpsfrsqlpac2) mysql> GRANT CLONE_ADMIN on *.* TO clone_user@'%';
          
(vpsfrsqlpac2) mysql> SHOW GRANTS FOR clone_user@'%';
+----------------------------------------------+
| Grants for clone_user@%                      |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `clone_user`@`%`       |
| GRANT CLONE_ADMIN ON *.* TO `clone_user`@`%` |
+----------------------------------------------+

The privilege CLONE_ADMIN includes the BACKUP_ADMIN and SHUTDOWN privileges.

The donor server address is added in the variable clone_valid_donor_list, it is a layer of security :

(vpsfrsqlpac2) mysql> SET GLOBAL clone_valid_donor_list = 'vpsfrsqlpac1:40008';

Executing the CLONE statement

The CLONE statement is executed from the recipient server :

(vpsfrsqlpac2) mysql> CLONE INSTANCE FROM clone_user@vpsfrsqlpac1:40008 IDENTIFIED BY '**********'

Add the option DATA DIRECTORY = '<dir>' to clone an instance in another directory without removing the existing recipient server data. Using this option, the cloning operation does not restart the recipient server to switch to the new directory.

Checking progress

The cloning progress status is available in the table performance_schema.clone_status :

SELECT state as "State", 
       CAST(BEGIN_TIME AS DATETIME) as "Start time",
       CASE WHEN end_time IS NULL THEN
         LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(begin_time))), 10, ' ')
       ELSE
         LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(begin_time))), 10, ' ')
       END as "Duration"
FROM performance_schema.clone_status;
+-------------+---------------------+------------+
| State       | Start time          | Duration   |
+-------------+---------------------+------------+
| In Progress | 2021-01-20 01:34:29 |     1.22 m |
+-------------+---------------------+------------+

The cloning progress details can be monitored querying the table performance_schema.clone_progress :

SELECT stage as "Stage", 
       state as "State",
       
       CAST(begin_time AS TIME) as "Start time",
       
       CASE WHEN end_time IS NULL THEN
          LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(begin_time))), 10, ' ')
       ELSE
          LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(begin_time))), 10, ' ')
       END as "Duration",
       
       LPAD(CONCAT(ROUND(estimate/1024/1024,0), " MB"), 16, ' ') as "Estimate",
       
       CASE WHEN begin_time IS NULL THEN LPAD('0%', 7, ' ')
            WHEN estimate > 0 THEN
               LPAD(CONCAT(CAST(ROUND(data*100/estimate, 0) AS CHAR), "%"), 7, ' ')
            WHEN end_time IS NULL THEN LPAD('0%', 7, ' ')
            ELSE LPAD('100%', 7, ' ')
       END as "Done(%)"

  FROM performance_schema.clone_progress;
+-----------+-------------+------------+------------+------------------+---------+
| Stage     | State       | Start time | Duration   | Estimate         | Done(%) |
+-----------+-------------+------------+------------+------------------+---------+
| DROP DATA | Completed   | 09:59:21   |  845.87 ms |             0 MB |    100% |
| FILE COPY | In Progress | 09:59:21   |     1.98 m |          3254 MB |     42% |
| PAGE COPY | Not Started | NULL       | NULL       |             0 MB |      0% |
| REDO COPY | Not Started | NULL       | NULL       |             0 MB |      0% |
| FILE SYNC | Not Started | NULL       | NULL       |             0 MB |      0% |
| RESTART   | Not Started | NULL       | NULL       |             0 MB |      0% |
| RECOVERY  | Not Started | NULL       | NULL       |             0 MB |      0% |
+-----------+-------------+------------+------------+------------------+---------+

+-----------+-----------+------------+------------+------------------+---------+
| Stage     | State     | Start time | Duration   | Estimate         | Done(%) |
+-----------+-----------+------------+------------+------------------+---------+
| DROP DATA | Completed | 09:59:21   |  845.87 ms |             0 MB |    100% |
| FILE COPY | Completed | 09:59:21   |     4.65 m |          3254 MB |    100% |
| PAGE COPY | Completed | 10:04:01   |  301.54 ms |             0 MB |    100% |
| REDO COPY | Completed | 10:04:01   |  300.47 ms |             0 MB |    100% |
| FILE SYNC | Completed | 10:04:01   |  477.28 ms |             0 MB |    100% |
| RESTART   | Completed | 10:04:02   |     3.06 s |             0 MB |    100% |
| RECOVERY  | Completed | 10:04:05   |  470.53 ms |             0 MB |    100% |
+-----------+-----------+------------+------------+------------------+---------+

The final status (success / failure) is stored in the table performance_schema.clone_status :

(vpsfrsqlpac2) mysql> SELECT state,
                                begin_time, end_time,
                                source,
                                error_no, error_message
                                FROM performance_schema.clone_status
+-----------+-------------------------+-------------------------+--------------------+----------+---------------+
| state     | begin_time              | end_time                | source             | error_no | error_message |
+-----------+-------------------------+-------------------------+--------------------+----------+---------------+
| Completed | 2021-01-20 09:59:21.126 | 2021-01-20 10:04:06.204 | vpsfrsqlpac1:40008 |        0 |               |
+-----------+-------------------------+-------------------------+--------------------+----------+---------------+

Handling errors

A backup lock is taken to block DDL operations in the donor server and the timeout to acquire this lock is controlled by the parameter clone_ddl_timeout : DDL operations may generate an error if this timeout is reached.

If any error occurs, the cloning operation is rolled back and all cloned data removed. At this stage, user data has been destroyed in the recipient server (databases…), but not the mysql database, the recipient server is always in a consistent state to run admin commands. The mysql database swapping by its clone is performed just before the restart final step, the cloning can be re-executed with no extra steps once the error root cause has been identified and corrected.

+--------+-------------------------+-------------------------+--------------------+----------+---------------------------+
| state  | begin_time              | end_time                | source             | error_no | error_message             |
+--------+-------------------------+-------------------------+--------------------+----------+---------------------------+
| Failed | 2021-01-20 12:20:50.535 | 2021-01-20 12:23:26.844 | vpsfrsqlpac1:40008 |     3862 | Clone Donor Error: 1317 : |
|        |                         |                         |                    |          | Query execution was       |
|        |                         |                         |                    |          | interrupted.              |
+--------+-------------------------+-------------------------+--------------------+----------+---------------------------+

(vpsfrsqlpac2) mysql> CLONE INSTANCE FROM clone_user@vpsfrsqlpac1:40008 IDENTIFIED BY '**********';

The stage in error is retrieved by querying the performance_schema.clone_progress table :

+-----------+-------------+------------+------------+------------------+---------+
| Stage     | State       | Start time | Duration   | Estimate         | Done(%) |
+-----------+-------------+------------+------------+------------------+---------+
| DROP DATA | Completed   | 02:20:51   |  917.33 ms |             0 MB |    100% |
| FILE COPY | Failed      | 02:20:52   |     2.59 m |          3254 MB |     55% |
| PAGE COPY | Not Started | NULL       | NULL       |             0 MB |      0% |
| REDO COPY | Not Started | NULL       | NULL       |             0 MB |      0% |

About non InnoDB tables (MyISAM, CSV), a warning is raised in the recipient log file :

2021-01-20T09:52:23.533528Z 0 [Warning] [MY-013461] [InnoDB] Non innodb table: sqlpac.article_en_details is not cloned and is empty.

Cloning steps

User databases are dropped to be replaced but the process does not overwrite system files (mysql system tablespace, undo, innodb default tablespace, buffer pool…), it creates .#clone files for them :

-rw-r----- 1 mysql dba    68852 Jan 20 09:38  ib_buffer_pool
-rw-r----- 1 mysql dba    86048 Jan 20 09:38  ib_buffer_pool.#clone
-rw-r----- 1 mysql dba 79691776 Jan 20 09:38  ibdata1
-rw-r----- 1 mysql dba 79691776 Jan 20 09:38  ibdata1.#clone
-rw-r----- 1 mysql dba 26214400 Jan 20 09:38  mysql.ibd
-rw-r----- 1 mysql dba 26214400 Jan 20 09:38  mysql.ibd.#clone
-rw-r----- 1 mysql dba 22020096 Jan 20 09:38  undo_001
-rw-r----- 1 mysql dba        0 Jan 20 09:38  undo_001.#clone
-rw-r----- 1 mysql dba 22020096 Jan 20 09:38  undo_002
-rw-r----- 1 mysql dba        0 Jan 20 09:38  undo_002.#clone
drwxr-x--- 2 mysql dba     4096 Jan 20 09:38 '#clone'

A directory #clone is created, this directory contains persistent or temporary files for the cloning internal mechanics :

#new_files
#old_files
#replace_files
#status_error
#status_files
#status_in_progress
#status_recovery
#view_progress
#view_status

The last 2 files #view_progress and #view_status are persistent and used to populate respectively the performance_schema.clone_progress and performance_schema.clone_status tables.

When the cloning process comes to an end, it swaps the .#clone files and if the option DATA DIRECTORY is not used it restarts the recipient server with the new MySQL 8.0 SQL command RESTART.

Enabling replication

During the cloning process, the binlog file + position and the GTID value if exists are extracted from the donor server and transferred to the recipient server in the performance_schema.clone_status table.

(vpsfrsqlpac2) mysql> SELECT * FROM performance_schema.clone_status \G;
             ID: 1
            PID: 0
          STATE: Completed
     BEGIN_TIME: 2021-01-19 09:32:17.855
       END_TIME: 2021-01-19 09:37:02.582
         SOURCE: vpsfrsqlpac1:40008
    DESTINATION: LOCAL INSTANCE
       ERROR_NO: 0
  ERROR_MESSAGE:
    BINLOG_FILE: binlog-srvmysqlpac.000010
BINLOG_POSITION: 184592856
  GTID_EXECUTED:

That’s all ! Replication can be started using these values (binary log file + position in this use case) :

MySQL >= 8.0.23

	MySQL >= 8.0.23
`(vpsfrsqlpac2) mysql> CHANGE MASTER TO MASTER_HOST='vpsfrsqlpac1', MASTER_PORT=40008, MASTER_USER='standby_user', MASTER_PASSWORD='*************', MASTER_LOG_FILE='binlog-srvmysqlpac.000010', MASTER_LOG_POS=184592856, GET_MASTER_PUBLIC_KEY=1; START REPLICA;`	`(vpsfrsqlpac2) mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='vpsfrsqlpac1', SOURCE_PORT=40008, SOURCE_USER='standby_user', SOURCE_PASSWORD='*************', SOURCE_LOG_FILE='binlog-srvmysqlpac.000010', SOURCE_LOG_POS=184592856, GET_SOURCE_PUBLIC_KEY=1; START REPLICA;`

(vpsfrsqlpac2) mysql>
                
CHANGE MASTER
   TO MASTER_HOST='vpsfrsqlpac1',
      MASTER_PORT=40008,
      MASTER_USER='standby_user',
      MASTER_PASSWORD='*************',
      MASTER_LOG_FILE='binlog-srvmysqlpac.000010',
      MASTER_LOG_POS=184592856,
      GET_MASTER_PUBLIC_KEY=1;
      
START REPLICA;

(vpsfrsqlpac2) mysql>
                    
CHANGE REPLICATION SOURCE
   TO SOURCE_HOST='vpsfrsqlpac1',
      SOURCE_PORT=40008,
      SOURCE_USER='standby_user',
      SOURCE_PASSWORD='*************',
      SOURCE_LOG_FILE='binlog-srvmysqlpac.000010',
      SOURCE_LOG_POS=184592856,
      GET_SOURCE_PUBLIC_KEY=1;

START REPLICA;

No need to redefine the replication filters : they are specified in the configuration files or in the startup parameters, they are not modified and affected by the CLONE / RESTART commands.

Conclusion

The new CLONE plugin is powerful for provisioning replicas. 2 major inconvenients :

DDL and TRUNCATE statements are locked while cloning a source server : some applications intensively use these commands (temporary tables…).
MySQL versions must be exactly the same, due to the MySQL dictionary cloned in mysql database.