How to fix the broken slave using Clone Plugin ?

by | May 29, 2023 | 8.0, Innodb, MySQL | 0 comments

What is the clone Plugin ?

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

We encountered a broken slave in MySQL Version 8.0.30. We have mentioned about clone plugin in our replicaSet blog. Here is a similar need for a different use case. So we already had a regular asynchronous replication. The slave node went offline few weeks before and when it came back online we encountered this issue [ Cannot replicate because the master purged required binary logs ] which means master has purged the binary logs that slave is looking for. Now we the only option left is rebuild the node as below. How do you want to rebuild the node gives you multiple options as below.

  • backup/restore
    • mysqldump/restore
    • my dumper/myloader
    • xrtabackup/mysqlbackup
  • clone plugin

In this blog we will covering an easy method of rebuilding slaves if you are using MySQL 8.0.17 or above. 

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: xx.xx.xx.xxx
                  Master_User: xxxxxx
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000025
          Read_Master_Log_Pos: 471054846
               Relay_Log_File: mysql-relay-bin.000045
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000025
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
...........
          Exec_Master_Log_Pos: 471054846
              Relay_Log_Space: 471055968
...........
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 13114
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate because the master purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID sets and the missing purged transactions are too long to print in this message. For more information, please see the master's error log or the manual for GTID_SUBTRACT'
               Last_SQL_Errno: 0
...........
             Master_Server_Id: 111316213
                  Master_UUID: 49931526-58ed-11ed-bd25-0050568954a8
             Master_Info_File: mysql.slave_master_info
...........
            Executed_Gtid_Set: 09220645-5550-11ed-a6ea-xxxxxxxx:1-661,
                Auto_Position: 1
...........
1 row in set, 1 warning (0.24 sec)

mysql> stop replica; reset replica all;
Query OK, 0 rows affected (0.21 sec)

Query OK, 0 rows affected (0.26 sec)

Its a simple master-replica setup with one broken slave. It’s an easy operation to build a slave node using backup and restore method but its time consuming effort and requires continuous attention of the DBA. However, using clone plugin it becomes lot more easier with simpler steps and better status tracking mechanism. With the traditional backup and restore methods we can only provide an approximate estimation however with cloning process we would know exactly what’s happening and how much time it might take to complete etc.

Enable the Clone Plugin

There are couple of ways to enable the clone plugin a) at runtime dynamically b) at startup which would need mysql restart on both the nodes donor[Master] and recipient[Slave/BrokenNode].

at runtime

### Validate the clone plugin status before enabling it as expected it is not enabled on this host. 

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_NAME = 'clone';
Empty set (0.03 sec)

### Install the clone plugin plugin is not enabled

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.00 sec)

### Validate the clone plugin status after enabling it

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)

OR

at server startup


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

Defining the Donor nodes for rebuilding the Broken Slave

Assign a valid donor server details to clone_valid_donor_list variable. you are allowed to give a comma-separated list of values is permitted in the following format: “ :PORT1,HOST2:PORT2,HOST3:PORT3”. 

mysql> show global variables like '%clone_valid_donor_list%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| clone_valid_donor_list |       |
+------------------------+-------+
1 row in set (0.16 sec)

mysql> SET GLOBAL clone_valid_donor_list = 'donor_host_IP:3306';
Query OK, 0 rows affected (0.16 sec)

mysql> show global variables like '%clone_valid_donor_list%';
+------------------------+------------------+
| Variable_name          | Value            |
+------------------------+------------------+
| clone_valid_donor_list | XX.XX.XX.XX:3306 |
+------------------------+------------------+
1 row in set (0.15 sec)

What privileges do you need for cloning ?

The Donor would need BACKUP_ADMIN privilege for the clone_usr to be able to do a backup operation like cloning the master data on the slave node. The recipient would need CLONE_ADMIN privilege for the clone_usr to perform the clone operations on the slave node. So we created a user with both BACKUP_ADMIN and CLONE_ADMIN privileges as below on both the donor and recipient node with IP restricted just to make sure the user is secured and are able clone each other when needed.

Donor access

CREATE USER clone_usr@Recipient_IP IDENTIFIED BY 'secret_password';
GRANT BACKUP_ADMIN,CLONE_ADMIN ON *.* TO `clone_usr`@`Recipient_IP`;

Recipient access

CREATE USER clone_usr@Donor_IP IDENTIFIED BY 'secret_password';
GRANT CLONE_ADMIN,BACKUP_ADMIN ON *.* TO `clone_usr`@`Donor_IP`;

How to monitor Cloning process ?

Before starting the cloning process We need to make sure we enable the necessary performance schema metrics to monitor the status of the cloning process. 

mysql> select * from performance_schema.setup_instruments WHERE NAME LIKE 'stage/innodb/clone%';
Empty set (0.01 sec)

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

mysql> select * from performance_schema.setup_instruments WHERE NAME LIKE 'stage/innodb/clone%';
+--------------------------------+---------+-------+------------+------------+---------------+
| NAME                           | ENABLED | TIMED | PROPERTIES | VOLATILITY | DOCUMENTATION |
+--------------------------------+---------+-------+------------+------------+---------------+
| stage/innodb/clone (file copy) | YES     | YES   | progress   |          0 | NULL          |
| stage/innodb/clone (redo copy) | YES     | YES   | progress   |          0 | NULL          |
| stage/innodb/clone (page copy) | YES     | YES   | progress   |          0 | NULL          |
+--------------------------------+---------+-------+------------+------------+---------------+
3 rows in set (0.16 sec)

