MySQL 8 has recently released clone plugin which makes DBA’s task of rebuilding the DB servers more easy.

• Cloning is a process of creating an exact copy of the original. In technical terms cloning alias to (Backup + Recovery), MySQL database cloning requires a sequence of actions to be performed manually or in a scripted fashion with and without the tools involved.
• Cloning is the first step when you want to configure the replication slave or Joining a new server to the InnoDB cluster. There was no native support for auto provisioning earlier. Percona XtraDB Cluster (MySQL + Galera Cluster) does cloning using xtrabackup tool by default when a new node joins the cluster.
• Now MySQL simplified this task, In this post, We will see how to clone the database using clone plugin and its internals.

Clone Plugin :

• Clone Plugin was bundled with MySQL 8.0.17 , which enables the automatic node provisioning from an existing node ( Donor).
• The clone plugin permits cloning data locally or from a remote MySQL server instance. The cloned data is a physical snapshot of data stored in InnoDB.

Types of cloning :

1. Remote cloning
2. Local cloning

Remote Cloning :

• The remote cloning operation is initiated on the local server (recipient), cloned data is transferred over the network from the Remote server (donor) to the recipient.
• By default, during remote cloning operation removes the data in the recipient data directory and replaces it with the cloned data.
• Optionally, you can clone data to a different directory on the recipient to avoid removing existing data.

Local Cloning :

• The clone plugin permits cloning data locally. Cloned data is a physical snapshot of data stored in InnoDB that includes schemas, tables, tablespaces, and data dictionary.
• The cloned data comprises a fully functional data directory, which permits using the clone plugin for MySQL server provisioning.

Plugin Installation :

• To load the plugin at server startup we need to add the following in my.cnf file and restart the server for the new settings to take effect.

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

Runtime Plugin installation :

• We can load the plugin at runtime, use the below statement,

 
mysql> install plugin clone soname 'mysql_clone.so';
Query OK, 0 rows affected (0.27 sec)
	

• In this install plugin registers the mysql.plugins system table to cause the plugin to be loaded.
• To check whether the plugin is loaded we can use information_schema.

 
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name='clone';
+-------------+---------------+
| plugin_name | plugin_status |
+-------------+---------------+
| clone       | ACTIVE        |
+-------------+---------------+
1 row in set (0.00 sec)
	

Cloning Remote Data :

Remote Cloning Prerequisites

1) To perform a cloning operation, the clone plugin must be active on both the donor and recipient MySQL servers.

2) A MySQL user on the donor and recipient is required for executing the cloning operation It’s called “clone user”.

3) The donor and recipient must have the same MySQL server version 8.0.17 and higher.

4) The donor and recipient MySQL server instances must run on the same operating system and platform.

Required Privileges :

1) The donor node clone user requires the “BACKUP_ADMIN” privilege for accessing and transferring data from the donor, and for blocking DDL during the cloning operation.

2) The recipient, the clone user requires the “CLONE_ADMIN” privilege for replacing recipient data, blocking DDL during the cloning operation, and automatically restarting the server.

Step 1 :

• Login the Donor node and create a new clone user with required privilege.

 
mysql> create user 'mydbops_clone_user'@'%' identified by 'Mydbops@8017';
Query OK, 0 rows affected (0.04 sec)

mysql> grant backup_admin on *.* to 'mydbops_clone_user'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'mydbops_clone_user'@'%';
+-------------------------------------------------------+
| Grants for mydbops_clone_user@%                       |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `mydbops_clone_user`@`%`        |
| GRANT BACKUP_ADMIN ON *.* TO `mydbops_clone_user`@`%` |
+-------------------------------------------------------+
2 rows in set (0.01 sec)
	

Step 2 :

• Login the recipient node and create a new clone user with required privilege.

 
mysql> create user 'mydbops_clone_user'@'%' identified by 'Mydbops@8017';
Query OK, 0 rows affected (0.04 sec)

mysql> grant clone_admin on *.* to 'mydbops_clone_user'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'mydbops_clone_user'@'%';
+------------------------------------------------------+
| Grants for mydbops_clone_user@%                      |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `mydbops_clone_user`@`%`       |
| GRANT CLONE_ADMIN ON *.* TO `mydbops_clone_user`@`%` |
+------------------------------------------------------+
2 rows in set (0.00 sec)
	

Step 3 :

