1include/master-slave.inc 2Warnings: 3Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. 4Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 5[connection master] 6drop procedure if exists master_procedure; 7create procedure master_procedure() 8begin 9drop table if exists t1; 10create table t1(id1 int); 11insert into t1 values(1); 12insert into t1 values(2); 13select @@global.gtid_mode; 14select @@global.gtid_deployment_step; 15end; 16// 17drop procedure if exists slave_procedure; 18create procedure slave_procedure() 19begin 20select @@global.gtid_mode; 21select @@global.gtid_deployment_step; 22select * from t1; 23end; 24// 25** Both Master and slave with gtid_deployment_step=0 and gtid_mode=OFF ** 26call master_procedure(); 27@@global.gtid_mode 28OFF 29@@global.gtid_deployment_step 300 31call slave_procedure(); 32@@global.gtid_mode 33OFF 34@@global.gtid_deployment_step 350 36id1 371 382 39include/rpl_restart_server.inc [server_number=2 gtids=on parameters: --gtid_deployment_step=1] 40** Slave with gtid_deployment_step=1 and gtid_mode=ON ** 41call master_procedure(); 42@@global.gtid_mode 43OFF 44@@global.gtid_deployment_step 450 46select @@global.gtid_executed; 47@@global.gtid_executed 48 49include/start_slave.inc 50include/sync_slave_sql_with_master.inc 51call slave_procedure(); 52@@global.gtid_mode 53ON 54@@global.gtid_deployment_step 551 56id1 571 582 59select @@global.gtid_executed; 60@@global.gtid_executed 61 62include/rpl_restart_server.inc [server_number=1 gtids=on parameters: --gtid_deployment_step=1] 63** Both master and slave with gtid_deployment_step=1 and gtid_mode=ON ** 64call master_procedure(); 65@@global.gtid_mode 66ON 67@@global.gtid_deployment_step 681 69select @@global.gtid_executed; 70@@global.gtid_executed 71 72include/sync_slave_sql_with_master.inc 73call slave_procedure(); 74@@global.gtid_mode 75ON 76@@global.gtid_deployment_step 771 78id1 791 802 81select @@global.gtid_executed; 82@@global.gtid_executed 83 84** Dynamically set gtid_deployment_step to 0 on master ** 85set global gtid_deployment_step= 0; 86call master_procedure(); 87@@global.gtid_mode 88ON 89@@global.gtid_deployment_step 900 91select @@global.gtid_executed; 92@@global.gtid_executed 93UUID:1-4 94call slave_procedure(); 95@@global.gtid_mode 96ON 97@@global.gtid_deployment_step 981 99id1 1001 1012 102select @@global.gtid_executed; 103@@global.gtid_executed 104UUID:1-4 105include/rpl_restart_server.inc [server_number=1 gtids=on parameters: --gtid_deployment_step=0] 106** Master with gtid_deployment_step=0 and gtid_mode=ON, slave with gtid_deployment_step=1 and gtid_mode=ON ** 107call master_procedure(); 108@@global.gtid_mode 109ON 110@@global.gtid_deployment_step 1110 112select @@global.gtid_executed; 113@@global.gtid_executed 114UUID:1-8 115include/sync_slave_sql_with_master.inc 116call slave_procedure(); 117@@global.gtid_mode 118ON 119@@global.gtid_deployment_step 1201 121id1 1221 1232 124select @@global.gtid_executed; 125@@global.gtid_executed 126UUID:1-8 127include/rpl_restart_server.inc [server_number=2 gtids=on parameters: --gtid_deployment_step=0] 128** Both master and slave with gtid_deployment_step=0 and gtid_mode=ON ** 129call master_procedure(); 130@@global.gtid_mode 131ON 132@@global.gtid_deployment_step 1330 134select @@global.gtid_executed; 135@@global.gtid_executed 136UUID:1-12 137include/start_slave.inc 138include/sync_slave_sql_with_master.inc 139call slave_procedure(); 140@@global.gtid_mode 141ON 142@@global.gtid_deployment_step 1430 144id1 1451 1462 147select @@global.gtid_executed; 148@@global.gtid_executed 149UUID:1-12 150drop table t1; 151drop procedure master_procedure; 152drop procedure slave_procedure; 153** Testing scenario where gtid_deployment_step is turned OFF in master causing it to 154generate gtid_events from the middle of active binlog ** 155include/rpl_reset.inc 156include/rpl_restart_server.inc [server_number=2 gtids=on parameters: --gtid_deployment_step=0] 157include/rpl_restart_server.inc [server_number=1 gtids=on parameters: --gtid_deployment_step=1] 158create table t1(id1 int); 159insert into t1 values(100); 160insert into t1 values(200); 161include/start_slave.inc 162include/sync_slave_sql_with_master.inc 163select * from t1; 164id1 165100 166200 167include/rpl_restart_server.inc [server_number=1 gtids=on parameters: --gtid_deployment_step=0] 168insert into t1 values(300); 169insert into t1 values(400); 170select @@global.gtid_executed; 171@@global.gtid_executed 172UUID:1-2 173include/sync_slave_sql_with_master.inc 174include/stop_slave.inc 175change master to master_auto_position=1; 176include/start_slave.inc 177select * from t1; 178id1 179100 180200 181300 182400 183select @@global.gtid_executed; 184@@global.gtid_executed 185UUID:1-2 186drop table t1; 187include/rpl_end.inc 188