--source include/have_innodb.inc --let $rpl_topology=1->2, 1->3, 1->4, 1->5 --source include/rpl_init.inc # Set up a topology with one master and 4 slaves. # # Replicate some events leaving the four slaves at different points # in different domains. # # Then promote one slave as new master, bringing it ahead of all others # using START SLAVE UNTIL master_gtid_pos. --connection server_1 ALTER TABLE mysql.rpl_slave_state ENGINE=InnoDB; CREATE TABLE t4 (a INT, b INT, PRIMARY KEY (a,b)) Engine=InnoDB; # Function to extract one GTID from a list. delimiter |; CREATE FUNCTION extract_gtid(d VARCHAR(100), s VARCHAR(100)) RETURNS VARCHAR(100) DETERMINISTIC BEGIN SET s= CONCAT(",", s, ","); SET s= SUBSTR(s FROM LOCATE(CONCAT(",", d, "-"), s) + 1); SET s= SUBSTR(s FROM 1 FOR LOCATE(",", s) - 1); RETURN s; END| delimiter ;| --save_master_pos --connection server_2 --sync_with_master --source include/stop_slave.inc CHANGE MASTER TO master_use_gtid=1; --connection server_3 --sync_with_master --source include/stop_slave.inc CHANGE MASTER TO master_use_gtid=1; --connection server_4 --sync_with_master --source include/stop_slave.inc CHANGE MASTER TO master_use_gtid=1; --connection server_5 --sync_with_master --source include/stop_slave.inc CHANGE MASTER TO master_use_gtid=1; # Create three separate replication streams on master server_1. # # Then use START SLAVE UNTIL to get the different streams interleaved # differently spread over multiple binlogs on the different slaves, to # test that new master promotion is able to deal with this. --connection server_1 SET gtid_domain_id= 1; CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; --let $d1_begin= `SELECT extract_gtid("1", @@GLOBAL.gtid_pos)` INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t4 VALUES (1, 1); --let $d1_mid= `SELECT extract_gtid("1", @@GLOBAL.gtid_pos)` INSERT INTO t1 VALUES (3); INSERT INTO t1 VALUES (4); INSERT INTO t4 VALUES (1, 3); --let $d1_end= `SELECT extract_gtid("1", @@GLOBAL.gtid_pos)` SET gtid_domain_id= 2; CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; --let $d2_begin= `SELECT extract_gtid("2", @@GLOBAL.gtid_pos)` INSERT INTO t2 VALUES (1); INSERT INTO t2 VALUES (2); INSERT INTO t4 VALUES (2, 1); --let $d2_mid= `SELECT extract_gtid("2", @@GLOBAL.gtid_pos)` INSERT INTO t2 VALUES (3); INSERT INTO t2 VALUES (4); INSERT INTO t4 VALUES (2, 3); --let $d2_end= `SELECT extract_gtid("2", @@GLOBAL.gtid_pos)` SET gtid_domain_id= 3; CREATE TABLE t3 (a INT PRIMARY KEY) ENGINE=InnoDB; --let $d3_begin= `SELECT extract_gtid("3", @@GLOBAL.gtid_pos)` INSERT INTO t3 VALUES (1); INSERT INTO t3 VALUES (2); INSERT INTO t4 VALUES (3, 1); --let $d3_mid= `SELECT extract_gtid("3", @@GLOBAL.gtid_pos)` INSERT INTO t3 VALUES (3); INSERT INTO t3 VALUES (4); INSERT INTO t4 VALUES (3, 3); --let $d3_end= `SELECT extract_gtid("3", @@GLOBAL.gtid_pos)` # Slave server_2 (that will be promoted to master) is in the middle # of each stream. --connection server_2 eval START SLAVE UNTIL master_gtid_pos= "$d1_mid,$d2_mid,$d3_mid"; # The remaining slaves sit at different points each in different domains. --connection server_3 eval START SLAVE UNTIL master_gtid_pos= "$d1_begin,$d2_mid,$d3_end"; --connection server_4 eval START SLAVE UNTIL master_gtid_pos= "$d2_begin,$d3_mid,$d1_end"; --connection server_5 eval START SLAVE UNTIL master_gtid_pos= "$d3_begin,$d1_mid,$d2_end"; --connection server_2 --source include/wait_for_slave_to_stop.inc SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; SELECT * FROM t4 ORDER BY a,b; --connection server_3 --source include/wait_for_slave_to_stop.inc SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; SELECT * FROM t4 ORDER BY a,b; --connection server_4 --source include/wait_for_slave_to_stop.inc SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; SELECT * FROM t4 ORDER BY a,b; --connection server_5 --source include/wait_for_slave_to_stop.inc SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; SELECT * FROM t4 ORDER BY a,b; --echo *** Now replicate all extra changes from 3,4,5 to 2, in preparation for making 2 the new master. *** --connection server_3 --let $server3_pos= `SELECT @@GLOBAL.gtid_pos` --connection server_2 --replace_result $SERVER_MYPORT_3 SERVER_MYPORT_3 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_3; eval START SLAVE UNTIL master_gtid_pos = "$server3_pos"; --source include/wait_for_slave_to_stop.inc SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; SELECT * FROM t4 ORDER BY a,b; --connection server_4 --let $server4_pos= `SELECT @@GLOBAL.gtid_pos` --connection server_2 --replace_result $SERVER_MYPORT_4 SERVER_MYPORT_4 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_4; eval START SLAVE UNTIL master_gtid_pos = "$server4_pos"; --source include/wait_for_slave_to_stop.inc SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; SELECT * FROM t4 ORDER BY a,b; --connection server_5 --let $server5_pos= `SELECT @@GLOBAL.gtid_pos` --connection server_2 --replace_result $SERVER_MYPORT_5 SERVER_MYPORT_5 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_5; eval START SLAVE UNTIL master_gtid_pos = "$server5_pos"; --source include/wait_for_slave_to_stop.inc SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; SELECT * FROM t4 ORDER BY a,b; --echo *** Now make 2 master and point 3,4,5 to the new master 2 --connection server_2 SET gtid_domain_id= 1; INSERT INTO t1 values (5); INSERT INTO t4 values (1,5); SET gtid_domain_id= 2; INSERT INTO t2 values (5); INSERT INTO t4 values (2,5); SET gtid_domain_id= 3; INSERT INTO t3 values (5); INSERT INTO t4 values (3,5); --connection server_3 --replace_result $SERVER_MYPORT_2 SERVER_MYPORT_2 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_2; --source include/start_slave.inc --connection server_4 --replace_result $SERVER_MYPORT_2 SERVER_MYPORT_2 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_2; --source include/start_slave.inc --connection server_5 --replace_result $SERVER_MYPORT_2 SERVER_MYPORT_2 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_2; --source include/start_slave.inc --connection server_2 --save_master_pos --connection server_3 --sync_with_master SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; SELECT * FROM t4 ORDER BY a,b; --connection server_5 --sync_with_master SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; SELECT * FROM t4 ORDER BY a,b; --connection server_5 --sync_with_master SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; SELECT * FROM t4 ORDER BY a,b; --echo *** Now let the old master join up as slave. *** --connection server_1 --replace_result $SERVER_MYPORT_2 SERVER_MYPORT_2 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_2, master_user = "root", master_use_gtid = 1; --source include/start_slave.inc --sync_with_master SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; SELECT * FROM t4 ORDER BY a,b; --echo *** Finally move things back and clean up. *** --connection server_1 --source include/stop_slave.inc RESET SLAVE ALL; --connection server_2 --replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_1; --source include/start_slave.inc --connection server_3 --source include/stop_slave.inc --replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_1; --source include/start_slave.inc --connection server_4 --source include/stop_slave.inc --replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_1; --source include/start_slave.inc --connection server_5 --source include/stop_slave.inc --replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_1; --source include/start_slave.inc --connection server_1 SET gtid_domain_id = 0; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; DROP FUNCTION extract_gtid; --source include/rpl_end.inc