Create MySQL Instance Replica using Clone Command

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.

 336 Total Views,  3 Views Today

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the Knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

two + 7 =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru
Direct Call:+91-9310167776
Email: info@dbsguru.com

Share Learn Grow!


Click on the below technical group to join us to share expertise in PostgreSQL and MySQL on WhatsApp group. The purpose of this group is only help to the DBA community.

PostgreSQL Technical Group.

MySQL technical Group.

NOTE: Excuse us for spammer/promoter i.e don't join the group for spam, will be kicked off without warnings.

Thanks.
Team DBsGuru.

Share Learn Grow!

Welcome to DBsGuru! We wish you a very healthy day, hope and pray things to go in a good way for all of humanity. Stay safe!

We encourage technology experts to contribute share technical knowledge in form of writing technical articles/blogs, SQL commands for daily usage (basic to a high level), Carrier guidance on any technology, and become an author.

We have a ready platform for you with no profit no loss (as of now, in the future you may also earn revenue) if you are ready to contribute to writing articles, click on the registration link and the article will be published as an individual contributor on your name.

Click here for registration

Thanks,
Team DBsGuru
We Commit We Deliver