第三部分测试主服务器挂掉,提升某个从服务器的情况。
主服务器(别名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端口。
相关阅读:
MySQL 主从复制事件校验 MySQL Replication Event Checksum http://www.linuxidc.com/Linux/2013-04/82716.htm
----------------------------------------分割线----------------------------------------
然后再构造一种最特殊的情况:
当主服务器挂掉的时候,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
*************************** 1. ROW ***************************
Slave_IO_State: Connecting TO master
Master_Host: 127.0.0.1
Master_User: repl_user
Master_Port: 5610
Connect_Retry: 60
Master_Log_File: black-bin.000001
Read_Master_Log_Pos: 1809
Relay_Log_File: mysql_sandbox5613-relay-bin.000002
Relay_Log_Pos: 1382
Relay_Master_Log_File: black-bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
......
......
Last_IO_Errno: 2003
Last_IO_Error: error connecting TO master 'repl_user@127.0.0.1:5610' - retry-TIME: 60 retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:
场景构造完毕。