假设有一个一主二从的环境,当主库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> select * from test.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 | Japan | 2018-03-04 22:23:28 |
| 6 | UK | 2018-03-04 22:23:28 |
+------+-------+---------------------+
6 rows in set (0.00 sec)
mysql> insert into t_repl(id,name) values(7,'Jacky'),(8,'Tom');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
Slave1库:
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 299 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 173 | Slave has read all relay log; waiting for more updates | NULL |
| 4 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> select * from test.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 | Japan | 2018-03-04 22:23:28 |
| 6 | UK | 2018-03-04 22:23:28 |
| 7 | Jacky | 2018-03-05 18:55:32 |
| 8 | Tom | 2018-03-05 18:55:32 |
+------+-------+---------------------+
8 rows in set (0.00 sec)
mysql>
Slave2库:
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 356 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 291 | Slave has read all relay log; waiting for more updates | NULL |
| 4 | root | localhost | test | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> select * from test.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 | Japan | 2018-03-04 22:23:28 |
| 6 | UK | 2018-03-04 22:23:28 |
| 7 | Jacky | 2018-03-05 18:55:32 |
| 8 | Tom | 2018-03-05 18:55:32 |
+------+-------+---------------------+
8 rows in set (0.00 sec)
mysql>
3、模拟主库Master宕机;
mysql> shutdown;
Query OK, 0 rows affected (0.01 sec)
mysql> system service mysql.server status;
ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql) exists
mysql>
或进入主库,设置只读;
mysql> SET GLOBAL read-only=1;
4、确保从库都执行了relay log的全部更新
在每个从库上执行stop slave io_thread,然后检查show processlist的输出,状态是Slave has read all relay log; waiting for xxx,表示更新都执行完毕;
主库宕掉的结果(主库坏)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 2 | system user | | NULL | Connect | 7500 | Slave has read all relay log; waiting for more updates | NULL |
| 4 | root | localhost | test | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
主从切换的结果(主库好)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> show processlist;
+----+-------------+-----------+------+---------+---------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+---------+--------------------------------------------------------+------------------+
| 5 | system user | | NULL | Connect | 1650914 | Waiting for master to send event | NULL |
| 6 | system user | | NULL | Connect | 0 | Slave has read all relay log; waiting for more updates | NULL |
| 9 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+---------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
5、将从库Slave1重置成主库;
mysql> show master status;
Empty set (0.00 sec)
mysql> stop slave; # 停止从服务
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL read-only=0; # 设置从库可写
Query OK, 0 rows affected (0.00 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)
-- RESET SLAVE ALL是清除从库的同步复制信息、包括连接信息和二进制文件名、位置
-- 从库上执行这个命令后,使用show slave status将不会有输出。
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
-- reset master; 删除所有的binglog日志文件,并将日志索引文件清空,重新开始所有新的日志文件。
mysql> show master status;
Query OK, 0 rows affected (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
6、从库新建主从复制账户backup
mysql> SELECT Host,User FROM mysql.user; # 查看backup用户是否存在,验证密码是否正确。
mysql> GRANT ALL PRIVILEGES ON *.* TO 'backup'@'192.168.1.%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
7、检查从库Slave1的log-bin是否打开,没打开则打开;
vim /etc/my.cnf
log-bin=mysql-bin # 新主库开启binlog日志
#log-slave-updates # 注掉
read-only = 0 # 改为0,可写
重启从库:
systemctl restart mysqld
8、删除从库Slave1上的master.info和relay-log.info,否则下次重启则按照从库启动;
9、在Slave2上,执行stop slave停止从库服务,然后执行change master to重新指向主库slave1,再执行start slave启动从库;
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_host='192.168.1.111';
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql>
10、检查Slave2的状态,发现已经指向新的主库Slave1;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.111
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql111-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 373
Relay_Master_Log_File: mysql111-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 799
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 111
Master_UUID: c8368e4a-1fa4-11e8-aa25-000c299f40a9
Master_Info_File: /usr/local/mysql-5.7.21-linux-glibc2.12-x86_64/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
11、将应用程序指向新的主库Slave1,这样Slave1的所有更新写入到Slave1的Binlog中,从而同步到新的从库Slave2中;
新的主库Slave1:
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 | Japan | 2018-03-04 22:23:28 |
| 6 | UK | 2018-03-04 22:23:28 |
| 7 | Jacky | 2018-03-05 18:55:32 |
| 8 | Tom | 2018-03-05 18:55:32 |
+------+-------+---------------------+
8 rows in set (0.00 sec)
mysql> insert into t_repl(id,name) values(9,'Slave1-->Master');
Query OK, 1 row affected (0.01 sec)
mysql>
新的从库Slave2:
mysql> use test;
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 | Japan | 2018-03-04 22:23:28 |
| 6 | UK | 2018-03-04 22:23:28 |
| 7 | Jacky | 2018-03-05 18:55:32 |
| 8 | Tom | 2018-03-05 18:55:32 |
| 9 | Slave1-->Master | 2018-03-05 21:28:54 |
+------+-----------------+---------------------+
9 rows in set (0.00 sec)
11、最后,如果主库Master修复,则将其重新配置成Slave1的从库;
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000011',MASTER_LOG_POS=106;
mysql> start slave;
mysql> show slave status;
参考
https://blog.csdn.net/alen_liu_sz/article/details/79451581
转载于:https://blog.51cto.com/moerjinrong/2156328