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
|
include/master-slave.inc
[connection master]
connection server_2;
call mtr.add_suppression("Timeout waiting for reply of binlog");
# Master server_1 and Slave server_2 initialization ...
connection server_2;
include/stop_slave.inc
connection server_1;
RESET MASTER;
set @@session.gtid_domain_id=10;
set @@global.rpl_semi_sync_master_enabled = 1;
set @@global.rpl_semi_sync_master_wait_point=AFTER_SYNC;
connection server_2;
RESET MASTER;
ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
set @@session.gtid_domain_id=20;
set @@global.rpl_semi_sync_slave_enabled = 1;
# a 1948 warning is expected
set @@global.gtid_slave_pos = "";
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-1. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos
CHANGE MASTER TO master_use_gtid= slave_pos;
include/start_slave.inc
# server_1 -> server_2 semisync link is set up.
connection server_1;
CREATE TABLE t1 (a INT PRIMARY KEY, b INT default 0) ENGINE=Innodb;
INSERT INTO t1(a) VALUES (1);
include/save_master_gtid.inc
connection server_2;
include/sync_with_master_gtid.inc
# Circular configuration server_1 -> server_2 -> server_1 ...
connection server_1;
set @@global.gtid_strict_mode = true;
set @@global.rpl_semi_sync_slave_enabled = 1;
CHANGE MASTER TO master_host='127.0.0.1', master_port=$SERVER_MYPORT_2, master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=SLAVE_POS;
include/start_slave.inc
... is done.
## A. no out-of-order gtid error for own transaction made round trip
connection server_2;
set @@global.gtid_strict_mode = true;
set @@global.rpl_semi_sync_master_enabled = 1;
INSERT INTO t1(a) VALUES (2);
include/save_master_gtid.inc
connection server_1;
include/stop_slave.inc
include/start_slave.inc
#
# the successful sync is a required proof
#
include/sync_with_master_gtid.inc
update t1 set b=b+1 where a=2;
include/save_master_gtid.inc
connection server_2;
include/sync_with_master_gtid.inc
# Post-execution state check on both servers synchronized with each other
connection server_1;
# ... the gtid states on server_1
SHOW VARIABLES LIKE 'gtid_slave_pos';
Variable_name Value
gtid_slave_pos 0-2-1,10-1-3,20-2-1
SHOW VARIABLES LIKE 'gtid_binlog_pos';
Variable_name Value
gtid_binlog_pos 0-2-1,10-1-3,20-2-1
SELECT * from t1;
a b
1 0
2 1
connection server_2;
# The gtid states on server_2 must be equal to ...
SHOW VARIABLES LIKE 'gtid_binlog_pos';
Variable_name Value
gtid_binlog_pos 0-2-1,10-1-3,20-2-1
SHOW VARIABLES LIKE 'gtid_slave_pos';
Variable_name Value
gtid_slave_pos 0-2-1,10-1-3,20-2-1
SELECT * from t1;
a b
1 0
2 1
## B. out-of-order gtid error for a "foreign" server-id transaction
connection server_1;
set statement sql_log_bin=0 for call mtr.add_suppression("Slave: An attempt was made to binlog GTID 10-2-4");
set @@session.server_id=2;
INSERT INTO t1(a) VALUES (3);
set @@session.server_id=default;
include/save_master_gtid.inc
connection server_2;
include/sync_with_master_gtid.inc
INSERT INTO t1(a) VALUES (4);
include/save_master_gtid.inc
connection server_1;
include/wait_for_slave_sql_error.inc [errno=1950]
set sql_slave_skip_counter=1;
include/start_slave.inc
include/sync_with_master_gtid.inc
connection server_2;
set statement sql_log_bin=0 for call mtr.add_suppression("Slave: An attempt was made to binlog GTID 20-1-3");
set @@session.server_id=1;
INSERT INTO t1(a) VALUES (5);
set @@session.server_id=default;
include/save_master_gtid.inc
connection server_1;
include/sync_with_master_gtid.inc
INSERT INTO t1(a) VALUES (6);
include/save_master_gtid.inc
connection server_2;
include/wait_for_slave_sql_error.inc [errno=1950]
set sql_slave_skip_counter=1;
include/start_slave.inc
include/sync_with_master_gtid.inc
# Post-execution state check on both servers synchronized with each other
connection server_1;
# ... the gtid states on server_1
SHOW VARIABLES LIKE 'gtid_slave_pos';
Variable_name Value
gtid_slave_pos 0-2-1,10-1-5,20-1-3
SHOW VARIABLES LIKE 'gtid_binlog_pos';
Variable_name Value
gtid_binlog_pos 0-2-1,10-1-5,20-1-3
SELECT * from t1;
a b
1 0
2 1
3 0
4 0
5 0
6 0
connection server_2;
include/sync_with_master_gtid.inc
# The gtid states on server_2 must be equal to ...
SHOW VARIABLES LIKE 'gtid_binlog_pos';
Variable_name Value
gtid_binlog_pos 0-2-1,10-1-5,20-1-3
SHOW VARIABLES LIKE 'gtid_slave_pos';
Variable_name Value
gtid_slave_pos 0-2-1,10-1-5,20-1-3
SELECT * from t1;
a b
1 0
2 1
3 0
4 0
5 0
6 0
#
# Cleanup
#
connection server_1;
DROP TABLE t1;
include/save_master_gtid.inc
connection server_2;
include/sync_with_master_gtid.inc
connection server_1;
include/stop_slave.inc
set @@global.rpl_semi_sync_master_enabled = default;
set @@global.rpl_semi_sync_slave_enabled = default;
set @@global.rpl_semi_sync_master_wait_point=default;
set @@global.gtid_ignore_duplicates = default;
set @@global.gtid_strict_mode = default;
connection server_2;
include/stop_slave.inc
set @@global.gtid_ignore_duplicates = default;
set @@global.rpl_semi_sync_master_enabled = default;
set @@global.rpl_semi_sync_slave_enabled = default;
set @@global.gtid_strict_mode = default;
include/start_slave.inc
include/rpl_end.inc
|
