1include/rpl_init.inc [topology=1->2, 1->3, 1->4, 1->5] 2connection server_1; 3ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; 4CREATE TABLE t4 (a INT, b INT, PRIMARY KEY (a,b)) Engine=InnoDB; 5CREATE FUNCTION extract_gtid(d VARCHAR(100), s VARCHAR(100)) 6RETURNS VARCHAR(100) DETERMINISTIC 7BEGIN 8SET s= CONCAT(",", s, ","); 9SET s= SUBSTR(s FROM LOCATE(CONCAT(",", d, "-"), s) + 1); 10SET s= SUBSTR(s FROM 1 FOR LOCATE(",", s) - 1); 11RETURN s; 12END| 13connection server_2; 14include/stop_slave.inc 15CHANGE MASTER TO master_use_gtid=current_pos; 16connection server_3; 17include/stop_slave.inc 18CHANGE MASTER TO master_use_gtid=current_pos; 19connection server_4; 20include/stop_slave.inc 21CHANGE MASTER TO master_use_gtid=current_pos; 22connection server_5; 23include/stop_slave.inc 24CHANGE MASTER TO master_use_gtid=current_pos; 25connection server_1; 26SET gtid_domain_id= 1; 27CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; 28INSERT INTO t1 VALUES (1); 29INSERT INTO t1 VALUES (2); 30INSERT INTO t4 VALUES (1, 1); 31INSERT INTO t1 VALUES (3); 32INSERT INTO t1 VALUES (4); 33INSERT INTO t4 VALUES (1, 3); 34SET gtid_domain_id= 2; 35CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; 36INSERT INTO t2 VALUES (1); 37INSERT INTO t2 VALUES (2); 38INSERT INTO t4 VALUES (2, 1); 39INSERT INTO t2 VALUES (3); 40INSERT INTO t2 VALUES (4); 41INSERT INTO t4 VALUES (2, 3); 42SET gtid_domain_id= 3; 43CREATE TABLE t3 (a INT PRIMARY KEY) ENGINE=InnoDB; 44INSERT INTO t3 VALUES (1); 45INSERT INTO t3 VALUES (2); 46INSERT INTO t4 VALUES (3, 1); 47INSERT INTO t3 VALUES (3); 48INSERT INTO t3 VALUES (4); 49INSERT INTO t4 VALUES (3, 3); 50connection server_2; 51START SLAVE UNTIL master_gtid_pos= "1-1-4,2-1-4,3-1-4"; 52connection server_3; 53START SLAVE UNTIL master_gtid_pos= "1-1-1,2-1-4,3-1-7"; 54connection server_4; 55START SLAVE UNTIL master_gtid_pos= "2-1-1,3-1-4,1-1-7"; 56connection server_5; 57START SLAVE UNTIL master_gtid_pos= "3-1-1,1-1-4,2-1-7"; 58connection server_2; 59include/wait_for_slave_to_stop.inc 60SELECT * FROM t1 ORDER BY a; 61a 621 632 64SELECT * FROM t2 ORDER BY a; 65a 661 672 68SELECT * FROM t3 ORDER BY a; 69a 701 712 72SELECT * FROM t4 ORDER BY a,b; 73a b 741 1 752 1 763 1 77connection server_3; 78include/wait_for_slave_to_stop.inc 79SELECT * FROM t1 ORDER BY a; 80a 81SELECT * FROM t2 ORDER BY a; 82a 831 842 85SELECT * FROM t3 ORDER BY a; 86a 871 882 893 904 91SELECT * FROM t4 ORDER BY a,b; 92a b 932 1 943 1 953 3 96connection server_4; 97include/wait_for_slave_to_stop.inc 98SELECT * FROM t1 ORDER BY a; 99a 1001 1012 1023 1034 104SELECT * FROM t2 ORDER BY a; 105a 106SELECT * FROM t3 ORDER BY a; 107a 1081 1092 110SELECT * FROM t4 ORDER BY a,b; 111a b 1121 1 1131 3 1143 1 115connection server_5; 116include/wait_for_slave_to_stop.inc 117SELECT * FROM t1 ORDER BY a; 118a 1191 1202 121SELECT * FROM t2 ORDER BY a; 122a 1231 1242 1253 1264 127SELECT * FROM t3 ORDER BY a; 128a 129SELECT * FROM t4 ORDER BY a,b; 130a b 1311 1 1322 1 1332 3 134*** Now replicate all extra changes from 3,4,5 to 2, in preparation for making 2 the new master. *** 135connection server_3; 136connection server_2; 137CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_3; 138START SLAVE UNTIL master_gtid_pos = "SERVER3_POS"; 139include/wait_for_slave_to_stop.inc 140SELECT * FROM t1 ORDER BY a; 141a 1421 1432 144SELECT * FROM t2 ORDER BY a; 145a 1461 1472 148SELECT * FROM t3 ORDER BY a; 149a 1501 1512 1523 1534 154SELECT * FROM t4 ORDER BY a,b; 155a b 1561 1 1572 1 1583 1 1593 3 160connection server_4; 161connection server_2; 162CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_4; 163START SLAVE UNTIL master_gtid_pos = "SERVER4_POS"; 164include/wait_for_slave_to_stop.inc 165SELECT * FROM t1 ORDER BY a; 166a 1671 1682 1693 1704 171SELECT * FROM t2 ORDER BY a; 172a 1731 1742 175SELECT * FROM t3 ORDER BY a; 176a 1771 1782 1793 1804 181SELECT * FROM t4 ORDER BY a,b; 182a b 1831 1 1841 3 1852 1 1863 1 1873 3 188connection server_5; 189connection server_2; 190CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_5; 191START SLAVE UNTIL master_gtid_pos = "SERVER5_POS"; 192include/wait_for_slave_to_stop.inc 193SELECT * FROM t1 ORDER BY a; 194a 1951 1962 1973 1984 199SELECT * FROM t2 ORDER BY a; 200a 2011 2022 2033 2044 205SELECT * FROM t3 ORDER BY a; 206a 2071 2082 2093 2104 211SELECT * FROM t4 ORDER BY a,b; 212a b 2131 1 2141 3 2152 1 2162 3 2173 1 2183 3 219*** Now make 2 master and point 3,4,5 to the new master 2 220connection server_2; 221SET gtid_domain_id= 1; 222INSERT INTO t1 values (5); 223INSERT INTO t4 values (1,5); 224SET gtid_domain_id= 2; 225INSERT INTO t2 values (5); 226INSERT INTO t4 values (2,5); 227SET gtid_domain_id= 3; 228INSERT INTO t3 values (5); 229INSERT INTO t4 values (3,5); 230connection server_3; 231CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_2; 232include/start_slave.inc 233connection server_4; 234CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_2; 235include/start_slave.inc 236connection server_5; 237CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_2; 238include/start_slave.inc 239connection server_2; 240connection server_3; 241SELECT * FROM t1 ORDER BY a; 242a 2431 2442 2453 2464 2475 248SELECT * FROM t2 ORDER BY a; 249a 2501 2512 2523 2534 2545 255SELECT * FROM t3 ORDER BY a; 256a 2571 2582 2593 2604 2615 262SELECT * FROM t4 ORDER BY a,b; 263a b 2641 1 2651 3 2661 5 2672 1 2682 3 2692 5 2703 1 2713 3 2723 5 273connection server_5; 274SELECT * FROM t1 ORDER BY a; 275a 2761 2772 2783 2794 2805 281SELECT * FROM t2 ORDER BY a; 282a 2831 2842 2853 2864 2875 288SELECT * FROM t3 ORDER BY a; 289a 2901 2912 2923 2934 2945 295SELECT * FROM t4 ORDER BY a,b; 296a b 2971 1 2981 3 2991 5 3002 1 3012 3 3022 5 3033 1 3043 3 3053 5 306connection server_5; 307SELECT * FROM t1 ORDER BY a; 308a 3091 3102 3113 3124 3135 314SELECT * FROM t2 ORDER BY a; 315a 3161 3172 3183 3194 3205 321SELECT * FROM t3 ORDER BY a; 322a 3231 3242 3253 3264 3275 328SELECT * FROM t4 ORDER BY a,b; 329a b 3301 1 3311 3 3321 5 3332 1 3342 3 3352 5 3363 1 3373 3 3383 5 339*** Now let the old master join up as slave. *** 340connection server_1; 341CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_2, 342master_user = "root", master_use_gtid = current_pos; 343include/start_slave.inc 344SELECT * FROM t1 ORDER BY a; 345a 3461 3472 3483 3494 3505 351SELECT * FROM t2 ORDER BY a; 352a 3531 3542 3553 3564 3575 358SELECT * FROM t3 ORDER BY a; 359a 3601 3612 3623 3634 3645 365SELECT * FROM t4 ORDER BY a,b; 366a b 3671 1 3681 3 3691 5 3702 1 3712 3 3722 5 3733 1 3743 3 3753 5 376*** Finally move things back and clean up. *** 377connection server_1; 378include/stop_slave.inc 379RESET SLAVE ALL; 380connection server_2; 381CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_1; 382include/start_slave.inc 383connection server_3; 384include/stop_slave.inc 385CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_1; 386include/start_slave.inc 387connection server_4; 388include/stop_slave.inc 389CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_1; 390include/start_slave.inc 391connection server_5; 392include/stop_slave.inc 393CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_1; 394include/start_slave.inc 395connection server_1; 396SET gtid_domain_id = 0; 397DROP TABLE t1; 398DROP TABLE t2; 399DROP TABLE t3; 400DROP TABLE t4; 401DROP FUNCTION extract_gtid; 402include/rpl_end.inc 403