mysql clone plugin

原创

wangwei-dba

修改于 2021-02-08 16:11:05

1.5K0

修改于 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.通过配置文件加载

[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

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

INSTALL PLUGIN clone SONAME 'mysql_clone.so';

3.检查插件状态

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

 mysql> create user 'clone_user'@'%' identified by 'clone_123456';
 mysql> GRANT BACKUP_ADMIN ON *.* TO 'clone_user';

2.创建clone数据存放目录

 mkidr /data/mysql/mysql3318/ -p
 chown mysql:mysql -R /data/mysql/mysql3318

3.clone语句执行

 mysql> CLONE LOCAL DATA DIRECTORY = '/data/mysql/mysql3318/data';

4.启动新clone的数据库

 /usr/local/mysql8020/bin/mysqld --defaults-file=/data/mysql/mysql3312/my3312.cnf --user=mysql &

启动报错：

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*

查询复制进度：

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的权限

 mysql> CREATE USER 'donor_clone_user'@'%' IDENTIFIED BY '123456';
 mysql> GRANT BACKUP_ADMIN on *.* to 'donor_clone_user'@'123456';

2.安装clon插件

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';

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

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

mysql> CREATE USER 'recipient_clone_user'@'%' IDENTIFIED BY 'password';
mysql> GRANT CLONE_ADMIN on *.* to 'recipient_clone_user'@'%';

2.安装clon插件

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';

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

mysql> SET GLOBAL clone_valid_donor_list = '192.168.6.82:3312';

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

mysql> CLONE INSTANCE FROM 'donor_clone_user'@'192.168.6.82':3312
       IDENTIFIED BY '123456';

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

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


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的实例数据

/usr/local/mysql8020/bin/mysqld --defaults-file=/data/mysql/mysql3333/my3333.cnf --user=mysql &

Cloning for Replication（利用克隆做复制）

基于GTID复制

在master创建复制用户

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

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 删除。

云数据库 SQL Server