The clone plugin was introduced in MySQL 8.0.17 and offers a convenient method for cloning data from either a local or remote MySQL server instance. This cloning process creates a physical snapshot of the data stored in InnoDB, including schemas, tables, tablespaces, and data dictionary metadata. The clone plugin allows for easy provisioning of MySQL servers by generating a fully functional data directory. In this blog post, we will explore the steps to configure a new MySQL replica using the clone plugin.
Installation of the Clone Plugin
To load the clone plugin during server startup, you can utilize the –plugin-load-add option and specify the library file containing the plugin. Ensure that you add this option each time the server starts when using this plugin-loading method. To make this configuration change, add the following lines to your my.cnf file, adjusting the plugin library file name extension according to your platform.
1 2 | [mysqld] plugin-load-add=mysql_clone.so |
Use the following query to verify the plugin status.
1 2 3 | SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'clone'; |
Alternatively, you can load the plugin at runtime by executing the following statement, and adjusting the .so suffix based on your platform:
1 | INSTALL PLUGIN clone SONAME 'mysql_clone.so'; |
Controlling the plugin activation state
If the plugin has been registered previously using INSTALL PLUGIN or loaded with –plugin-load-add, you can use the –clone option during server startup to control the plugin’s activation state. For example, to load the plugin at startup and ensure it remains active without being removed at runtime, use the following options in your configuration file:
1 2 3 | [mysqld] plugin-load-add=mysql_clone.so clone=FORCE_PLUS_PERMANENT |
To prevent the server from starting if the clone plugin fails to initialize, use the –clone option with a value of FORCE or FORCE_PLUS_PERMANENT. This ensures the clone plugin is essential for running the server.
Creating a user with required privileges
To execute the clone operation, the user must have the BACKUP_ADMIN privilege on *.*. You can create a new user and grant the necessary privileges using the following statements:
1 2 3 | CREATE USER 'kbp_clone_user'@'%' IDENTIFIED BY 'BFuJ4#93SLG'; GRANT BACKUP_ADMIN ON *.* TO 'kbp_clone_user'@'%'; SHOW GRANTS FOR 'kbp_clone_user'@'%'; |
Receiver instance
To clone the instance from a donor, you need to install the mysql_clone plugin on the receiver instance. Execute the following statement to install the plugin:
1 | INSTALL PLUGIN clone SONAME 'mysql_clone.so'; |
You can verify the plugin installation using the following query:
1 2 3 | SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'clone'; |
Configuring clone valid donor list
To ensure successful cloning, the recipient’s clone_valid_donor_list setting must include the host address of the donor MySQL server instance. This variable requires a MySQL user with the SYSTEM_VARIABLES_ADMIN privilege to configure. Use the SHOW VARIABLES syntax to check the current value of clone_valid_donor_list. If the donor is not specified, an error will be encountered. Set the clone_valid_donor_list using the following statement:
1 | SET GLOBAL clone_valid_donor_list='donor_ip:3306'; |
Cloning an instance from a donor server
To clone an instance from a donor server, use the clone instance statement with the specified donor server IP address, port, username, and password. The following statement initiates the cloning process:
1 | CLONE INSTANCE FROM kbp_clone_user@'donor_ip:3306 IDENTIFIED BY 'DFkJ4#83LIs'; |
Monitoring the cloning progress
To monitor the progress of the cloning process, you can query the performance_schema.clone_progress table. Use the following statement to retrieve relevant information:
1 | SELECT * FROM performance_schema.clone_progress; |
The performance_schema.clone_progress table provides details about the current status and progress of the cloning operation.
Set up replication after cloning
Once the cloning process is completed, you can query the performance_schema.clone_status table to obtain the binlog coordinates required to initiate replication:
1 | SELECT binlog_file, binlog_position FROM performance_schema.clone_statusG |
In order for each replica to establish a connection with the source in MySQL, it is imperative to create a user account on the source. For example, to create a new user, repl_user, with replication privileges, allowing connections from any host within the example.com domain, execute the following statements on the source server:
1 2 | CREATE USER 'repl_user'@'%.example.com' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%.example.com'; |
And, to set up replication, you can use the following statements.
1 2 3 | CHANGE REPLICATION SOURCE TO SOURCE_HOST='Donor host_name or IP', SOURCE_USER='repl_user', SOURCE_PASSWORD='Password', SOURCE_LOG_FILE='source_log_name', SOURCE_LOG_POS=source_log_pos; START REPLICA; |
Replace the placeholders with the appropriate values:
- Donor host_name or IP refers to the IP address or hostname of the donor instance.
- repl_user is the username for replication user credentials.
- Password represents the password for the replication user.
- source_log_name is the name of the binary log file obtained from the previous step.
source_log_pos denotes the position within the log file obtained from the previous step.
By executing the statements, you effectively configure replication between the donor and recipient instances, allowing data synchronization to occur.
Local cloning
For local cloning, where data is cloned within the same server or node, ensure that the MySQL server has the necessary write access to create the specified directory. Use the following statement to initiate the local cloning process with a specific data directory for the recipient server. To set up replication, follow these steps: First, copy the cloned data to the replica server. Next, manually replace the data directory by stopping MySQL, then restart it after assigning ownership to the MySQL OS user. Once the restore process is completed, you can query the performance_schema.clone_status table to obtain the binlog coordinates necessary for initiating replication.
1 | CLONE LOCAL DATA DIRECTORY='/var/lib/mysql_clone/backup_dir/'; |
Once the restore process is completed, you can query the performance_schema.clone_status table to obtain the binlog coordinates required to initiate replication:
1 | SELECT binlog_file, binlog_position FROM performance_schema.clone_statusG |
Conclusion
The clone plugin in MySQL simplifies the process of replicating data, either locally or from a remote server instance. By following the steps outlined in this blog post, you can configure replication using the clone plugin successfully. The clone plugin provides a convenient way to create physical snapshots of data and provision MySQL servers efficiently.
Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.
Is clone an option to produce replicas (PS80) from a XtraDB (PXC80) 8.0 instance?
Or
Does XtraDB support clone?