mysql怎么创建副本_使用clone plugin快速创建MySQL副本

(root@localhost:)[(none)]> INSTALL PLUGIN CLONE SONAME "mysql_clone.so";

Query OK, 0 rows affected (0.02 sec)

创建用户并授予相关权限

(root@localhost:)[(none)]>

(root@localhost:)[(none)]> CREATE USER dbayang IDENTIFIED BY "dbayang";

Query OK, 0 rows affected (0.02 sec)

(root@localhost:)[(none)]>

(root@localhost:)[(none)]> GRANT CLONE_ADMIN ON *.* to dbayang;

Query OK, 0 rows affected (0.01 sec)

(root@localhost:)[(none)]>

(root@localhost:)[(none)]> GRANT BACKUP_ADMIN ON *.* to dbayang;

Query OK, 0 rows affected (0.01 sec)

(root@localhost:)[(none)]> GRANT SELECT ON performance_schema.* TO dbayang;

Query OK, 0 rows affected (0.01 sec)

(root@localhost:)[(none)]> GRANT EXECUTE ON *.* to dbayang;

Query OK, 0 rows affected (0.00 sec)

step2：配置接收节点(Recipient)

安装支持特性的插件

(root@localhost:)[(none)]> INSTALL PLUGIN CLONE SONAME "mysql_clone.so";

Query OK, 0 rows affected (0.01 sec)

(root@localhost:)[(none)]> CREATE USER dbayang IDENTIFIED BY "dbayang";

Query OK, 0 rows affected (0.03 sec)

(root@localhost:)[(none)]> GRANT BACKUP_ADMIN ON *.* to dbayang;

Query OK, 0 rows affected (0.01 sec)

(root@localhost:)[(none)]>

(root@localhost:)[(none)]> GRANT SELECT ON performance_schema.* TO dbayang;

Query OK, 0 rows affected (0.00 sec)

(root@localhost:)[(none)]>

(root@localhost:)[(none)]> GRANT EXECUTE ON *.* to dbayang;

Query OK, 0 rows affected (0.01 sec)

配置donor节点地址列表：

(root@localhost:)[(none)]> SET GLOBAL clone_valid_donor_list = "127.0.0.1:3306";

Query OK, 0 rows affected (0.01 sec)

(root@localhost:)[(none)]> show variables like 'clone_valid_donor_list%';

+------------------------+----------------+

| Variable_name          | Value          |

+------------------------+----------------+

| clone_valid_donor_list | 127.0.0.1:3306 |

+------------------------+----------------+

1 row in set (0.01 sec)

step3创建数据克隆副本

使用clone 语句进行复制donor节点实例:

语法：

CLONE INSTANCE FROM USER@HOST:PORT

IDENTIFIED BY 'password'

[DATA DIRECTORY [=] 'clone_dir']

[REQUIRE [NO] SSL];

(root@localhost:)[(none)]> CLONE INSTANCE

->      FROM dbayang@127.0.0.1:3306

->      IDENTIFIED BY "dbayang";

Query OK, 0 rows affected (26.93 sec)

clone操作完成数据库自动重启。

使用以下语句检查clone进度等信息。

(root@localhost:)[(none)]> select STATE, CAST(BEGIN_TIME AS DATETIME) 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

-> from performance_schema.clone_status;

+-----------+---------------------+------------+

| STATE     | START TIME     | DURATION   |

+-----------+---------------------+------------+

| Completed | 2019-07-25 14:15:29 |  32.01 s |

+-----------+---------------------+------------+

1 row in set (0.02 sec)

(root@localhost:)[(none)]> 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   | Estima |    Done(%) |

+-----------+-----------+------------+------------+------------------+---------+

| DROP DATA | Completed | 14:15:29   |   340.77 ms |      0 MB |  100% |

| FILE COPY | Completed | 14:15:29   |    20.89 s |  2,623 MB |  100% |

| PAGE COPY | Completed | 14:15:50   |  401.78 ms |      0 MB |  100% |

| REDO COPY | Completed | 14:15:50   |  300.52 ms |      0 MB |  100% |

| FILE SYNC | Completed | 14:15:51   |    4.99 s |      0 MB |   100% |

| RESTART  | Completed | 14:15:56   |    4.05 s |       0 MB |   100% |

| RECOVERY | Completed | 14:16:00    |     1.03 s |      0 MB |   100% |

+-----------+-----------+------------+------------+------------------+---------+

7 rows in set (0.00 sec)

使用下面SQL语句查看任务总体信息。

(root@localhost:)[(none)]> select STATE, ERROR_NO, BINLOG_FILE, BINLOG_POSITION, GTID_EXECUTED,

-> CAST(BEGIN_TIME AS DATETIME) as "START TIME",

-> CAST(END_TIME AS DATETIME) as "FINISH TIME",

-> sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME)))

-> as DURATION

-> from performance_schema.clone_status \G

*************************** 1. row ***************************

STATE: Completed

ERROR_NO: 0

BINLOG_FILE: mysql-bin.000020

BINLOG_POSITION: 4119280

GTID_EXECUTED:

START TIME: 2019-07-25 14:15:29

FINISH TIME: 2019-07-25 14:16:01

DURATION: 32.01 s

1 row in set (0.00 sec)

--The end