mysql主从切换—一主两从架构
原创
©著作权归作者所有：来自51CTO博客作者Liujun_Deng的原创作品，请联系作者获取转载授权，否则将追究法律责任
文档课题：mysql主从切换.
数据库：mysql 8.0.27
系统：rhel 7.3
安装包：mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz
系统架构：一主两从
环境介绍：
1、应用场景
生产环境中，当主库A发生故障时，需要将从库B切换成主库，如下模拟主库Master宕机，slave01升级为Master，slave02保持不变，并实现slave01和slave02数据同步.
2、模拟异常
主库A 192.168.133.111将mysql进程关闭，模拟异常.
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> system service mysql status;
ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql) exists
3、确认中继日志
确保从库已执行完relay log中的全部更新，查看从库状态是否为has read all relay log.
3.1、查Slave01
mysql> stop slave io_thread;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show processlist \G
*************************** 1. row ***************************
Id: 5
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 9918
State: Waiting on empty queue
Info: NULL
*************************** 2. row ***************************
Id: 8
User: root
Host: localhost
db: booksDB
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 3. row ***************************
Id: 16
User: system user
Host:
db: NULL
Command: Query
Time: 6812
State: Replica has read all relay log; waiting for more updates
Info: NULL
*************************** 4. row ***************************
Id: 17
User: system user
Host:
db: NULL
Command: Connect
Time: 6812
State: Waiting for an event from Coordinator
Info: NULL
*************************** 5. row ***************************
Id: 18
User: system user
Host:
db: NULL
Command: Connect
Time: 8302
State: Waiting for an event from Coordinator
Info: NULL
*************************** 6. row ***************************
Id: 19
User: system user
Host:
db: NULL
Command: Connect
Time: 8302
State: Waiting for an event from Coordinator
Info: NULL
*************************** 7. row ***************************
Id: 20
User: system user
Host:
db: NULL
Command: Connect
Time: 8302
State: Waiting for an event from Coordinator
Info: NULL
7 rows in set (0.00 sec)
3.2、查slave02
mysql> stop slave io_thread;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show processlist;
+----+-----------------+-----------+---------+---------+-------+----------------------------------------------------------+-------
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+---------+---------+-------+----------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 11573 | Waiting on empty queue | NULL |
| 8 | root | localhost | booksDB | Query | 0 | init | show processlist |
| 10 | system user | | NULL | Query | 8516 | Replica has read all relay log; waiting for more updates | NULL |
| 11 | system user | | NULL | Connect | 8516 | Waiting for an event from Coordinator | NULL |
| 12 | system user | | NULL | Connect | 8629 | Waiting for an event from Coordinator | NULL |
| 13 | system user | | NULL | Connect | 8629 | Waiting for an event from Coordinator | NULL |
| 14 | system user | | NULL | Connect | 8629 | Waiting for an event from Coordinator | NULL |
+----+-----------------+-----------+---------+---------+-------+----------------------------------------------------------+-------
7 rows in set (0.00 sec)
4、开始切换
停止从库B slave服务，然后执行reset master，将其重置成主库.
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
说明：从库B配置文件中需开启binlog，否则无法执行reset maste.
5、切换后操作
5.1、建复制用户
从库B切换为主库后，在新主库B上添加具有replication权限的用户repluser.
mysql> create user 'repluser'@'%' identified by 'repluser';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave,replication client on *.* to 'repluser'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show master status \G
*************************** 1. row ***************************
File: slave_log.000001
Position: 732
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
5.2、删新主库中继日志
删新主库B上的relay-log，否则下次重启会按照从库启动.
对配置文件my.cnf如下参数进行注释.
#relay-log=mysql-slave1-log
#read_only=ON
#relay_log_index=relay-log.index
5.3、从库C配置
在从库C上配置复制参数，具体操作如下.
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> change master to master_host='192.168.133.112';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'CHANGE MASTER' is deprecated and will be removed in a future release. Please use CHANGE REPLICATION SOURCE instead |
| Warning | 1287 | 'MASTER_HOST' is deprecated and will be removed in a future release. Please use SOURCE_HOST instead |
+---------+------+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead |
+---------+------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show replica status \G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.133.112
Source_User: repluser
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: slave_log.000001
Read_Source_Log_Pos: 732
Relay_Log_File: mysql-slave1-log.000002
Relay_Log_Pos: 947
Relay_Source_Log_File: slave_log.000001
Replica_IO_Running: Yes
Replica_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_Source_Log_Pos: 732
Relay_Log_Space: 1157
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 2
Source_UUID: ed0ed633-fd2c-11ed-af12-0050563cca0d
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 1
Network_Namespace:
1 row in set (0.00 sec)
说明：如上所示，slave_io_running和slave_sql_running均为yes，主从切换成功.
6、数据验证
6.1、新主库B删数据
主库B执行如下：
mysql> use booksDB
Database changed
mysql> show tables;
+-------------------+
| Tables_in_booksDB |
+-------------------+
| books |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from books;
+-------+---------------------------+-----------+
| bk_id | bk_title | copyright |
+-------+---------------------------+-----------+
| 11026 | Guide to MySQL 5.7 | 2008 |
| 11028 | Learning C++ | 2005 |
| 11033 | Study Html | 2011 |
| 11035 | How to use php | 2003 |
| 11041 | Inside VC++ | 2011 |
| 11069 | MYSQL professional | 2009 |
| 11072 | Teach yourself javascript | 2005 |
| 11078 | Learning MYSQL | 2010 |
+-------+---------------------------+-----------+
8 rows in set (0.00 sec)
mysql> delete from books where bk_id=11026;
Query OK, 1 row affected (0.00 sec)
mysql> select * from books;
+-------+---------------------------+-----------+
| bk_id | bk_title | copyright |
+-------+---------------------------+-----------+
| 11028 | Learning C++ | 2005 |
| 11033 | Study Html | 2011 |
| 11035 | How to use php | 2003 |
| 11041 | Inside VC++ | 2011 |
| 11069 | MYSQL professional | 2009 |
| 11072 | Teach yourself javascript | 2005 |
| 11078 | Learning MYSQL | 2010 |
+-------+---------------------------+-----------+
7 rows in set (0.00 sec)
6.2、从库C验证
mysql> use booksDB
Database changed
mysql> select * from books;
+-------+---------------------------+-----------+
| bk_id | bk_title | copyright |
+-------+---------------------------+-----------+
| 11028 | Learning C++ | 2005 |
| 11033 | Study Html | 2011 |
| 11035 | How to use php | 2003 |
| 11041 | Inside VC++ | 2011 |
| 11069 | MYSQL professional | 2009 |
| 11072 | Teach yourself javascript | 2005 |
| 11078 | Learning MYSQL | 2010 |
+-------+---------------------------+-----------+
7 rows in set (0.00 sec)
说明：切换后，新主库B和从库C数据实时同步.
参考网址：https://www.cnblogs.com/linjiqin/p/11208948.html
