MySQL 5.6主从复制第三部分[把从服务器提升为主服务器]
MySQL 5.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端口。
MySQL 5.6主从复制第一部分[简介及配置]
MySQL 5.6主从复制第二部分[恢复某一台从服务器]
MySQL 5.6主从复制第三部分[把从服务器提升为主服务器]
MySQL 5.6主从复制第四部分[一些被忽视的操作细节]
MySQL 主从复制事件校验 MySQL Replication Event Checksum
使用pt-table-checksum检查主从复制是否正常
----------------------------------------分割线----------------------------------------
然后再构造一种最特殊的情况:
当主服务器挂掉的时候,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:
场景构造完毕。
|
评论暂时关闭