For MySQL 8.0, we should use the clone plugin to clone InnoDB tables to another server without blocking. While cloning is happening, the source server can operate normally (nothing is blocked except DDL), with a bit higher load due to data copying/streaming/compressing (depending on the clone command).
Consider the following 2-node MySQL 8.0 Replication setup:
- 192.168.10.11 – Master
- 192.168.10.12 – Slave
Note: Make sure your tables are all running on InnoDB storage engine beforehand!
1) Create the clone user called “cloner”, and allow them to access from all database nodes (both master & slave). On the master, do:
CREATE USER `cloner`@`192.168.10.11` IDENTIFIED BY 'cl0n3rP455';
GRANT backup_admin, clone_admin ON *.* TO `cloner`@`192.168.10.11`;
CREATE USER `cloner`@`192.168.10.12` IDENTIFIED BY 'cl0n3rP455';
GRANT backup_admin, clone_admin ON *.* TO `cloner`@`192.168.10.12`;2) Create replication user called “replication” if does not exist. On the master, do:
CREATE USER `replication`@`192.168.10.11` IDENTIFIED BY 'r3plP455';
GRANT REPLIATION SLAVE ON *.* TO `replication`@`192.168.10.11`;
CREATE USER `replication`@`192.168.10.12` IDENTIFIED BY 'r3plP455';
GRANT REPLICATION SLAVE ON *.* TO `replication`@`192.168.10.12`;3) On the slave, super_read_only and read_only are commonly set to ON. As root, you have to disable super_read_only momentarily to allow yourself to make changes to the server:
SET GLOBAL super_read_only = 0;4) Add all database nodes into clone_valid_donor_list:
SET GLOBAL clone_valid_donor_list='192.168.10.11:3306';
SET GLOBAL clone_valid_donor_list='192.168.10.12:3306';5) We are now ready to sync from the master via the clone plugin. This makes it very easy to rebuild a broken slave. On the slave, run the following statements:
STOP SLAVE;
CLONE INSTANCE FROM cloner@192.168.10.11:3306 IDENTIFIED BY 'cl0n3rP455';
exit;6) After the cloning process completes, we must exit from the current session to load the new data from the master. Then re-establish the replication link to the master using GTID:
CHANGE MASTER TO MASTER_HOST = '192.168.10.11', MASTER_USER = 'replication', MASTER_PASSWORD='r3plP455', MASTER_AUTO_POSITION = 1;
START SLAVE;7) Verify with:
SHOW SLAVE STATUS\G** Ensure you see “Slave_IO_running: Yes” and “Slave_SQL_running: Yes“.
8) Turn off event_scheduler on the slave and set read_only and super_read_only to ON to protect the slave against accidental writes. On slave, do:
SET GLOBAL event_scheduler = OFF;
SET GLOBAL super_read_only = ON;
SET GLOBAL read_only = ON;At this point, the slave should be replicating from the master correctly. Good luck!
