MySQL8.0 - MySQL8.0 Clone 技术测试 - 《MySQL》

数据库版本
IP分配
用户及插件安装
创建主从

搭建数据库和老版本没有区别
参考文档
https://mp.weixin.qq.com/s/ELKV3ZnyYZmhWsJQtQ-AGQ
https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-remote.html

数据库版本

MySQL 8.0.17,且该功能是在该版本出现的

IP分配

IP	PORT	角色
172.30.1.156	3010	donor/master
172.30.1.156	3011	recipient/salve

用户及插件安装

mysql必须以mysqld_safe 方式启动

172.30.1.156:3310 donor 操作

创建有clone 权限用户,recipient 端会通过这个用户访问donor
mysql> CREATE USER 'clone_user'@'172.16.30.156' IDENTIFIED BY 'clone_user';
mysql> GRANT BACKUP_ADMIN on . to 'clone_user'@'172.16.30.156'
安装clone插件
mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
检查插件
mysql>  SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'clone';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| clone       | ACTIVE        |
+-------------+---------------+
1 row in set (0.01 sec)

查看donor状态

mysql> show master status;
+-----------------+----------+--------------+------------------+--------------------------------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+-----------------+----------+--------------+------------------+--------------------------------------------+
| mybinlog.000004 |     4595 |              |                  | ea1bbf9c-b9a8-11e9-a4bb-00505697ea24:1-877 |
+-----------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)

172.30.1.156:3311 recipient 操作

创建有clone 权限用户,recipient 端登录这个用户进行clone 操作
db-root用户
mysql> CREATE USER 'clone1'@'172.16.30.156' IDENTIFIED BY 'clone1';
mysql> GRANT BACKUP_ADMIN on *.* to 'clone1'@'172.16.30.156'
安装clone插件
mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
检查插件
mysql>  SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'clone';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| clone       | ACTIVE        |
+-------------+---------------+
1 row in set (0.01 sec)

在recipient 端配置克隆donor 列表

db-root用户
mysql>  SHOW VARIABLES LIKE 'clone_valid_donor_list';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| clone_valid_donor_list |       |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> set global clone_valid_donor_list = '172.30.1.156:3310';
Query OK, 0 rows affected (0.00 sec)
mysql>  SHOW VARIABLES LIKE 'clone_valid_donor_list';
+------------------------+-------------------+
| Variable_name          | Value             |
+------------------------+-------------------+
| clone_valid_donor_list | 172.30.1.156:3310 |
+------------------------+-------------------+
1 row in set (0.00 sec)

设置日志记录级别

下一步不是强制性的，但使用默认的 log_error_verbosity，错误日志不会显示有关克隆进度的大量信息。所以，对于这个例子，我会将详细程度调整到更高的级别（在供体和受体机上）：

mysql > set global log_error_verbosity=3;
Query OK, 0 rows affected (0.00 sec)

进行clone 操作

mysql >  CLONE INSTANCE FROM clone_user@'172.16.30.156':3310  identified by 'clone_user';

recipient 端error日志记录clone信息

2019-08-08T16:38:43.788142+08:00 9 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Tune stop, Data: 156 MiB/sec, Target: 238 MiB/sec..'
2019-08-08T16:38:48.188345+08:00 9 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Total Data: 3154 MiB @ 126 MiB/sec, Network: 3155 MiB @ 126 MiB/sec.'
2019-08-08T16:38:48.418657+08:00 9 [Note] [MY-013458] [InnoDB] Clone Apply State Change : Number of tasks = 8
2019-08-08T16:38:48.418730+08:00 9 [Note] [MY-013458] [InnoDB] Clone Apply State PAGE COPY:
2019-08-08T16:38:48.518919+08:00 9 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Total Data: 3154 MiB @ 124 MiB/sec, Network: 3155 MiB @ 125 MiB/sec.'
2019-08-08T16:38:48.737194+08:00 9 [Note] [MY-013458] [InnoDB] Clone Apply State Change : Number of tasks = 8
2019-08-08T16:38:48.737313+08:00 9 [Note] [MY-013458] [InnoDB] Clone Apply State REDO COPY:
2019-08-08T16:38:48.842594+08:00 9 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Total Data: 3154 MiB @ 123 MiB/sec, Network: 3155 MiB @ 123 MiB/sec.'
2019-08-08T16:38:49.028383+08:00 9 [Note] [MY-013458] [InnoDB] Clone Apply State Change : Number of tasks = 8
2019-08-08T16:38:49.028461+08:00 9 [Note] [MY-013458] [InnoDB] Clone Apply State FLUSH DATA:
2019-08-08T16:38:49.035714+08:00 9 [Note] [MY-013458] [InnoDB] Clone Apply State FLUSH REDO:
2019-08-08T16:38:49.036601+08:00 9 [Note] [MY-012651] [InnoDB] Progress in MB:
100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
2019-08-08T16:38:53.199794+08:00 9 [Note] [MY-012651] [InnoDB] Progress in MB:
100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
2019-08-08T16:38:59.059703+08:00 9 [Note] [MY-013458] [InnoDB] Clone Apply State DONE

注意在clone 完成后recipient数据库会自动关闭

重启recipient 数据库

查看clone 的位置

mysql>  SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;.
+-----------------+-----------------+
| BINLOG_FILE     | BINLOG_POSITION |
+-----------------+-----------------+
| mybinlog.000004 |            1647 |
+-----------------+-----------------+
1 row in set (0.00 sec)
mysql> SELECT @@GLOBAL.GTID_EXECUTED;(该gtid为donor 端gtid 且相同证明clone已完成)
+--------------------------------------------+
| @@GLOBAL.GTID_EXECUTED                      |
+--------------------------------------------+
| ea1bbf9c-b9a8-11e9-a4bb-00505697ea24:1-877 |
+--------------------------------------------+

创建主从

master 3010 创建复制用户

mysql> create user 'repl_user'@'172.30.1.156' identified by 'repl_user';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on . to  'repl_user'@'172.30.1.156';

slave 3011

change master to
master_host='172.30.1.156',
master_port=3310,
master_user='repl_user',
master_password='repl_user',
master_auto_position=1

复制报错

如果按照上面slave的操作将报错

Last_IO_Error: error connecting to master 'repl_user@172.30.1.156:3310' -
retry-time: 60 retries: 1 message:
Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

MySQL8.0默认指定使用需要SSL的身份验证插件caching_sha2_password，而我们在创建同步复制账号时候没有指定REQUIRE SSL。
为了降低这件事情的复杂性，我们选择了社区的解决方法，选择绕过SSL插件的验证，改为mysql_native_password验证来做同步复制。

解决办法

1 第一种修改用户的加密方式

master

mysql> create user 'rpl2'@'172.30.1.156' IDENTIFIED WITH mysql_native_password by 'pl2!123';
mysql> grant replication slave on *.* to  'rpl2'@'172.30.1.156';

slave

change master to
master_host='172.30.1.156',
master_port=3310,
master_user='rpl2',
master_password='rpl2!123',
master_auto_position=1

2第二种:在slave 创建复制的时候增加加密选项

master

mysql> create user 'repl_user'@'172.30.1.156' identified by 'repl_user';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on . to  'repl_user'@'172.30.1.156;

‘
slave

change master to
master_host='172.30.1.156',
master_port=3310,
master_user='repl_user',
master_password='repl_user',
MASTER_SSL=1,
master_auto_position=1

mysql>start slave;