1500字范文 > MySQL一主二从复制环境切换主从库

MySQL一主二从复制环境切换主从库

时间：2023-11-29 16:33:23

相关推荐

MySQL一主二从复制环境切换主从库

假设有一个一主二从的环境，当主库M出现故障时，需要将其中一个从库S1切换为主库，同时将S2指向新的主库S1，如果可能，需要将故障的主库M修复并重置为新的从库。

搭建一主二从复制环境可参考：MySQL搭建主从复制环境

下面将演示一主二从复制环境主从库的切换，具体如下：

1、环境信息；

Mater： 192.168.1.110Slave1：192.168.1.111Slave2：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 | -03-04 17:56:57 || 2 | Repl | -03-04 20:10:45 || 3 | USA | -03-04 22:19:48 || 4 | China | -03-04 22:19:48 || 5 | Japan | -03-04 22:23:28 || 6 | UK | -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: 0mysql>

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 | -03-04 17:56:57 || 2 | Repl | -03-04 20:10:45 || 3 | USA | -03-04 22:19:48 || 4 | China | -03-04 22:19:48 || 5 | Japan | -03-04 22:23:28 || 6 | UK | -03-04 22:23:28 || 7 | Jacky | -03-05 18:55:32 || 8 | Tom | -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 | -03-04 17:56:57 || 2 | Repl | -03-04 20:10:45 || 3 | USA | -03-04 22:19:48 || 4 | China | -03-04 22:19:48 || 5 | Japan | -03-04 22:23:28 || 6 | UK | -03-04 22:23:28 || 7 | Jacky | -03-05 18:55:32 || 8 | Tom | -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) existsmysql>

或进入主库,设置只读;

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/flog-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 eventMaster_Host: 192.168.1.111Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql111-bin.000002Read_Master_Log_Pos: 154Relay_Log_File: mysql-relay-bin.000003Relay_Log_Pos: 373Relay_Master_Log_File: mysql111-bin.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 154Relay_Log_Space: 799Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 111Master_UUID: c8368e4a-1fa4-11e8-aa25-000c299f40a9Master_Info_File: /usr/local/mysql-5.7.21-linux-glibc2.12-x86_64/data/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select *from t_repl;+------+-------+---------------------+| id | name | cdate|+------+-------+---------------------+| 1 | Alen | -03-04 17:56:57 || 2 | Repl | -03-04 20:10:45 || 3 | USA | -03-04 22:19:48 || 4 | China | -03-04 22:19:48 || 5 | Japan | -03-04 22:23:28 || 6 | UK | -03-04 22:23:28 || 7 | Jacky | -03-05 18:55:32 || 8 | Tom | -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 changedmysql> select *from t_repl;+------+-----------------+---------------------+| id | name | cdate|+------+-----------------+---------------------+| 1 | Alen | -03-04 17:56:57 || 2 | Repl | -03-04 20:10:45 || 3 | USA | -03-04 22:19:48 || 4 | China | -03-04 22:19:48 || 5 | Japan | -03-04 22:23:28 || 6 | UK | -03-04 22:23:28 || 7 | Jacky | -03-05 18:55:32 || 8 | Tom | -03-05 18:55:32 || 9 | Slave1-->Master | -03-05 21:28:54 |+------+-----------------+---------------------+9 rows in set (0.00 sec)