之前其实也已经看了一篇克隆插件的文章,但是那个主要是看别人微信发的,自己给保留在博客上,感觉写的不够仔细,今天我在这里再重复写一遍,以便自己理解和学习。
1.什么是克隆插件?
克隆插件可以看做一个功能,就是将本地或者远程的mysql server 实例上的数据通过快照的形式将它克隆出来以便可以用作备份、搭建备节点等等。。。
The clone plugin, introduced in MySQL 8.0.17, permits cloning data locally or from a remote MySQL server instance. Cloned data is a physical snapshot of data stored in
InnoDB
that includes schemas, tables, tablespaces, and data dictionary metadata. The cloned data comprises a fully functional data directory, which permits using the clone plugin for MySQL server provisioning.
其中克隆方式又分为本地克隆和远程克隆两种。
- 本地克隆:本地克隆就是将数据克隆在启动克隆的mysql server 实例上。
- 远程克隆
远程克隆包括启动克隆操作的本地mysql实例(''the recipient")和源数据所在的远程Mysql实例("the donor"),当在接受者上启动远程克隆操作时,克隆数据通过网络从捐赠者传输到接受者。默认情况下,远程克隆操作在克隆提供者的数据之前,会从接受数据目录中删除现有的用户创建的schema,tables,tablespaces以及二进制日志。此外还可以就将数据克隆到收件人的两一个目录,以避免从当前收件人数据目录中删除数据。
2.安装克隆插件
克隆插件必须安装到donor和recipient双方的mysql server实例上。 在配置文件my.cnf中添加
[mysqld] plugin-load-add=mysql_clone.so
可以在数据库运行时安装
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
查看安装情况
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'clone'; +------------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +------------------------+---------------+ | clone | ACTIVE | +------------------------+---------------+
3.本地克隆
1)创建克隆用户
mysql> create user 'clone_user'@'%' identified by 'clone_pass';
mysql> grant backup_admin on *.* to 'clone_user'@'%';
2)创建一个目录(放克隆数据的)
# mkdir -p /data/backup
# chown -R mysql.mysql /data/backup/
3)执行克隆
# mysql -uclone_user -pclone_pass
mysql> clone local data directory='/data/backup/3306';
注意:1.克隆的目录必须是绝对路径,2./data/backup必须存在,且mysql对其有可写权限 3. 最后一级目录3306不能存在,这个是留着克隆时自动创建出来的。
4) 查看克隆目录
[root@mysql01 3307]# ls -ltr total 167944 drwxr-x--- 2 mysql mysql 6 Mar 30 22:42 mysql drwxr-x--- 2 mysql mysql 19 Mar 30 22:42 test drwxr-x--- 2 mysql mysql 28 Mar 30 22:42 sys -rw-r----- 1 mysql mysql 6077 Mar 30 22:42 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 Mar 30 22:42 ibdata1 -rw-r----- 1 mysql mysql 25165824 Mar 30 22:42 mysql.ibd -rw-r----- 1 mysql mysql 16777216 Mar 30 22:42 undo_002 -rw-r----- 1 mysql mysql 16777216 Mar 30 22:42 undo_001 -rw-r----- 1 mysql mysql 50331648 Mar 30 22:42 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 Mar 30 22:42 ib_logfile1 drwxr-x--- 2 mysql mysql 89 Mar 30 22:42 #clone
4.远程克隆
1)在 Donor 实例上创建克隆用户,加载 Clone Plugin
mysql> create user 'donor_user'@'%' identified by 'donor_pass'; mysql> grant backup_admin on *.* to 'donor_user'@'%'; mysql> install plugin clone soname 'mysql_clone.so';
2)在 Recipient 实例上创建克隆用户,加载 Clone Plugin
mysql> create user 'recipient_user'@'%' identified by 'recipient_pass'; mysql> grant clone_admin on *.* to 'recipient_user'@'%'; mysql> install plugin clone soname 'mysql_clone.so';
3)在 Recipient 实例上设置 Donor 白名单,Recipient 只能克隆白名单中的实例:
mysql> set global clone_valid_donor_list = '192.168.244.10:3306';
4)在recipient发起克隆
# mysql -urecipient_user -precipient_pass mysql> clone instance from 'donor_user'@'192.168.244.10':3306 identified by 'donor_pass';
或者克隆在recipient一个备份目录里
mysql> CLONE INSTANCE FROM 'user'@'example.donor.host.com':3306 IDENTIFIED BY 'password' DATA DIRECTORY = '/path/to/clone_dir';
这里关于远程克隆的一些限制:https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-remote.html
5.监控cloning 操作
1.检查clone操作目前的状态
1)连接recipient Mysql server instance
2)查询 clone_status表
mysql> SELECT STATE FROM performance_schema.clone_status; +-----------+ | STATE | +-----------+ | Completed | +-----------+
如果克隆失败,可以查询clone_status表的错误信息
mysql> SELECT STATE, ERROR_NO, ERROR_MESSAGE FROM performance_schema.clone_status; +-----------+----------+---------------+ | STATE | ERROR_NO | ERROR_MESSAGE | +-----------+----------+---------------+ | Failed | xxx | "xxxxxxxxxxx" | +-----------+----------+---------------+
2. 查看clone操作每一步的细节
root@mysqldb 23:18: [(none)]> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress; +-----------+-----------+----------------------------+ | STAGE | STATE | END_TIME | +-----------+-----------+----------------------------+ | DROP DATA | Completed | 2023-03-29 22:48:57.126660 | | FILE COPY | Completed | 2023-03-29 22:48:57.439297 | | PAGE COPY | Completed | 2023-03-29 22:48:57.445958 | | REDO COPY | Completed | 2023-03-29 22:48:57.447353 | | FILE SYNC | Completed | 2023-03-29 22:48:57.491618 | | RESTART | Completed | 2023-03-29 22:49:01.075586 | | RECOVERY | Completed | 2023-03-29 22:49:02.425688 | +-----------+-----------+----------------------------+
3.Monitoring Cloning Operations Using Performance Schema Stage Events
这里主要通过事件的方式进行监控
参考:https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-monitoring.html
标签:插件,01,克隆,22,clone,30,user,mysql From: https://www.cnblogs.com/zmc60/p/17274792.html