MySQL clone plugin (for 8.0.17 +)

MySQL Clone Plugin(8.0.17 +)

1, What is a clone plug-in?

  • Allow copying data from a local or remote MySQL server instance
  • It can take a physical snapshot of InnoDB's schemas, tables, tablespaces, and data dictionary metadata
  • Supports replication, such as for MGR

2, Using the clone plug-in

2.1 installing the clone plug-in

# Mode 1: configuration file loading plug-in
[mysqld]
plugin-load-add=mysql_clone.so

# Method 2: using the install plugin statement, you can mysql.plugins Registered in table
install plugin clone SONAME 'mysql_clone.so';

If the installation error is 1123 (HY000): can't initialize function 'clone'; plugin initialization function failed., check explicit_ defaults_ for_ Whether timestamp is on (1): Reference Bug #96281

2.2 clone local data

Clone the local MySQL data directory to another directory. The user needs BACKUP_ADMIN permission

# Creating users and authorizations
create user 'clone_backup'@'localhost' identified by 'xxx';

grant backup_admin on *.* to 'clone_backup'@'localhost';

# Clone the data directory to / backup/clone-local-1823. The directory must be an absolute path, the directory must not exist, and the path must be writable by MySQL service
# Tablespaces and tables outside of the cloned data directory are not supported
# You can use the cloned directory to start the MySQL service
mkdir /backup
chown mysql.mysql /backup
clone local data directory '/backup/clone-local-1823';

# Start service using clone directory
./bin/mysqld_safe \
--datadir=/backup/clone-local-1823/ \
--port=3309 \
--user=mysql \
--log-error=error.log \
--socket=/tmp/mysql3309.sock \
--lower_case_table_names=1 \
--mysqlx_port=33069 \
--mysqlx_socket=/tmp/mysqlx3309.sock \
--pid-file=mysqld.pid

During cloning, when the lock is occupied, DDL operation (Waiting for backup lock) will be blocked. DML will encounter waiting for handler commit at the final stage of the completion of clone. The cloned data is the data snapshot at the time point when the clone is executed.

2.3 clone remote instance data

You can clone to the DATA DIRECTORY of the current running instance of the remote machine, delete the current data, and then restart the service automatically. You can also clone the data to the directory by specifying an absolute path that does not exist through DATA DIRECTORY. You can clone the table spaces outside the DATA DIRECTORY;

Clone preparation steps:

  • Both MySQL instances need to install and activate the clone plug-in
  • Create related user accounts
    • Source side: Backup required_ Admin permission, blocking DDL operation during clone
    • Destination: Clone required_ Admin (including backup implicitly_ Admin and SHUTDOWN permissions). During cloning, DDL operations will be blocked and services will be restarted automatically
  • The MySQL version and OS version of the source side and the destination side need to be consistent to ensure that the destination side has enough disk space to store the cloned InnoDB data
  • The character set and proofreading set of the source and destination MySQL services should be consistent
  • InnoDB of source and destination MySQL services_ page_ Size and innodb_data_file_path configuration needs to be consistent
  • For example, for clone encrypted or page compressed data, the file system block size of the source and the destination should be the same. For page compressed data, the destination system also needs to support sparse files and hole punching; encrypted data also needs to use secure connection;
  • Only one clone is supported at the same time to ensure that the source IP address is in the destination clone_valid_donor_list parameter configuration;
  • max_allowed_packet must be 2MB minimum
  • The UNDO tablespace file on the source side needs to be unique. Select tablespace_ NAME, FILE_ NAME FROM INFORMATION_ SCHEMA.FILES WHERE FILE_ Type like 'UNDO log'; checkable
  • The target end needs to restart automatically due to MySQL instance (no restart is needed for cloning to another directory), and needs to be monitored by the daemons

Source (10.82.30.102:3308):

# Create user
create user 'clone_server'@'10.82.30.101' identified by 'xxx';
grant backup_admin on *.* to 'clone_server'@'10.82.30.101';

# Install plug-ins
install plugin clone SONAME 'mysql_clone.so';

Destination (10.82.30.101:3308):

# Create user
create user 'clone_client'@'localhost' identified by 'xxx';
grant clone_admin on *.* to 'clone_client'@'localhost';

# Install plug-ins
install plugin clone SONAME 'mysql_clone.so';

# Add source IP to white list
set global clone_valid_donor_list='10.82.30.102:3308';

# Log in with clone user
./bin/mysql -S /tmp/mysql3308.sock -uclone_client -p

# 1, Execute the clone command to copy the data to the instance data directory
clone instance from 'clone_server'@'10.82.30.102':3308 identified by 'xxx';

# View clone status
select id,pid,state,source,destination,error_no,error_message,binlog_file,binlog_position from performance_schema.clone_status;

# 2, Execute the clone command to copy the data to the specified directory (the directory must not exist, and the MySQL service has write permission)
mkdir /backup
chown mysql.mysql /backup

# Operation users also need BACKUP_ADMIN permission, not mentioned in the document
grant backup_admin on *.* to 'clone_client'@'localhost';

clone instance from 'clone_server'@'10.82.30.102':3308 identified by 'xxx' data directory='/backup/clone-102';

Tags: Database MySQL snapshot socket

Posted on Fri, 12 Jun 2020 02:23:59 -0400 by douga