1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
|
include/master-slave.inc
[connection master]
*** Preparing ***
connection slave;
include/stop_slave.inc
RESET SLAVE;
SET @restore_slave_net_timeout=@@global.slave_net_timeout;
connection master;
RESET MASTER;
SET @restore_slave_net_timeout=@@global.slave_net_timeout;
connection slave;
*** Default value ***
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root';
slave_net_timeout/slave_heartbeat_timeout=2.0000
include/reset_slave.inc
*** Reset slave affect ***
SET @@global.slave_net_timeout=30;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=5;
include/reset_slave.inc
SHOW GLOBAL STATUS LIKE 'slave_heartbeat_period';
Variable_name Value
Slave_heartbeat_period 15.000
*** Default value if slave_net_timeout changed ***
SET @@global.slave_net_timeout=50;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20;
SHOW GLOBAL STATUS LIKE 'slave_heartbeat_period';
Variable_name Value
Slave_heartbeat_period 25.000
SET @@global.slave_net_timeout=@restore_slave_net_timeout;
include/reset_slave.inc
*** Warning if updated slave_net_timeout < slave_heartbeat_timeout ***
SET @@global.slave_net_timeout=FLOOR(SLAVE_HEARTBEAT_TIMEOUT)-1;
SET @@global.slave_net_timeout=@restore_slave_net_timeout;
RESET SLAVE;
*** Warning if updated slave_heartbeat_timeout > slave_net_timeout ***
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=SLAVE_NET_TIMEOUT;
Warnings:
Warning 1704 The requested value for the heartbeat period exceeds the value of `slave_net_timeout' seconds. A sensible value for the period should be less than the timeout
RESET SLAVE;
*** CHANGE MASTER statement only updates slave_heartbeat_period ***
SET @@global.slave_net_timeout=20;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=5;
SHOW VARIABLES LIKE 'slave_net_timeout';
Variable_name Value
slave_net_timeout 20
SHOW GLOBAL STATUS LIKE 'slave_heartbeat_period';
Variable_name Value
Slave_heartbeat_period 5.000
SET @@global.slave_net_timeout=2*@@global.slave_net_timeout;
SHOW VARIABLES LIKE 'slave_net_timeout';
Variable_name Value
slave_net_timeout 40
SHOW GLOBAL STATUS LIKE 'slave_heartbeat_period';
Variable_name Value
Slave_heartbeat_period 5.000
SET @@global.slave_net_timeout=@restore_slave_net_timeout;
RESET SLAVE;
*** Update slave_net_timeout on master ***
connection master;
SET @@global.slave_net_timeout=500;
connection slave;
SET @@global.slave_net_timeout=200;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20;
include/start_slave.inc
connection master;
connection slave;
SHOW VARIABLES LIKE 'slave_net_timeout';
Variable_name Value
slave_net_timeout 200
SHOW GLOBAL STATUS LIKE 'slave_heartbeat_period';
Variable_name Value
Slave_heartbeat_period 100.000
SET @@global.slave_net_timeout=@restore_slave_net_timeout;
include/stop_slave.inc
RESET SLAVE;
connection master;
SET @@global.slave_net_timeout=@restore_slave_net_timeout;
*** Start/stop slave ***
connection slave;
SET @@global.slave_net_timeout=100;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=20;
include/start_slave.inc
connection master;
connection slave;
SHOW GLOBAL STATUS LIKE 'slave_heartbeat_period';
Variable_name Value
Slave_heartbeat_period 20.000
include/stop_slave.inc
SHOW GLOBAL STATUS LIKE 'slave_heartbeat_period';
Variable_name Value
Slave_heartbeat_period 20.000
*** Reload slave ***
connection slave;
SET @@global.slave_net_timeout=50;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=30;
include/rpl_restart_server.inc [server_number=2]
SHOW GLOBAL STATUS LIKE 'slave_heartbeat_period';
Variable_name Value
Slave_heartbeat_period 30.000
SET @restore_slave_net_timeout=@@global.slave_net_timeout;
*** Disable heartbeat ***
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=0;
SHOW GLOBAL STATUS LIKE 'slave_heartbeat_period';
Variable_name Value
Slave_heartbeat_period 0.000
SHOW STATUS LIKE 'slave_received_heartbeats';
Variable_name Value
Slave_received_heartbeats 0
include/start_slave.inc
connection master;
connection slave;
SHOW GLOBAL STATUS LIKE 'slave_heartbeat_period';
Variable_name Value
Slave_heartbeat_period 0.000
SHOW STATUS LIKE 'slave_received_heartbeats';
Variable_name Value
Slave_received_heartbeats 0
include/stop_slave.inc
SHOW GLOBAL STATUS LIKE 'slave_heartbeat_period';
Variable_name Value
Slave_heartbeat_period 0.000
SHOW STATUS LIKE 'slave_received_heartbeats';
Variable_name Value
Slave_received_heartbeats 0
RESET SLAVE;
SELECT SLAVE_HEARTBEAT_TIMEOUT = 0 AS Result;
Result
0
*** Min slave_heartbeat_timeout ***
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=0.001;
SHOW GLOBAL STATUS LIKE 'slave_heartbeat_period';
Variable_name Value
Slave_heartbeat_period 0.001
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=0.0009;
Warnings:
Warning 1703 The requested value for the heartbeat period is less than 1 millisecond. The value is reset to 0, meaning that heartbeating will effectively be disabled
SHOW GLOBAL STATUS LIKE 'slave_heartbeat_period';
Variable_name Value
Slave_heartbeat_period 0.000
RESET SLAVE;
*** Max slave_heartbeat_timeout ***
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=4294967;
Warnings:
Warning 1704 The requested value for the heartbeat period exceeds the value of `slave_net_timeout' seconds. A sensible value for the period should be less than the timeout
SHOW GLOBAL STATUS LIKE 'slave_heartbeat_period';
Variable_name Value
Slave_heartbeat_period 4294967.000
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=4294968;
ERROR HY000: The requested value for the heartbeat period is either negative or exceeds the maximum allowed (4294967 seconds)
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=8589935;
ERROR HY000: The requested value for the heartbeat period is either negative or exceeds the maximum allowed (4294967 seconds)
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=4294967296;
ERROR HY000: The requested value for the heartbeat period is either negative or exceeds the maximum allowed (4294967 seconds)
RESET SLAVE;
*** Misc incorrect values ***
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD='-1';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''-1'' at line 1
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD='123abc';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''123abc'' at line 1
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD='';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '''' at line 1
RESET SLAVE;
*** Running slave ***
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=0.1;
include/start_slave.inc
connection master;
connection slave;
Heartbeat event received
*** Stopped slave ***
include/stop_slave.inc
Number of received heartbeat events while slave stopped: 0
*** Started slave ***
include/start_slave.inc
Heartbeat event received
*** Stopped IO thread ***
include/stop_slave_io.inc
Number of received heartbeat events while io thread stopped: 0
*** Started IO thread ***
START SLAVE IO_THREAD;
include/wait_for_slave_io_to_start.inc
Heartbeat event received
*** Stopped SQL thread ***
include/stop_slave_sql.inc
Heartbeat events are received while sql thread stopped (1 means 'yes'): 1
*** Started SQL thread ***
START SLAVE SQL_THREAD;
include/wait_for_slave_sql_to_start.inc
Heartbeat event received
*** Stopped SQL thread by error ***
connection master;
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(10), c LONGTEXT);
connection slave;
INSERT INTO t1 VALUES (1, 'on slave', NULL);
connection master;
INSERT INTO t1 VALUES (1, 'on master', NULL);
connection slave;
set sql_log_bin= 0;
call mtr.add_suppression("Slave SQL.*Duplicate entry .1. for key .PRIMARY.. on query.* error.* 1062");
call mtr.add_suppression("Slave SQL.*Request to stop slave SQL Thread received while applying a group that has non-transactional changes; waiting for completion of the group");
set sql_log_bin= 1;
Heartbeat events are received while sql thread stopped (1 means 'yes'): 1
include/stop_slave_io.inc
set sql_log_bin= 0;
DROP TABLE t1;
set sql_log_bin= 1;
*** Master send to slave ***
connection master;
CREATE EVENT e1
ON SCHEDULE EVERY 1 SECOND
DO
BEGIN
UPDATE test.t1 SET a = a + 1 WHERE a < 10;
END|
Warnings:
Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
connection slave;
include/reset_slave.inc
Warnings:
Warning 1948 Specified value for @@gtid_slave_pos contains no value for replication domain 0. This conflicts with the binary log which contains GTID 0-2-2. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=5;
include/start_slave.inc
connection master;
SET @@global.event_scheduler=1;
connection slave;
connection master;
connection slave;
Received heartbeats meet expectations: TRUE
connection master;
DELETE FROM t1;
DROP EVENT e1;
connection slave;
*** Flush logs on slave ***
STOP SLAVE;
include/reset_slave.inc
set sql_log_bin= 0;
DROP TABLE t1;
set sql_log_bin= 1;
connection master;
DROP TABLE t1;
RESET MASTER;
connection slave;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=0.5;
include/start_slave.inc
Heartbeat events are received while rotation of relay logs (1 means 'yes'): 1
*** Compressed protocol ***
connection master;
SET @@global.slave_compressed_protocol=1;
connection slave;
include/stop_slave.inc
include/reset_slave.inc
SET @@global.slave_compressed_protocol=1;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=0.1;
include/start_slave.inc
Heartbeat event received
SET @@global.slave_compressed_protocol=0;
connection master;
SET @@global.slave_compressed_protocol=0;
*** Reset master ***
connection slave;
STOP SLAVE;
include/reset_slave.inc
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=0.1;
include/start_slave.inc
connection master;
RESET MASTER;
connection slave;
Heartbeat events are received after reset of master (1 means 'yes'): 1
*** Reload master ***
connection slave;
STOP SLAVE;
include/reset_slave.inc
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=0.1;
include/start_slave.inc
Heartbeat event received
include/rpl_restart_server.inc [server_number=1]
include/wait_for_slave_io_to_start.inc
Heartbeat event received
*** Circular replication ***
include/rpl_reset.inc
include/stop_slave.inc
include/rpl_change_topology.inc [new topology=1->2->1]
connection master;
CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD=1;
include/start_slave.inc
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(10));
INSERT INTO t1 VALUES(1, 'on master');
connection slave;
CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD=0.1;
include/start_slave.inc
INSERT INTO t1 VALUES(2, 'on slave');
connection master;
SELECT * FROM t1 ORDER BY a;
a b
1 on master
2 on slave
connection slave;
SELECT * FROM t1 ORDER BY a;
a b
1 on master
2 on slave
connection master;
Heartbeat event received on master
connection slave;
Heartbeat event received on slave
*** Clean up ***
connection master;
DROP TABLE t1;
connection slave;
SET @@global.slave_net_timeout=@restore_slave_net_timeout;
include/rpl_end.inc
|
