mysql+clone_一篇搞懂MySQL 8.0 Clone技术在线搭建主从复制全过程

recipient_user@192.168.3.134:(none) 03:39:46 >clone instance from ‘donor_user’@‘192.168.3.133’:3307 identified by ‘password’;Query OK, 0 rows affected (2.34 sec)recipient_user@192.168.3.134:(none) 03:39:57 >Restarting mysqld…2020-03-12T07:40:01.285267Z mysqld_safe Number of processes running now: 02020-03-12T07:40:01.290169Z mysqld_safe mysqld restarted

至此，远程数据的克隆已经完成了。

通过查询两张表来监控一下克隆的进度和结果状态：

–查看clone进度和状态

root@localhost :(none) 03:34:49>SELECT * FROM performance_schema.clone_progress;±-----±----------±----------±---------------------------±---------------------------±--------±----------±----------±----------±-----------±--------------+| ID |STAGE | STATE |BEGIN_TIME | END_TIME |THREADS | ESTIMATE |DATA | NETWORK |DATA_SPEED | NETWORK_SPEED |±-----±----------±----------±---------------------------±---------------------------±--------±----------±----------±----------±-----------±--------------+| 1 |DROP DATA | Completed |2020- 03- 1215:29:15.385694| 2020-03-12 15:29:15.634609 |1| 0 |0| 0 |0| 0 || 1 |FILE COPY | Completed |2020- 03- 1215:29:15.634765| 2020-03-12 15:29:17.452961 |1| 465800520 |465800520| 465833356 |0| 0 || 1 |PAGE COPY | Completed |2020- 03- 1215:29:17.453144| 2020-03-12 15:29:17.554224 |1| 0 |0| 99 |0| 0 || 1 |REDO COPY | Completed |2020- 03- 1215:29:17.554413| 2020-03-12 15:29:17.654430 |1| 2560 |2560| 3031 |0| 0 || 1 |FILE SYNC | Completed |2020- 03- 1215:29:17.654596| 2020-03-12 15:29:17.730172 |1| 0 |0| 0 |0| 0 || 1 |RESTART | Completed |2020- 03- 1215:29:17.730172| 2020-03-12 15:29:22.160372 |0| 0 |0| 0 |0| 0 || 1 |RECOVERY | Completed |2020- 03- 1215:29:22.160372| 2020-03-12 15:29:22.478889 |0| 0 |0| 0 |0| 0 |±-----±----------±----------±---------------------------±---------------------------±--------±----------±----------±----------±-----------±--------------+7rows inset ( 0. 00sec)root@localhost :(none) 03:34:52>SELECT * FROM performance_schema.clone_statusG*************************** 1. row ***************************ID:1PID:0STATE:CompletedBEGIN_TIME:2020- 03- 1215:29:15.385END_TIME:2020- 03- 1215:29:22.479SOURCE:192.168. 3.133:3307DESTINATION:LOCAL INSTANCEERROR_NO:0ERROR_MESSAGE:BINLOG_FILE:mysql-bin. 000002BINLOG_POSITION:421GTID_EXECUTED:3e75bf2f- 6401- 11ea- 8995- 000c29db65a6:11row inset ( 0. 00sec)

–在主库133上建立复制账号：

root@localhost:(none) 04: 12: 23>create user repl@‘ 192.168.3.%’ identified by‘repl’;Query OK, 0rows affected ( 0.00sec)root@localhost:(none) 04: 13: 03>grant all on . to repl@‘ 192.168.3.%’;Query OK, 0rows affected ( 0.00sec)

–在从库134上进行复制步骤的完成：

root@localhost:(none) 04:16:09 >change master to master_host=‘192.168.3.133’,master_port=3307,master_user=‘repl’,master_password=‘repl’,master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.00 sec)root@localhost:(none) 04:17:32 >start slave;Query OK, 0 rows affected (0.00 sec)root@localhost:(none) 04:17:35 >show slave statusG*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.3.133Master_User: replMaster_Port: 3307Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 1193Relay_Log_File: mgr3-relay-bin.000002Relay_Log_Pos: 904Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 1193Relay_Log_Space: 1103Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 207Master_UUID: 3e75bf2f-6401-11ea-8995-000c29db65a6Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set: 3e75bf2f-6401-11ea-8995-000c29db65a6:3-4Executed_Gtid_Set: 3e75bf2f-6401-11ea-8995-000c29db65a6:1-4Auto_Position: 1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:Master_public_key_path:Get_master_public_key: 0Network_Namespace:1 row in set (0.00 sec)

至此远程从库通过clone插件的方式搭建成功了，非常简单也非常快速，不需要mysqldump也不需要xtrabackup，在线搭建成功，非常快，以后用8.0可以考虑这种便捷的方式了。

最后将clone技术的 限制条件列出：返回搜狐，查看更多

版本大于等于8.0.17且不支持跨版本

两台机器具有相同的操作系统OS

两台MySQL实例具体相同的 innodb_page_size 和 innodb_data_file_path(ibdata文件名)

同一时刻仅仅允许有一个克隆任务存在

recipient 需要设置变量clone_valid_donor_list

max_allowed_packet 大于2M

doner的undo表空间文件名称不能重复

不会克隆my.cnf文件

不会克隆binlog

仅仅支持innodb引擎