Clone plugin搭建MySQL8.0 主从复制
前面2篇分别利用逻辑备份mysqldump和屋里备份xtrabackup搭建了MySQL主从复制，分别适用不同的场景，逻辑备份适用数据量小点的，物理备份适用数据量大点的。那么类比oracle，如果搭建的时候，没有足够的本地空间来落地，有没有RMAN duplicate from active database ， 不落地直接通过网络传输的方案，答案是有的。
MySQL 8.0.17 引入的一个重大特性引入的Clone Plugin是一个重大特性，它允许从本地或者远程的MySQL中克隆数据。克隆的数据包括schema、表、表空间、元数据等等，是一个完整的数据目录。插件可以使用克隆的目录配置和恢复一个MySQL Server。需要强调的是，Clone Plugin 只备份 InnoDB 存储引擎表，其他类型表不进行备份。
克隆支持2种方式 本地克隆和远程克隆，本次采用远程克隆的方式来进行搭建。
1、此方式强依赖于网络，网络传输速率限制本方案的执行。
2、需要提前初始化一个从库实例
3、从库必须systemctl或者mysqld_safe方式启动（从库的clone_admin权限要能把数据库可以restart）。
本次搭建的环境信息如下：
OS版本:Red Hat Enterprise Linux Server release 7.6 (Maipo)
MySQL版本：Server version: 8.0.37
安装clone plugin
MySQL 软件有个插件目录，专门存放各种自带但没有安装，或者来自第三方的插件，这个目录正式系统变量plugin_dir所指定的位置。对于插件来说，我们在安装MySQL软件的时候，相关文件默认就已经安装到系统里了，只是没有应用。在MySQL环境中应用插件的操作，应该叫加载更合适，
mysql> show variables like '%plugin%';
+-----------------------------------------------+------------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------------+------------------------------------------------------+
| default_authentication_plugin | caching_sha2_password |
| plugin_dir | /opt/mysql-8.0.37-linux-glibc2.17-x86_64/lib/plugin/ |
| replication_optimize_for_static_plugin_config | ON |
+-----------------------------------------------+------------------------------------------------------+
3 rows in set (0.03 sec)
[root@postgre ~]# cd /opt/mysql-8.0.37-linux-glibc2.17-x86_64/lib/plugin/
[root@postgre plugin]# ll
total 35844
-rwxr-xr-x. 1 mysql mysql 39904 Mar 28 04:45 adt_null.so
-rwxr-xr-x. 1 mysql mysql 55104 Mar 28 04:45 authentication_fido_client.so
-rwxr-xr-x. 1 mysql mysql 7215856 Mar 28 04:47 authentication_kerberos_client.so
-rwxr-xr-x. 1 mysql mysql 7066816 Mar 28 04:45 authentication_ldap_sasl_client.so
-rwxr-xr-x. 1 mysql mysql 7303512 Mar 28 04:45 authentication_oci_client.so
-rwxr-xr-x. 1 mysql mysql 18024 Mar 28 04:45 auth_socket.so
-rwxr-xr-x. 1 mysql mysql 41752 Mar 28 04:45 component_audit_api_message_emit.so
-rwxr-xr-x. 1 mysql mysql 2540688 Mar 28 04:48 component_keyring_file.so
-rwxr-xr-x. 1 mysql mysql 38520 Mar 28 04:45 component_log_filter_dragnet.so
-rwxr-xr-x. 1 mysql mysql 64128 Mar 28 04:45 component_log_sink_json.so
-rwxr-xr-x. 1 mysql mysql 27456 Mar 28 04:45 component_log_sink_syseventlog.so
-rwxr-xr-x. 1 mysql mysql 72456 Mar 28 04:45 component_mysqlbackup.so
-rwxr-xr-x. 1 mysql mysql 19768 Mar 28 04:45 component_query_attributes.so
-rwxr-xr-x. 1 mysql mysql 95736 Mar 28 04:45 component_reference_cache.so
-rwxr-xr-x. 1 mysql mysql 196016 Mar 28 04:45 component_validate_password.so
-rwxr-xr-x. 1 mysql mysql 100592 Mar 28 04:46 connection_control.so
-rwxr-xr-x. 1 mysql mysql 452856 Mar 28 04:46 ddl_rewriter.so
drwxr-xr-x. 1 mysql mysql 1280 Mar 28 05:08 debug
-rwxr-xr-x. 1 mysql mysql 5776192 Mar 28 04:50 group_replication.so
-rwxr-xr-x. 1 mysql mysql 73800 Mar 28 04:45 ha_example.so
-rwxr-xr-x. 1 mysql mysql 103104 Mar 28 04:45 ha_mock.so
-rwxr-xr-x. 1 mysql mysql 152616 Mar 28 04:46 innodb_engine.so
-rwxr-xr-x. 1 mysql mysql 340376 Mar 28 04:46 keyring_file.so
-rwxr-xr-x. 1 mysql mysql 35072 Mar 28 04:46 keyring_udf.so
-rwxr-xr-x. 1 mysql mysql 350384 Mar 28 04:46 libmemcached.so
-rwxr-xr-x. 1 mysql mysql 3007688 Mar 28 04:45 libpluginmecab.so
-rwxr-xr-x. 1 mysql mysql 17632 Mar 28 04:46 locking_service.so
-rwxr-xr-x. 1 mysql mysql 18720 Mar 28 04:45 mypluglib.so
-rwxr-xr-x. 1 mysql mysql 428392 Mar 28 04:46 mysql_clone.so
-rwxr-xr-x. 1 mysql mysql 17448 Mar 28 04:45 mysql_no_login.so
-rwxr-xr-x. 1 mysql mysql 18208 Mar 28 04:46 rewrite_example.so
-rwxr-xr-x. 1 mysql mysql 247416 Mar 28 04:46 rewriter.so
-rwxr-xr-x. 1 mysql mysql 154992 Mar 28 04:46 semisync_master.so
-rwxr-xr-x. 1 mysql mysql 57264 Mar 28 04:46 semisync_replica.so
-rwxr-xr-x. 1 mysql mysql 57672 Mar 28 04:46 semisync_slave.so
-rwxr-xr-x. 1 mysql mysql 154616 Mar 28 04:46 semisync_source.so
-rwxr-xr-x. 1 mysql mysql 164760 Mar 28 04:46 validate_password.so
-rwxr-xr-x. 1 mysql mysql 95968 Mar 28 04:46 version_token.so
我们在插件目录下，可以找到clone相关插件
[root@postgre plugin]# ll *clone*
-rwxr-xr-x. 1 mysql mysql 428392 Mar 28 04:46 mysql_clone.so
动态加载：
要验证插件安装，请检查 INFORMATION_SCHEMA.PLUGINS表或使用SHOW PLUGINS语句。
show plugins;
或者
select plugin_name,plugin_status from information_Schema.plugins where plugin_name=‘clone’;
mysql> select plugin_name,plugin_status from information_Schema.plugins where plugin_name='clone';
Empty set (0.01 sec)
--没查出来，表示clone插件没加载
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> show plugins;
+----------------------------------+----------+--------------------+----------------+---------+
| Name | Status | Type | Library | License |
+----------------------------------+----------+--------------------+----------------+---------+
...........
| clone | ACTIVE | CLONE | mysql_clone.so | GPL |
+----------------------------------+----------+--------------------+----------------+---------+
49 rows in set (0.00 sec)
STATUS 状态显示为 ACTIVE 代表插件加载成功。
[root@postgre data]# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[1] 10447
[root@postgre data]# ps -ef|grep mysql
mysql 10447 9220 6 17:29 pts/1 00:00:04 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
root 10491 9220 0 17:30 pts/1 00:00:00 grep --color=auto mysql
创建3308实例
mkdir -p /data/mysql/mysql3308/{data,logs,tmp}
cp /data/mysql/mysql3306/my3306.cnf /data/mysql/mysql3308/my3308.cnf
chown -R mysql:mysql /data/mysql/mysql3308/
sed -i ‘s/3306/3308/g’ /data/mysql/mysql3308/my3308.cnf
注意：这里一定要用mysqld_safe启动，后面再说原因。
一定要检查如下参数：
GTID要开，server_id要不一样，binlog要开，而且格式要为row。
3306实例配置
加载plugin插件
创建clone用户并授权
创建复制专用账号并授权
主要语句如下：
install plugin clone soname ‘mysql_clone.so’;
create user ‘zhuo’@‘%’ identified by ‘zhuo’;
grant backup_admin on . to zhuo;
create user repl@‘%’ identified by ‘repl’;
grant replication slave on . to repl@‘%’;
3308实例配置
加载plugins插件
创建clone restart用户并授权
设置远程复制白名单。
install plugin clone soname ‘mysql_clone.so’;
create user ‘zhuo10’@‘%’ identified by ‘zhuo10’;
grant clone_admin on . to zhuo10;
set global clone_valid_donor_list=‘192.168.33.28:3306’;
set global log_error_verbosity=3;
clone instance from ‘zhuo’@192.168.33.28:3306 identified by ‘zhuo’;
--创建此用户，是为了clone的时候restart实例。
mysql> create user 'clone10'@'%' identified by 'clone10';
Query OK, 0 rows affected (0.02 sec)
mysql> grant clone_admin on *.* to clone10;
Query OK, 0 rows affected (0.01 sec)
mysql> set global clone_valid_donor_list='10.1.11.30:3306';
Query OK, 0 rows affected (0.00 sec)
mysql> set global log_error_verbosity=3;
Query OK, 0 rows affected (0.02 sec)
mysql> clone instance from 'clone'@10.1.11.30:3306 identified by 'clone';
ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).
--此处clone用户为3306实例里面创建的clone专用用户，为了克隆实例。
--此处ERROR 3707 (HY000): ，就是因为不能restart，3308实例必须以systemctl或者msyqld_safe的方式启动，所以报错。
以mysqld_safe方式启动，重新clone
[root@postgre plugin]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql3308/my3308.cnf &
mysql> clone instance from 'clone'@10.1.11.30:3306 identified by 'clone';
ERROR 3869 (HY000): Clone system configuration: 10.1.11.30:3306 is not found in clone_valid_donor_list:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
--其实此处已经clone完成了，test数据库已经都过来了，单为了保险起见，重新克隆一遍。（如果数据库较大，此处也不用重新clone了）
mysql> set global clone_valid_donor_list='10.1.11.30:3306';
Query OK, 0 rows affected (0.00 sec)
mysql> set global log_error_verbosity=3;
Query OK, 0 rows affected (0.00 sec)
mysql> clone instance from 'clone'@10.1.11.30:3306 identified by 'clone';
Query OK, 0 rows affected (0.74 sec)
mysql> Restarting mysqld...
2024-08-09T02:32:39.104425Z mysqld_safe Number of processes running now: 0
2024-08-09T02:32:39.109015Z mysqld_safe mysqld restarted
配置为从库
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------------------------------+
| mybinlog.000002 | 157 | | | f25d3a39-53a2-11ef-9508-000c29a318a4:1-12 |
+-----------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
--查看从库的GTID信息，已经执行到了主库的日志位置，所以可以直接auto自动定位。
mysql> change master to master_host='10.1.11.30',master_port=3306,master_user='repl',master_password='repl',master_auto_position=1,GET_MASTER_PUBLIC_KEY=1;
Query OK, 0 rows affected, 9 warnings (0.05 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.1.11.30
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: postgre-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
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: 0
Relay_Log_Space: 157
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: NULL
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: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: f25d3a39-53a2-11ef-9508-000c29a318a4:1-12
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.19 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.1.11.30
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mybinlog.000008
Read_Master_Log_Pos: 1658
Relay_Log_File: postgre-relay-bin.000002
Relay_Log_Pos: 417
Relay_Master_Log_File: mybinlog.000008
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: 1658
Relay_Log_Space: 629
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: 3306
Master_UUID: f25d3a39-53a2-11ef-9508-000c29a318a4
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:
Executed_Gtid_Set: f25d3a39-53a2-11ef-9508-000c29a318a4:1-12
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
Clone Plugin 的限制
Clone Plugin 虽好，但也有一些限制。
进行远程克隆的两个 MySQL 实例版本号必须完全一样。5.7 和 8.0 显然不行，8.0.19 和 8.0.20 小版本不同也不行。
MySQL 8.0.27 版本前不允许 DDL 操作，DDL 操作会被阻塞，甚至影响后续的 SELECT查询操作；MySQL 8.0.27 版本已经可以在备份过程中进行并发的 DDL 操作。
Clone Plugin 不备份 MySQL 配置文件，而 Xtrabackup 是备份的。
整个过程如下：
3306实例：
加载plugin插件
创建clone用户并授权
创建复制专用账号并授权
mysql> install plugin clone soname 'mysql_clone.so';
mysql> show plugins;
mysql> create user 'clone'@'%' identified by 'clone';
mysql> grant backup_admin on *.* to clone;
mysql> create user repl@'%' identified by 'repl';
mysql> grant replication slave on *.* to repl@'%';
3308实例：
加载plugins插件
创建clone restart用户并授权
设置远程复制白名单。
mysql> install plugin clone soname 'mysql_clone.so';
mysql> create user 'clone10'@'%' identified by 'clone10';
mysql> grant clone_admin on *.* to clone10;
mysql> set global clone_valid_donor_list='10.1.11.30:3306';
mysql> set global log_error_verbosity=3;
mysql> clone instance from 'clone'@10.1.11.30:3306 identified by 'clone';
mysql> change master to master_host='10.1.11.30', master_port=3306, master_user='repl', master_password='repl',master_auto_position=1 GET_MASTER_PUBLIC_KEY=1;
扫一扫
539
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
