test MySQL 8.0.17 CLONE

clone.sh

#!/bin/bash
set -x
dbdeployer deploy single 5.7.26 --master --sandbox-directory=master_5_7_26 --port=18000 --db-password=different --db-user=different
dbdeployer deploy single 8.0.17 --master --sandbox-directory=master_8_0_17 --port=18001 --db-password=different --db-user=different
dbdeployer deploy single 8.0.17 --master --sandbox-directory=slave_8_0_17  --port=18002

~/sandboxes/master_5_7_26/use -t < fill.sql

du -sh ~/sandboxes/*/data

# Change to the directory containing test_db (https://github.com/datacharmer/test_db)
# cd ~/workdir/git/test_db
# load the test db into the master
# ~/sandboxes/master_5_7_26/use < employees.sql

dbdeployer admin upgrade master_5_7_26 master_8_0_17

if [ "$?" != "0" ] ; then exit 1; fi

~/sandboxes/master_8_0_17/use -e 'set persist general_log=1'
~/sandboxes/master_8_0_17/use -e "install plugin clone soname 'mysql_clone.so'"

# clone the receiver
~/sandboxes/slave_8_0_17/use -e 'set persist general_log=1'
~/sandboxes/slave_8_0_17/use -e "install plugin clone soname 'mysql_clone.so'"
~/sandboxes/slave_8_0_17/use -e "set persist clone_valid_donor_list='127.0.0.1:18001'"
~/sandboxes/slave_8_0_17/use -e "CLONE INSTANCE FROM different@127.0.0.1:18001 IDENTIFIED BY 'different'"

du -sh ~/sandboxes/*/data

# if all has gone well, the slave will have all the data from master, and also
# its users have changed. It is now accessible only from user 'different', not from 'msandbox'

fill.sql

use test;
drop table if exists t1, t2;

create table t1 (id int not null auto_increment primary key , msg varchar(30));
create table t2 (id int not null auto_increment primary key , msg varchar(30));

drop procedure if exists show_tables;

set @start_time = now();

delimiter //

create procedure show_tables() deterministic reads sql data
begin
    select "t1" as table_name, format(count(*), 0) as row_num from t1
    union
    select "t2" as table_name, format(count(*), 0) as row_num from t2
    union
    select "--------", "--------"
    union
    select "elapsed", timediff(now(), @start_time);
end //

delimiter ;

insert into t1 values (null, rand()), (null, rand()), (null, rand());
insert into t1 select null, rand() from t1;
insert into t1 select null, rand() from t1;

insert into t2 values (null, rand());
insert into t2 select null, rand() from t2;
insert into t2 select null, rand() from t2;
insert into t2 select null, rand() from t2;
insert into t2 select null, rand() from t2;
insert into t1 select null, rand() from t1 join t2;
insert into t1 select null, rand() from t1 join t2;
insert into t1 select null, rand() from t1 join t2;

insert into t1 select null, rand() from t1 join t2;
call show_tables();
insert into t1 select null, rand() from t1;
insert into t1 select null, rand() from t1;
insert into t2 select null, rand() from t1 ;
call show_tables();
insert into t2 select null, rand() from t1 limit 990976;
insert into t1 select null, rand() from t1 limit 990992;
call show_tables();
insert into t2 select null, rand() from t1 limit 1000000;
insert into t1 select null, rand() from t1 limit 1000000;
call show_tables();
drop procedure show_tables;

sample output

$ ./test_clone.sh
+ dbdeployer deploy single 5.7.26 --master --sandbox-directory=master_5_7_26 --port=18000 --db-password=different --db-user=different
Database installed in $HOME/sandboxes/master_5_7_26
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
+ dbdeployer deploy single 8.0.17 --master --sandbox-directory=master_8_0_17 --port=18001 --db-password=different --db-user=different
Database installed in $HOME/sandboxes/master_8_0_17
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
+ dbdeployer deploy single 8.0.17 --master --sandbox-directory=slave_8_0_17 --port=18002
Database installed in $HOME/sandboxes/slave_8_0_17
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
+ /Users/gmax/sandboxes/master_5_7_26/use -t
+------------+-----------+
| table_name | row_num   |
+------------+-----------+
| t1         | 1,002,252 |
| t2         | 16        |
| --------   | --------  |
| elapsed    | 00:00:04  |
+------------+-----------+
+------------+-----------+
| table_name | row_num   |
+------------+-----------+
| t1         | 4,009,008 |
| t2         | 4,009,024 |
| --------   | --------  |
| elapsed    | 00:00:30  |
+------------+-----------+
+------------+-----------+
| table_name | row_num   |
+------------+-----------+
| t1         | 5,000,000 |
| t2         | 5,000,000 |
| --------   | --------  |
| elapsed    | 00:00:39  |
+------------+-----------+
+------------+-----------+
| table_name | row_num   |
+------------+-----------+
| t1         | 6,000,000 |
| t2         | 6,000,000 |
| --------   | --------  |
| elapsed    | 00:00:47  |
+------------+-----------+
+ du -sh /Users/gmax/sandboxes/master_5_7_26/data /Users/gmax/sandboxes/master_8_0_17/data /Users/gmax/sandboxes/slave_8_0_17/data
1.1G	/Users/gmax/sandboxes/master_5_7_26/data
165M	/Users/gmax/sandboxes/master_8_0_17/data
168M	/Users/gmax/sandboxes/slave_8_0_17/data
+ dbdeployer admin upgrade master_5_7_26 master_8_0_17
stop /Users/gmax/sandboxes/master_5_7_26
stop /Users/gmax/sandboxes/master_8_0_17
Data directory master_5_7_26/data moved to master_8_0_17/data
........ sandbox server started

The data directory from master_8_0_17/data is preserved in master_8_0_17/data-master_8_0_17
The data directory from master_5_7_26/data is now used in master_8_0_17/data
master_5_7_26 is not operational and can be deleted
+ '[' 0 '!=' 0 ']'
+ /Users/gmax/sandboxes/master_8_0_17/use -e 'set persist general_log=1'
+ /Users/gmax/sandboxes/master_8_0_17/use -e 'install plugin clone soname '\''mysql_clone.so'\'''
+ /Users/gmax/sandboxes/slave_8_0_17/use -e 'set persist general_log=1'
+ /Users/gmax/sandboxes/slave_8_0_17/use -e 'install plugin clone soname '\''mysql_clone.so'\'''
+ /Users/gmax/sandboxes/slave_8_0_17/use -e 'set persist clone_valid_donor_list='\''127.0.0.1:18001'\'''
+ /Users/gmax/sandboxes/slave_8_0_17/use -e 'CLONE INSTANCE FROM different@127.0.0.1:18001 IDENTIFIED BY '\''different'\'''
+ du -sh /Users/gmax/sandboxes/master_8_0_17/data /Users/gmax/sandboxes/slave_8_0_17/data
1.1G	/Users/gmax/sandboxes/master_8_0_17/data
968M	/Users/gmax/sandboxes/slave_8_0_17/data

For more info, see:

• The MySQL Manual on CLONE
• How to download and use the lastest MySQL 8 with dbdeployer