EVOLUTION-MANAGER

Edit File: rpl_gtid_master_promote.result

include/rpl_init.inc [topology=1->2, 1->3, 1->4, 1->5] connection server_1; ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; CREATE TABLE t4 (a INT, b INT, PRIMARY KEY (a,b)) Engine=InnoDB; 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| connection server_2; include/stop_slave.inc CHANGE MASTER TO master_use_gtid=current_pos; connection server_3; include/stop_slave.inc CHANGE MASTER TO master_use_gtid=current_pos; connection server_4; include/stop_slave.inc CHANGE MASTER TO master_use_gtid=current_pos; connection server_5; include/stop_slave.inc CHANGE MASTER TO master_use_gtid=current_pos; connection server_1; SET gtid_domain_id= 1; CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t4 VALUES (1, 1); INSERT INTO t1 VALUES (3); INSERT INTO t1 VALUES (4); INSERT INTO t4 VALUES (1, 3); SET gtid_domain_id= 2; CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t2 VALUES (1); INSERT INTO t2 VALUES (2); INSERT INTO t4 VALUES (2, 1); INSERT INTO t2 VALUES (3); INSERT INTO t2 VALUES (4); INSERT INTO t4 VALUES (2, 3); SET gtid_domain_id= 3; CREATE TABLE t3 (a INT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t3 VALUES (1); INSERT INTO t3 VALUES (2); INSERT INTO t4 VALUES (3, 1); INSERT INTO t3 VALUES (3); INSERT INTO t3 VALUES (4); INSERT INTO t4 VALUES (3, 3); connection server_2; START SLAVE UNTIL master_gtid_pos= "1-1-4,2-1-4,3-1-4"; connection server_3; START SLAVE UNTIL master_gtid_pos= "1-1-1,2-1-4,3-1-7"; connection server_4; START SLAVE UNTIL master_gtid_pos= "2-1-1,3-1-4,1-1-7"; connection server_5; START SLAVE UNTIL master_gtid_pos= "3-1-1,1-1-4,2-1-7"; connection server_2; include/wait_for_slave_to_stop.inc SELECT * FROM t1 ORDER BY a; a 1 2 SELECT * FROM t2 ORDER BY a; a 1 2 SELECT * FROM t3 ORDER BY a; a 1 2 SELECT * FROM t4 ORDER BY a,b; a b 1 1 2 1 3 1 connection server_3; include/wait_for_slave_to_stop.inc SELECT * FROM t1 ORDER BY a; a SELECT * FROM t2 ORDER BY a; a 1 2 SELECT * FROM t3 ORDER BY a; a 1 2 3 4 SELECT * FROM t4 ORDER BY a,b; a b 2 1 3 1 3 3 connection server_4; include/wait_for_slave_to_stop.inc SELECT * FROM t1 ORDER BY a; a 1 2 3 4 SELECT * FROM t2 ORDER BY a; a SELECT * FROM t3 ORDER BY a; a 1 2 SELECT * FROM t4 ORDER BY a,b; a b 1 1 1 3 3 1 connection server_5; include/wait_for_slave_to_stop.inc SELECT * FROM t1 ORDER BY a; a 1 2 SELECT * FROM t2 ORDER BY a; a 1 2 3 4 SELECT * FROM t3 ORDER BY a; a SELECT * FROM t4 ORDER BY a,b; a b 1 1 2 1 2 3 *** Now replicate all extra changes from 3,4,5 to 2, in preparation for making 2 the new master. *** connection server_3; connection server_2; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_3; START SLAVE UNTIL master_gtid_pos = "SERVER3_POS"; include/wait_for_slave_to_stop.inc SELECT * FROM t1 ORDER BY a; a 1 2 SELECT * FROM t2 ORDER BY a; a 1 2 SELECT * FROM t3 ORDER BY a; a 1 2 3 4 SELECT * FROM t4 ORDER BY a,b; a b 1 1 2 1 3 1 3 3 connection server_4; connection server_2; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_4; START SLAVE UNTIL master_gtid_pos = "SERVER4_POS"; include/wait_for_slave_to_stop.inc SELECT * FROM t1 ORDER BY a; a 1 2 3 4 SELECT * FROM t2 ORDER BY a; a 1 2 SELECT * FROM t3 ORDER BY a; a 1 2 3 4 SELECT * FROM t4 ORDER BY a,b; a b 1 1 1 3 2 1 3 1 3 3 connection server_5; connection server_2; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_5; START SLAVE UNTIL master_gtid_pos = "SERVER5_POS"; include/wait_for_slave_to_stop.inc SELECT * FROM t1 ORDER BY a; a 1 2 3 4 SELECT * FROM t2 ORDER BY a; a 1 2 3 4 SELECT * FROM t3 ORDER BY a; a 1 2 3 4 SELECT * FROM t4 ORDER BY a,b; a b 1 1 1 3 2 1 2 3 3 1 3 3 *** 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; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_2; include/start_slave.inc connection server_4; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_2; include/start_slave.inc connection server_5; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_2; include/start_slave.inc connection server_2; connection server_3; SELECT * FROM t1 ORDER BY a; a 1 2 3 4 5 SELECT * FROM t2 ORDER BY a; a 1 2 3 4 5 SELECT * FROM t3 ORDER BY a; a 1 2 3 4 5 SELECT * FROM t4 ORDER BY a,b; a b 1 1 1 3 1 5 2 1 2 3 2 5 3 1 3 3 3 5 connection server_5; SELECT * FROM t1 ORDER BY a; a 1 2 3 4 5 SELECT * FROM t2 ORDER BY a; a 1 2 3 4 5 SELECT * FROM t3 ORDER BY a; a 1 2 3 4 5 SELECT * FROM t4 ORDER BY a,b; a b 1 1 1 3 1 5 2 1 2 3 2 5 3 1 3 3 3 5 connection server_5; SELECT * FROM t1 ORDER BY a; a 1 2 3 4 5 SELECT * FROM t2 ORDER BY a; a 1 2 3 4 5 SELECT * FROM t3 ORDER BY a; a 1 2 3 4 5 SELECT * FROM t4 ORDER BY a,b; a b 1 1 1 3 1 5 2 1 2 3 2 5 3 1 3 3 3 5 *** Now let the old master join up as slave. *** connection server_1; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_2, master_user = "root", master_use_gtid = current_pos; include/start_slave.inc SELECT * FROM t1 ORDER BY a; a 1 2 3 4 5 SELECT * FROM t2 ORDER BY a; a 1 2 3 4 5 SELECT * FROM t3 ORDER BY a; a 1 2 3 4 5 SELECT * FROM t4 ORDER BY a,b; a b 1 1 1 3 1 5 2 1 2 3 2 5 3 1 3 3 3 5 *** Finally move things back and clean up. *** connection server_1; include/stop_slave.inc RESET SLAVE ALL; connection server_2; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_1; include/start_slave.inc connection server_3; include/stop_slave.inc CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_1; include/start_slave.inc connection server_4; include/stop_slave.inc CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_1; include/start_slave.inc connection server_5; include/stop_slave.inc CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_1; 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; include/rpl_end.inc