前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >mysql clone plugin

mysql clone plugin

原创
作者头像
wangwei-dba
修改2021-02-08 16:11:05
1.6K0
修改2021-02-08 16:11:05
举报
文章被收录于专栏:mysql-dba

mysql clone plugin 从mysql8.0.17新增的新功能

克隆分为:

Cloning Data Locally(本地克隆)

Cloning Remote Data(远程克隆)

克隆可以用于数据库备份(这样就不用依赖第三方工具进行备份了)

克隆可以用于搭建主从复制环境

以下在mysql8.0.18的环境下操作

clone插件安装

1.通过配置文件加载

代码语言:javascript
复制
[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

2.运行的数据库上直接install pugin

代码语言:javascript
复制
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

3.检查插件状态

代码语言:javascript
复制
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME = 'clone';
+------------------------+---------------+
| PLUGIN_NAME            | PLUGIN_STATUS |
+------------------------+---------------+
| clone                  | ACTIVE        |
+------------------------+---------------+

Cloning Data Locally

1.创建clone用户并赋予权限backup_admin

代码语言:javascript
复制
 mysql> create user 'clone_user'@'%' identified by 'clone_123456';
 mysql> GRANT BACKUP_ADMIN ON *.* TO 'clone_user';

2.创建clone数据存放目录

代码语言:javascript
复制
 mkidr /data/mysql/mysql3318/ -p
 chown mysql:mysql -R /data/mysql/mysql3318

3.clone语句执行

代码语言:javascript
复制
 mysql> CLONE LOCAL DATA DIRECTORY = '/data/mysql/mysql3318/data'; 

4.启动新clone的数据库

代码语言:javascript
复制
 /usr/local/mysql8020/bin/mysqld --defaults-file=/data/mysql/mysql3312/my3312.cnf --user=mysql &

启动报错:

代码语言:javascript
复制
2020-05-20T06:16:54.227559-00:00 1 [Note] [MY-012905] [InnoDB] Cannot create undolog/undo_001 because ./undo_001 already uses Space ID=4294967279! Did you change innodb_undo_directory?
2020-05-20T06:16:54.236136-00:00 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Invalid Filename.
2020-05-20T06:16:54.788165-00:00 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2020-05-20T06:16:54.804444-00:00 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-05-20T06:16:54.806619-00:00 0 [ERROR] [MY-010119] [Server] Aborting

解决方法删除 undo_001 undo_002

[root@vg1 data]# rm -rf undo_00*

查询复制进度:

代码语言:javascript
复制
mysql> SELECT *  FROM performance_schema.clone_progress;
+------+-----------+-----------+----------------------------+----------------------------+---------+-----------+-----------+---------+------------+---------------+
| ID   | STAGE     | STATE     | BEGIN_TIME                 | END_TIME                   | THREADS | ESTIMATE  | DATA      | NETWORK | DATA_SPEED | NETWORK_SPEED |
+------+-----------+-----------+----------------------------+----------------------------+---------+-----------+-----------+---------+------------+---------------+
|    1 | DROP DATA | Completed | 2020-05-20 14:11:26.449663 | 2020-05-20 14:11:26.473050 |       1 |         0 |         0 |       0 |          0 |             0 |
|    1 | FILE COPY | Completed | 2020-05-20 14:11:26.473124 | 2020-05-20 14:11:26.854035 |       1 | 151229818 | 151229818 |       0 |          0 |             0 |
|    1 | PAGE COPY | Completed | 2020-05-20 14:11:26.854210 | 2020-05-20 14:11:26.874196 |       1 |         0 |         0 |       0 |          0 |             0 |
|    1 | REDO COPY | Completed | 2020-05-20 14:11:26.874634 | 2020-05-20 14:11:26.876252 |       1 |      6144 |      6144 |       0 |          0 |             0 |
|    1 | FILE SYNC | Completed | 2020-05-20 14:11:26.876385 | 2020-05-20 14:11:29.618170 |       1 |         0 |         0 |       0 |          0 |             0 |
|    1 | RESTART   | Completed | 2020-05-20 14:11:29.618170 | 2020-05-20 14:16:47.658859 |       0 |         0 |         0 |       0 |          0 |             0 |
|    1 | RECOVERY  | Completed | 2020-05-20 14:16:47.658859 | 2020-05-20 14:16:49.546178 |       0 |         0 |         0 |       0 |          0 |             0 |
+------+-----------+-----------+----------------------------+----------------------------+---------+-----------+-----------+---------+------------+---------------+
7 rows in set (0.04 sec)
mysql> 

Cloning Remote Data

用管理员账号登录到奉献者的mysql实例上

1.创建一个clone用户并赋予backup_admin的权限

代码语言:javascript
复制
 mysql> CREATE USER 'donor_clone_user'@'%' IDENTIFIED BY '123456';
 mysql> GRANT BACKUP_ADMIN on *.* to 'donor_clone_user'@'123456';

2.安装clon插件

代码语言:javascript
复制
mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';

用管理员账号登录到接收者的mysql实例上

1.创建一个clone用户并赋予clone_admin权限

代码语言:javascript
复制
mysql> CREATE USER 'recipient_clone_user'@'%' IDENTIFIED BY 'password';
mysql> GRANT CLONE_ADMIN on *.* to 'recipient_clone_user'@'%';

2.安装clon插件

代码语言:javascript
复制
mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';

3.添加奉献者的主机地址到变量clone_valid_donor_list中

代码语言:javascript
复制
mysql> SET GLOBAL clone_valid_donor_list = '192.168.6.82:3312';

用新建的clone用户登录到接收者的mysql实例上执行clone语句

代码语言:javascript
复制
mysql> CLONE INSTANCE FROM 'donor_clone_user'@'192.168.6.82':3312
       IDENTIFIED BY '123456';

clone会删除原来的接收者的数据然后重启实例,检查数据

代码语言:javascript
复制
mysql> SELECT *  FROM performance_schema.clone_progress;
+------+-----------+-----------+----------------------------+----------------------------+---------+-----------+-----------+-----------+------------+---------------+
| ID   | STAGE     | STATE     | BEGIN_TIME                 | END_TIME                   | THREADS | ESTIMATE  | DATA      | NETWORK   | DATA_SPEED | NETWORK_SPEED |
+------+-----------+-----------+----------------------------+----------------------------+---------+-----------+-----------+-----------+------------+---------------+
|    1 | DROP DATA | Completed | 2020-05-20 14:35:41.497756 | 2020-05-20 14:35:41.790794 |       1 |         0 |         0 |         0 |          0 |             0 |
|    1 | FILE COPY | Completed | 2020-05-20 14:35:41.790946 | 2020-05-20 14:35:42.632340 |       1 | 151229818 | 151229818 | 151245765 |          0 |             0 |
|    1 | PAGE COPY | Completed | 2020-05-20 14:35:42.632528 | 2020-05-20 14:35:42.745793 |       1 |         0 |         0 |        99 |          0 |             0 |
|    1 | REDO COPY | Completed | 2020-05-20 14:35:42.746203 | 2020-05-20 14:35:42.846414 |       1 |      2560 |      2560 |      3031 |          0 |             0 |
|    1 | FILE SYNC | Completed | 2020-05-20 14:35:42.846607 | 2020-05-20 14:35:45.372399 |       1 |         0 |         0 |         0 |          0 |             0 |
|    1 | RESTART   | Completed | 2020-05-20 14:35:45.372399 | 2020-05-20 14:35:49.743221 |       0 |         0 |         0 |         0 |          0 |             0 |
|    1 | RECOVERY  | Completed | 2020-05-20 14:35:49.743221 | 2020-05-20 14:35:50.404347 |       0 |         0 |         0 |         0 |          0 |             0 |
+------+-----------+-----------+----------------------------+----------------------------+---------+-----------+-----------+-----------+------------+---------------+

Cloning to a Named Directory(克隆到指定的目录)

默认情况下,远程克隆操作会删除原来接收者的数据,替换为clone的数据,通过Cloning to a Named Directory可以避免删除原来的数据

把数据clone到指定的目录

clone操作和上面远程clone操作步骤一样

唯一不同的是执行的clone语句指定DATA DIRECTORY

代码语言:javascript
复制

mysql> CLONE INSTANCE FROM 'clone_user'@'192.168.6.81':3312 IDENTIFIED BY '123456' DATA DIRECTORY = '/data/mysql/mysql3333/data';
Query OK, 0 rows affected (3.75 sec)
[root@localhost data]# pwd
/data/mysql/mysql3333/data
[root@localhost data]# ll
total 2244616
drwxr-x---. 2 mysql mysql         89 May 20 06:43 #clone
drwxr-x---. 2 mysql mysql         20 May 20 06:43 czx
-rw-r-----. 1 mysql mysql       5498 May 20 06:43 ib_buffer_pool
-rw-r-----. 1 mysql mysql  104857600 May 20 06:43 ibdata1
-rw-r-----. 1 mysql mysql 1073741824 May 20 06:43 ib_logfile0
-rw-r-----. 1 mysql mysql 1073741824 May 20 06:43 ib_logfile1
drwxr-x---. 2 mysql mysql          6 May 20 06:43 mysql
-rw-r-----. 1 mysql mysql   25165824 May 20 06:43 mysql.ibd
drwxr-x---. 2 mysql mysql         28 May 20 06:43 sys
-rw-r-----. 1 mysql mysql   10485760 May 20 06:43 undo_001
-rw-r-----. 1 mysql mysql   10485760 May 20 06:43 undo_002

可用通过mysqld 启动clone的实例数据

代码语言:javascript
复制
/usr/local/mysql8020/bin/mysqld --defaults-file=/data/mysql/mysql3333/my3333.cnf --user=mysql &

Cloning for Replication(利用克隆做复制)

基于GTID复制

在master创建复制用户

代码语言:javascript
复制
mysql> create user repl@'%' identified by '123456' ;
Query OK, 0 rows affected (0.14 sec)
mysql> grant replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)

