Create MySQL Instance Replica using Clone Command

MySQL 8 has recently released clone plugin which makes cloning process very easy and handy to perform.

In this blog we will see How to install and perform clone of mysql instance.

I will clone complete mysql instance server_id 3 into server_id 4, Please note in server_id 4 there is no employee and repl_test schema/DB.

Donor

mysql doner >prompt Mysql Doner >
PROMPT set to 'Mysql Doner >'
Mysql Doner >

Mysql Doner >show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| repl_test          |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

Mysql Doner >show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3     |
+---------------+-------+
1 row in set (0.00 sec)

Mysql Doner >

Recipient

mysql Recipient>prompt Mysql Recipient>
PROMPT set to 'Mysql Recipient>'
Mysql Recipient>

Mysql Recipient>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.05 sec)

Mysql Recipient >show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 4     |
+---------------+-------+
1 row in set (0.01 sec)

Step 1. Install clone plugin: Install Clone Plugin in both Donor and Recipient mysql server.

Donor

Mysql Doner >show variables like '%plug%';
+-------------------------------+--------------------------+
| Variable_name                 | Value                    |
+-------------------------------+--------------------------+
| default_authentication_plugin | caching_sha2_password    |
| plugin_dir                    | /usr/lib64/mysql/plugin/ |
+-------------------------------+--------------------------+
2 rows in set (0.01 sec)

Mysql Doner >
Mysql Doner >system ls -l /usr/lib64/mysql/plugin/*clone*
-rwxr-xr-x. 1 root root 2797080 Sep 23 16:56 /usr/lib64/mysql/plugin/mysql_clone.so
Mysql Doner >

Mysql Doner >INSTALL PLUGIN CLONE SONAME "mysql_clone.so";
Query OK, 0 rows affected (0.20 sec)

Mysql Doner >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)

Recipient

Mysql Recipient>show variables like '%plug%';
+-------------------------------+--------------------------+
| Variable_name                 | Value                    |
+-------------------------------+--------------------------+
| default_authentication_plugin | caching_sha2_password    |
| plugin_dir                    | /u01/mysql-8/lib/plugin/ |
+-------------------------------+--------------------------+
2 rows in set (0.39 sec)

Mysql Recipient>
Mysql Recipient>system ls -l /u01/mysql-8/lib/plugin/*clone*
-rwxr-xr-x. 1 mysql mysql 1133424 Sep 23 22:20 /u01/mysql-8/lib/plugin/mysql_clone.so
Mysql Recipient>

Mysql Recipient > INSTALL PLUGIN CLONE SONAME "mysql_clone.so";
Query OK, 0 rows affected (0.50 sec)

Mysql Recipient >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)

Step 2. Create necessary clone user: Create necessary user and provide required privilege’s to clone user. Add variable clone_valid_donor_list in recipient side with correct ip or hostname and port.

Donor

Mysql Doner >INSTALL PLUGIN CLONE SONAME "mysql_clone.so";
Query OK, 0 rows affected (0.20 sec)


Mysql Doner >CREATE USER clone_user IDENTIFIED BY "Root@1234";
Query OK, 0 rows affected (0.00 sec)

Mysql Doner >GRANT BACKUP_ADMIN ON *.* to clone_user;
Query OK, 0 rows affected (0.00 sec)

Mysql Doner >GRANT SELECT ON performance_schema.* TO clone_user;
Query OK, 0 rows affected (0.00 sec)

Mysql Doner >GRANT EXECUTE ON *.* to clone_user;
Query OK, 0 rows affected (0.01 sec)

Recipient

Mysql Recipient >SET GLOBAL clone_valid_donor_list = "192.168.114.170:3307";
Query OK, 0 rows affected (0.00 sec)

Mysql Recipient >CREATE USER clone_user IDENTIFIED BY "Root@1234";
Query OK, 0 rows affected (0.08 sec)

Mysql Recipient >GRANT CLONE_ADMIN ON *.* to clone_user;
Query OK, 0 rows affected (0.05 sec)

Mysql Recipient > GRANT SELECT ON performance_schema.* TO clone_user;
Query OK, 0 rows affected (0.00 sec)

Mysql Recipient >GRANT EXECUTE ON *.* to clone_user;
Query OK, 0 rows affected (0.02 sec)

Mysql Recipient >

Step 3. Start cloning process in recipient server: Use command CLONE INSTANCE to create a replica of the donor in recipient server.

Mysql Recipient >CLONE INSTANCE FROM clone_user@192.168.114.170:3307 IDENTIFIED BY "Root@1234";
Query OK, 0 rows affected (37.98 sec)

Step 4. Monitoring Cloning process : Use below query in recipient side to monitor cloning process.

mysql> select 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-03-09 16:54:57 |    26.22 s |
+-------------+---------------------+------------+
1 row in set (0.41 sec)


mysql> select STAGE, 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(FORMAT(ROUND(ESTIMATE/1024/1024,0), 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 BINARY), "%"), 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 | 16:54:57   |  342.71 ms |             0 MB | 0x20202031303025 |
| FILE COPY | Completed | 16:54:58   |    33.14 s |           359 MB | 0x20202031303025 |
| PAGE COPY | Completed | 16:55:31   |  327.88 ms |             0 MB | 0x20202031303025 |
| REDO COPY | Completed | 16:55:31   |  302.31 ms |             0 MB | 0x20202031303025 |
| FILE SYNC | Completed | 16:55:31   |     3.42 s |             0 MB | 0x20202031303025 |
| RESTART   | Completed | 16:55:35   |      4.2 s |             0 MB | 0x20202031303025 |
| RECOVERY  | Completed | 16:55:39   |     1.43 s |             0 MB | 0x20202031303025 |
+-----------+-----------+------------+------------+------------------+------------------+
7 rows in set (0.09 sec)



mysql>  select STATE, ERROR_NO, BINLOG_FILE, BINLOG_POSITION, GTID_EXECUTED,
    -> CAST(BEGIN_TIME AS DATETIME) as "START TIME",
    -> CAST(END_TIME AS DATETIME) as "FINISH TIME",
    -> sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME)))
    -> as DURATION
    -> from performance_schema.clone_status \G
*************************** 1. row ***************************
          STATE: Completed
       ERROR_NO: 0
    BINLOG_FILE: binlog.000002
BINLOG_POSITION: 1167
  GTID_EXECUTED: 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-32,
98411332-6aad-11eb-809a-000c29f9d9e6:1-4
     START TIME: 2021-03-09 16:54:57
    FINISH TIME: 2021-03-09 16:55:40
       DURATION: 43.59 s
1 row in set (0.00 sec)

mysql>

Step 5. Verify recipient: Very schema/DB employees and repl_test created in recipient side.

Mysql Recipient >show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| repl_test          |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

Mysql Recipient >

Reference: https://mysqlserverteam.com/clone-create-mysql-instance-replica/

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

DBsGuru