MySQL :: MySQL 8.4 Reference Manual :: 7.6.7.3 Cloning Remote Data (original) (raw)

7.6.7.3 Cloning Remote Data

The clone plugin supports the following syntax for cloning remote data; that is, cloning data from a remote MySQL server instance (the donor) and transferring it to the MySQL instance where the cloning operation was initiated (the recipient).

CLONE INSTANCE FROM 'user'@'host':port
IDENTIFIED BY 'password'
[DATA DIRECTORY [=] 'clone_dir']
[REQUIRE [NO] SSL];

where:

Note

By default, user-created InnoDB tables and tablespaces that reside in the data directory on the donor MySQL server instance are cloned to the data directory on the recipient MySQL server instance. If the DATA DIRECTORY [=] '_`clonedir`_' clause is specified, they are cloned to the specified directory.

User-created InnoDB tables and tablespaces that reside outside of the data directory on the donor MySQL server instance are cloned to the same path on the recipient MySQL server instance. An error is reported if a table or tablespace already exists.

By default, the InnoDB system tablespace, redo logs, and undo tablespaces are cloned to the same locations that are configured on the donor (as defined byinnodb_data_home_dir andinnodb_data_file_path,innodb_log_group_home_dir, and innodb_undo_directory, respectively). If the DATA DIRECTORY [=] '_`clonedir`_' clause is specified, those tablespaces and logs are cloned to the specified directory.

Remote Cloning Prerequisites

To perform a cloning operation, the clone plugin must be active on both the donor and recipient MySQL server instances. For installation instructions, seeSection 7.6.7.1, “Installing the Clone Plugin”.

A MySQL user on the donor and recipient is required for executing the cloning operation (the “clone user”).

Instructions for creating the clone user and granting the required privileges are included in the remote cloning example that follows this prerequisite information.

The following prerequisites are checked when theCLONE INSTANCE statement is executed:

mysql> SHOW VARIABLES LIKE 'version';  
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| version       | 8.4.5 |  
+---------------+-------+  
mysql> SELECT FILE_NAME FROM INFORMATION_SCHEMA.FILES;  
mysql> SHOW VARIABLES LIKE 'innodb_page_size';  
mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';  
mysql> SHOW VARIABLES LIKE 'clone_valid_donor_list';  
mysql> SHOW VARIABLES LIKE 'max_allowed_packet';

The following prerequisites also apply:

mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES  
       WHERE FILE_TYPE LIKE 'UNDO LOG';

For information about dropping and adding undo tablespace files, see Section 17.6.3.4, “Undo Tablespaces”.

ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).

This error does not indicate a cloning failure. It means that the recipient MySQL server instance must be started again manually after the data is cloned. After starting the server manually, you can connect to the recipient MySQL server instance and check the Performance Schema clone tables to verify that the cloning operation completed successfully (seeMonitoring Cloning Operations using Performance Schema Clone Tables.) The RESTART statement has the same monitoring process requirement. For more information, see Section 15.7.8.8, “RESTART Statement”. This requirement is not applicable if cloning to a named directory using the DATA DIRECTORY clause, as an automatic restart is not performed in this case.

Cloning Remote Data

The following example demonstrates cloning remote data. By default, a remote cloning operation removes user-created data (schemas, tables, tablespaces) and binary logs on the recipient, clones the new data to the recipient data directory, and restarts the MySQL server afterward.

The example assumes that remote cloning prerequisites are met. See Remote Cloning Prerequisites.

  1. Login to the donor MySQL server instance with an administrative user account.
    1. Create a clone user with theBACKUP_ADMIN privilege.
    mysql> CREATE USER 'donor_clone_user'@'example.donor.host.com' IDENTIFIED BY 'password';  
mysql> GRANT BACKUP_ADMIN on *.* to 'donor_clone_user'@'example.donor.host.com';
    1. Install the clone plugin:
    mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
  2. Login to the recipient MySQL server instance with an administrative user account.
    1. Create a clone user with theCLONE_ADMIN privilege.
    mysql> CREATE USER 'recipient_clone_user'@'example.recipient.host.com' IDENTIFIED BY 'password';  
mysql> GRANT CLONE_ADMIN on *.* to 'recipient_clone_user'@'example.recipient.host.com';
    1. Install the clone plugin:
    mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
    1. Add the host address of the donor MySQL server instance to theclone_valid_donor_list variable setting.
    mysql> SET GLOBAL clone_valid_donor_list = 'example.donor.host.com:3306';
  3. Log on to the recipient MySQL server instance as the clone user you created previously (recipient_clone_user'@'example.recipient.host.com) and execute theCLONE INSTANCE statement.
mysql> CLONE INSTANCE FROM 'donor_clone_user'@'example.donor.host.com':3306  
       IDENTIFIED BY 'password';

After the data is cloned, the MySQL server instance on the recipient is restarted automatically.
For information about monitoring cloning operation status and progress, seeSection 7.6.7.10, “Monitoring Cloning Operations”.

Cloning to a Named Directory

By default, a remote cloning operation removes user-created data (schemas, tables, tablespaces) and binary logs from the recipient data directory before cloning data from the donor MySQL Server instance. By cloning to a named directory, you can avoid removing data from the current recipient data directory.

The procedure for cloning to a named directory is the same procedure described inCloning Remote Data with one exception: The CLONE INSTANCE statement must include the DATA DIRECTORY clause. For example:

mysql> CLONE INSTANCE FROM 'user'@'example.donor.host.com':3306
       IDENTIFIED BY 'password'
       DATA DIRECTORY = '/path/to/clone_dir';

An absolute path is required, and the directory must not exist. The MySQL server must have the necessary write access to create the directory.

When cloning to a named directory, the recipient MySQL server instance is not restarted automatically after the data is cloned. If you want to restart the MySQL server on the named directory, you must do so manually:

$> mysqld_safe --datadir=/path/to/clone_dir

where /path/to/clonedir is the path to the named directory on the recipient.

Configuring an Encrypted Connection for Cloning

You can configure an encrypted connection for remote cloning operations to protect data as it is cloned over the network. An encrypted connection is required by default when cloning encrypted data. (seeSection 7.6.7.5, “Cloning Encrypted Data”.)

The instructions that follow describe how to configure the recipient MySQL server instance to use an encrypted connection. It is assumed that the donor MySQL server instance is already configured to use encrypted connections. If not, refer to Section 8.3.1, “Configuring MySQL to Use Encrypted Connections” for server-side configuration instructions.

To configure the recipient MySQL server instance to use an encrypted connection:

  1. Make the client certificate and key files of the donor MySQL server instance available to the recipient host. Either distribute the files to the recipient host using a secure channel or place them on a mounted partition that is accessible to the recipient host. The client certificate and key files to make available include:
    • ca.pem
      The self-signed certificate authority (CA) file.
    • client-cert.pem
      The client public key certificate file.
    • client-key.pem
      The client private key file.
  2. Configure the following SSL options on the recipient MySQL server instance.
    • clone_ssl_ca
      Specifies the path to the self-signed certificate authority (CA) file.
    • clone_ssl_cert
      Specifies the path to the client public key certificate file.
    • clone_ssl_key
      Specifies the path to the client private key file.
      For example:
clone_ssl_ca=/path/to/ca.pem  
clone_ssl_cert=/path/to/client-cert.pem  
clone_ssl_key=/path/to/client-key.pem
  1. To require that an encrypted connection is used, include the REQUIRE SSL clause when issuing theCLONE statement on the recipient.
mysql> CLONE INSTANCE FROM 'user'@'example.donor.host.com':3306  
       IDENTIFIED BY 'password'  
       DATA DIRECTORY = '/path/to/clone_dir'  
       REQUIRE SSL;

If an SSL clause is not specified, the clone plugin attempts to establish an encrypted connection by default, falling back to an unencrypted connection if the encrypted connection attempt fails.
Note
If you are cloning encrypted data, an encrypted connection is required by default regardless of whether the REQUIRE SSL clause is specified. Using REQUIRE NO SSL causes an error if you attempt to clone encrypted data.