MySQL8.0.17推出了一个重量级的功能：clone plugin。允许用户可以将当前实例进行本地或者远程的clone操作。适用于整个实例快速备份和mgr新成员加入。
安装clone插件
mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so'; Query OK, 0 rows affected (0.06 sec) mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS -> FROM INFORMATION_SCHEMA.PLUGINS -> WHERE PLUGIN_NAME = 'clone'; +-------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +-------------+---------------+ | clone | ACTIVE | +-------------+---------------+ 1 row in set (0.01 sec)
本地clone
还不用先创建目录
mysql> CLONE LOCAL DATA DIRECTORY = '/home/scutech/mysql/enterprise/monitor/mysql/data2'; ERROR 1007 (HY000): Can't create database '/home/scutech/mysql/enterprise/monitor/mysql/data2'; database exists mysql> CLONE LOCAL DATA DIRECTORY = '/home/scutech/mysql/enterprise/monitor/mysql/data2'; Query OK, 0 rows affected (17.10 sec)
clone完成后启动新的实例：
sudo /home/scutech/mysql/enterprise/monitor/mysql/bin/mysqld_safe --defaults-file=/home/scutech/mysql/enterprise/monitor/mysql/my2.cnf --pid-file=/home/scutech/mysql/enterprise/monitor/mysql/runtime/mysqld2.pid --user=scutech &
登录进入新的实例
root@scutech:~# mysql --socket=/home/scutech/mysql/enterprise/monitor/mysql/tmp/mysql2.sock mysql> show variables like 'data%'; +---------------+-----------------------------------------------------+ | Variable_name | Value | +---------------+-----------------------------------------------------+ | datadir | /home/scutech/mysql/enterprise/monitor/mysql/data2/ | +---------------+-----------------------------------------------------+ 1 row in set (0.03 sec)
远程clone
远程clone需要启动clone操作的本地MySQL服务器实例(“recipient”)和源数据所在的远程MySQL服务器实例(“donor”)。和我之前想象的不一样，clone是在recipient上启动远程clone操作，clone数据通过网络从发送方传输到接收方。默认情况下，远程clone操作删除recipient数据目录中的数据，并用clone的数据替换它。可以选择将数据复制到recipient上的另一个目录，以避免删除现有数据。
clone plugin还支持复制。除了clone数据之外，clone操作还从donor提取和传输复制坐标，并将它们应用于recipient，这使得可以使用clone plugin来提供组复制成员和复制从属节点。与复制大量事务相比，使用clone plugin进行供应要快得多，效率也高得多。组复制成员还可以配置为使用clone插件作为另一种恢复方法，以便成员自动选择从种子成员检索组数据的最有效方法。clone plugin支持加密的和页面压缩的数据。
clone INSTANCE FROM ‘user’@‘host’:port IDENTIFIED BY ‘password’ [DATA DIRECTORY [=] ‘clone_dir’] [REQUIRE [NO] SSL];
语法里指定： ip，端口，用户，密码，目录。
clone的限制
支持步骤datadir目录的文件clone，但要有访问权限，可以检查 SELECT FILE_NAME FROM
INFORMATION_SCHEMA.FILES;识别出来的内容才会被clone；
clone插件必须在发送方（donor）和接收方（recipient）的MySQL服务器实例上都是活动的
源端Donor登录的用户需要BACKUP_ADMIN特权来访问和传输来自clone服务器的数据，并在操作期间阻塞DDL。
目标端Recipient用户需要clone_ADMIN特权来替换收件人数据，在操作期间阻塞DDL，并自动重新启动服务器。clone_ADMIN特权隐式地包括BACKUP_ADMIN和SHUTDOWN特权。
clone用于复制
clone完成后，在源上查看
mysql> show master status; +----------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------+----------+--------------+------------------+-------------------+ | monitor.000001 | 156 | | | | +----------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
在recipient上查看：
mysql> SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status; +----------------+-----------------+ | BINLOG_FILE | BINLOG_POSITION | +----------------+-----------------+ | monitor.000001 | 156 | +----------------+-----------------+ 1 row in set (0.03 sec)
查看clone状态
这里有两种方式。
1 performance_schema下提供了两张表： clone_status，clone_progress来监控执行状态和情况
mysql> SELECT STATE, ERROR_NO, ERROR_MESSAGE FROM performance_schema.clone_status; +-----------+----------+---------------+ | STATE | ERROR_NO | ERROR_MESSAGE | +-----------+----------+---------------+ | Completed | 0 | | +-----------+----------+---------------+ 1 row in set (0.00 sec) mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress; +-----------+-----------+----------------------------+ | STAGE | STATE | END_TIME | +-----------+-----------+----------------------------+ | DROP DATA | Completed | 2020-06-29 05:53:08.728786 | | FILE COPY | Completed | 2020-06-29 05:53:24.039983 | | PAGE COPY | Completed | 2020-06-29 05:53:24.245478 | | REDO COPY | Completed | 2020-06-29 05:53:24.448446 | | FILE SYNC | Completed | 2020-06-29 05:53:25.815581 | | RESTART | Completed | 2020-06-29 05:54:39.487882 | | RECOVERY | Completed | 2020-06-29 05:54:42.656002 | +-----------+-----------+----------------------------+ 7 rows in set (0.00 sec)
2 通过performance_schema.setup_instruments参看执行情况
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/clone%'; mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%'; mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current WHERE EVENT_NAME LIKE 'stage/innodb/clone%'; +--------------------------------+----------------+----------------+ | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED | +--------------------------------+----------------+----------------+ | stage/innodb/clone (redo copy) | 1 | 1 | +--------------------------------+----------------+----------------+ mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_history WHERE EVENT_NAME LIKE 'stage/innodb/clone%'; +--------------------------------+----------------+----------------+ | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED | +--------------------------------+----------------+----------------+ | stage/innodb/clone (file copy) | 301 | 301 | | stage/innodb/clone (page copy) | 0 | 0 | | stage/innodb/clone (redo copy) | 1 | 1 | +--------------------------------+----------------+----------------+ mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE WHERE NAME LIKE '%clone%'; +----------------------------------------------+---------+ | NAME | ENABLED | +----------------------------------------------+---------+ | wait/synch/mutex/innodb/clone_snapshot_mutex | NO | | wait/synch/mutex/innodb/clone_sys_mutex | NO | | wait/synch/mutex/innodb/clone_task_mutex | NO | | wait/io/file/innodb/innodb_clone_file | YES | | stage/innodb/clone (file copy) | YES | | stage/innodb/clone (redo copy) | YES | | stage/innodb/clone (page copy) | YES | | statement/abstract/clone | YES | | statement/clone/local | YES | | statement/clone/client | YES | | statement/clone/server | YES | | memory/innodb/clone | YES | | memory/clone/data | YES | +----------------------------------------------+---------+
