mysql5.6主從復制第三部分
第三部分測試主服務器掛掉，提升某個從服務器的情況。
繼續用mysql::sandbox來測試。
主服務器(別名black)(server-id:1)安裝在 /home/modify/sandboxes/msb_5_6_10/ 使用5610端口。
從服務器(別名blue)(server-id:2)安裝在 /home/modify/sandboxes/msb_5_6_10_a/ 使用5611端口。
從服務器(別名green)(server-id:3)安裝在 /home/modify/sandboxes/msb_5_6_10_b/ 使用5612端口。
從服務器(別名red)(server-id:4)安裝在 /home/modify/sandboxes/msb_5_6_10_c/ 使用5613端口。
然後再構造一種最特殊的情況：
當主服務器掛掉的時候，blue獲取到的gtid及transaction比green要少， green比red要少，然後要求把green提升為主服務器。
主black 寫入一條數據:
mysql [localhost] {msandbox} (test) > SHOW CREATE TABLE t;
+-------+-------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+-------+-------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`id` INT(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 ROW IN SET (0.00 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO t SET id=1;
Query OK, 1 ROW affected (0.00 sec)
mysql [localhost] {msandbox} (test) > SHOW master STATUS;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | POSITION | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| black-bin.000002 | 6771 | | | 05b47d41-7b10-11e2-9fff-00241db92e69:1-30 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 ROW IN SET (0.00 sec)
從blue:
獲取到gtid:30之後，就停止復制：
mysql [localhost] {msandbox} (test) > SHOW slave STATUS\G
......
......
Retrieved_Gtid_Set: 05b47d41-7b10-11e2-9fff-00241db92e69:16-30
Executed_Gtid_Set: 05b47d41-7b10-11e2-9fff-00241db92e69:1-30
Auto_Position: 1
mysql [localhost] {msandbox} (test) > stop slave;
Query OK, 0 ROWS affected (0.00 sec)
主black 繼續寫入一條數據:
mysql [localhost] {msandbox} (test) > INSERT INTO t SET id=2;
Query OK, 1 ROW affected (1.27 sec)
mysql [localhost] {msandbox} (test) > SHOW master STATUS;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | POSITION | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| black-bin.000002 | 7141 | | | 05b47d41-7b10-11e2-9fff-00241db92e69:1-31 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 ROW IN SET (0.00 sec)
從green:
獲取到gtid:31之後，停止復制：
mysql [localhost] {msandbox} (test) > SHOW slave STATUS\G
.......
.......
Retrieved_Gtid_Set: 05b47d41-7b10-11e2-9fff-00241db92e69:18-31
Executed_Gtid_Set: 05b47d41-7b10-11e2-9fff-00241db92e69:1-31
Auto_Position: 1
mysql [localhost] {msandbox} (test) > stop slave;
Query OK, 0 ROWS affected (0.00 sec)
主black 繼續寫入一條數據:
mysql [localhost] {msandbox} (test) > INSERT INTO t SET id=3;
Query OK, 1 ROW affected (0.00 sec)
mysql [localhost] {msandbox} (test) > SHOW master STATUS;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | POSITION | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| black-bin.000002 | 7511 | | | 05b47d41-7b10-11e2-9fff-00241db92e69:1-32 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 ROW IN SET (0.00 sec)
從red:
獲取到gtid:32之後，停止復制：
mysql [localhost] {msandbox} (test) > SHOW slave STATUS\G
......
......
Retrieved_Gtid_Set: 05b47d41-7b10-11e2-9fff-00241db92e69:20-32
Executed_Gtid_Set: 05b47d41-7b10-11e2-9fff-00241db92e69:1-32
Auto_Position: 1
關閉主black:
[MODIFY@H209 msb_5_6_10]$ ./stop
三台從服務器都START slave:
mysql [localhost] {msandbox} (test) > START slave;
Query OK, 0 ROWS affected, 1 warning (0.00 sec)
現在slave的狀態應該是IO thread在connecting black(5610端口)，但是連接失敗。 SQL thread應該是正常的:
mysql [localhost] {msandbox} (test) > SHOW slave STATUS \G
