mysql_clone
- How to Install 安装
Using the INSTALL PLUGIN statement:
Unix/Linux:
mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Windows:
mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.dll';
Using the plugin-load option in the MySQL configuration file:
Unix/Linux:
[mysqld]
plugin-load = mysql_clone.so
Windows:
[mysqld]
plugin-load = mysql_clone.dll
Using the plugin-load-add option in the MySQL configuration file:
Unix/Linux:
[mysqld]
plugin-load-add = mysql_clone.so
Windows:
[mysqld]
plugin-load-add = mysql_clone.dll
- How to Verify 安装校验
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)
- How to Create a Local Backup of the Data Directory 新增逻辑备份的目录
mysql> CLONE LOCAL DATA DIRECTORY '/mysql/backup/clone';
Query OK, 0 rows affected (1 min 4.49 sec)
- How to Create a Replica 建立复制关系
4.1 install PLUGIN 安装插件
4.2 On the recipient, set the clone_valid_donor_list option to include the donor for the cloning operation
mysql> SET GLOBAL clone_valid_donor_list = "10.57.19.100:3306";
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER clone_user IDENTIFIED BY "clone_password";
mysql> GRANT CLONE_ADMIN ON *.* to clone_user;
4.3 On the donor, ensure there is a user that is allowed to connect from the recipient host with the BACKUP_ADMIN privilege
mysql> CREATE USER clone_user@'%' IDENTIFIED BY '<password>';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT BACKUP_ADMIN on *.* TO clone_user@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT SELECT ON performance_schema.* TO clone_user;
mysql> GRANT EXECUTE ON *.* to clone_user;
4.4 Start the clone operation: 开始复制
mysql> CLONE INSTANCE FROM clone_user@10.57.19.100:3306 IDENTIFIED BY "clone_password";
Query OK, 0 rows affected (1 min 7.34 sec)
4.5 BASE on GTID 基于GTID
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='<source host of replication>', SOURCE_PORT=<port on source of replication>, SOURCE_SSL=1, SOURCE_AUTO_POSITION=1;
Query OK, 0 rows affected (0.04 sec)
mysql> START REPLICA USER='<replication user>' PASSWORD='<replication password>';
Query OK, 0 rows affected (0.03 sec)
4.6 BASE on Position 基于binlog 位置
mysql> SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;
+---------------+-----------------+
| BINLOG_FILE | BINLOG_POSITION |
+---------------+-----------------+
| binlog.000004 | 761 |
+---------------+-----------------+
1 row in set (0.00 sec)
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='<source host of the clone>', SOURCE_PORT=3306, SOURCE_SSL=1, SOURCE_LOG_FILE='binlog.000004', SOURCE_LOG_POS=761;
Query OK, 0 rows affected (0.04 sec)
mysql> START REPLICA USER='<replication user>' PASSWORD='<replication password>';
Query OK, 0 rows affected (0.03 sec)
4.7 BASE on MGR 基于MGR
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (5.65 sec)
- How to Monitor 如何监控
5.1 Clone Tables 表级别的克隆
mysql> SELECT * FROM clone_status\G
*************************** 1. row ***************************
ID: 1
PID: 24
STATE: In Progress
BEGIN_TIME: 2019-07-11 13:05:20.364
END_TIME: NULL
SOURCE: LOCAL INSTANCE
DESTINATION: /mysql/backup/clone/
ERROR_NO: 0
ERROR_MESSAGE:
BINLOG_FILE:
BINLOG_POSITION: 0
GTID_EXECUTED:
1 row in set (0.00 sec)
mysql> SELECT * FROM clone_progress;
+------+-----------+-------------+----------------------------+----------------------------+---------+------------+------------+---------+------------+---------------+
| ID | STAGE | STATE | BEGIN_TIME | END_TIME | THREADS | ESTIMATE | DATA | NETWORK | DATA_SPEED | NETWORK_SPEED |
+------+-----------+-------------+----------------------------+----------------------------+---------+------------+------------+---------+------------+---------------+
| 1 | DROP DATA | Completed | 2019-07-11 13:05:20.364075 | 2019-07-11 13:05:20.364859 | 1 | 0 | 0 | 0 | 0 | 0 |
| 1 | FILE COPY | In Progress | 2019-07-11 13:05:20.364962 | NULL | 1 | 2759120174 | 1696830766 | 0 | 414269680 | 0 |
| 1 | PAGE COPY | Not Started | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | REDO COPY | Not Started | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | FILE SYNC | Not Started | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | RESTART | Not Started | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | RECOVERY | Not Started | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 |
+------+-----------+-------------+----------------------------+----------------------------+---------+------------+------------+---------+------------+---------------+
7 rows in set (0.00 sec)
mysql> select STAGE, STATE, CAST(BEGIN_TIME AS TIME) as "START TIME",
CASE WHEN END_TIME IS NULL THEN
LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ')
ELSE
LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ')
END 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(CAST(ROUND(DATA*100/ESTIMATE, 0) AS BINARY), "%"), 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 | DURATION | Estimate | Done(%) |
+-----------+-------------+------------+------------+-----------+---------+
| DROP DATA | Completed | 17:23:26 | 790.86 ms | 0 MB | 100% |
| FILE COPY | In Progress | 17:23:27 | 4.85 m | 94,729 MB | 47% |
| PAGE COPY | Not Started | NULL | NULL | 0 MB | 0% |
| REDO COPY | Not Started | NULL | NULL | 0 MB | 0% |
| FILE SYNC | Not Started | NULL | NULL | 0 MB | 0% |
| RESTART | Not Started | NULL | NULL | 0 MB | 0% |
| RECOVERY | Not Started | NULL | NULL | 0 MB | 0% |
+-----------+-------------+------------+------------+-----------+---------+
mysql> 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
from performance_schema.clone_progress;
select STAGE, STATE, CAST(BEGIN_TIME AS TIME) as "START TIME",
CAST(END_TIME AS TIME) as "FINISH TIME",
LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ')
as DURATION
from performance_schema.clone_progress;
+-----------+-----------+------------+-------------+------------+
| STAGE | STATE | START TIME | FINISH TIME | DURATION |
+-----------+-----------+------------+-------------+------------+
| DROP DATA | Completed | 17:23:26 | 17:23:27 | 790.86 ms |
| FILE COPY | Completed | 17:23:27 | 17:33:47 | 10.33 m |
| PAGE COPY | Completed | 17:33:47 | 17:34:03 | 15.91 s |
| REDO COPY | Completed | 17:34:03 | 17:34:04 | 1.07 s |
| FILE SYNC | Completed | 17:34:04 | 17:35:32 | 1.46 m |
| RESTART | Completed | 17:35:32 | 17:35:40 | 7.77 s |
| RECOVERY | Completed | 17:35:40 | 17:40:27 | 4.79 m |
+-----------+-----------+------------+-------------+------------+
5.2 Memory Usage 内存使用
mysql> SELECT * FROM memory_summary_global_by_event_name WHERE EVENT_NAME IN ('memory/innodb/clone', 'memory/clone/data')\G
*************************** 1. row ***************************
EVENT_NAME: memory/clone/data
COUNT_ALLOC: 8
COUNT_FREE: 8
SUM_NUMBER_OF_BYTES_ALLOC: 33587200
SUM_NUMBER_OF_BYTES_FREE: 33587200
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 0
HIGH_COUNT_USED: 1
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
HIGH_NUMBER_OF_BYTES_USED: 4198400
*************************** 2. row ***************************
EVENT_NAME: memory/innodb/clone
COUNT_ALLOC: 93
COUNT_FREE: 88
SUM_NUMBER_OF_BYTES_ALLOC: 470920
SUM_NUMBER_OF_BYTES_FREE: 450208
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 5
HIGH_COUNT_USED: 5
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 20712
HIGH_NUMBER_OF_BYTES_USED: 20712
2 rows in set (0.01 sec)
mysql> SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name IN ('memory/innodb/clone', 'memory/clone/data');
+---------------------+---------------+---------------+-------------------+------------+------------+----------------+
| event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+---------------------+---------------+---------------+-------------------+------------+------------+----------------+
| memory/innodb/clone | 5 | 20.23 KiB | 4.05 KiB | 5 | 20.23 KiB | 4.05 KiB |
+---------------------+---------------+---------------+-------------------+------------+------------+----------------+
1 row in set (0.00 sec)
