文档课题：MySQL使用Clone特性搭建主从复制.
数据库：MySQL 8.0.27
环境介绍：
1、安装clone插件
--关闭防火墙，否则远程clone时会报错ERROR 3862.
[root@leo-827mgr-master ~]# systemctl stop firewalld
[root@leo-827mgr-master ~]# systemctl disable firewalld
(root@Master) [(none)]> select version();
+-----------+
| version() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.00 sec)
(root@Master) [(none)]> install plugin clone soname 'mysql_clone.so';
Query OK, 0 rows affected (0.01 sec)
(root@Master) [(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)
2、本地克隆
说明：此处在主机名为leo-827mgr-master上操作.
2.1、建用户以及目录
--创建克隆账号，该账号需要backup_admin权限.
(root@Master) [(none)]> create user clone_user@'%' identified by 'clone_4U';
Query OK, 0 rows affected (0.01 sec)
(root@Master) [(none)]> grant backup_admin on *.* to clone_user@'%';
Query OK, 0 rows affected (0.00 sec)
--创建clone目录，该目录属主设置为启动mysql服务的用户.
[mysql@leo-827mgr-master mysql]$ pwd
/mysql
[mysql@leo-827mgr-master mysql]$ mkdir clone_dir
2.2、建测试数据
--创建测试数据
(root@Master) [(none)]> create database booksDB;
Query OK, 1 row affected (0.00 sec)
(root@Master) [(none)]> use booksDB;
Database changed
(root@Master) [booksDB]> create table books
-> (
-> bk_id int not null primary key,
-> bk_title varchar(50) not null,
-> copyright year not null
-> );
Query OK, 0 rows affected (0.01 sec)
(root@Master) [booksDB]> insert into books values
-> (11078,'Learning MYSQL',2010),
-> (11033,'Study Html',2011),
-> (11035,'How to use php',2003),
-> (11072,'Teach yourself javascript',2005),
-> (11028,'Learning C++',2005),
-> (11069,'MYSQL professional',2009),
-> (11026,'Guide to MySQL 5.7',2008),
-> (11041,'Inside VC++',2011);
Query OK, 8 rows affected (0.02 sec)
Records: 8 Duplicates: 0 Warnings: 0
(root@Master) [booksDB]> select * from books;
+-------+---------------------------+-----------+
| bk_id | bk_title | copyright |
+-------+---------------------------+-----------+
| 11026 | Guide to MySQL 5.7 | 2008 |
| 11028 | Learning C++ | 2005 |
| 11033 | Study Html | 2011 |
| 11035 | How to use php | 2003 |
| 11041 | Inside VC++ | 2011 |
| 11069 | MYSQL professional | 2009 |
| 11072 | Teach yourself javascript | 2005 |
| 11078 | Learning MYSQL | 2010 |
+-------+---------------------------+-----------+
8 rows in set (0.00 sec)
2.3、开始克隆
--用clone账号登录数据库做本地克隆.
[mysql@leo-827mgr-master ~]$ mysql -uclone_user -pclone_4U
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 9
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, 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.
(clone_user@Master) [(none)> clone local data directory='/mysql/clone_dir/data';
Query OK, 0 rows affected (0.77 sec)
2.4、验证clone数据的可用性
--使用clone数据启动mysql实例，关闭数据库.
[mysql@leo-827mgr-master mysql]$ ps -ef|grep mysql
root 12860 12681 0 20:14 pts/1 00:00:00 su - mysql
mysql 12861 12860 0 20:14 pts/1 00:00:00 -bash
mysql 12973 12861 0 20:15 pts/1 00:00:00 /bin/sh /opt/mysql-8.0.27-linux-glibc2.17-x86_64-minimal/bin/mysqld_safe --defaults-file=/home/mysql/etc/my.cnf
mysql 13198 12973 1 20:15 pts/1 00:00:19 /opt/mysql-8.0.27-linux-glibc2.17-x86_64-minimal/bin/mysqld --defaults-file=/home/mysql/etc/my.cnf --basedir=/opt/mysql --datadir=/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/opt/logs/mysql_error.log --pid-file=/mysql/data/mysql.pid --socket=/mysql/data/mysql.sock --port=3306
root 13445 13362 0 20:29 pts/2 00:00:00 su - mysql
mysql 13446 13445 0 20:29 pts/2 00:00:00 -bash
mysql 13595 12861 0 20:39 pts/1 00:00:00 mysql -uclone_user -px xxxxxx
mysql 13642 13446 0 20:42 pts/2 00:00:00 ps -ef
mysql 13643 13446 0 20:42 pts/2 00:00:00 grep --color=auto mysql
[mysql@leo-827mgr-master mysql]$ kill -9 12973 13198
--修改目录
[mysql@leo-827mgr-master mysql]$ mv data data_bak
[mysql@leo-827mgr-master mysql]$ mv clone_dir/data .
[mysql@leo-827mgr-master mysql]$ ll data
total 184264
drwxr-x---. 2 mysql mysql 23 Oct 6 20:41 booksDB
drwxr-x---. 2 mysql mysql 89 Oct 6 20:41 #clone
-rw-r-----. 1 mysql mysql 6226 Oct 6 20:41 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Oct 6 20:41 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Oct 6 20:41 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Oct 6 20:41 ib_logfile1
drwxr-x---. 2 mysql mysql 6 Oct 6 20:41 mysql
-rw-r-----. 1 mysql mysql 25165824 Oct 6 20:41 mysql.ibd
drwxr-x---. 2 mysql mysql 28 Oct 6 20:41 sys
-rw-r-----. 1 mysql mysql 16777216 Oct 6 20:41 undo_001
-rw-r-----. 1 mysql mysql 16777216 Oct 6 20:41 undo_002
--重启数据库
[mysql@leo-827mgr-master mysql]$ mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
[1] 13683
[mysql@leo-827mgr-master mysql]$ 2023-10-06T12:47:01.176993Z mysqld_safe Logging to '/opt/logs/mysql_error.log'.
2023-10-06T12:47:01.203604Z mysqld_safe Starting mysqld daemon with databases from /mysql/data
--确认数据
[mysql@leo-827mgr-master ~]$ mysql -uroot -p -P 3306 -h 127.0.0.1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 9
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, 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.
(root@Master) [(none)> use booksDB;
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
(root@Master) [booksDB> select * from books;
+-------+---------------------------+-----------+
| bk_id | bk_title | copyright |
+-------+---------------------------+-----------+
| 11026 | Guide to MySQL 5.7 | 2008 |
| 11028 | Learning C++ | 2005 |
| 11033 | Study Html | 2011 |
| 11035 | How to use php | 2003 |
| 11041 | Inside VC++ | 2011 |
| 11069 | MYSQL professional | 2009 |
| 11072 | Teach yourself javascript | 2005 |
| 11078 | Learning MYSQL | 2010 |
+-------+---------------------------+-----------+
8 rows in set (0.00 sec)
说明：如上所示，成功运用clone的数据开启实例，并能查到相关数据.
3、远程克隆
说明：远程克隆中，源端成为捐赠者，目标端称为接收者，以下在接收者(主机名：leo-827mgr-slave01)操作.
3.1、关闭防火墙
[root@leo-827mgr-slave01 ~]# systemctl stop firewalld
[root@leo-827mgr-slave01 ~]# systemctl disable firewalld
3.2、安装插件
(root@Slave01) [(none)> install plugin clone soname 'mysql_clone.so';
Query OK, 0 rows affected (0.01 sec)
3.3、建用户及列表清单
说明：创建克隆账号，该账号需clone_admin权限，该权限比捐赠者上的克隆账号多出shutdown权限，克隆完后需重启数据库.
(root@Slave01) [(none)> create user clone_user@'%' identified by 'clone_4U';
Query OK, 0 rows affected (0.01 sec)
(root@Slave01) [(none)> grant clone_admin on *.* to clone_user@'%';
Query OK, 0 rows affected (0.00 sec)
设置捐赠者列表清单
(root@Slave01) [(none)> set global clone_valid_donor_list='192.168.133.126:3306';
Query OK, 0 rows affected (0.00 sec)
3.4、远程克隆
--使用clone账号登录开始远程克隆.
[mysql@leo-827mgr-slave01 ~]$ mysql -uclone_user -pclone_4U
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 10
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, 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.
(clone_user@Slave01) [(none)> clone instance from clone_user@'192.168.133.126':3306 identified by 'clone_4U';
Query OK, 0 rows affected (0.99 sec)
(clone_user@Slave01) [(none)> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 8
Current database: *** NONE ***
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)
(clone_user@Slave01) [(none)> exit
Bye
[mysql@leo-827mgr-slave01 ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 9
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, 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.
(root@Slave01) [(none)> show databases;
+--------------------+
| Database |
+--------------------+
| booksDB |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
(root@Slave01) [(none)> use booksDB;
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
(root@Slave01) [booksDB> select * from books;
+-------+---------------------------+-----------+
| bk_id | bk_title | copyright |
+-------+---------------------------+-----------+
| 11026 | Guide to MySQL 5.7 | 2008 |
| 11028 | Learning C++ | 2005 |
| 11033 | Study Html | 2011 |
| 11035 | How to use php | 2003 |
| 11041 | Inside VC++ | 2011 |
| 11069 | MYSQL professional | 2009 |
| 11072 | Teach yourself javascript | 2005 |
| 11078 | Learning MYSQL | 2010 |
+-------+---------------------------+-----------+
8 rows in set (0.00 sec)
说明：如上所示，成功运用clone技术将捐赠者上的booksDB数据库克隆到目标端.
4、创建主从复制关系
--捐赠者(192.168.133.126)创建复制账号
(root@Master) [(none)> create user repl@ '%' identified with 'mysql_native_password' by 'repl@12345';
Query OK, 0 rows affected (0.02 sec)
(root@Master) [(none)> grant replication slave on *.* to repl@ '%';
Query OK, 0 rows affected (0.00 sec)
--接收者(192.168.133.127)设置主从关系
(root@Slave01) [(none)> change master to master_host = '192.168.133.126',master_user = 'repl',master_password = 'repl@12345',master_port = 3306,master_auto_position = 1;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
(root@Slave01) [(none)> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
(root@Slave01) [(none)> show slave status G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.133.126
Master_User: repl
Mas服务器托管网ter_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 699
Relay_Log_File: leo-827mgr-slave01-relay-bin.000002
Relay_Log_Pos: 874
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 699
Relay_Log_Space: 1096
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 71c3e81f-6446-11ee-a822-000c29a63753
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 71c3e81服务器托管网f-6446-11ee-a822-000c29a63753:1-2
Executed_Gtid_Set: 71c3e81f-6446-11ee-a822-000c29a63753:1-2,
ffeda4cd-6441-11ee-b625-000c29a63753:1-7
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
(root@Slave01) [(none)> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| clone_user | % |
| repl | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
5、相关SQL语句
--查克隆状态及错误
(root@Slave01) [(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@Slave01) [(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 | 2023-10-06 21:06:17 | 2023-10-06 21:06:17 | 156.51 ms | 0MB | 100% |
| FILE COPY | Completed | 2023-10-06 21:06:17 | 2023-10-06 21:06:18 | 271.84 ms | 68MB | 100% |
| PAGE COPY | Completed | 2023-10-06 21:06:18 | 2023-10-06 21:06:18 | 46.42 ms | 0MB | 100% |
| REDO COPY | Completed | 2023-10-06 21:06:18 | 2023-10-06 21:06:18 | 15.18 ms | 0MB | 100% |
| FILE SYNC | Completed | 2023-10-06 21:06:18 | 2023-10-06 21:06:18 | 306.7 ms | 0MB | 100% |
| RESTART | Completed | 2023-10-06 21:06:18 | 2023-10-06 21:06:22 | 3.93 s | 0MB | 100% |
| RECOVERY | Completed | 2023-10-06 21:06:22 | 2023-10-06 21:06:23 | 668.15 ms | 0MB | 100% |
+-----------+-----------+---------------------+---------------------+------------+------------------+---------+
7 rows in set (0.00 sec)
参考网址：https://www.modb.pro/db/585505服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net
相关推荐: pytest + yaml 框架 -30.jsonpath 表达式提取接口返回结果
前言 pytest-yaml-yoyo 插件可以支持3种表达式提取接口返回结果。本篇详细介绍jsonpath 表达式提取json格式的返回结果。 jsonpath 表达式基础语法 相关语法可以参考下表 JSONPath 描述 $ 跟节点 @ 现行节点 . or…
