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
|
CHANGE MASTER 'slave1' TO master_port=MYPORT_1, master_host='127.0.0.1', master_user='root';
CHANGE MASTER 'slave2' TO master_port=MYPORT_2, master_host='127.0.0.1', master_user='root';
set default_master_connection = 'slave1';
START SLAVE;
include/wait_for_slave_to_start.inc
set default_master_connection = 'slave2';
START SLAVE;
include/wait_for_slave_to_start.inc
set default_master_connection = '';
CHANGE MASTER TO master_port=MYPORT_3, master_host='127.0.0.1', master_user='root';
start all slaves;
Warnings:
Note 1937 SLAVE '' started
include/wait_for_slave_to_start.inc
SET GLOBAL gtid_domain_id= 1;
SET SESSION gtid_domain_id= 1;
CREATE TABLE t3 (a INT PRIMARY KEY, b VARCHAR(10)) ENGINE=InnoDB;
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(10));
INSERT INTO t1 VALUES (1, "initial");
INSERT INTO t3 VALUES (101, "initial 1");
SET GLOBAL gtid_domain_id= 2;
SET SESSION gtid_domain_id= 2;
CREATE TABLE t2 (a INT PRIMARY KEY, b VARCHAR(10)) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1, "initial");
SET SQL_LOG_BIN=0;
CREATE TABLE t3 (a INT PRIMARY KEY, b VARCHAR(10));
SET SQL_LOG_BIN=1;
INSERT INTO t3 VALUES (201, "initial 2");
SHOW ALL SLAVES STATUS;
Connection_name
Slave_SQL_State Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_IO_State Waiting for master to send event
Master_Host 127.0.0.1
Master_User root
Master_Port MYPORT_3
Connect_Retry 60
Master_Log_File server3-bin.000001
Read_Master_Log_Pos 1501
Relay_Log_File mysqld-relay-bin.000002
Relay_Log_Pos 1790
Relay_Master_Log_File server3-bin.000001
Slave_IO_Running Yes
Slave_SQL_Running Yes
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno 0
Last_Error
Skip_Counter 0
Exec_Master_Log_Pos 1501
Relay_Log_Space 2088
Until_Condition None
Until_Log_File
Until_Log_Pos 0
Master_SSL_Allowed No
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master 0
Master_SSL_Verify_Server_Cert No
Last_IO_Errno 0
Last_IO_Error
Last_SQL_Errno 0
Last_SQL_Error
Replicate_Ignore_Server_Ids
Master_Server_Id 3
Master_SSL_Crl
Master_SSL_Crlpath
Using_Gtid No
Retried_transactions 0
Max_relay_log_size 1073741824
Executed_log_entries 25
Slave_received_heartbeats 0
Slave_heartbeat_period 60.000
Gtid_Slave_Pos 1-1-4,2-2-3
*** Now move slave2 to replicate from both master1 and master2 instead of just slave1 ***
STOP ALL SLAVES;
Warnings:
Note 1938 SLAVE '' stopped
INSERT INTO t1 VALUES (2, "switch1");
INSERT INTO t3 VALUES (102, "switch1 a");
INSERT INTO t2 VALUES (2, "switch1");
INSERT INTO t3 VALUES (202, "switch1 b");
CHANGE MASTER 'slave1' TO master_port=MYPORT_1, master_host='127.0.0.1', master_user='root', master_use_gtid=current_pos;
CHANGE MASTER 'slave2' TO master_port=MYPORT_2, master_host='127.0.0.1', master_user='root', master_use_gtid=current_pos;
SET default_master_connection = 'slave1';
START SLAVE;
include/wait_for_slave_to_start.inc
SET default_master_connection = 'slave2';
START SLAVE;
include/wait_for_slave_to_start.inc
set default_master_connection = '';
*** Move slave1 to replicate from slave2 instead of from master1 and master2 ***
STOP SLAVE 'slave1';
INSERT INTO t1 VALUES (3, "switch 2");
INSERT INTO t3 VALUES (103, "switch 2 a");
INSERT INTO t2 VALUES (3, "switch 2");
INSERT INTO t3 VALUES (203, "switch 2 b");
STOP SLAVE 'slave2';
INSERT INTO t2 VALUES (4, "switch 3");
INSERT INTO t3 VALUES (204, "switch 3 b");
CHANGE MASTER TO master_port=MYPORT_4, master_host='127.0.0.1', master_user='root', master_use_gtid=current_pos;
START SLAVE;
SELECT * FROM t1 ORDER BY a;
a b
1 initial
2 switch1
3 switch 2
SELECT * FROM t2 ORDER BY a;
a b
1 initial
2 switch1
3 switch 2
4 switch 3
SELECT * FROM t3 ORDER BY a;
a b
101 initial 1
102 switch1 a
103 switch 2 a
201 initial 2
202 switch1 b
203 switch 2 b
204 switch 3 b
DROP TABLE t1;
SET SQL_LOG_BIN=0;
DROP TABLE t3;
SET SQL_LOG_BIN=1;
DROP TABLE t2;
DROP TABLE t3;
SET GLOBAL gtid_domain_id=0;
STOP ALL SLAVES;
Warnings:
Note 1938 SLAVE '' stopped
include/reset_master_slave.inc
SET GLOBAL gtid_domain_id=0;
STOP ALL SLAVES;
Warnings:
Note 1938 SLAVE 'slave2' stopped
Note 1938 SLAVE 'slave1' stopped
include/reset_master_slave.inc
SET GLOBAL gtid_domain_id=0;
include/reset_master_slave.inc
SET GLOBAL gtid_domain_id=0;
include/reset_master_slave.inc
|
