mysql 8.0.31 使用clone特性做备份 (5)

参考文档：https://www.cnblogs.com/—wunian/p/12806418.html
数据库版本
select version();
clone插件安装

install plugin clone soname 'mysql_clone.so';

查看插件状态

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

create user clone_user identified by '123456';
grant backup_admin on *.* to clone_user;

创建clone目录

clone目录属主属组设置为启动mysql服务的用户

[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 = ‘/clone_dir/mysql’;
Query OK, 0 rows affected (0.24 sec)
测试用克隆恢复数据库
service mysql stop
mv data data_bak
mv clone_dir/mysql data
service mysql start
完整测试记录如下：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

mysql> install plugin clone soname 'mysql_clone.so';
Query OK, 0 rows affected (0.04 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.00 sec)

mysql> create user clone_user identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant backup_admin on *.* to clone_user;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@mysql1 ~]# pwd
/root
[root@mysql1 ~]# mkdir /data
[root@mysql1 ~]# mv /data /clone_dir
[root@mysql1 ~]# chown -R mysql.mysql /clone_dir
[root@mysql1 ~]# mysql -u root -pabcd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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.

mysql> create database t;
Query OK, 1 row affected (0.00 sec)

mysql> create table t.t(id int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t.t values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t.t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> clone local data directory = '/clone_dir/mysql';
ERROR 1227 (42000): Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation
mysql> exit
Bye
[root@mysql1 ~]# mysql -u clone_user -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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.

mysql> clone local data directory = '/clone_dir/mysql';
Query OK, 0 rows affected (0.91 sec)

mysql> exit
Bye
[root@mysql1 ~]# ll /clone_dir/mysql/
total 69636
drwxr-x---. 2 mysql mysql       89 Dec 16 10:53 #clone
-rw-r-----. 1 mysql mysql     4052 Dec 16 10:53 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Dec 16 10:53 ibdata1
drwxr-x---. 2 mysql mysql       23 Dec 16 10:53 #innodb_redo
drwxr-x---. 2 mysql mysql       22 Dec 16 10:53 jycdb
drwxr-x---. 2 mysql mysql        6 Dec 16 10:53 mysql
-rw-r-----. 1 mysql mysql 25165824 Dec 16 10:53 mysql.ibd
drwxr-x---. 2 mysql mysql       28 Dec 16 10:53 sys
drwxr-x---. 2 mysql mysql       19 Dec 16 10:53 t
-rw-r-----. 1 mysql mysql 16777216 Dec 16 10:53 undo_001
-rw-r-----. 1 mysql mysql 16777216 Dec 16 10:53 undo_002
[root@mysql1 ~]# ll /clone_dir/
total 0
drwxr-x---. 8 mysql mysql 172 Dec 16 10:53 mysql
[root@mysql1 ~]# ll /mysql/data/
total 91348
-rw-r-----. 1 mysql mysql       56 Dec  7 17:08 auto.cnf
-rw-------. 1 mysql mysql     1676 Dec  7 17:08 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  7 17:08 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  7 17:08 client-cert.pem
-rw-------. 1 mysql mysql     1676 Dec  7 17:08 client-key.pem
-rw-r-----. 1 mysql mysql   589824 Dec 16 10:54 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql  8978432 Dec  7 17:08 #ib_16384_1.dblwr
drwxr-x---. 3 mysql mysql       22 Dec 16 10:53 #ib_archive
-rw-r-----. 1 mysql mysql     4052 Dec  7 18:40 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Dec 16 10:53 ibdata1
-rw-r-----. 1 mysql mysql 12582912 Dec  7 18:40 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Dec  7 18:45 #innodb_redo
drwxr-x---. 2 mysql mysql      187 Dec  7 18:40 #innodb_temp
drwxr-x---. 2 mysql mysql       22 Dec  7 17:16 jycdb
drwxr-x---. 2 mysql mysql      143 Dec  7 17:08 mysql
-rw-r-----. 1 mysql mysql     4013 Dec  7 18:40 mysql1.err
-rw-r-----. 1 mysql mysql        6 Dec  7 18:40 mysql1.pid
-rw-r-----. 1 mysql mysql      180 Dec  7 17:09 mysql-bin.000001
-rw-r-----. 1 mysql mysql     3145 Dec  7 18:03 mysql-bin.000002
-rw-r-----. 1 mysql mysql      180 Dec  7 18:40 mysql-bin.000003
-rw-r-----. 1 mysql mysql     1292 Dec 16 10:52 mysql-bin.000004
-rw-r-----. 1 mysql mysql      116 Dec  7 18:40 mysql-bin.index
-rw-r-----. 1 mysql mysql 25165824 Dec 16 10:52 mysql.ibd
drwxr-x---. 2 mysql mysql     8192 Dec 16 10:50 performance_schema
-rw-------. 1 mysql mysql     1676 Dec  7 17:08 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Dec  7 17:08 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  7 17:08 server-cert.pem
-rw-------. 1 mysql mysql     1676 Dec  7 17:08 server-key.pem
drwxr-x---. 2 mysql mysql       28 Dec  7 17:08 sys
drwxr-x---. 2 mysql mysql       19 Dec 16 10:52 t
-rw-r-----. 1 mysql mysql 16777216 Dec 16 10:54 undo_001
-rw-r-----. 1 mysql mysql 16777216 Dec 16 10:54 undo_002
[root@mysql1 ~]# ll /mysql/data/
total 91348
-rw-r-----. 1 mysql mysql       56 Dec  7 17:08 auto.cnf
-rw-------. 1 mysql mysql     1676 Dec  7 17:08 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  7 17:08 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  7 17:08 client-cert.pem
-rw-------. 1 mysql mysql     1676 Dec  7 17:08 client-key.pem
-rw-r-----. 1 mysql mysql   589824 Dec 16 10:54 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql  8978432 Dec  7 17:08 #ib_16384_1.dblwr
drwxr-x---. 3 mysql mysql       22 Dec 16 10:53 #ib_archive
-rw-r-----. 1 mysql mysql     4052 Dec  7 18:40 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Dec 16 10:53 ibdata1
-rw-r-----. 1 mysql mysql 12582912 Dec  7 18:40 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Dec  7 18:45 #innodb_redo
drwxr-x---. 2 mysql mysql      187 Dec  7 18:40 #innodb_temp
drwxr-x---. 2 mysql mysql       22 Dec  7 17:16 jycdb
drwxr-x---. 2 mysql mysql      143 Dec  7 17:08 mysql
-rw-r-----. 1 mysql mysql     4013 Dec  7 18:40 mysql1.err
-rw-r-----. 1 mysql mysql        6 Dec  7 18:40 mysql1.pid
-rw-r-----. 1 mysql mysql      180 Dec  7 17:09 mysql-bin.000001
-rw-r-----. 1 mysql mysql     3145 Dec  7 18:03 mysql-bin.000002
-rw-r-----. 1 mysql mysql      180 Dec  7 18:40 mysql-bin.000003
-rw-r-----. 1 mysql mysql     1292 Dec 16 10:52 mysql-bin.000004
-rw-r-----. 1 mysql mysql      116 Dec  7 18:40 mysql-bin.index
-rw-r-----. 1 mysql mysql 25165824 Dec 16 10:52 mysql.ibd
drwxr-x---. 2 mysql mysql     8192 Dec 16 10:50 performance_schema
-rw-------. 1 mysql mysql     1676 Dec  7 17:08 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Dec  7 17:08 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  7 17:08 server-cert.pem
-rw-------. 1 mysql mysql     1676 Dec  7 17:08 server-key.pem
drwxr-x---. 2 mysql mysql       28 Dec  7 17:08 sys
drwxr-x---. 2 mysql mysql       19 Dec 16 10:52 t
-rw-r-----. 1 mysql mysql 16777216 Dec 16 10:54 undo_001
-rw-r-----. 1 mysql mysql 16777216 Dec 16 10:54 undo_002
[root@mysql1 ~]# ll /clone_dir/
total 0
drwxr-x---. 8 mysql mysql 172 Dec 16 10:53 mysql
[root@mysql1 ~]# ll /clone_dir/mysql/
total 69636
drwxr-x---. 2 mysql mysql       89 Dec 16 10:53 #clone
-rw-r-----. 1 mysql mysql     4052 Dec 16 10:53 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Dec 16 10:53 ibdata1
drwxr-x---. 2 mysql mysql       23 Dec 16 10:53 #innodb_redo
drwxr-x---. 2 mysql mysql       22 Dec 16 10:53 jycdb
drwxr-x---. 2 mysql mysql        6 Dec 16 10:53 mysql
-rw-r-----. 1 mysql mysql 25165824 Dec 16 10:53 mysql.ibd
drwxr-x---. 2 mysql mysql       28 Dec 16 10:53 sys
drwxr-x---. 2 mysql mysql       19 Dec 16 10:53 t
-rw-r-----. 1 mysql mysql 16777216 Dec 16 10:53 undo_001
-rw-r-----. 1 mysql mysql 16777216 Dec 16 10:53 undo_002
[root@mysql1 ~]# ll /clone_dir/mysql/mysql
total 0
[root@mysql1 ~]# ll /mysql/data/
total 91348
-rw-r-----. 1 mysql mysql       56 Dec  7 17:08 auto.cnf
-rw-------. 1 mysql mysql     1676 Dec  7 17:08 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  7 17:08 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  7 17:08 client-cert.pem
-rw-------. 1 mysql mysql     1676 Dec  7 17:08 client-key.pem
-rw-r-----. 1 mysql mysql   589824 Dec 16 10:54 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql  8978432 Dec  7 17:08 #ib_16384_1.dblwr
drwxr-x---. 3 mysql mysql       22 Dec 16 10:53 #ib_archive
-rw-r-----. 1 mysql mysql     4052 Dec  7 18:40 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Dec 16 10:53 ibdata1
-rw-r-----. 1 mysql mysql 12582912 Dec  7 18:40 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Dec  7 18:45 #innodb_redo
drwxr-x---. 2 mysql mysql      187 Dec  7 18:40 #innodb_temp
drwxr-x---. 2 mysql mysql       22 Dec  7 17:16 jycdb
drwxr-x---. 2 mysql mysql      143 Dec  7 17:08 mysql
-rw-r-----. 1 mysql mysql     4013 Dec  7 18:40 mysql1.err
-rw-r-----. 1 mysql mysql        6 Dec  7 18:40 mysql1.pid
-rw-r-----. 1 mysql mysql      180 Dec  7 17:09 mysql-bin.000001
-rw-r-----. 1 mysql mysql     3145 Dec  7 18:03 mysql-bin.000002
-rw-r-----. 1 mysql mysql      180 Dec  7 18:40 mysql-bin.000003
-rw-r-----. 1 mysql mysql     1292 Dec 16 10:52 mysql-bin.000004
-rw-r-----. 1 mysql mysql      116 Dec  7 18:40 mysql-bin.index
-rw-r-----. 1 mysql mysql 25165824 Dec 16 10:52 mysql.ibd
drwxr-x---. 2 mysql mysql     8192 Dec 16 10:50 performance_schema
-rw-------. 1 mysql mysql     1676 Dec  7 17:08 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Dec  7 17:08 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Dec  7 17:08 server-cert.pem
-rw-------. 1 mysql mysql     1676 Dec  7 17:08 server-key.pem
drwxr-x---. 2 mysql mysql       28 Dec  7 17:08 sys
drwxr-x---. 2 mysql mysql       19 Dec 16 10:52 t
-rw-r-----. 1 mysql mysql 16777216 Dec 16 10:54 undo_001
-rw-r-----. 1 mysql mysql 16777216 Dec 16 10:54 undo_002
[root@mysql1 ~]# ll /mysql/data/mysql
total 32
-rw-r-----. 1 mysql mysql  5972 Dec  7 17:08 general_log_213.sdi
-rw-r-----. 1 mysql mysql    35 Dec  7 18:03 general_log.CSM
-rw-r-----. 1 mysql mysql     0 Dec  7 17:08 general_log.CSV
-rw-r-----. 1 mysql mysql 12505 Dec  7 17:08 slow_log_214.sdi
-rw-r-----. 1 mysql mysql    35 Dec  7 18:03 slow_log.CSM
-rw-r-----. 1 mysql mysql     0 Dec  7 17:08 slow_log.CSV
[root@mysql1 ~]# pwd
/root
[root@mysql1 ~]# service mysql stop
Shutting down MySQL.. SUCCESS! 
[root@mysql1 ~]# pwd
/root
[root@mysql1 ~]# cd /mysql/data
[root@mysql1 data]# ls
auto.cnf         client-key.pem     ib_buffer_pool  jycdb             mysql-bin.000002  mysql.ibd           server-cert.pem  undo_001
ca-key.pem       #ib_16384_0.dblwr  ibdata1         mysql             mysql-bin.000003  performance_schema  server-key.pem   undo_002
ca.pem           #ib_16384_1.dblwr  #innodb_redo    mysql1.err        mysql-bin.000004  private_key.pem     sys
client-cert.pem  #ib_archive        #innodb_temp    mysql-bin.000001  mysql-bin.index   public_key.pem      t
[root@mysql1 data]# cd ..
[root@mysql1 mysql]# mv data data_bak
[root@mysql1 mysql]# mv /clone_dir/mysql data
[root@mysql1 mysql]# chown -R mysql.mysql data
[root@mysql1 mysql]# service mysql start
Starting MySQL.Logging to '/mysql/data/mysql1.err'.
... SUCCESS! 
[root@mysql1 mysql]# mysql -u root -pabcd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jycdb              |
| mysql              |
| performance_schema |
| sys                |
| t                  |
+--------------------+
6 rows in set (0.01 sec)

mysql> use t;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------+
| Tables_in_t |
+-------------+
| t           |
+-------------+
1 row in set (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql>