假设有一个一主二从的环境,当主库M出现故障时,需要将其中一个从库S1切换为主库,同时将S2指向新的主库S1,如果可能,需要将故障的主库M修复并重置为新的从库。
搭建一主二从复制环境可参考:MySQL搭建主从复制环境。
下面将演示一主二从复制环境主从库的切换,具体如下:
1、环境信息;
Mater:192.168.1.110
Slave1:192.168.1.111
Slave2:192.168.1.112
2、查看主备库状态;
Master库:
mysql> show processlist;
+----+------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 2 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 3 | repl | 192.168.1.112:49819 | NULL | Binlog Dump | 207 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 4 | repl | 192.168.1.111:53017 | NULL | Binlog Dump | 165 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select *from t_repl;
+------+-------+---------------------+
| id | name | cdate |
+------+-------+---------------------+
| 1 | Alen | 2018-03-04 17:56:57 |
| 2 | Repl | 2018-03-04 20:10:45 |
| 3 | USA | 2018-03-04 22:19:48 |
| 4 | China | 2018-03-04 22:19:48 |
| 5