MySQL8.0新特性之clone
长话短说，这个东西可以快速克隆MySQL实例，相关人士测试，效率远胜xtrabackup，另外已经由公司开始用该功能做数据库备份
数据库版本
(root@localhost) [(none)]> select version();
+-----------+
| version() |
+-----------+
| 8.0.19 |
+-----------+
1 row in set (0.00 sec)
测试实例都是新初始化的干净实例
Ⅰ、clone插件安装
在线安装插件
(root@localhost) [(none)]> install plugin clone soname 'mysql_clone.so';
Query OK, 0 rows affected (0.00 sec)
查看插件状态
(root@localhost) [(none)]> select plugin_name, plugin_status from information_schema.plugins where plugin_name = 'clone';
+-------------+---------------+
| plugin_name | plugin_status |
+-------------+---------------+
| clone | ACTIVE |
+-------------+---------------+
1 row in set (0.00 sec)
Ⅱ、本地克隆
创建克隆账号
该账号需要backup_admin权限
(root@localhost) [(none)]> create user clone_user identified by '123456';
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(none)]> grant backup_admin on *.* to clone_user;
Query OK, 0 rows affected (0.01 sec)
创建clone目录
clone目录属主属组设置为启动mysql服务的用户
[root@master data]# ll
total 4
drwxr-xr-x 6 mysql mysql 4096 Apr 20 16:17 mysql
[root@master data]# pwd
/data
[root@master data]# mkdir clone_dir
[root@master data]# chown -R mysql:mysql clone_dir
创建测试数据
(root@localhost) [(none)]> create database t;
Query OK, 1 row affected (0.01 sec)
(root@localhost) [(none)]> create table t.t(id int);
Query OK, 0 rows affected (0.03 sec)
(root@localhost) [(none)]> insert into t.t values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
(root@localhost) [(none)]> select * from t.t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
用clone账号登陆做本地克隆
(clone_user@localhost) [(none)]> clone local data directory = '/data/clone_dir/mysql';
Query OK, 0 rows affected (0.24 sec)
启动并检查clone数据目录
[root@master data]# /etc/init.d/mysql.server stop
Shutting down MySQL.. [ OK ]
[root@master data]# mv mysql mysql_bak
[root@master data]# mv clone_dir/mysql .
[root@master data]# ll mysql
total 155672
drwxr-x--- 2 mysql mysql 4096 Apr 20 17:00 #clone
-rw-r----- 1 mysql mysql 5360 Apr 20 17:00 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Apr 20 17:00 ibdata1
-rw-r----- 1 mysql mysql 50331648 Apr 20 17:00 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Apr 20 17:00 ib_logfile1
drwxr-x--- 2 mysql mysql 4096 Apr 20 17:00 mysql
-rw-r----- 1 mysql mysql 25165824 Apr 20 17:00 mysql.ibd
drwxr-x--- 2 mysql mysql 4096 Apr 20 17:00 sys
drwxr-x--- 2 mysql mysql 4096 Apr 20 17:00 t
-rw-r----- 1 mysql mysql 10485760 Apr 20 17:00 undo_001
-rw-r----- 1 mysql mysql 10485760 Apr 20 17:00 undo_002
[root@master data]# /etc/init.d/mysql.server start
Starting MySQL.Logging to '/data/mysql/error.log'.
. [ OK ]
[root@master data]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(root@localhost) [(none)]> select * from t.t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
Ⅲ、远程克隆
远程克隆中，源叫做捐赠者，目标叫做接收者
这里用上面的实例作为捐赠者，下面在接收者上操作
接收者建议保持为空，如果不为空会被清空
在线安装插件
(root@localhost) [(none)]> install plugin clone soname 'mysql_clone.so';
Query OK, 0 rows affected (0.02 sec)
创建克隆账号
这里的克隆账号需要clone_admin权限，这个权限比捐赠者上的克隆账号多了shutdown权限，克隆完后需要重启数据库，所以非mysqld_safe启动则会报错，但不影响克隆，手动重启数据库即可。
(root@localhost) [(none)]> create user clone_user identified by '123456';
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(none)]> grant clone_admin on *.* to clone_user;
Query OK, 0 rows affected (0.01 sec)
设置捐赠者列表清单
(root@localhost) [(none)]> set global clone_valid_donor_list = '192.168.0.65:3306';
Query OK, 0 rows affected (0.00 sec)
用clone账号登陆做远程克隆
(clone_user@localhost) [(none)]> clone instance from clone_user@'192.168.0.65':3306 identified by '123456';
Query OK, 0 rows affected (1.03 sec)
(clone_user@localhost) [(none)]> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8
Current database: *** NONE ***
说明已经重启了
退出重连查看数据
我这里mysqld_safe自动重启了数据库，不需要手动启动数据库
(root@localhost) [(none)]> select * from t.t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.01 sec)
Ⅳ、捐赠者和接收者创建复制关系
捐赠者上创建复制账号
(root@localhost) [(none)]> create user rpl identified with 'mysql_native_password' by '123';
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(none)]> grant replication slave on *.* to rpl;
Query OK, 0 rows affected (0.00 sec)
接收者设置主从关系
(root@localhost) [(none)]> change master to master_host = '192.168.0.65',
-> master_user = 'rpl',
-> master_password = '123',
-> master_port = 3306,
-> master_auto_position = 1;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
启动并检查复制关系
(root@localhost) [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.65
Master_User: rpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 195
Relay_Log_File: slave1-relay-bin.000002
Relay_Log_Pos: 363
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Ⅴ、其他相关小操作
查看克隆过程状态及错误
(root@localhost) [(none)]> 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)
查看克隆步骤
(root@localhost) [(none)]> select
-> stage,
-> state,
-> cast(begin_time as DATETIME) as "START TIME",
-> cast(end_time as DATETIME) as "FINISH TIME",
-> lpad(sys.format_time(power(10,12) * (unix_timestamp(end_time) - unix_timestamp(begin_time))), 10, ' ') as DURATION,
-> lpad(concat(format(round(estimate/1024/1024,0), 0), "MB"), 16, ' ') as "Estimate",
-> case when begin_time is NULL then LPAD('%0', 7, ' ')
-> when estimate > 0 then
-> lpad(concat(round(data*100/estimate, 0), "%"), 7, ' ')
-> when end_time is NULL then lpad('0%', 7, ' ')
-> else lpad('100%', 7, ' ')
-> end as "Done(%)"
-> from performance_schema.clone_progress;
+-----------+-------------+---------------------+---------------------+------------+------------------+---------+
| stage | state | START TIME | FINISH TIME | DURATION | Estimate | Done(%) |
+-----------+-------------+---------------------+---------------------+------------+------------------+---------+
| DROP DATA | Completed | 2020-04-21 18:22:44 | 2020-04-21 18:22:44 | 303.87 ms | 0MB | 100% |
| FILE COPY | In Progress | 2020-04-21 18:22:44 | NULL | NULL | 3,600MB | 96% |
| PAGE COPY | Not Started | NULL | NULL | NULL | 0MB | %0 |
| REDO COPY | Not Started | NULL | NULL | NULL | 0MB | %0 |
| FILE SYNC | Not Started | NULL | NULL | NULL | 0MB | %0 |
| RESTART | Not Started | NULL | NULL | NULL | 0MB | %0 |
| RECOVERY | Not Started | NULL | NULL | NULL | 0MB | %0 |
+-----------+-------------+---------------------+---------------------+------------+------------------+---------+
7 rows in set (0.01 sec)
+-----------+-----------+---------------------+---------------------+------------+------------------+---------+
| stage | state | START TIME | FINISH TIME | DURATION | Estimate | Done(%) |
+-----------+-----------+---------------------+---------------------+------------+------------------+---------+
| DROP DATA | Completed | 2020-04-21 18:22:44 | 2020-04-21 18:22:44 | 303.87 ms | 0MB | 100% |
| FILE COPY | Completed | 2020-04-21 18:22:44 | 2020-04-21 18:23:06 | 22.28 s | 3,600MB | 100% |
| PAGE COPY | Completed | 2020-04-21 18:23:06 | 2020-04-21 18:23:08 | 2.01 s | 0MB | 100% |
| REDO COPY | Completed | 2020-04-21 18:23:08 | 2020-04-21 18:23:09 | 300.20 ms | 0MB | 100% |
| FILE SYNC | Completed | 2020-04-21 18:23:09 | 2020-04-21 18:23:09 | 327.89 ms | 0MB | 100% |
| RESTART | Completed | 2020-04-21 18:23:09 | 2020-04-21 18:23:13 | 3.90 s | 0MB | 100% |
| RECOVERY | Completed | 2020-04-21 18:23:13 | 2020-04-21 18:23:13 | 482.81 ms | 0MB | 100% |
+-----------+-----------+---------------------+---------------------+------------+------------------+---------+
7 rows in set (0.01 sec)
查看克隆次数
只能看本地克隆次数，该命令记录clone命令执行次数
远程克隆，在接收者上执行，clone成功后服务重启，这个值会被置零
(root@localhost) [(none)]> show global status like 'Com_clone';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_clone | 1 |
+---------------+-------+
1 row in set (0.00 sec)
停止克隆
直接kill克隆线程即可