在slave端执行change master

代码语言:javascript
复制
mysql> change master to master_host='192.168.6.82', master_user='repl', master_password='123456',master_port=3312, master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> show slave status \G;
Monitoring Cloning Operations
Connect to the recipient MySQL server instance.
Query the clone_status table:
mysql> SELECT STATE FROM performance_schema.clone_status;
+-----------+
| STATE     |
+-----------+
| Completed |
+-----------+
Connect to the recipient MySQL server instance.
Query the clone_progress table. For example, the following query provides state and end time data for each stage of the cloning operation:
mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
+-----------+-----------+----------------------------+
| stage     | state     | end_time                   |
+-----------+-----------+----------------------------+
| DROP DATA | Completed | 2019-01-27 22:45:43.141261 |
| FILE COPY | Completed | 2019-01-27 22:45:44.457572 |
| PAGE COPY | Completed | 2019-01-27 22:45:44.577330 |
| REDO COPY | Completed | 2019-01-27 22:45:44.679570 |
| FILE SYNC | Completed | 2019-01-27 22:45:44.918547 |
| RESTART   | Completed | 2019-01-27 22:45:48.583565 |
| RECOVERY  | Completed | 2019-01-27 22:45:49.626595 |
+-----------+-----------+----------------------------+
mysql> SELECT *  FROM performance_schema.clone_progress;
+------+-----------+-------------+----------------------------+----------------------------+---------+-----------+-----------+-----------+------------+---------------+
| ID   | STAGE     | STATE       | BEGIN_TIME                 | END_TIME                   | THREADS | ESTIMATE  | DATA      | NETWORK   | DATA_SPEED | NETWORK_SPEED |
+------+-----------+-------------+----------------------------+----------------------------+---------+-----------+-----------+-----------+------------+---------------+
|    1 | DROP DATA | Completed   | 2020-05-19 15:50:16.391300 | 2020-05-19 15:50:16.501601 |       1 |         0 |         0 |         0 |          0 |             0 |
|    1 | FILE COPY | Completed   | 2020-05-19 15:50:16.502777 | 2020-05-19 15:50:17.356592 |       1 | 151229818 | 151229818 | 151245763 |          0 |             0 |
|    1 | PAGE COPY | Completed   | 2020-05-19 15:50:17.356831 | 2020-05-19 15:50:17.462537 |       1 |         0 |         0 |        99 |          0 |             0 |
|    1 | REDO COPY | Completed   | 2020-05-19 15:50:17.463045 | 2020-05-19 15:50:17.563600 |       1 |      4608 |      4608 |      5005 |          0 |             0 |
|    1 | FILE SYNC | Completed   | 2020-05-19 15:50:17.563808 | 2020-05-19 15:50:20.135486 |       1 |         0 |         0 |         0 |          0 |             0 |
|    1 | RESTART   | Not Started | NULL                       | NULL                       |       0 |         0 |         0 |         0 |          0 |             0 |
|    1 | RECOVERY  | Not Started | NULL                       | NULL                       |       0 |         0 |         0 |         0 |          0 |             0 |
+------+-----------+-------------+----------------------------+----------------------------+---------+-----------+-----------+-----------+------------+---------------+
7 rows in set (0.00 sec)

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档