文章目录
慢查询
在数据库中，默认没有开启慢查询功能。
超出多少时间，认为超时
mysql> show variables like "slow%";
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/lnmp1-slow.log |
+---------------------+--------------------------------------+
data目录中也没有慢查询日志
[root@lnmp1 ~]# cd /usr/local/mysql/data/
[root@lnmp1 data]# ls
auto.cnf lnmp1-relay-bin-group_replication_applier.000004
binlog.000001 lnmp1-relay-bin-group_replication_applier.index
binlog.000002 lnmp1-relay-bin-group_replication_recovery.000001
binlog.index lnmp1-relay-bin-group_replication_recovery.000002
ca-key.pem lnmp1-relay-bin-group_replication_recovery.000003
ca.pem lnmp1-relay-bin-group_replication_recovery.index
client-cert.pem mysql
client-key.pem mysql.sock
ib_buffer_pool mysql.sock.lock
ibdata1 performance_schema
ib_logfile0 private_key.pem
ib_logfile1 public_key.pem
ibtmp1 server-cert.pem
lnmp1.err server-key.pem
lnmp1.pid sys
lnmp1-relay-bin-group_replication_applier.000002 test
lnmp1-relay-bin-group_replication_applier.000003
建议在生产环境中开启慢查询
在全局中输入开启命令即可
mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)
目录中生成慢查询日志
[root@lnmp1 data]# ls
auto.cnf ibtmp1 mysql
binlog.000001 lnmp1.err mysql.sock
binlog.000002 lnmp1.pid mysql.sock.lock
binlog.index lnmp1-relay-bin-group_replication_applier.000002 performance_schema
ca-key.pem lnmp1-relay-bin-group_replication_applier.000003 private_key.pem
ca.pem lnmp1-relay-bin-group_replication_applier.000004 public_key.pem
client-cert.pem lnmp1-relay-bin-group_replication_applier.index server-cert.pem
client-key.pem lnmp1-relay-bin-group_replication_recovery.000001 server-key.pem
ib_buffer_pool lnmp1-relay-bin-group_replication_recovery.000002 sys
ibdata1 lnmp1-relay-bin-group_replication_recovery.000003 test
ib_logfile0 lnmp1-relay-bin-group_replication_recovery.index
ib_logfile1 lnmp1-slow.log
[root@lnmp1 data]# ll lnmp1-slow.log
-rw-r----- 1 mysql mysql 192 Jul 26 09:46 lnmp1-slow.log
查看慢查询的参数
默认等待10s，若超出10s，认为超时异常，需要记录到日志中
mysql> show variables like "long%";
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
模拟select sleep(10)
- 模拟10s
mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
| 0 |
+-----------+
1 row in set (10.00 sec)
- 查看慢查询日志
[root@lnmp1 data]# cat lnmp1-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.31-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /usr/local/mysql/data/mysql.sock
Time Id Command Argument
# Time: 2021-07-26T01:48:41.308647Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 10.000387 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1627264121;
select sleep(10);
MySQL路由器
部署MySQL路由器
- 在Group的第一个节点上做引导组
现在是OFFLINE模式
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+-----------+-------------+-------------+--------------+
| group_replication_applier | | | NULL | OFFLINE |
+---------------------------+-----------+-------------+-------------+--------------+
1 row in set (0.00 sec)
引导组开启
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
开启组复制
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.35 sec)
现在查看组复制的状态，发现启动成功，状态是ONLINE模式
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 708ab081-e6eb-11eb-984d-525400f25677 | lnmp1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
- 第二个节点和第三个节点，启动组复制
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (3.45 sec)
查看组复制的状态为ONLINE
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 326ae3c3-e6ee-11eb-81b9-525400f6b245 | lnmp2 | 3306 | ONLINE |
| group_replication_applier | 708ab081-e6eb-11eb-984d-525400f25677 | lnmp1 | 3306 | ONLINE |
| group_replication_applier | aa5d0efe-e6f0-11eb-81fb-52540015cbaa | lnmp3 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
-
第1，2，3节点是MySQL组复制集群中的节点
-
准备第四个节点，作为MySQL路由器
-
在主机上安装mysql-router
[root@lnmp4 ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm
- 配置路由器
如果访问的是7001端口，只读，就调度到组复制集群中的任意一个节点上
如果访问的是7002端口，读写，就调度到组复制集群中的任意一个节点上；需要注意的一点是，如果设置的是一主多从的主从复制，不是组复制，那么，在读写模块中，需要设定destinations为集群中的master
[root@lnmp4 ~]# vim /etc/mysqlrouter/mysqlrouter.conf
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.21.1:3306,172.25.21.2:3306,172.25.21.3:3306
routing_strategy = round-robin
[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.21.1:3306,172.25.21.2:3306,172.25.21.3:3306
routing_strategy = first-available
- 启MySQL路由器
[root@lnmp4 ~]# systemctl start mysqlrouter.service
[root@lnmp4 ~]# netstat -antpl | grep :700
tcp 0 0 0.0.0.0:7001 0.0.0.0:* LISTEN 3677/mysqlrouter
tcp 0 0 0.0.0.0:7002 0.0.0.0:* LISTEN 3677/mysqlrouter
mysql-router是MySQL原生配置，所以，对用户完全透明
创建一个MySQL客户端
- 在第一台组复制节点上，设定远程登陆的用户
（后端3台主机mysql都会有）
mysql> grant select on *.* to user1@'%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.20 sec)
mysql> grant all on test.* to user2@'%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> flush privileges; //刷新授权表，这样子三台组复制节点都会有这2个用户
Query OK, 0 rows affected (0.08 sec)
- 客户端连接MySQL数据库
连接成功，可以查看到test库中数据
[root@foundation21 ~]# mysql -h 172.25.21.4 -P 7001 -u user1 -pwestos //只读
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.002 sec)
MySQL [(none)]> select * from test.t1;
+----+------------+
| c1 | c2 |
+----+------------+
| 1 | yao |
| 2 | qian |
| 3 | look |
| 4 | Outlook |
| 5 | Outlook123 |
+----+------------+
5 rows in set (0.335 sec)
- 客户端以读写的身份连接到MySQL路由器
并写入数据
[root@foundation21 ~]# mysql -h 172.25.21.4 -P 7002 -u user2 -pwestos
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.001 sec)
MySQL [(none)]> select * from test.t1;
+----+------------+
| c1 | c2 |
+----+------------+
| 1 | yao |
| 2 | qian |
| 3 | look |
| 4 | Outlook |
| 5 | Outlook123 |
+----+------------+
5 rows in set (0.001 sec)
插入数据
MySQL [(none)]> insert into test.t1 values(6,'Outlook1234');
Query OK, 1 row affected (0.289 sec)
MySQL [(none)]> select * from test.t1;
+----+-------------+
| c1 | c2 |
+----+-------------+
| 1 | yao |
| 2 | qian |
| 3 | look |
| 4 | Outlook |
| 5 | Outlook123 |
| 6 | Outlook1234 |
+----+-------------+
6 rows in set (0.001 sec)
- 客户端连接7002端口，首先通过server4（MySQL路由器）连到server1（第一个组复制节点）
server1（第一个组复制节点）可以查看到客户端写入的数据
mysql> select * from test.t1;
+----+-------------+
| c1 | c2 |
+----+-------------+
| 1 | yao |
| 2 | qian |
| 3 | look |
| 4 | Outlook |
| 5 | Outlook123 |
| 6 | Outlook1234 |
+----+-------------+
也可以在server1上查看到server4连接了server1的3306端口
[root@lnmp1 data]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 3876 mysql 16u IPv6 27400 0t0 TCP *:mysql (LISTEN)
mysqld 3876 mysql 54u IPv6 33407 0t0 TCP server1:mysql->server4:54164 (ESTABLISHED)
server2上查看3306端口，显示没有被server4占用
[root@lnmp2 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 3850 mysql 24u IPv6 24413 0t0 TCP *:mysql (LISTEN)
- 客户端退出连接，再次进入MySQL数据库，这时，server4（MySQL路由器连接的应该是server2）
server2查看3306端口，正在和server4（MySQL路由器连接着）
[root@lnmp2 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 3850 mysql 24u IPv6 24413 0t0 TCP *:mysql (LISTEN)
mysqld 3850 mysql 53u IPv6 31806 0t0 TCP server2:mysql->server4:41528 (ESTABLISHED)
server1的3306端口没有连接
[root@lnmp1 data]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 3876 mysql 16u IPv6 27400 0t0 TCP *:mysql (LISTEN)
【反向代理】
MySQL路由器相当于做了反向代理，客户端是和server4（MySQL路由器）连接的，再由路由器将客户端的请求代理到后端的服务器上
健康检查
【MySQL路由器有健康检查功能】
【轻量化的读写分离】
模拟故障
- stop数据库server1
[root@lnmp1 data]# /etc/init.d/mysqld stop
Shutting down MySQL.............. SUCCESS!
- 客户端检测是否可以成功登陆数据库
依旧可以成功登陆
[root@foundation21 ~]# mysql -h 172.25.21.4 -P 7001 -u user1 -pwestos
MySQL [(none)]> select * from test.t1;
- server2使用lsof命令查看到server4（MySQL路由器）连接到自己的3306端口
[root@lnmp2 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 3850 mysql 24u IPv6 24413 0t0 TCP *:mysql (LISTEN)
mysqld 3850 mysql 46u IPv6 31813 0t0 TCP server2:mysql->server4:41534 (ESTABLISHED)
[root@lnmp3 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 3848 mysql 23u IPv6 30854 0t0 TCP *:mysql (LISTEN)
server1目前被宕掉，所以，没有连接3306端口
[root@lnmp1 data]# lsof -i :3306
MHA高可用
MHA内部的结构可以是一主两从，也可以是一主一从
MHA分为管理端和agent端，管理端可以管控多个组，1个组相当于是启动一个组，agent端是在每个节点上安装
部署的架构是，4个节点，3个节点做一主两从，最后一个节点作为管理节点
搭建一主两从
- stop之前server4上的MySQL路由器服务
[root@lnmp4 ~]# systemctl stop mysqlrouter.service
- 3个节点stop数据库，为了更改数据库的配置
[root@lnmp1 data]# /etc/init.d/mysqld stop
Shutting down MySQL.............. SUCCESS!
[root@lnmp2 ~]# /etc/init.d/mysqld stop
[root@lnmp3 ~]# /etc/init.d/mysqld stop
- 初始化数据库server1
server1作为一主两从的主
清除数据库的数据
[root@lnmp1 data]# pwd
/usr/local/mysql/data
[root@lnmp1 data]# rm -fr *
修改数据库的配置信息：GTID；具备主从功能（updates）；二进制日志
[root@lnmp1 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server_id=1
gtid_mode=ON
enforce-gtid-consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
初始化msyqld
[root@lnmp1 data]# mysqld --initialize --user=mysql
2021-07-26T02:48:43.517446Z 1 [Note] A temporary password is generated for root@localhost: ;pNUS1pR+cjj
启动数据库
[root@lnmp1 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/lnmp1.err'.
.. SUCCESS!
初始化密码
[root@lnmp1 data]# mysql_secure_installation
server1作为一主两从的主
授权远程登陆用户
[root@lnmp1 data]# mysql -pwestos
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 | 710 | | | fd52ce14-edbb-11eb-9562-525400f25677:1-3 |
+---------------+----------+--------------+------------------+------------------------------------------+
mysql> grant replication slave on *.* to repl@'%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.12 sec)
- 初始化数据库server2
清除server2数据库的数据
[root@lnmp2 ~]# cd /usr/local/mysql/data/
[root@lnmp2 data]# rm -fr *
修改配置文件
[root@lnmp2 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server_id=2
gtid_mode=ON
enforce-gtid-consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
初始化
[root@lnmp2 data]# mysqld --initialize --user=mysql
2021-07-26T02:53:56.616476Z 1 [Note] A temporary password is generated for root@localhost: uomds>=wc6sT
启动MySQL
[root@lnmp2 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/lnmp2.err'.
. SUCCESS!
初始化密码
[root@lnmp2 data]# mysql_secure_installation
server2 登陆数据库，设定自己的master，并启动slave模式，查看slave状态
[root@lnmp2 data]# mysql -pwestos
mysql> change master to master_host='172.25.21.1', master_user='repl', master_password='westos',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (1.20 sec)
mysql> start slave;
Query OK, 0 rows affected (0.13 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 993
Relay_Log_File: lnmp2-relay-bin.000002
Relay_Log_Pos: 1200
Relay_Master_Log_File: binlog.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: 993
Relay_Log_Space: 1407
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: 1
Master_UUID: fd52ce14-edbb-11eb-9562-525400f25677
Master_Info_File: mysql.slave_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: fd52ce14-edbb-11eb-9562-525400f25677:1-4
Executed_Gtid_Set: b80dc301-edbc-11eb-97e1-525400f6b245:1-3,
fd52ce14-edbb-11eb-9562-525400f25677:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
- 初始化数据库server3
清除server3数据库的数据
[root@lnmp3 ~]# cd /usr/local/mysql/data/
[root@lnmp3 data]# rm -fr *
修改配置文件
[root@lnmp3 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server_id=3
gtid_mode=ON
enforce-gtid-consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
初始化
[root@lnmp3 data]# mysqld --initialize --user=mysql
2021-07-26T03:00:08.938138Z 1 [Note] A temporary password is generated for root@localhost: QKB<ch!iQ7vY
启动mysql
初始化密码
[root@lnmp3 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/lnmp3.err'.
SUCCESS!
[root@lnmp3 data]# mysql_secure_installation
server3登陆数据库，并设定master，启动slave模式
mysql> change master to master_host='172.25.21.1', master_user='repl', master_password='westos',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.78 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 993
Relay_Log_File: lnmp3-relay-bin.000002
Relay_Log_Pos: 1200
Relay_Master_Log_File: binlog.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: 993
Relay_Log_Space: 1407
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: 1
Master_UUID: fd52ce14-edbb-11eb-9562-525400f25677
Master_Info_File: mysql.slave_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: fd52ce14-edbb-11eb-9562-525400f25677:1-4
Executed_Gtid_Set: 96177eed-edbd-11eb-9e4b-52540015cbaa:1-3,
fd52ce14-edbb-11eb-9562-525400f25677:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
【一主两从完成】
部署管理节点
- server4作为MHA管理节点，需要安装高可用软件
[root@lnmp4 ~]# cd MHA-7/
[root@lnmp4 MHA-7]# yum install -y *.rpm
[root@lnmp4 MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
mha4mysql-manager-0.58.tar.gz perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm perl-Net-Telnet-3.03-19.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@lnmp4 MHA-7]#
- 一主两从集群中的每个节点需要安装高可用的工具
[root@lnmp1 ~]# ll
total 56
-rw-r--r-- 1 root root 1900 Jul 11 21:40 dump.db
-rw-r--r-- 1 root root 36328 Jul 26 11:08 mha4mysql-node-0.58-0.el7.centos.noarch.rpm
-rw-r--r-- 1 root root 14800 Jun 2 12:21 test
[root@lnmp1 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
- 配置管理节点server4
创建一个高可用配置目录
[root@lnmp4 ~]# mkdir /etc/masterha
[root@lnmp4 ~]# cd /etc/masterha/
通过源码包，生成配置文件 > （全局和局部的文件内容导入到同一个文件中）
[root@lnmp4 MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz
[root@lnmp4 MHA-7]# cd mha4mysql-manager-0.58/
[root@lnmp4 mha4mysql-manager-0.58]# ls
AUTHORS COPYING lib MANIFEST README samples tests
bin debian Makefile.PL MANIFEST.SKIP rpm t
[root@lnmp4 mha4mysql-manager-0.58]# cd samples/conf/
[root@lnmp4 conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app.cnf
修改配置文件
删除一个[ server default ]（合并）
server2：预设server1宕掉后，server2接管
server3：不会作为master
[root@lnmp4 conf]# vim /etc/masterha/app.cnf
[server default]
user=root
password=westos //修改root密码，数据库管理员的密码
ssh_user=root //各个节点做免密，
repl_user=repl //用于主从复制的用户
repl_password=westos
master_binlog_dir= /usr/local/mysql/data //二进制目录
remote_workdir=/tmp //远程目录是tmp
secondary_check_script= masterha_secondary_check -s 172.25.21.1 -s 172.25.21.2
ping_interval=3 //每隔3s，ping一下
# master_ip_failover_script= /script/masterha/master_ip_failover //手工切换脚本
# shutdown_script= /script/masterha/power_manager //关机脚本
# report_script= /script/masterha/send_report //报告脚本
# master_ip_online_change_script= /script/masterha/master_ip_online_change //在线故障切换的脚本
manager_workdir=/etc/masterha/app1 //配置工作目录
manager_log=/etc/masterha/app1/manager.log //日志存点
[server1]
hostname=172.25.21.1
[server2]
hostname=172.25.21.2
candidate_master=1
[server3]
hostname=172.25.21.3
no_master=1
创建配置目录的工作目录
[root@lnmp4 conf]# mkdir /etc/masterha/app1
检测免密是否到位
- 3个node节点对server4（管理节点）免密
[root@lnmp4 conf]# ssh-keygen
[root@lnmp4 conf]# ssh-copy-id lnmp1
[root@lnmp4 conf]# ssh-copy-id lnmp2
[root@lnmp4 conf]# ssh-copy-id lnmp3
- 检测免密是否成功
提示，主从节点之间也需要彼此免密
[root@lnmp4 ~]# masterha_check_ssh --conf=/etc/masterha/app.cnf
Mon Jul 26 11:24:07 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 26 11:24:07 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:24:07 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:24:07 2021 - [info] Starting SSH connection tests..
Mon Jul 26 11:24:07 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
Mon Jul 26 11:24:07 2021 - [debug] Connecting via SSH from root@172.25.21.1(172.25.21.1:22) to root@172.25.21.2(172.25.21.2:22)..
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Mon Jul 26 11:24:07 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.25.21.1(172.25.21.1:22) to root@172.25.21.2(172.25.21.2:22) failed! //管理节点提示从server1节点到server2节点需要做免密
Mon Jul 26 11:24:08 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
Mon Jul 26 11:24:07 2021 - [debug] Connecting via SSH from root@172.25.21.2(172.25.21.2:22) to root@172.25.21.1(172.25.21.1:22)..
Warning: Permanently added '172.25.21.1' (ECDSA) to the list of known hosts.
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Mon Jul 26 11:24:08 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.25.21.2(172.25.21.2:22) to root@172.25.21.1(172.25.21.1:22) failed!
Mon Jul 26 11:24:08 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
Mon Jul 26 11:24:08 2021 - [debug] Connecting via SSH from root@172.25.21.3(172.25.21.3:22) to root@172.25.21.1(172.25.21.1:22)..
Warning: Permanently added '172.25.21.1' (ECDSA) to the list of known hosts.
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Mon Jul 26 11:24:08 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.25.21.3(172.25.21.3:22) to root@172.25.21.1(172.25.21.1:22) failed!
SSH Configuration Check Failed!
at /usr/bin/masterha_check_ssh line 44.
- 将管理节点的 ssh目录发送给集群中的所有节点，实现4个节点之间彼此免密
[root@lnmp4 ~]# scp -r .ssh/ lnmp1:
known_hosts 100% 537 677.9KB/s 00:00
id_rsa 100% 1679 2.3MB/s 00:00
id_rsa.pub 100% 392 771.7KB/s 00:00
[root@lnmp4 ~]# scp -r .ssh/ lnmp2:
known_hosts 100% 537 837.4KB/s 00:00
id_rsa 100% 1679 2.6MB/s 00:00
id_rsa.pub 100% 392 628.2KB/s 00:00
[root@lnmp4 ~]# scp -r .ssh/ lnmp3:
known_hosts 100% 537 819.4KB/s 00:00
id_rsa 100% 1679 3.2MB/s 00:00
id_rsa.pub 100% 392 901.1KB/s 00:00
检测ssh是否成功
[root@lnmp4 ~]# masterha_check_ssh --conf=/etc/masterha/app.cnf
Mon Jul 26 11:25:07 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 26 11:25:07 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:25:07 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:25:07 2021 - [info] Starting SSH connection tests..
Mon Jul 26 11:25:07 2021 - [debug]
Mon Jul 26 11:25:07 2021 - [debug] Connecting via SSH from root@172.25.21.1(172.25.21.1:22) to root@172.25.21.2(172.25.21.2:22)..
Mon Jul 26 11:25:07 2021 - [debug] ok.
Mon Jul 26 11:25:07 2021 - [debug] Connecting via SSH from root@172.25.21.1(172.25.21.1:22) to root@172.25.21.3(172.25.21.3:22)..
Mon Jul 26 11:25:07 2021 - [debug] ok.
Mon Jul 26 11:25:08 2021 - [debug]
Mon Jul 26 11:25:07 2021 - [debug] Connecting via SSH from root@172.25.21.2(172.25.21.2:22) to root@172.25.21.1(172.25.21.1:22)..
Mon Jul 26 11:25:07 2021 - [debug] ok.
Mon Jul 26 11:25:07 2021 - [debug] Connecting via SSH from root@172.25.21.2(172.25.21.2:22) to root@172.25.21.3(172.25.21.3:22)..
Mon Jul 26 11:25:08 2021 - [debug] ok.
Mon Jul 26 11:25:08 2021 - [debug]
Mon Jul 26 11:25:08 2021 - [debug] Connecting via SSH from root@172.25.21.3(172.25.21.3:22) to root@172.25.21.1(172.25.21.1:22)..
Mon Jul 26 11:25:08 2021 - [debug] ok.
Mon Jul 26 11:25:08 2021 - [debug] Connecting via SSH from root@172.25.21.3(172.25.21.3:22) to root@172.25.21.2(172.25.21.2:22)..
Mon Jul 26 11:25:08 2021 - [debug] ok.
Mon Jul 26 11:25:08 2021 - [info] All SSH connection tests passed successfully.
检测主从复制是否到位
- 检测主从复制是否到位
保证后端的主从一定已经上线
[root@lnmp4 ~]# masterha_check_repl --conf=/etc/masterha/app.cnf
Mon Jul 26 11:26:32 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 26 11:26:32 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:26:32 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:26:32 2021 - [info] MHA::MasterMonitor version 0.58.
Mon Jul 26 11:26:32 2021 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 172.25.21.1(172.25.21.1:3306) :1045:Access denied for user 'root'@'server4' (using password: YES), but this is not a MySQL crash. Check MySQL server settings. //要给root全部权限
Mon Jul 26 11:26:32 2021 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Mon Jul 26 11:26:32 2021 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 172.25.21.3(172.25.21.3:3306) :1045:Access denied for user 'root'@'server4' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
Mon Jul 26 11:26:32 2021 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Mon Jul 26 11:26:32 2021 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 172.25.21.2(172.25.21.2:3306) :1045:Access denied for user 'root'@'server4' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
Mon Jul 26 11:26:32 2021 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Mon Jul 26 11:26:33 2021 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations
Mon Jul 26 11:26:33 2021 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 329.
Mon Jul 26 11:26:33 2021 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Mon Jul 26 11:26:33 2021 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!
- 管理员只有localhost
给root用户授权
[root@lnmp1 ~]# mysql -pwestos
mysql> grant all on *.* to root@'%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.13 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.35 sec)
- 检查主从复制的状态
[root@lnmp4 ~]# masterha_check_repl --conf=/etc/masterha/app.cnf
Mon Jul 26 11:28:35 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 26 11:28:35 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:28:35 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:28:35 2021 - [info] MHA::MasterMonitor version 0.58.
Mon Jul 26 11:28:36 2021 - [info] GTID failover mode = 1
Mon Jul 26 11:28:36 2021 - [info] Dead Servers:
Mon Jul 26 11:28:36 2021 - [info] Alive Servers:
Mon Jul 26 11:28:36 2021 - [info] 172.25.21.1(172.25.21.1:3306)
Mon Jul 26 11:28:36 2021 - [info] 172.25.21.2(172.25.21.2:3306)
Mon Jul 26 11:28:36 2021 - [info] 172.25.21.3(172.25.21.3:3306)
Mon Jul 26 11:28:36 2021 - [info] Alive Slaves:
Mon Jul 26 11:28:36 2021 - [info] 172.25.21.2(172.25.21.2:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Mon Jul 26 11:28:36 2021 - [info] GTID ON
Mon Jul 26 11:28:36 2021 - [info] Replicating from 172.25.21.1(172.25.21.1:3306)
Mon Jul 26 11:28:36 2021 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Jul 26 11:28:36 2021 - [info] 172.25.21.3(172.25.21.3:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Mon Jul 26 11:28:36 2021 - [info] GTID ON
Mon Jul 26 11:28:36 2021 - [info] Replicating from 172.25.21.1(172.25.21.1:3306)
Mon Jul 26 11:28:36 2021 - [info] Not candidate for the new Master (no_master is set)
Mon Jul 26 11:28:36 2021 - [info] Current Alive Master: 172.25.21.1(172.25.21.1:3306)
Mon Jul 26 11:28:36 2021 - [info] Checking slave configurations..
Mon Jul 26 11:28:36 2021 - [info] read_only=1 is not set on slave 172.25.21.2(172.25.21.2:3306).
Mon Jul 26 11:28:36 2021 - [info] read_only=1 is not set on slave 172.25.21.3(172.25.21.3:3306).
Mon Jul 26 11:28:36 2021 - [info] Checking replication filtering settings..
Mon Jul 26 11:28:36 2021 - [info] binlog_do_db= , binlog_ignore_db=
Mon Jul 26 11:28:36 2021 - [info] Replication filtering check ok.
Mon Jul 26 11:28:36 2021 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Mon Jul 26 11:28:36 2021 - [info] Checking SSH publickey authentication settings on the current master..
Mon Jul 26 11:28:36 2021 - [info] HealthCheck: SSH to 172.25.21.1 is reachable.
Mon Jul 26 11:28:36 2021 - [info]
172.25.21.1(172.25.21.1:3306) (current master)
+--172.25.21.2(172.25.21.2:3306)
+--172.25.21.3(172.25.21.3:3306)
Mon Jul 26 11:28:36 2021 - [info] Checking replication health on 172.25.21.2..
Mon Jul 26 11:28:36 2021 - [info] ok.
Mon Jul 26 11:28:36 2021 - [info] Checking replication health on 172.25.21.3..
Mon Jul 26 11:28:36 2021 - [info] ok.
Mon Jul 26 11:28:36 2021 - [warning] master_ip_failover_script is not defined.
Mon Jul 26 11:28:36 2021 - [warning] shutdown_script is not defined.
Mon Jul 26 11:28:36 2021 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
以上2个脚本的检测过程，必须保证都成功
MHA故障切换过程
手动切换
- 设置新的master为server2
YES
YES
脚本VIP迁移没有定义 YES
此时，master正在运行，没有宕掉
masterha_master_switch --conf=/etc/masterha/app.cnf
--master_state=alive //当前master的状态
--new_master_host=172.25.21.2 //切换的位置
--new_master_port=3306 //切换的新的master的端口
--orig_master_is_new_slave //设定原始master为新的slave
--running_updates_limit=10000 //设定超时时间
[root@lnmp4 ~]# masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=alive --new_master_host=172.25.21.2 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
Mon Jul 26 11:33:40 2021 - [info] MHA::MasterRotate version 0.58.
Mon Jul 26 11:33:40 2021 - [info] Starting online master switch..
Mon Jul 26 11:33:40 2021 - [info]
Mon Jul 26 11:33:40 2021 - [info] * Phase 1: Configuration Check Phase..
Mon Jul 26 11:33:40 2021 - [info]
Mon Jul 26 11:33:40 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 26 11:33:40 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:33:40 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:33:41 2021 - [info] GTID failover mode = 1
Mon Jul 26 11:33:41 2021 - [info] Current Alive Master: 172.25.21.1(172.25.21.1:3306)
Mon Jul 26 11:33:41 2021 - [info] Alive Slaves:
Mon Jul 26 11:33:41 2021 - [info] 172.25.21.2(172.25.21.2:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Mon Jul 26 11:33:41 2021 - [info] GTID ON
Mon Jul 26 11:33:41 2021 - [info] Replicating from 172.25.21.1(172.25.21.1:3306)
Mon Jul 26 11:33:41 2021 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Jul 26 11:33:41 2021 - [info] 172.25.21.3(172.25.21.3:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Mon Jul 26 11:33:41 2021 - [info] GTID ON
Mon Jul 26 11:33:41 2021 - [info] Replicating from 172.25.21.1(172.25.21.1:3306)
Mon Jul 26 11:33:41 2021 - [info] Not candidate for the new Master (no_master is set)
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 172.25.21.1(172.25.21.1:3306)? (YES/no): YES //切换
Mon Jul 26 11:33:43 2021 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Mon Jul 26 11:33:43 2021 - [info] ok.
Mon Jul 26 11:33:43 2021 - [info] Checking MHA is not monitoring or doing failover..
Mon Jul 26 11:33:43 2021 - [info] Checking replication health on 172.25.21.2..
Mon Jul 26 11:33:43 2021 - [info] ok.
Mon Jul 26 11:33:43 2021 - [info] Checking replication health on 172.25.21.3..
Mon Jul 26 11:33:43 2021 - [info] ok.
Mon Jul 26 11:33:43 2021 - [info] 172.25.21.2 can be new master.
Mon Jul 26 11:33:43 2021 - [info]
From:
172.25.21.1(172.25.21.1:3306) (current master)
+--172.25.21.2(172.25.21.2:3306)
+--172.25.21.3(172.25.21.3:3306)
To:
172.25.21.2(172.25.21.2:3306) (new master)
+--172.25.21.3(172.25.21.3:3306)
+--172.25.21.1(172.25.21.1:3306)
Starting master switch from 172.25.21.1(172.25.21.1:3306) to 172.25.21.2(172.25.21.2:3306)? (yes/NO): YES //是否实施
Mon Jul 26 11:33:48 2021 - [info] Checking whether 172.25.21.2(172.25.21.2:3306) is ok for the new master..
Mon Jul 26 11:33:48 2021 - [info] ok.
Mon Jul 26 11:33:48 2021 - [info] 172.25.21.1(172.25.21.1:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Mon Jul 26 11:33:49 2021 - [info] 172.25.21.1(172.25.21.1:3306): Resetting slave pointing to the dummy host.
Mon Jul 26 11:33:49 2021 - [info] ** Phase 1: Configuration Check Phase completed.
Mon Jul 26 11:33:49 2021 - [info]
Mon Jul 26 11:33:49 2021 - [info] * Phase 2: Rejecting updates Phase..
Mon Jul 26 11:33:49 2021 - [info]
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): YES
Mon Jul 26 11:33:54 2021 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Mon Jul 26 11:33:54 2021 - [info] Executing FLUSH TABLES WITH READ LOCK..
Mon Jul 26 11:33:54 2021 - [info] ok.
Mon Jul 26 11:33:54 2021 - [info] Orig master binlog:pos is binlog.000002:1425.
Mon Jul 26 11:33:54 2021 - [info] Waiting to execute all relay logs on 172.25.21.2(172.25.21.2:3306)..
Mon Jul 26 11:33:54 2021 - [info] master_pos_wait(binlog.000002:1425) completed on 172.25.21.2(172.25.21.2:3306). Executed 0 events.
Mon Jul 26 11:33:54 2021 - [info] done.
Mon Jul 26 11:33:54 2021 - [info] Getting new master's binlog name and position..
Mon Jul 26 11:33:54 2021 - [info] binlog.000002:1981
Mon Jul 26 11:33:54 2021 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.25.21.2', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Mon Jul 26 11:33:54 2021 - [info]
Mon Jul 26 11:33:54 2021 - [info] * Switching slaves in parallel..
Mon Jul 26 11:33:54 2021 - [info]
Mon Jul 26 11:33:54 2021 - [info] -- Slave switch on host 172.25.21.3(172.25.21.3:3306) started, pid: 14074
Mon Jul 26 11:33:54 2021 - [info]
Mon Jul 26 11:33:55 2021 - [info] Log messages from 172.25.21.3 ...
Mon Jul 26 11:33:55 2021 - [info]
Mon Jul 26 11:33:54 2021 - [info] Waiting to execute all relay logs on 172.25.21.3(172.25.21.3:3306)..
Mon Jul 26 11:33:54 2021 - [info] master_pos_wait(binlog.000002:1425) completed on 172.25.21.3(172.25.21.3:3306). Executed 0 events.
Mon Jul 26 11:33:54 2021 - [info] done.
Mon Jul 26 11:33:54 2021 - [info] Resetting slave 172.25.21.3(172.25.21.3:3306) and starting replication from the new master 172.25.21.2(172.25.21.2:3306)..
Mon Jul 26 11:33:55 2021 - [info] Executed CHANGE MASTER.
Mon Jul 26 11:33:55 2021 - [info] Slave started.
Mon Jul 26 11:33:55 2021 - [info] End of log messages from 172.25.21.3 ...
Mon Jul 26 11:33:55 2021 - [info]
Mon Jul 26 11:33:55 2021 - [info] -- Slave switch on host 172.25.21.3(172.25.21.3:3306) succeeded.
Mon Jul 26 11:33:55 2021 - [info] Unlocking all tables on the orig master:
Mon Jul 26 11:33:55 2021 - [info] Executing UNLOCK TABLES..
Mon Jul 26 11:33:55 2021 - [info] ok.
Mon Jul 26 11:33:55 2021 - [info] Starting orig master as a new slave..
Mon Jul 26 11:33:55 2021 - [info] Resetting slave 172.25.21.1(172.25.21.1:3306) and starting replication from the new master 172.25.21.2(172.25.21.2:3306)..
Mon Jul 26 11:33:56 2021 - [info] Executed CHANGE MASTER.
Mon Jul 26 11:33:56 2021 - [info] Slave started.
Mon Jul 26 11:33:56 2021 - [info] All new slave servers switched successfully.
Mon Jul 26 11:33:56 2021 - [info]
Mon Jul 26 11:33:56 2021 - [info] * Phase 5: New master cleanup phase..
Mon Jul 26 11:33:56 2021 - [info]
Mon Jul 26 11:33:57 2021 - [info] 172.25.21.2: Resetting slave info succeeded.
Mon Jul 26 11:33:57 2021 - [info] Switching master to 172.25.21.2(172.25.21.2:3306) completed successfully.
- 验证切换结果
server1查看自己的状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 1981
Relay_Log_File: lnmp1-relay-bin.000002
Relay_Log_Pos: 961
Relay_Master_Log_File: binlog.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: 1981
Relay_Log_Space: 1168
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: 2
Master_UUID: b80dc301-edbc-11eb-97e1-525400f6b245
Master_Info_File: mysql.slave_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: b80dc301-edbc-11eb-97e1-525400f6b245:1-3
Executed_Gtid_Set: b80dc301-edbc-11eb-97e1-525400f6b245:1-3,
fd52ce14-edbb-11eb-9562-525400f25677:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
server2 查看状态，发现自己现在是新的master
mysql> show slave status\G;
Empty set (0.00 sec)
ERROR:
No query specified
mysql> show master status\G;
*************************** 1. row ***************************
File: binlog.000002
Position: 1981
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: b80dc301-edbc-11eb-97e1-525400f6b245:1-3,
fd52ce14-edbb-11eb-9562-525400f25677:1-6
1 row in set (0.00 sec)
ERROR:
No query specified
模拟故障
- 宕掉master（server2）
[root@lnmp2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
- 管理节点手动切换
masterha_master_switch
--master_state=dead //当前master的状态是dead
--conf=/etc/masterha/app.cnf //配置文件
--dead_master_host=172.25.21.2 //宕掉主机的IP
--dead_master_port=3306 //宕掉主机的端口
--new_master_host=172.25.21.1 //新master的IP
--new_master_port=3306
--ignore_last_failover //忽略之前的错误，手工不需要关注这个参数
YES
YES
YES
[root@lnmp4 ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app.cnf --dead_master_host=172.25.21.2 --dead_master_port=3306 --new_master_host=172.25.21.1 --new_master_port=3306 --ignore_last_failover
--dead_master_ip=<dead_master_ip> is not set. Using 172.25.21.2.
Mon Jul 26 11:38:36 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 26 11:38:36 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:38:36 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:38:36 2021 - [info] MHA::MasterFailover version 0.58.
Mon Jul 26 11:38:36 2021 - [info] Starting master failover.
Mon Jul 26 11:38:36 2021 - [info]
Mon Jul 26 11:38:36 2021 - [info] * Phase 1: Configuration Check Phase..
Mon Jul 26 11:38:36 2021 - [info]
Mon Jul 26 11:38:37 2021 - [info] GTID failover mode = 1
Mon Jul 26 11:38:37 2021 - [info] Dead Servers:
Mon Jul 26 11:38:37 2021 - [info] 172.25.21.2(172.25.21.2:3306)
Mon Jul 26 11:38:37 2021 - [info] Checking master reachability via MySQL(double check)...
Mon Jul 26 11:38:37 2021 - [info] ok.
Mon Jul 26 11:38:37 2021 - [info] Alive Servers:
Mon Jul 26 11:38:37 2021 - [info] 172.25.21.1(172.25.21.1:3306)
Mon Jul 26 11:38:37 2021 - [info] 172.25.21.3(172.25.21.3:3306)
Mon Jul 26 11:38:37 2021 - [info] Alive Slaves:
Mon Jul 26 11:38:37 2021 - [info] 172.25.21.1(172.25.21.1:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Mon Jul 26 11:38:37 2021 - [info] GTID ON
Mon Jul 26 11:38:37 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Mon Jul 26 11:38:37 2021 - [info] 172.25.21.3(172.25.21.3:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Mon Jul 26 11:38:37 2021 - [info] GTID ON
Mon Jul 26 11:38:37 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Mon Jul 26 11:38:37 2021 - [info] Not candidate for the new Master (no_master is set)
Master 172.25.21.2(172.25.21.2:3306) is dead. Proceed? (yes/NO): YES
Mon Jul 26 11:38:52 2021 - [info] Starting GTID based failover.
Mon Jul 26 11:38:52 2021 - [info]
Mon Jul 26 11:38:52 2021 - [info] ** Phase 1: Configuration Check Phase completed.
Mon Jul 26 11:38:52 2021 - [info]
Mon Jul 26 11:38:52 2021 - [info] * Phase 2: Dead Master Shutdown Phase..
Mon Jul 26 11:38:52 2021 - [info]
Mon Jul 26 11:38:53 2021 - [info] HealthCheck: SSH to 172.25.21.2 is reachable.
Mon Jul 26 11:38:53 2021 - [info] Forcing shutdown so that applications never connect to the current master..
Mon Jul 26 11:38:53 2021 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.
Mon Jul 26 11:38:53 2021 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Mon Jul 26 11:38:53 2021 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Mon Jul 26 11:38:53 2021 - [info]
Mon Jul 26 11:38:53 2021 - [info] * Phase 3: Master Recovery Phase..
Mon Jul 26 11:38:53 2021 - [info]
Mon Jul 26 11:38:53 2021 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Mon Jul 26 11:38:53 2021 - [info]
Mon Jul 26 11:38:53 2021 - [info] The latest binary log file/position on all slaves is binlog.000002:1981
Mon Jul 26 11:38:53 2021 - [info] Retrieved Gtid Set: b80dc301-edbc-11eb-97e1-525400f6b245:1-3
Mon Jul 26 11:38:53 2021 - [info] Latest slaves (Slaves that received relay log files to the latest):
Mon Jul 26 11:38:53 2021 - [info] 172.25.21.1(172.25.21.1:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Mon Jul 26 11:38:53 2021 - [info] GTID ON
Mon Jul 26 11:38:53 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Mon Jul 26 11:38:53 2021 - [info] 172.25.21.3(172.25.21.3:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Mon Jul 26 11:38:53 2021 - [info] GTID ON
Mon Jul 26 11:38:53 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Mon Jul 26 11:38:53 2021 - [info] Not candidate for the new Master (no_master is set)
Mon Jul 26 11:38:53 2021 - [info] The oldest binary log file/position on all slaves is binlog.000002:1981
Mon Jul 26 11:38:53 2021 - [info] Retrieved Gtid Set: b80dc301-edbc-11eb-97e1-525400f6b245:1-3
Mon Jul 26 11:38:53 2021 - [info] Oldest slaves:
Mon Jul 26 11:38:53 2021 - [info] 172.25.21.1(172.25.21.1:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Mon Jul 26 11:38:53 2021 - [info] GTID ON
Mon Jul 26 11:38:53 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Mon Jul 26 11:38:53 2021 - [info] 172.25.21.3(172.25.21.3:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Mon Jul 26 11:38:53 2021 - [info] GTID ON
Mon Jul 26 11:38:53 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Mon Jul 26 11:38:53 2021 - [info] Not candidate for the new Master (no_master is set)
Mon Jul 26 11:38:53 2021 - [info]
Mon Jul 26 11:38:53 2021 - [info] * Phase 3.3: Determining New Master Phase..
Mon Jul 26 11:38:53 2021 - [info]
Mon Jul 26 11:38:53 2021 - [info] 172.25.21.1 can be new master.
Mon Jul 26 11:38:53 2021 - [info] New master is 172.25.21.1(172.25.21.1:3306)
Mon Jul 26 11:38:53 2021 - [info] Starting master failover..
Mon Jul 26 11:38:53 2021 - [info]
From:
172.25.21.2(172.25.21.2:3306) (current master)
+--172.25.21.1(172.25.21.1:3306)
+--172.25.21.3(172.25.21.3:3306)
To:
172.25.21.1(172.25.21.1:3306) (new master)
+--172.25.21.3(172.25.21.3:3306)
Starting master switch from 172.25.21.2(172.25.21.2:3306) to 172.25.21.1(172.25.21.1:3306)? (yes/NO): YES
Mon Jul 26 11:38:58 2021 - [info] New master decided manually is 172.25.21.1(172.25.21.1:3306)
Mon Jul 26 11:38:58 2021 - [info]
Mon Jul 26 11:38:58 2021 - [info] * Phase 3.3: New Master Recovery Phase..
Mon Jul 26 11:38:58 2021 - [info]
Mon Jul 26 11:38:58 2021 - [info] Waiting all logs to be applied..
Mon Jul 26 11:38:58 2021 - [info] done.
Mon Jul 26 11:38:58 2021 - [info] Getting new master's binlog name and position..
Mon Jul 26 11:38:58 2021 - [info] binlog.000002:1981
Mon Jul 26 11:38:58 2021 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.25.21.1', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Mon Jul 26 11:38:58 2021 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: binlog.000002, 1981, b80dc301-edbc-11eb-97e1-525400f6b245:1-3,
fd52ce14-edbb-11eb-9562-525400f25677:1-6
Mon Jul 26 11:38:58 2021 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address.
Mon Jul 26 11:38:58 2021 - [info] Setting read_only=0 on 172.25.21.1(172.25.21.1:3306)..
Mon Jul 26 11:38:58 2021 - [info] ok.
Mon Jul 26 11:38:58 2021 - [info] ** Finished master recovery successfully.
Mon Jul 26 11:38:58 2021 - [info] * Phase 3: Master Recovery Phase completed.
Mon Jul 26 11:38:58 2021 - [info]
Mon Jul 26 11:38:58 2021 - [info] * Phase 4: Slaves Recovery Phase..
Mon Jul 26 11:38:58 2021 - [info]
Mon Jul 26 11:38:58 2021 - [info]
Mon Jul 26 11:38:58 2021 - [info] * Phase 4.1: Starting Slaves in parallel..
Mon Jul 26 11:38:58 2021 - [info]
Mon Jul 26 11:38:58 2021 - [info] -- Slave recovery on host 172.25.21.3(172.25.21.3:3306) started, pid: 14101. Check tmp log /etc/masterha/app1/172.25.21.3_3306_20210726113836.log if it takes time..
Mon Jul 26 11:38:59 2021 - [info]
Mon Jul 26 11:38:59 2021 - [info] Log messages from 172.25.21.3 ...
Mon Jul 26 11:38:59 2021 - [info]
Mon Jul 26 11:38:58 2021 - [info] Resetting slave 172.25.21.3(172.25.21.3:3306) and starting replication from the new master 172.25.21.1(172.25.21.1:3306)..
Mon Jul 26 11:38:58 2021 - [info] Executed CHANGE MASTER.
Mon Jul 26 11:38:59 2021 - [info] Slave started.
Mon Jul 26 11:38:59 2021 - [info] gtid_wait(b80dc301-edbc-11eb-97e1-525400f6b245:1-3,
fd52ce14-edbb-11eb-9562-525400f25677:1-6) completed on 172.25.21.3(172.25.21.3:3306). Executed 0 events.
Mon Jul 26 11:38:59 2021 - [info] End of log messages from 172.25.21.3.
Mon Jul 26 11:38:59 2021 - [info] -- Slave on host 172.25.21.3(172.25.21.3:3306) started.
Mon Jul 26 11:38:59 2021 - [info] All new slave servers recovered successfully.
Mon Jul 26 11:38:59 2021 - [info]
Mon Jul 26 11:38:59 2021 - [info] * Phase 5: New master cleanup phase..
Mon Jul 26 11:38:59 2021 - [info]
Mon Jul 26 11:38:59 2021 - [info] Resetting slave info on the new master..
Mon Jul 26 11:38:59 2021 - [info] 172.25.21.1: Resetting slave info succeeded.
Mon Jul 26 11:38:59 2021 - [info] Master failover to 172.25.21.1(172.25.21.1:3306) completed successfully.
Mon Jul 26 11:38:59 2021 - [info]
----- Failover Report -----
app: MySQL Master failover 172.25.21.2(172.25.21.2:3306) to 172.25.21.1(172.25.21.1:3306) succeeded
Master 172.25.21.2(172.25.21.2:3306) is down!
Check MHA Manager logs at lnmp4 for details.
Started manual(interactive) failover.
Selected 172.25.21.1(172.25.21.1:3306) as a new master.
172.25.21.1(172.25.21.1:3306): OK: Applying all logs succeeded.
172.25.21.3(172.25.21.3:3306): OK: Slave started, replicating from 172.25.21.1(172.25.21.1:3306)
172.25.21.1(172.25.21.1:3306): Resetting slave info succeeded.
Master failover to 172.25.21.1(172.25.21.1:3306) completed successfully.
在做了故障切换后，会在配置目录app1中，生成锁定文件（阻止做频繁的故障切换）
所以，需要加上–ignore_last_failover（或者删除锁定文件）
[root@lnmp4 ~]# cd /etc/masterha/app1/
[root@lnmp4 app1]# ls
app.failover.complete
- server1查看状态
server1现在是master
mysql> show slave status\G;
Empty set (0.01 sec)
ERROR:
No query specified
mysql> show master status\G;
*************************** 1. row ***************************
File: binlog.000002
Position: 1981
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: b80dc301-edbc-11eb-97e1-525400f6b245:1-3,
fd52ce14-edbb-11eb-9562-525400f25677:1-6
1 row in set (0.00 sec)
ERROR:
No query specified
- server3依旧是slave
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 1981
Relay_Log_File: lnmp3-relay-bin.000002
Relay_Log_Pos: 405
Relay_Master_Log_File: binlog.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: 1981
Relay_Log_Space: 612
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: 1
Master_UUID: fd52ce14-edbb-11eb-9562-525400f25677
Master_Info_File: mysql.slave_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: 96177eed-edbd-11eb-9e4b-52540015cbaa:1-3,
b80dc301-edbc-11eb-97e1-525400f6b245:1-3,
fd52ce14-edbb-11eb-9562-525400f25677:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
- server2之前是故障的master，现在变成slave，
需要重新设置server2的主从复制
设置server2的master
开启slave模式
mysql> change master to master_host='172.25.21.1', master_user='repl', master_password='westos', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.75 sec)
mysql> start slave;
Query OK, 0 rows affected (0.13 sec)
自动切换
- 管理节点检测ssh状态
[root@lnmp4 app1]# masterha_check_ssh --conf=/etc/masterha/app.cnf
- 管理节点检测主从复制状态
[root@lnmp4 app1]# masterha_check_repl --conf=/etc/masterha/app.cnf
- 删除app1中的锁定文件
[root@lnmp4 app1]# pwd
/etc/masterha/app1
[root@lnmp4 app1]# ls
app.failover.complete
[root@lnmp4 app1]# rm -f app.failover.complete
- 启动的命令（打入后台）
[root@lnmp4 app1]# masterha_manager --conf=/etc/masterha/app.cnf &
[1] 14142
[root@lnmp4 app1]# Mon Jul 26 11:45:39 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 26 11:45:39 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:45:39 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
模拟故障
- 模拟故障，停止server1（master）的MySQL
[root@lnmp1 ~]# /etc/init.d/mysqld stop
故障出现，管理节点激活后台的切换命令
[root@lnmp4 app1]# Mon Jul 26 11:45:39 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 26 11:45:39 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:45:39 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:46:48 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 26 11:46:48 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Mon Jul 26 11:46:48 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
[1]+ Done masterha_manager --conf=/etc/masterha/app.cnf
查看到配置目录中生成了锁定文件
[root@lnmp4 ~]# cd /etc/masterha/app1/
[root@lnmp4 app1]# ls
app.failover.complete manager.log
- 恢复server1
启动MySQL，设定master，启动slave模式
mysql> change master to master_host='172.25.21.2', master_user='repl', master_password='westos', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.56 sec)
mysql> start slave;
Query OK, 0 rows affected (0.31 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 234
Relay_Log_File: lnmp1-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: binlog.000003
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: 234
Relay_Log_Space: 568
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: 2
Master_UUID: b80dc301-edbc-11eb-97e1-525400f6b245
Master_Info_File: mysql.slave_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: b80dc301-edbc-11eb-97e1-525400f6b245:1-3,
fd52ce14-edbb-11eb-9562-525400f25677:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
- 删除锁定文件，不要阻止后续的切换
MHA故障切换过程：
配置文件检测
宕机的master处理（摘除VIP）
比对dead master和新的slave中中继日志，保存到server4的MHA Manager目录下
添加脚本
CD：维护MySQL：邮件报警平台
- 管理节点添加master_ip_脚本到/usr/local/bin（记得添加执行权限）
[root@foundation21 mysql]# scp master_ip_failover master_ip_online_change root@172.25.21.4:/usr/local/bin
[root@lnmp4 ~]# cd /usr/local/bin/
[root@lnmp4 bin]# ll
total 8
-rwxr-xr-x 1 root root 2156 Jul 26 11:51 master_ip_failover
-rwxr-xr-x 1 root root 3813 Jul 26 11:51 master_ip_online_change
- 在脚本中设置VIP
[root@lnmp4 bin]# vim master_ip_failover
my $vip = '172.25.21.100/24';
[root@lnmp4 bin]# vim master_ip_online_change
my $vip = ‘172.25.21.100/24’;
vim 自动切换
- 修改管理节点的配置文件
[root@lnmp4 bin]# vim /etc/masterha/app.cnf
[server default]
user=root
password=westos
ssh_user=root
repl_user=repl
repl_password=westos
master_binlog_dir= /usr/local/mysql/data
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 172.25.21.1 -s 172.25.21.2
ping_interval=3
master_ip_failover_script= /usr/local/bin/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
master_ip_online_change_script= /usr/local/bin/master_ip_online_change
manager_workdir=/etc/masterha/app1
manager_log=/etc/masterha/app1/manager.log
[server1]
hostname=172.25.21.1
[server2]
hostname=172.25.21.2
candidate_master=1
[server3]
hostname=172.25.21.3
no_master=1
【当前master是server2】
mysql> show slave status\G;
Empty set (0.00 sec)
ERROR:
No query specified
热切换（master在线）
- 在master（server2）上添加一个临时IP
[root@lnmp2 ~]# ip addr add 172.25.21.100/24 dev eth0
- 客户端测试，使用VIP进行远程登陆
登陆成功
[root@foundation21 mysql]# mysql -h 172.25.21.100 -uroot -pwestos
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.31-log Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show master status\G;
*************************** 1. row ***************************
File: binlog.000003
Position: 234
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: b80dc301-edbc-11eb-97e1-525400f6b245:1-3,
fd52ce14-edbb-11eb-9562-525400f25677:1-6
1 row in set (0.000 sec)
ERROR: No query specified
- 删除app1配置目录中的锁定文件
进行切换
[root@lnmp4 bin]# cd /etc/masterha/app1/
[root@lnmp4 app1]# ls
app.failover.complete manager.log
[root@lnmp4 app1]# rm -f app.failover.complete
[root@lnmp4 app1]# ls
manager.log
将server2由master转换为slave
server1作为新的master
[root@lnmp4 app1]# masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=alive --new_master_host=172.25.21.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
Mon Jul 26 12:02:24 2021 - [info] MHA::MasterRotate version 0.58.
Mon Jul 26 12:02:24 2021 - [info] Starting online master switch..
Mon Jul 26 12:02:24 2021 - [info]
Mon Jul 26 12:02:24 2021 - [info] * Phase 1: Configuration Check Phase..
Mon Jul 26 12:02:24 2021 - [info]
Mon Jul 26 12:02:24 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 26 12:02:24 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Mon Jul 26 12:02:24 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
Mon Jul 26 12:02:25 2021 - [info] GTID failover mode = 1
Mon Jul 26 12:02:25 2021 - [info] Current Alive Master: 172.25.21.2(172.25.21.2:3306)
Mon Jul 26 12:02:25 2021 - [info] Alive Slaves:
Mon Jul 26 12:02:25 2021 - [info] 172.25.21.1(172.25.21.1:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Mon Jul 26 12:02:25 2021 - [info] GTID ON
Mon Jul 26 12:02:25 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Mon Jul 26 12:02:25 2021 - [info] 172.25.21.3(172.25.21.3:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Mon Jul 26 12:02:25 2021 - [info] GTID ON
Mon Jul 26 12:02:25 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Mon Jul 26 12:02:25 2021 - [info] Not candidate for the new Master (no_master is set)
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 172.25.21.2(172.25.21.2:3306)? (YES/no): YES
Mon Jul 26 12:02:27 2021 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Mon Jul 26 12:02:27 2021 - [info] ok.
Mon Jul 26 12:02:27 2021 - [info] Checking MHA is not monitoring or doing failover..
Mon Jul 26 12:02:27 2021 - [info] Checking replication health on 172.25.21.1..
Mon Jul 26 12:02:27 2021 - [info] ok.
Mon Jul 26 12:02:27 2021 - [info] Checking replication health on 172.25.21.3..
Mon Jul 26 12:02:27 2021 - [info] ok.
Mon Jul 26 12:02:27 2021 - [info] 172.25.21.1 can be new master.
Mon Jul 26 12:02:27 2021 - [info]
From:
172.25.21.2(172.25.21.2:3306) (current master)
+--172.25.21.1(172.25.21.1:3306)
+--172.25.21.3(172.25.21.3:3306)
To:
172.25.21.1(172.25.21.1:3306) (new master)
+--172.25.21.3(172.25.21.3:3306)
+--172.25.21.2(172.25.21.2:3306)
Starting master switch from 172.25.21.2(172.25.21.2:3306) to 172.25.21.1(172.25.21.1:3306)? (yes/NO): YES
Mon Jul 26 12:02:29 2021 - [info] Checking whether 172.25.21.1(172.25.21.1:3306) is ok for the new master..
Mon Jul 26 12:02:29 2021 - [info] ok.
Mon Jul 26 12:02:29 2021 - [info] 172.25.21.2(172.25.21.2:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Mon Jul 26 12:02:30 2021 - [info] 172.25.21.2(172.25.21.2:3306): Resetting slave pointing to the dummy host.
Mon Jul 26 12:02:30 2021 - [info] ** Phase 1: Configuration Check Phase completed.
Mon Jul 26 12:02:30 2021 - [info]
Mon Jul 26 12:02:30 2021 - [info] * Phase 2: Rejecting updates Phase..
Mon Jul 26 12:02:30 2021 - [info]
Mon Jul 26 12:02:30 2021 - [info] Executing master ip online change script to disable write on the current master:
Mon Jul 26 12:02:30 2021 - [info] /usr/local/bin/master_ip_online_change --command=stop --orig_master_host=172.25.21.2 --orig_master_ip=172.25.21.2 --orig_master_port=3306 --orig_master_user='root' --new_master_host=172.25.21.1 --new_master_ip=172.25.21.1 --new_master_port=3306 --new_master_user='root' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx
***************************************************************
Disabling the VIP - 172.25.21.100/24 on old master: 172.25.21.2
***************************************************************
RTNETLINK answers: Cannot assign requested address
Mon Jul 26 12:02:30 2021 - [info] ok.
Mon Jul 26 12:02:30 2021 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Mon Jul 26 12:02:30 2021 - [info] Executing FLUSH TABLES WITH READ LOCK..
Mon Jul 26 12:02:30 2021 - [info] ok.
Mon Jul 26 12:02:30 2021 - [info] Orig master binlog:pos is binlog.000003:234.
Mon Jul 26 12:02:30 2021 - [info] Waiting to execute all relay logs on 172.25.21.1(172.25.21.1:3306)..
Mon Jul 26 12:02:30 2021 - [info] master_pos_wait(binlog.000003:234) completed on 172.25.21.1(172.25.21.1:3306). Executed 0 events.
Mon Jul 26 12:02:30 2021 - [info] done.
Mon Jul 26 12:02:30 2021 - [info] Getting new master's binlog name and position..
Mon Jul 26 12:02:30 2021 - [info] binlog.000003:234
Mon Jul 26 12:02:30 2021 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.25.21.1', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Mon Jul 26 12:02:30 2021 - [info] Executing master ip online change script to allow write on the new master:
Mon Jul 26 12:02:30 2021 - [info] /usr/local/bin/master_ip_online_change --command=start --orig_master_host=172.25.21.2 --orig_master_ip=172.25.21.2 --orig_master_port=3306 --orig_master_user='root' --new_master_host=172.25.21.1 --new_master_ip=172.25.21.1 --new_master_port=3306 --new_master_user='root' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx
***************************************************************
Enabling the VIP - 172.25.21.100/24 on new master: 172.25.21.1
***************************************************************
Mon Jul 26 12:02:30 2021 - [info] ok.
Mon Jul 26 12:02:30 2021 - [info]
Mon Jul 26 12:02:30 2021 - [info] * Switching slaves in parallel..
Mon Jul 26 12:02:30 2021 - [info]
Mon Jul 26 12:02:30 2021 - [info] -- Slave switch on host 172.25.21.3(172.25.21.3:3306) started, pid: 14341
Mon Jul 26 12:02:30 2021 - [info]
Mon Jul 26 12:02:31 2021 - [info] Log messages from 172.25.21.3 ...
Mon Jul 26 12:02:31 2021 - [info]
Mon Jul 26 12:02:30 2021 - [info] Waiting to execute all relay logs on 172.25.21.3(172.25.21.3:3306)..
Mon Jul 26 12:02:30 2021 - [info] master_pos_wait(binlog.000003:234) completed on 172.25.21.3(172.25.21.3:3306). Executed 0 events.
Mon Jul 26 12:02:30 2021 - [info] done.
Mon Jul 26 12:02:30 2021 - [info] Resetting slave 172.25.21.3(172.25.21.3:3306) and starting replication from the new master 172.25.21.1(172.25.21.1:3306)..
Mon Jul 26 12:02:31 2021 - [info] Executed CHANGE MASTER.
Mon Jul 26 12:02:31 2021 - [info] Slave started.
Mon Jul 26 12:02:31 2021 - [info] End of log messages from 172.25.21.3 ...
Mon Jul 26 12:02:31 2021 - [info]
Mon Jul 26 12:02:31 2021 - [info] -- Slave switch on host 172.25.21.3(172.25.21.3:3306) succeeded.
Mon Jul 26 12:02:31 2021 - [info] Unlocking all tables on the orig master:
Mon Jul 26 12:02:31 2021 - [info] Executing UNLOCK TABLES..
Mon Jul 26 12:02:31 2021 - [info] ok.
Mon Jul 26 12:02:31 2021 - [info] Starting orig master as a new slave..
Mon Jul 26 12:02:31 2021 - [info] Resetting slave 172.25.21.2(172.25.21.2:3306) and starting replication from the new master 172.25.21.1(172.25.21.1:3306)..
Mon Jul 26 12:02:32 2021 - [info] Executed CHANGE MASTER.
Mon Jul 26 12:02:32 2021 - [info] Slave started.
Mon Jul 26 12:02:32 2021 - [info] All new slave servers switched successfully.
Mon Jul 26 12:02:32 2021 - [info]
Mon Jul 26 12:02:32 2021 - [info] * Phase 5: New master cleanup phase..
Mon Jul 26 12:02:32 2021 - [info]
Mon Jul 26 12:02:33 2021 - [info] 172.25.21.1: Resetting slave info succeeded.
Mon Jul 26 12:02:33 2021 - [info] Switching master to 172.25.21.1(172.25.21.1:3306) completed successfully.
- 客户端测试，VIP运行成功
[root@foundation21 mysql]# mysql -h 172.25.21.100 -uroot -pwestos
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.31-log Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show master status\G;
*************************** 1. row ***************************
File: binlog.000003
Position: 234
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: b80dc301-edbc-11eb-97e1-525400f6b245:1-3,
fd52ce14-edbb-11eb-9562-525400f25677:1-6
1 row in set (0.000 sec)
ERROR: No query specified
- 查看之前在server2（旧的master）上设置的临时VIP
发现没有，因为这个VIP已经漂浮到新的master（server1）上
[root@lnmp2 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 52:54:00:f6:b2:45 brd ff:ff:ff:ff:ff:ff
inet 172.25.21.2/24 brd 172.25.21.255 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::5054:ff:fef6:b245/64 scope link
valid_lft forever preferred_lft forever
查看server1的VIP
[root@lnmp1 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 52:54:00:f2:56:77 brd ff:ff:ff:ff:ff:ff
inet 172.25.21.1/24 brd 172.25.21.255 scope global eth0
valid_lft forever preferred_lft forever
inet 172.25.21.100/24 scope global secondary eth0
valid_lft forever preferred_lft forever
inet6 fe80::5054:ff:fef2:5677/64 scope link
valid_lft forever preferred_lft forever
- 管理节点查看配置目录app1，发现没有生成锁定文件
手工切换不会产生锁定文件
[root@lnmp4 app1]# ls
manager.log
自动切换指令
- 设定自动切换的命令（打入后台）
（当触发切换后，该后台脚本进程就会自动退出，避免重复切换，默认自动退出）
[root@lnmp4 app1]# masterha_manager --conf=/etc/masterha/app.cnf &
[1] 14345
[root@lnmp4 app1]# Mon Jul 26 12:06:20 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 26 12:06:20 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Mon Jul 26 12:06:20 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
模拟故障
- 模拟故障：master（server1）停止数据库
[root@lnmp1 ~]# /etc/init.d/mysqld stop
-
客户端依旧可以正常连接数据库
-
查看当前的master是server2
可以在server2的主机上查看到VIP
[root@lnmp2 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 52:54:00:f6:b2:45 brd ff:ff:ff:ff:ff:ff
inet 172.25.21.2/24 brd 172.25.21.255 scope global eth0
valid_lft forever preferred_lft forever
inet 172.25.21.100/24 scope global secondary eth0
valid_lft forever preferred_lft forever
inet6 fe80::5054:ff:fef6:b245/64 scope link
valid_lft forever preferred_lft forever
- 恢复server1
设定master，启动slave模式
mysql> change master to master_host='172.25.21.2', master_user='repl', master_password='westos', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.44 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 234
Relay_Log_File: lnmp1-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: binlog.000003
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: 234
Relay_Log_Space: 568
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: 2
Master_UUID: b80dc301-edbc-11eb-97e1-525400f6b245
Master_Info_File: mysql.slave_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: b80dc301-edbc-11eb-97e1-525400f6b245:1-3,
fd52ce14-edbb-11eb-9562-525400f25677:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
- 查看配置目录app1下，生成锁定文件
（下次切换时，需要删除这个文件，要么在命令后加上 --ignore_master的参数）
（否则，该文件就会阻止切换）
[root@lnmp4 app1]# Mon Jul 26 12:06:20 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 26 12:06:20 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Mon Jul 26 12:06:20 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
Mon Jul 26 12:06:55 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 26 12:06:55 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Mon Jul 26 12:06:55 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
[1]+ Done masterha_manager --conf=/etc/masterha/app.cnf
[root@lnmp4 app1]# ls
app.failover.complete manager.log
569
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