Start the cloning process

Once all the parameters to monitor the process

CLONE INSTANCE FROM clone_usr@xx.xx.xx.xx:3306 IDENTIFIED BY 'secret_password';

Clone Progress and status monitoring

There are two tables dedicated to clone plugin which gets created when we enable clone plugin under performance schema. 

select table_name from information_schema.tables where table_name like 'clone%' and table_schema='performance_schema';
+----------------+
| TABLE_NAME     |
+----------------+
| clone_progress | ---> Contains stage wise detailed information about the progress
| clone_status   | ---> Contains the instant status of the clone operation.
+----------------+

Once the clone has been process has been started on the node. from another session we can run the below queries to track on which stage of cloning is going on . when the databases are huge it can also help us in estimating ETA.

To view the different stages of Cloning process and monitoring its real time status

The cloning progress details can be monitored using performance_schema.clone_progress table where it would list out all the stages on the clone process and how much time it took for each stage.

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 | 21:56:14   |  294.07 ms |             0 MB | 0x20202031303025 |
| FILE COPY | Completed | 21:56:14   |     24.4 s |         5,235 MB | 0x20202031303025 |
| PAGE COPY | Completed | 21:56:38   |  240.62 ms |             3 MB | 0x20202031303025 |
| REDO COPY | Completed | 21:56:39   |  160.62 ms |            15 MB | 0x20202031303025 |
| FILE SYNC | Completed | 21:56:39   |  603.05 ms |             0 MB | 0x20202031303025 |
| RESTART   | Completed | 21:56:39   |     4.03 s |             0 MB | 0x20202031303025 |
| RECOVERY  | Completed | 21:56:43   |      1.6 s |             0 MB | 0x20202031303025 |
+-----------+-----------+------------+------------+------------------+------------------+

NOTE: After FILE SYNC stage is completed the clone command will return and the server will be restarted automatically. During the last two stages RESTART and RECOVERY the server will not be available. MySQL does a full recovery at this stage from the cloned data and applies the redo log changes and synchronises with the donor.

How much time it took to complete the clone process

Clone status can be monitored and fetched from performance_schema.clone_status table. 

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   |
+-----------+---------------------+------------+
| Completed | 2023-05-24 21:56:13 |    32.21 s |
+-----------+---------------------+------------+

Re-establishing the replication with the donor node

Once the restore/clone process is complete the recipient would clone with the same replication channels or configurations the donor had but after it restores if you want to change the replication master to donor or any other node with the same replication topology, it can be done using regular. 

STOP REPLICA;
CHANGE MASTER TO MASTER_HOST='xx.xx.xx.xxx',master_port=3306,master_user='xxxxxx',master_password='xxxxxx', MASTER_AUTO_POSITION=1;
START REPLICA;

Validate the slave status

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: xx.xx.xx.xxx
                  Master_User: xxxxxx
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000061
          Read_Master_Log_Pos: 722625447
               Relay_Log_File: mysql-relay-bin.000004
                Relay_Log_Pos: 722623951
        Relay_Master_Log_File: mysql-bin.000061
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
..................
          Exec_Master_Log_Pos: 722623783
              Relay_Log_Space: 722625980
..................
        Seconds_Behind_Master: 0
..................
            Executed_Gtid_Set: 09220645-5550-11ed-a6ea-xxxxxxxx,
                Auto_Position: 1
..................

Clone process also records the bin log file and position just in case if you don’t use GTID based replication you can use below coordinates to setup replication with the donor. 

mysql> SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;
+------------------+-----------------+
| BINLOG_FILE      | BINLOG_POSITION |
+------------------+-----------------+
| mysql-bin.000060 |        73342092 |
+------------------+-----------------+

Limitation of Cloning

There are some important limitation with cloning

  • Until MySQL 8.0.27, DDLs on the donor and recipient including TRUNCATE TABLE is not allowed during a cloning operation just in case if a DDL transaction comes it would be locked till the clone process completes.
  • From MySQL 8.0.27, concurrent DDL is permitted on the donor by default. Support for concurrent DDL on the donor is controlled by the clone_block_ddl variable
  • The donor and recipient must have exactly the same MySQL server version and release. For example, you cannot clone between MySQL 5.7 and MySQL 8.0, or between MySQL 8.0.19 and MySQL 8.0.20.
  • Only a single MySQL instance can be cloned at a time. Cloning multiple MySQL instances in a single cloning operation is not supported.
  • The clone plugin does not support cloning of MySQL server configurations and binary logs.
  • Non Innodb engine tables like MyISAM tables stored in any schema are cloned as empty tables.

Conclusion

Cloning is an amazing features of mysql which allows us to rebuild slaves or add nodes to Innodb cluster to scale horizontally in a faster and reliable way. It is making scaling extremely simplified and more reliable with out of the box solutions than other open source solutions. There are other technologies where scaling is as easy as just adding a new node to the existing cluster and data cloning automatically happens in the backend and mysql being so widely used was missing such feature for quite sometime till clone was introduced. MySQL has done some significant advancement with features like clone and tools like mysqlshell which makes it extremely unique and powerful tools for DBAs to perform advanced DBA operations.

Hope you find this information useful. Keep reading and follow us for more exclusive content for open source databases. 

Submit a Comment

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

%d bloggers like this: