MySQL使用Clone特性搭建主从复制
文档课题：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_4Umysql: [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 itsaffiliates. Other names may be
trademarks of their respectiveowners.
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 mysqlroot 12860
12681 0 20:14 pts/1 00:00:00 su - mysqlmysql 12861
12860 0 20:14 pts/1 00:00:00 -bashmysql 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.cnfmysql 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=3306root 13445
13362 0 20:29 pts/2 00:00:00 su - mysqlmysql 13446
13445 0 20:29 pts/2 00:00:00 -bashmysql 13595
12861 0 20:39 pts/1 00:00:00 mysql -uclone_user -px xxxxxxmysql 13642
13446 0 20:42 pts/2 00:00:00 ps -efmysql 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 datatotal 184264
drwxr-x---. 2 mysql mysql 23 Oct
6 20:41 booksDBdrwxr-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_logfile1drwxr-x---. 2 mysql mysql 6 Oct
6 20:41 mysql-rw-r-----. 1 mysql mysql
25165824 Oct 6 20:41 mysql.ibddrwxr-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.1Enter 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 itsaffiliates. Other names may be
trademarks of their respectiveowners.
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 namesYou 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_4Umysql: [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 itsaffiliates. Other names may be
trademarks of their respectiveowners.
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 queryNo connection. Trying to
reconnect...Connection id: 8
Current database: *** NONE ***
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)
(clone_user@Slave01)
[(none)> exitBye
[mysql@leo-827mgr-slave01 ~]$
mysql -uroot -pEnter 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 itsaffiliates. Other names may be
trademarks of their respectiveowners.
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 namesYou 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 eventMaster_Host: 192.168.133.126
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 699
Relay_Log_File:
leo-827mgr-slave01-relay-bin.000002Relay_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:
NoLast_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-000c29a63753Master_Info_File:
mysql.slave_master_infoSQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read
all relay log; waiting for more updatesMaster_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
71c3e81f-6446-11ee-a822-000c29a63753:1-2Executed_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
