Rebuilding a MySQL replica with the CLONE plugin: provision, throttle, attach with GTID and verify

#When to use this

A replica has errant GTIDs, corrupted tables or has been offline longer than the source keeps binary logs. Or you need a new replica for read scale or a migration. Rebuilding from a backup means restoring a full backup, applying binary logs and hoping the backup is recent enough. The clone plugin copies a running instance's InnoDB data over the network to the recipient, records the exact GTID set, and restarts the recipient ready to replicate.

This runbook covers a remote clone from a healthy donor (the source or another replica) to a recipient that will become a replica.

Caution

A remote CLONE INSTANCE drops all data on the recipient before copying, unless you use DATA DIRECTORY to clone into a separate directory. Check the hostname twice. Never run it on a server that holds the only copy of anything.

#Step 1: check the preconditions

Clone fails early, and sometimes late, if these don't match. Check both servers:

SELECT @@version, @@version_compile_os, @@version_compile_machine,
       @@innodb_page_size, @@character_set_server, @@collation_server,
       @@max_allowed_packet;

Check the donor's data size so you know the recipient has room:

SELECT ROUND(SUM(data_length + index_length)/1024/1024/1024, 1) AS innodb_gb
FROM   information_schema.tables
WHERE  engine = 'InnoDB';

Add undo tablespaces and redo generated during the copy. Leave at least 20% headroom.

#Step 2: install the plugin on both servers

INSTALL PLUGIN clone SONAME 'mysql_clone.so';

SELECT plugin_name, plugin_status
FROM   information_schema.plugins
WHERE  plugin_name = 'clone';

To keep it loaded across restarts, add it to my.cnf on both:

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

#Step 3: create the clone users

On the donor:

CREATE USER 'clone_donor'@'10.20.%' IDENTIFIED BY 'use-a-vault-secret' REQUIRE SSL;
GRANT BACKUP_ADMIN ON *.* TO 'clone_donor'@'10.20.%';

On the recipient:

CREATE USER 'clone_admin'@'localhost' IDENTIFIED BY 'use-a-vault-secret';
GRANT CLONE_ADMIN ON *.* TO 'clone_admin'@'localhost';

CLONE_ADMIN includes BACKUP_ADMIN and SHUTDOWN. Drop both users after the rebuild if you don't clone often.

#Step 4: protect the donor

The clone reads every InnoDB page from the donor. On a busy source this competes with production I/O. Run it from a replica where you can, and throttle it. These variables are set on the recipient:

SET GLOBAL clone_valid_donor_list       = '10.20.1.11:3306';
SET GLOBAL clone_max_data_bandwidth     = 200;  -- MiB/s read from donor disk, 0 = unlimited
SET GLOBAL clone_max_network_bandwidth  = 300;  -- MiB/s on the wire, 0 = unlimited
SET GLOBAL clone_autotune_concurrency   = ON;
SET GLOBAL clone_max_concurrency        = 8;
SET GLOBAL clone_enable_compression     = OFF;  -- ON trades donor CPU for network

Turn compression on only across a slow WAN link. On a fast LAN it usually just adds CPU on the donor.

From 8.0.27, concurrent DDL on the donor is allowed during the clone (clone_block_ddl = OFF by default). On earlier versions the donor blocks DDL for the whole copy, so schedule the clone away from deployments.

#Step 5: stop the old replica and run the clone

On the recipient, if it was replicating:

STOP REPLICA;

Make sure mysqld runs under systemd or mysqld_safe. The recipient restarts itself at the end of a remote clone. Without a supervisor process the clone completes, but the restart fails with error 3707 and you must start mysqld by hand.

Run the clone from a session that will survive a network drop, for example inside tmux:

CLONE INSTANCE FROM 'clone_donor'@'10.20.1.11':3306
  IDENTIFIED BY 'use-a-vault-secret'
  REQUIRE SSL;

The client connection drops when the recipient restarts. That is expected.

#Keep the old data instead

To keep the current data directory and clone alongside it:

CLONE INSTANCE FROM 'clone_donor'@'10.20.1.11':3306
  IDENTIFIED BY 'use-a-vault-secret'
  DATA DIRECTORY = '/data/mysql-clone';

The recipient does not restart in this case. Stop mysqld, point datadir at the new directory, and start it.

#Step 6: watch progress

From another session on the recipient (before the restart, and after it to see the final state):

SELECT stage, state,
       ROUND(estimate/1024/1024/1024, 1) AS est_gb,
       ROUND(data/1024/1024/1024, 1)     AS done_gb,
       ROUND(data_speed/1024/1024)       AS data_mib_s,
       ROUND(network_speed/1024/1024)    AS net_mib_s,
       begin_time, end_time
FROM   performance_schema.clone_progress;

Stages run in order: DROP DATA, FILE COPY, PAGE COPY, REDO COPY, FILE SYNC, RESTART, RECOVERY.

SELECT state, error_no, error_message, source,
       binlog_file, binlog_position, gtid_executed
FROM   performance_schema.clone_status;

STATE = 'Completed' and ERROR_NO = 0 mean success. Keep the GTID_EXECUTED, BINLOG_FILE and BINLOG_POSITION values. They are the exact point where replication must start.

#Step 7: attach as a replica

After the restart, check that GTIDs came across:

SELECT @@gtid_mode, @@server_uuid, @@global.gtid_executed;

gtid_executed should match clone_status.gtid_executed. server_uuid stays the recipient's own: auto.cnf is not cloned, which is what you want. Configuration files and binary logs are not cloned either.

Set the replication source explicitly, even if the donor was itself a replica. That way you know exactly which channel settings are in place:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.20.1.10',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'use-a-vault-secret',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL = 1,
  GET_SOURCE_PUBLIC_KEY = 1;

START REPLICA;

Without GTIDs, use SOURCE_LOG_FILE and SOURCE_LOG_POS from clone_status and drop SOURCE_AUTO_POSITION. These coordinates refer to the donor's binary logs, so the donor must be the replication source. Before 8.0.23, use the older CHANGE MASTER TO and START SLAVE syntax.

#Step 8: verify

SHOW REPLICA STATUS\G

Check Replica_IO_Running: Yes, Replica_SQL_Running: Yes, Seconds_Behind_Source falling, and Last_Error empty.

The clone copies InnoDB data only. MyISAM and CSV tables come across empty. Find them before you trust the replica:

SELECT table_schema, table_name, engine
FROM   information_schema.tables
WHERE  engine NOT IN ('InnoDB')
AND    table_schema NOT IN ('mysql','sys','performance_schema','information_schema');

Convert them to InnoDB on the source, or copy them separately. For a final consistency check on critical tables, run pt-table-checksum from the source.

#Failure modes

#Prevent it next time

• Keep the clone plugin installed and the user pre-created on all replicas so a rebuild takes one statement.
• Set super_read_only = ON in every replica's config so nobody writes to it and causes errant GTIDs.
• Size binlog_expire_logs_seconds on the source to cover your realistic replica outage, so short outages don't need a rebuild.
• Rehearse a clone into DATA DIRECTORY on a test host quarterly and record how long it takes per TB. That number is your replica rebuild RTO.