Rechercher dans le manuel MySQL
5.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).
- IDENTIFIED BY 'password'
where:
is the user that connects to the donor MySQL server instance.USER
is thepassword
password.USER
is theHOST
hostname
address of the donor MySQL server instance. Internet Protocol version 6 (IPv6) address format is not supported. An alias to the IPv6 address can be used instead. An IPv4 address can be used as is.
is thePORT
port
number of the donor MySQL server instance. (The X Protocol port specified bymysqlx_port
is not supported. Connecting to the donor MySQL server instance through MySQL Router is also not supported.)DATA DIRECTORY [=]
is an optional clause used to specify a directory on the recipient for the data you are cloning. Use this option if you do not want to remove existing data in the recipient data directory. An absolute path is required, and the directory must not exist. The MySQL server must have the necessary write access to create the directory.clone_dir
When the optional
DATA DIRECTORY [=]
clause is not used, a cloning operation removes existing data in the recipient data directory, replaces it with the cloned data, and automatically restarts the server afterward.clone_dir
[REQUIRE [NO] SSL]
explicitly specifies whether a secure connection is to be used or not when transferring cloned data over the network. An error is returned if the explicit specification cannot be satisfied. If an SSL clause is not specified, clone attempts to establish a secure connection by default, falling back to an insecure connection if the secure connection attempt fails. A secure connection is required when cloning encrypted data regardless of whether this clause is specified. For more information, see Configuring a Secure Connection for Cloning.
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 [=]
clause is specified, they are cloned to the specified
directory.
clone_dir
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 by
innodb_data_home_dir
and
innodb_data_file_path
,
innodb_log_group_home_dir
,
and innodb_undo_directory
,
respectively). If the DATA DIRECTORY [=]
clause is
specified, those tablespaces and logs are cloned to the
specified directory.
clone_dir
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, see Section 5.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”).
The
BACKUP_ADMIN
privilege is required for accessing and transferring data from the donor, and for blocking DDL during the cloning operation.On the recipient, the clone user requires the
CLONE_ADMIN
privilege for replacing recipient data, blocking DDL during the cloning operation, and automatically restarting the server. TheCLONE_ADMIN
privilege includes implicitBACKUP_ADMIN
andSHUTDOWN
privileges.
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 the
CLONE INSTANCE
statement is executed:
The donor and recipient must have the same MySQL server version. The clone plugin is supported in MYSQL 8.0.17 and higher.
The donor and recipient MySQL server instances must run on the same operating system and platform. For example, if the donor instance runs on a Linux 64-bit platform, the recipient instance must also run on that platform. Refer to your operating system documentation for information about how to determine your operating system platform.
The recipient must have enough disk space for the cloned data. By default, recipient data is removed prior to cloning the donor data, so you only require enough space for the donor data. If you clone to a named directory using the
DATA DIRECTORY
clause, you must have enough disk space for the existing recipient data and the cloned data. You can estimate the size of your data by checking the data directory size on your file system and the size of any tablespaces that reside outside of the data directory. When estimating data size on the donor, remember that onlyInnoDB
data is cloned. If you store data in other storage engines, adjust your data size estimate accordingly.InnoDB
permits creating some tablespace types outside of the data directory. If the donor MySQL server instance has tablespaces that reside outside of the data directory, the cloning operation must be able access those tablespaces. You can query theINFORMATION_SCHEMA.FILES
table to identify tablespaces that reside outside of the data directory. Files that reside outside of the data directory have a fully qualified path to a directory other than the data directory.Plugins that are active on the donor, including any keyring plugin, must also be active on the recipient. You can identify active plugins by issuing a
SHOW PLUGINS
statement or by querying theINFORMATION_SCHEMA.PLUGINS
table.The donor and recipient must have the same MySQL server character set and collation. For information about MySQL server character set and collation configuration, see Section 10.14, “Character Set Configuration”.
The same
innodb_page_size
andinnodb_data_file_path
settings are required on the donor and recipient. Theinnodb_data_file_path
setting on the donor and recipient must specify the same number of data files of an equivalent size. You can check variable settings usingSHOW VARIABLES
syntax.If cloning encrypted or page-compressed data, the donor and recipient must have the same file system block size. For page-compressed data, the recipient file system must support sparse files and hole punching for hole punching to occur on the recipient. For information about these features and how to identify tables and tablespaces that use them, see Section 5.6.7.4, “Cloning Encrypted Data”, and Section 5.6.7.5, “Cloning Compressed Data”. To determine your file system block size, refer to your operating system documentation.
A secure connection is required if you are cloning encrypted data. See Configuring a Secure Connection for Cloning.
The
clone_valid_donor_list
setting on the recipient must include the host address of the donor MySQL server instance. You can only clone data from a host on the valid donor list. A MySQL user with theSYSTEM_VARIABLES_ADMIN
privilege is required to configure this variable. Instructions for setting theclone_valid_donor_list
variable are provided in the remote cloning example that follows this section. You can check theclone_valid_donor_list
setting usingSHOW VARIABLES
syntax.There must be no other cloning operation running. Only a single cloning operation is permitted at a time. To determine if a clone operation is running, query the
clone_status
table. See Monitoring Cloning Operations using Performance Schema Clone Tables.
The following prerequisites also apply:
Undo tablespace file names on the donor must be unique. When data is cloned to the recipient, undo tablespaces, regardless of their location on the donor, are cloned to the
innodb_undo_directory
location on the recipient or to the directory specified by theDATA DIRECTORY [=]
clause, if used. Duplicate undo tablespace file names on the donor are not permitted for this reason. As of MySQL 8.0.18, an error is reported if duplicate undo tablespace file names are encountered during a cloning operation. Prior to MySQL 8.0.18, cloning undo tablespaces with the same file name could result in undo tablespace files being overwritten on the recipient.clone_dir
To view undo tablespace file names on the donor to ensure that they are unique, query
INFORMATION_SCHEMA.FILES
:For information about dropping and adding undo tablespace files, see Section 15.6.3.4, “Undo Tablespaces”.
By default, the recipient MySQL server instance is restarted automatically after the data is cloned. For an automatic restart to occur, a monitoring process must be available on the recipient to detect server shutdowns. Otherwise, the cloning operation halts with the following error after the data is cloned, and the recipient MySQL server instance is shut down:
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 restarted manually after the data is cloned. After restarting 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 (see Monitoring Cloning Operations using Performance Schema Clone Tables.) The
RESTART
statement has the same monitoring process requirement. For more information, see Section 13.7.7.8, “RESTART Syntax”. This requirement is not applicable if cloning to a named directory using theDATA DIRECTORY
clause, as an automatic restart is not performed in this case.Several variables control various aspects of a remote cloning operation. Before performing a remote cloning operation, review the variables and adjust settings as necessary to suit your computing environment. Clone variables are set on recipient MySQL server instance where the cloning operation is executed. See Section 5.6.7.12, “Clone System Variables”.
The following example demonstrates cloning remote data. By default, a remote cloning operation removes the data in the recipient data directory, replaces it with the cloned data, and restarts the MySQL server afterward.
The example assumes that remote cloning prerequisites are met. See Remote Cloning Prerequisites.
Login to the donor MySQL server instance with an administrative user account.
Login to the recipient MySQL server instance with an administrative user account.
Create a clone user with the
CLONE_ADMIN
privilege.Install the clone plugin:
Add the host address of the donor MySQL server instance to the
clone_valid_donor_list
variable setting.
Log on to the recipient MySQL server instance as the clone user you created previously (
clone_user'@'example.recipient.host.com
) and execute theCLONE INSTANCE
statement.- 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, see Section 5.6.7.9, “Monitoring Cloning Operations”.
By default, a remote cloning operation removes the data in the recipient data directory and replaces it with the cloned data. By cloning to a named directory, you can avoid removing existing data from the recipient data directory.
The procedure for cloning to a named directory is the same
procedure described in
Cloning Remote Data with one
exception: The CLONE INSTANCE
statement
must include the DATA DIRECTORY
clause. For
example:
- IDENTIFIED BY 'password'
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:
shell> mysqld_safe --datadir=/path/to/clone_dir
where /path/to/clone_dir
is the
path to the named directory on the recipient.
To protect data as it is cloned over the network, you can configure a secure connection with a certificate for remote cloning operations. A secure connection is required by default when cloning encrypted data. (see Section 5.6.7.4, “Cloning Encrypted Data”.)
To configure a secure connection with a certificate for a remote cloning operation:
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 using a secure channel or place them on a mounted partition that is accessible to the recipient host. For a default MySQL server installation, the client certificate and key files that must be made available to the recipient include:
ca.pem
The certificate authority (CA) file.
client-cert.pem
The public key certificate file.
client-key.pem
The private key file.
Configure the following SSL options on the recipient MySQL server instance. The specified certificate and key files must match the client certificate key files of the donor MySQL server instance.
Specifies the path to the certificate authority (CA) file.
Specifies the path to the public key certificate file.
Specifies the path to the private key file.
For example:
clone_ssl_ca=ca.pem clone_ssl_cert=client-cert.pem clone_ssl_key=client-key.pem
NoteDepending on where you place the client certificate and key files, you may have to specify a path to the file location; for example:
clone_ssl_ca=/donor_mysql_keys/ca.pem
.To require that a secure connection is used, include the
REQUIRE SSL
clause when issuing theCLONE
statement on the recipient host.- IDENTIFIED BY 'password'
If an SSL clause is not specified, clone attempts to establish a secure connection by default, falling back to an insecure connection if the secure connection attempt fails.
NoteIf you are cloning encrypted data, a secure connection is required by default regardless of whether the
REQUIRE SSL
clause is specified. UsingREQUIRE NO SSL
causes an error if you attempt to clone encrypted data.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-clone-plugin-remote.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.