• By default, a remote cloning operation removes the data in the recipient data directory and replaces it with the cloned data. By cloning to a named directory, you can avoid removing existing data from the recipient data directory.
• Here i am cloned the remote server data do different location using “DATA DIRECTORY” option.

 
mysql> clone instance from mydbops_clone_user@192.168.33.11:3306 identified by 'Mydbops@8017' data directory='/var/lib/mysql_backup/mysql';
Query OK, 0 rows affected (4.94 sec)
	

 
[root@mydbopslabs12 mysql]# pwd
/var/lib/mysql_backup/mysql
	

 
[root@mydbopslabs12 mysql]# ls -lrth
total 152M
drwxr-x---. 2 mysql mysql 6 Aug 25 09:12 mysql
drwxr-x---. 2 mysql mysql 28 Aug 25 09:12 sys
drwxr-x---. 2 mysql mysql 30 Aug 25 09:12 accounts
-rw-r-----. 1 mysql mysql 3.4K Aug 25 09:12 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12M Aug 25 09:12 ibdata1
-rw-r-----. 1 mysql mysql 23M Aug 25 09:12 mysql.ibd
-rw-r-----. 1 mysql mysql 10M Aug 25 09:12 undo_002
-rw-r-----. 1 mysql mysql 10M Aug 25 09:12 undo_001
-rw-r-----. 1 mysql mysql 48M Aug 25 09:12 ib_logfile0
-rw-r-----. 1 mysql mysql 48M Aug 25 09:12 ib_logfile1
drwxr-x---. 2 mysql mysql 89 Aug 25 09:12 #clone
	

Local Cloning :

• Cloning data from the local MySQL data directory to another directory on the same server where the MySQL server instance runs.

Step 1 :

 
mysql> grant BACKUP_ADMIN ON *.* TO 'mydbops_clone_user'@'%';
Query OK, 0 rows affected (0.10 sec)
	

Step 2 :

 
mysql> clone local data directory='/vagrant/clone_backup/mysql';
Query OK, 0 rows affected (3.94 sec)
	

Note :

• The MySQL server must have the necessary write access to create the directory.
• A local cloning operation does not support cloning of user-created tables or table-spaces that reside outside of the data directory.

How does the clone Plugin Works ?

• I have two standalone servers with the same configuration.

 
1) 192.168.33.25 -
   * 2 core
   * 4GB RAM
   * 50 GB SSD

2) 192.168.33.26 -
   * 2 core
   * 4GB RAM
   * 50 GB SSD
	

• i have installed the MySQL 8.0.17 and enabled the clone plugin for those two servers, and created the above mentioned users for donor & recipient nodes.

 
Donor  192.168.33.25
Recipient 192.168.33.26
	

Step 1 :

• I have created mydbops database and created 3 tables then loaded 2M records for each tables in donor node.

Step 2 :

• I have created another database called sysbench and started to loading the data.

Example :

 
[root@mydbopslabs25 sysbench]# sysbench oltp_insert.lua --table-size=2000000 --num-threads=2 --rand-type=uniform --db-driver=mysql --mysql-db=sysbench --tables=10 --mysql-user=test --mysql-password=Secret!@817 prepare
WARNING: --num-threads is deprecated, use --threads instead
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Initializing worker threads...

Creating table 'sbtest1'...
Creating table 'sbtest2'...
Inserting 2000000 records into 'sbtest1'
Inserting 2000000 records into 'sbtest2'
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest2'...
.
.
.
Inserting 2000000 records into 'sbtest8'
.
.
Inserting 2000000 records into 'sbtest10'
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest10'...
	

Step 3 :

• At the same time i have added the address of donor MySQL server instance (with port) in recipient node.

Example :

 
mysql > set global clone_valid_donor_list='192.168.33.25:3306';
Query OK, 0 rows affected (0.01 sec)
	

Step 4:

• initialised the cloning process in recipient node.

 
mysql> clone instance from mydbops_clone_user@192.168.33.25:3306 identified by 'Mydbops@123';
Query OK, 0 rows affected (4 min 20.48 sec)
	

• While running the cloning process i have analysed the mysql data directory, how it will clone the data and how it’s replacing data directory files.
• During this process it will not overwrite the existing undo & redo log files.It will create new files like this.

 
-rw-r-----. 1 mysql mysql 23M Aug 25 10:44 mysql.ibd.#clone
-rw-r-----. 1 mysql mysql 5.3K Aug 25 10:44 ib_buffer_pool.#clone
-rw-r-----. 1 mysql mysql 12M Aug 25 10:45 ibdata1.#clone
-rw-r-----. 1 mysql mysql 40M Aug 25 10:48 undo_002.#clone
-rw-r-----. 1 mysql mysql 40M Aug 25 10:48 undo_002.#clone
	

• Inside the data directory it will create #clone directory following files are created.

1.#view_progress: persists performance_schema.clone_progress data

2.#view_status: Persists persists performance_schema.clone_status data

3.#status_in_progress: Temporary file that exists when clone in progress

4.#status_error: Temporary file to indicate incomplete clone.

5.#status_recovery: Temporary file to hold recovery status information

6.#new_files: List of all files created during clone

7.#replace_files: List of all files to be replaced during recovery

• once the cloning process is completed it will swap the file and restart the mysql service.

• During this cloning we are able to access the data inside mysql in recipient node.It will close the connection while restarting (Swapping the files )the mysqld service.

Example :

 
[root@mydbopslabs26 vagrant]# mysql -e "select count(*) from mydbops.t1;select sleep(30);select count(*) from mydbops.t1;"
+----------+
| count(*) |
+----------+
| 2000000  |
+----------+
+-----------+
| sleep(30) |
+-----------+
| 0         |
+-----------+
ERROR 2006 (HY000) at line 1: MySQL server has gone away
	

• After the completion of cloning process will maintain a few stats in #clone directory.
• This directory will be located inside the mysql data directory.

1) #view_status

• This file will maintain the donor node host and mysql port details

2) #view_progress

• In this file will maintain the Progress of cloning status

Example :

 
2 1 1568463987624627 1568463988356572 0 0 0
2 2 1568463988357856 1568464039117879 1630655790 1630655790 1630750990
2 2 1568464039120173 1568464039648790 0 0 197
	

• Here “1568464039120173” is an epoch timestamp.

3) #status_recovery

• This file contain binlog co-ordinates.

Example :

 
./binlog.000011
190479203
	

Note :

• We can get those stat’s from performance schema too.

Page Tracking :

How the active changes to DB are tracked ?

• The pages modified during the cloning process are tracked either during mtr (mini transaction) address them to flush list or when they are  flushed to disk by I/O threads. We choose to track

Consistency of this phase is defined as follows,

* At start, it guarantees to track all pages that are not yet flushed. All
flushed pages would be included in “FILE COPY”.

* At end, it ensures that the pages are tracked at least up to the checkpoint
LSN. All modifications after checkpoint would be included in “REDO COPY”.

Monitoring Cloning Operations:

Is it possible to monitor the cloning progress ?

• Yes , A cloning operation may take a long/short time to complete, depending on the amount of data and other factors related to data transfer.
• You can monitor the status and progress of a cloning operation using performance schema.
• In Mysql 8.0.17 they introduced new Clone tables and Clone Instrumentation are introduced as well

Note :

• The clone_status and clone_progress Performance Schema tables can be used to monitor a cloning operation on the recipient MySQL server instance only.
• The clone_status table provides the state of the current or last executed cloning operation.
• A clone operation has four possible states:
  • Not Started
  • In Progress
  • Completed,
  • Failed.

Example :

 
mysql> select stage,state,begin_time as start_time,end_time,data,network from performance_schema.clone_progress;
+-----------+-------------+----------------------------+----------------------------+----------+----------+
| stage     | state       | start_time                 | end_time                   | data     | network  |
+-----------+-------------+----------------------------+----------------------------+----------+----------+
| DROP DATA | Completed   | 2019-08-25 09:27:53.725694 | 2019-08-25 09:27:53.922072 | 0        |  0       |
| FILE COPY | Completed   | 2019-08-25 09:27:53.922424 | 2019-08-25 09:27:54.651132 | 57904527 | 57915509 |
| PAGE COPY | Completed   | 2019-08-25 09:27:54.651463 | 2019-08-25 09:27:54.756606 | 0        | 99       |
| REDO COPY | Completed   | 2019-08-25 09:27:54.756926 | 2019-08-25 09:27:54.858837 | 2560     | 3031     |
| FILE SYNC | Completed   | 2019-08-25 09:27:54.859098 | 2019-08-25 09:27:55.273789 | 0        | 0        |
| RESTART   | Not Started | NULL                       | NULL                       | 0        | 0        |
| RECOVERY  | Not Started | NULL                       | NULL                       | 0        | 0        |
+-----------+-------------+----------------------------+----------------------------+----------+----------+
7 rows in set (0.00 sec)
	

