Skip to content

Instantly share code, notes, and snippets.

@datacharmer
Last active Aug 13, 2019
Embed
What would you like to do?
test MySQL 8.0.17 CLONE
#!/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'
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;
$ ./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
@datacharmer
Copy link
Author

datacharmer commented Aug 12, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment