connection node_2;
connection node_1;
connect node_3, 127.0.0.1, root, , test, $NODE_MYPORT_3;
connect node_2b, 127.0.0.1, root, , test, $NODE_MYPORT_2;
set wsrep_sync_wait=0;
connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1;
set wsrep_sync_wait=0;
connection node_1;
CREATE PROCEDURE insert_row (IN node varchar(10), IN repeat_count int)
BEGIN
DECLARE current_num int;
SET current_num = 0;
WHILE current_num < repeat_count do
INSERT INTO t1(node, name) VALUES (node, UUID());
SET current_num = current_num + 1;
END WHILE;
END|
CREATE TABLE t1 (id bigint not null primary key auto_increment, node VARCHAR(10), name VARCHAR(64)) ENGINE=innodb;
# node_1
show variables like '%gtid_binlog_pos%';
Variable_name	Value
gtid_binlog_pos	1111-1-2
connection node_2;
# node_2
show variables like '%gtid_binlog_pos%';
Variable_name	Value
gtid_binlog_pos	1111-1-2
connection node_3;
# node_3
show variables like '%gtid_binlog_pos%';
Variable_name	Value
gtid_binlog_pos	1111-1-2
connection node_1;
CALL insert_row('node1', 500);;
connection node_2;
CALL insert_row('node2', 500);;
connection node_3;
CALL insert_row('node3', 500);;
connection node_2;
# Shutdown node_2, force SST
connection node_2b;
# Wait until node_2 leaves cluster
connection node_1b;
connection node_1;
connection node_3;
connection node_1;
CALL insert_row('node1', 500);
connection node_3;
CALL insert_row('node3', 500);
CREATE TABLE t2(i int primary key) engine=innodb;
connection node_2;
# Restart node_2
# restart
connection node_1b;
# Wait until node_2 is back in cluster
# node2 has joined
# GTID in node1
show variables like 'wsrep_gtid_domain_id';
Variable_name	Value
wsrep_gtid_domain_id	1111
show variables like '%gtid_binlog_pos%';
Variable_name	Value
gtid_binlog_pos	1111-1-2503
connection node_2;
# GTID in node2
show variables like 'wsrep_gtid_domain_id';
Variable_name	Value
wsrep_gtid_domain_id	1111
show variables like '%gtid_binlog_pos%';
Variable_name	Value
gtid_binlog_pos	1111-1-2503
connection node_3;
# GTID in node3
show variables like 'wsrep_gtid_domain_id';
Variable_name	Value
wsrep_gtid_domain_id	1111
show variables like '%gtid_binlog_pos%';
Variable_name	Value
gtid_binlog_pos	1111-1-2503
# Shutdown node_3
connection node_3;
SET GLOBAL wsrep_provider_options = 'gmcast.isolate = 1';
# Wait until node_3 leaves cluster
connection node_1b;
connection node_1;
CALL insert_row('node1', 50);
CREATE TABLE t3(i int primary key) engine=innodb;
connection node_3;
# Rejoin node_3
SET GLOBAL wsrep_provider_options = 'gmcast.isolate = 0';
connection node_1b;
# Wait until node_3 is back in cluster
# node3 has joined
connection node_1;
# GTID in node1
show variables like 'wsrep_gtid_domain_id';
Variable_name	Value
wsrep_gtid_domain_id	1111
show variables like '%gtid_binlog_pos%';
Variable_name	Value
gtid_binlog_pos	1111-1-2554
connection node_2;
# GTID in node2
show variables like 'wsrep_gtid_domain_id';
Variable_name	Value
wsrep_gtid_domain_id	1111
show variables like '%gtid_binlog_pos%';
Variable_name	Value
gtid_binlog_pos	1111-1-2554
connection node_3;
# GTID in node3
show variables like 'wsrep_gtid_domain_id';
Variable_name	Value
wsrep_gtid_domain_id	1111
show variables like '%gtid_binlog_pos%';
Variable_name	Value
gtid_binlog_pos	1111-1-2554
# One by one shutdown all nodes
connection node_3;
# shutdown node_3
connection node_2;
# wait until node_3 is out of cluster
# shutdown node_2
connection node_1;
# wait until node_2 is out of cluster
# shutdown node_1
# Bootstrap from node_1
connection node_1;
# restart: --wsrep_new_cluster
show variables like 'wsrep_gtid_domain_id';
Variable_name	Value
wsrep_gtid_domain_id	1111
show variables like '%gtid_binlog_pos%';
Variable_name	Value
gtid_binlog_pos	1111-1-2554
ANALYZE TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	OK
CALL insert_row('node1', 100);;
# Restart node_2
connection node_2;
# restart
connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1;
set wsrep_sync_wait=0;
connection node_1c;
# wait until node_1 and node_2 are in cluster
connection node_2;
ALTER TABLE t2 ADD COLUMN (k int);
CALL insert_row('node2', 100);;
# Restart node_3
connection node_3;
# restart
connection node_1c;
# wait until all nodes are back in cluster
after cluster restart
connection node_2;
connection node_1;
connection node_1;
node1 GTID
show variables like 'wsrep_gtid_domain_id';
Variable_name	Value
wsrep_gtid_domain_id	1111
show variables like '%gtid_binlog_pos%';
Variable_name	Value
gtid_binlog_pos	1111-1-2756
connection node_2;
node2 GTID
show variables like 'wsrep_gtid_domain_id';
Variable_name	Value
wsrep_gtid_domain_id	1111
show variables like '%gtid_binlog_pos%';
Variable_name	Value
gtid_binlog_pos	1111-1-2756
connection node_3;
node3 GTID
show variables like 'wsrep_gtid_domain_id';
Variable_name	Value
wsrep_gtid_domain_id	1111
show variables like '%gtid_binlog_pos%';
Variable_name	Value
gtid_binlog_pos	1111-1-2756
connection node_1;
table size in node1
SELECT COUNT(*) FROM t1;
COUNT(*)
2750
connection node_2;
table size in node2
SELECT COUNT(*) FROM t1;
COUNT(*)
2750
connection node_3;
table size in node3
SELECT COUNT(*) FROM t1;
COUNT(*)
2750
connection node_2;
call mtr.add_suppression("WSREP: Ignoring server id for non bootstrap node");
call mtr.add_suppression("WSREP: Sending JOIN failed:.*");
call mtr.add_suppression("Sending JOIN failed:.*");
call mtr.add_suppression("WSREP: Failed to JOIN the cluster after SST.*");
connection node_3;
call mtr.add_suppression("WSREP: Ignoring server id for non bootstrap node");
call mtr.add_suppression("WSREP: Sending JOIN failed:.*");
call mtr.add_suppression("Sending JOIN failed:.*");
call mtr.add_suppression("WSREP: Failed to JOIN the cluster after SST.*");
# cleanup
connection node_1;
DROP PROCEDURE insert_row;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
connection node_3;
connection node_2;
disconnect node_3;
disconnect node_2b;
disconnect node_1b;
disconnect node_1c;