Snapshot Status :

INIT :

• The clone object is initialized identified by a Donor.

FILE COPY :

• The state changes from INIT to “FILE COPY” when snapshot_copy interface is called.
• Before making the state change we start “Page Tracking” at lSN “CLONE START LSN”.
• In this state we copy all database files and send to the recipient.

PAGE COPY :

• The state changes from “FILE COPY” to “PAGE COPY” after all files are copied and sent.
• Before making the state change we start “Redo Archiving” at lsn “CLONE FILE END LSN” and stop “Page Tracking”.
• In this state, all modified pages as identified by Page IDs between “CLONE START LSN” and “CLONE FILE END LSN” are read from “buffer pool” and sent.
• We would sort the pages by space ID, page ID to avoid random read(donor) and random write(recipient) as much as possible.

REDO COPY :

• The state changes from “PAGE COPY” to “REDO COPY” after all modified pages are sent.
• Before making the state change we stop “Redo Archiving” at lsn “CLONE LSN”.
• This is the LSN of the cloned database. We would also need to capture the replication coordinates at this point in future.
• It should be the replication coordinate of the last committed transaction up to the “CLONE LSN”.
• We send the redo logs from archived files in this state from “CLONE FILE END LSN” to “CLONE LSN” before moving to “Done” state.

Done :

• The clone object is kept in this state till destroyed by snapshot_end() call.

Performance Schema to Monitor Cloning:

• There are three stages of events for monitoring progress of a cloning operation.
• Each stage event reports WORK_COMPLETED and WORK_ESTIMATED values. Reported values are revised as the operation progresses.

1)stage/innodb/clone (file copy) :

• Indicates progress of the file copy phase of the cloning operation.
  • The number of files to be transferred is known at the start of the file copy phase, and the number of chunks is estimated based on the number of files.

2) stage/innodb/clone (page copy) :

• Indicates progress of the page copy phase of cloning operation.
• Once the file copy phase is completed, the number of pages to be transferred is known, and WORK_ESTIMATED is set to this value.

3) stage/innodb/clone (redo copy) :

• Indicates progress of the redo copy phase of cloning operation.
• Once the page copy phase is completed, the number of redo chunks to be transferred is known, and WORK_ESTIMATED is set to this value.

Enabling Monitoring

 
mysql> update performance_schema.setup_instruments set ENABLED='YES' where NAME LIKE 'stage/innodb/clone%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
	

• For more details refer the MySQL Manual page Clone-Plugin-Monitoring

Replication Configuration :

• The clone plugin supports replication, In addition to cloning data, a cloning operation extracts and transfers replication coordinates from the donor and applies them on the recipient.
• The clone plugin for provisioning is considerably faster and more efficient than replicating a large number of transactions.
• Both binary log position and GTID coordinates are extracted and transferred from the donor MySQL server instance.

Binlog and position :

• The binlog and position’s are stored in clone_status table.Need to check this log file and position in donor node.

 
mysql> select binlog_file,binlog_position from performance_schema.clone_status;
+------------------+-----------------+
| binlog_file      | binlog_position |
+------------------+-----------------+
| mysql-bin.000479 | 483007997       |
+------------------+-----------------+
	

• If you are using GTID use below query,

 
mysql> select @@global.gtid_executed;
	

• Here i am using binlog co-ordinates for replication.

 
mysql> change master to master_host ='192.168.33.11', master_port =3306,master_log_file ='mysql-bin.000479',master_log_pos =483007997;

mysql> start slave user='repl' password='Repl@123';
	

Limitations :

• The clone plugin has some limitations,
• DDL, including TRUNCATE TABLE, is not permitted during a cloning operation. Concurrent DML is permitted.
• An instance cannot be cloned from a different MySQL server version. The donor and recipient must have the same MySQL server version.
• The clone plugin does not support cloning of binary logs.
• The clone plugin only clones data stored in InnoDB. Other storage engine data is not cloned MyISAM and CSV engine tables.

Conclusion :

• I believe for now creating replicas has become much easier with the help of the MySQL 8.0.17 clone plugin.
• The clone plugin can be used to set up not only asynchronous replicas but provisioning Group Replication members also.