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