Mysql数据库之MHA高可用架构
MHA(Master High Availability)是由日本人yoshinorim开发的一款成熟且开源的MySQL高可用程序,它实现了MySQL主从环境下MASTER宕机后能够自动进行单次故障转移的功能,其本身由perl语言编写,安装方便使用简单。
在 MySQL 故障切换过程中,MHA 能做到在 0~30 秒之内自动完成数据库的故障切换操作,并且在进行故障切换的过程中,MHA 能在最大程度上保证数据的一致性,以达到真正意义上的高可用。
该软件由两部分组成:MHA Manager(管理节点)和 MHA Node(数据节点)。MHA Manager 可以单独部署在一台独立的机器上管理多个 master-slave 集群,也可以部署在一台 slave 节点上。MHA Node 运行在每台 MySQL 服务器上,MHA Manager 会定时探测集群中的 master 节点,当 master 出现故障时,它可以自动将最新数据的 slave 提升为新的 master,然后将所有其他的 slave 重新指向新的 master。整个故障转移过程对应用程序完全透明。
在 MHA 自动故障切换过程中,MHA 试图从宕机的主服务器上保存二进制日志,最大程度的保证数据的不丢失,但这并不总是可行的。例如,如果主服务器硬件故障或无法通过 ssh 访问,MHA 没法保存二进制日志,只进行故障转移而丢失了最新的数据。使用 MySQL 5.5 的半同步复制,可以大大降低数据丢失的风险。MHA 可以与半同步复制结合起来。如果只有一个 slave 已经收到了最新的二进制日志,MHA 可以将最新的二进制日志应用于其他所有的 slave 服务器上,因此可以保证所有节点的数据一致性。
文章目录
- Mysql数据库之MHA高可用架构
- MHA 的组成
- MHA 的特点
- MHA部署搭建
- 模拟故障
- 故障修复
MHA 的组成
- MHA Node(数据节点)
MHA Node 运行在每台 MySQL 服务器上。
- MHA Manager(管理节点)
MHA Manager 可以单独部署在一台独立的机器上,管理多个 master-slave 集群;也可以部署在一台 slave 节点上。
MHA Manager 会定时探测集群中的 master 节点。当 master 出现故障时,它可以自动将最新数据的 slave 提升为新的 master, 然后将所有其他的 slave 重新指向新的 master。整个故障转移过程对应用程序完全透明。
MHA 的特点
- 自动故障切换过程中,MHA试图从宕机的主服务器上保存二进制日志,最大程度的保证数据不丢失
- 使用半同步复制,可以大大降低数据丢失的风险,如果只有一个slave已经收到了最新的二进制日志,MHA可以将最新的二进制日志应用于其他所有的slave服务器上,因此可以保证所有节点的数据一致性
- 目前MHA支持一主多从架构,最少三台服务,即一主两从
MHA部署搭建
Master 节点服务器:192.168.237.128
MHA manager 节点服务器:192.168.237.129
Slave1 节点服务器:192.168.237.148
Slave2 节点服务器:192.168.237.138
master节点服务器(要安装mysql,我这里已经脚本安装好,具体安装过程见前几章)
[root@localhost opt]# systemctl stop firewalld
[root@localhost opt]# systemctl disable firewalld
[root@localhost opt]# setenforce 0
[root@localhost opt]# hostnamectl set-hostname Mysql1 #更改主机名,方便记忆
[root@localhost opt]# vim /etc/my.cnf #修改配置文件
server-id = 1
log_bin = master-bin
log-slave-updates = true
:wq
[root@localhost opt]# systemctl restart mysqld
[root@localhost opt]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/ #设置软连接
[root@localhost opt]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/ #设置软连接
[root@localhost opt]# mysql -uroot -p #登录
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to 'myslave'@'192.168.237.%' identified by '123'; #赋予权限
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on *.* to 'mha'@'192.168.237.%' identified by 'manager'; #赋予权限
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on *.* to 'mha'@'Mysql1' identified by 'manager'; #赋予权限
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> grant all privileges on *.* to 'mha'@'Mysql2' identified by 'manager'; #赋予权限
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> grant all privileges on *.* to 'mha'@'Mysql3' identified by 'manager'; #赋予权限
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> flush privileges; #刷新权限
Query OK, 0 rows affected (0.01 sec)
mysql> show master status; #在 Master 节点查看二进制文件和同步点
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 1595 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@localhost opt]# yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN #安装依赖
[root@localhost opt]# tar zxvf mha4mysql-node-0.57.tar.gz
[root@localhost opt]# cd mha4mysql-node-0.57
[root@localhost mha4mysql-node-0.57]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node
[root@localhost mha4mysql-node-0.57]# make && make install
Slave1 节点服务器
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
[root@localhost ~]# setenforce 0
[root@localhost ~]# hostnamectl set-hostname Mysql2
[root@localhost ~]# vim /etc/my.cnf
server-id = 2 #三台服务器的 server-id 不能一样
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
:wq
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@localhost ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
[root@localhost ~]# mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to 'myslave'@'192.168.237.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> grant all privileges on *.* to 'mha'@'192.168.237.%' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on *.* to 'mha'@'Mysql1' identified by 'manager';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> grant all privileges on *.* to 'mha'@'Mysql2' identified by 'manager';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> grant all privileges on *.* to 'mha'@'Mysql3' identified by 'manager';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> change master to master_host='192.168.237.128',master_user='myslave',master_password='123',master_log_file='master-bin.000001',master_log_pos=1595; #设置master服务器
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G #查看数据同步结果
Slave_IO_Running: Yes #确保 IO 和 SQL 线程都是 Yes,代表同步正常
Slave_SQL_Running: Yes
mysql> set global read_only=1; #开启只读
Query OK, 0 rows affected (0.00 sec)
[root@localhost opt]# yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN #安装依赖
[root@localhost opt]# tar zxvf mha4mysql-node-0.57.tar.gz
[root@localhost opt]# cd mha4mysql-node-0.57
[root@localhost mha4mysql-node-0.57]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node
[root@localhost mha4mysql-node-0.57]# make && make install
Slave2 节点服务器
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
[root@localhost ~]# setenforce 0
[root@localhost ~]# hostnamectl set-hostname Mysql3
[root@localhost ~]# vim /etc/my.cnf
server-id = 3 #三台服务器的 server-id 不能一样
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
:wq
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@localhost ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
[root@localhost ~]# mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to 'myslave'@'192.168.237.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> grant all privileges on *.* to 'mha'@'192.168.237.%' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on *.* to 'mha'@'Mysql1' identified by 'manager';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> grant all privileges on *.* to 'mha'@'Mysql2' identified by 'manager';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> grant all privileges on *.* to 'mha'@'Mysql3' identified by 'manager';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> change master to master_host='192.168.237.128',master_user='myslave',master_password='123',master_log_file='master-bin.000001',master_log_pos=1595; #设置master服务器
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G #查看数据同步结果
Slave_IO_Running: Yes #确保 IO 和 SQL 线程都是 Yes,代表同步正常
Slave_SQL_Running: Yes
mysql> set global read_only=1; #开启只读
Query OK, 0 rows affected (0.00 sec)
master节点服务器
mysql> create database wpc;
Query OK, 1 row affected (0.01 sec)
mysql> use wpc
Database changed
mysql> create table test(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test(id) values (1);
Query OK, 1 row affected (0.01 sec)
Slave1和Slave2测试
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test_db |
| wpc |
+--------------------+
6 rows in set (0.01 sec)
mysql> select * from wpc.test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
[root@localhost opt]# yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN #安装依赖
[root@localhost opt]# tar zxvf mha4mysql-node-0.57.tar.gz
[root@localhost opt]# cd mha4mysql-node-0.57
[root@localhost mha4mysql-node-0.57]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node
[root@localhost mha4mysql-node-0.57]# make && make install
MHA manager节点服务器
[root@localhost ~]# yum install epel-release --nogpgcheck -y
[root@localhost ~]# yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN
[root@localhost opt]# tar zxvf mha4mysql-node-0.57.tar.gz
[root@localhost opt]# cd mha4mysql-node-0.57
[root@localhost mha4mysql-node-0.57]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node
[root@localhost mha4mysql-node-0.57]# make && make install
[root@localhost ~]#cd /opt
[root@localhost opt]#tar zxvf mha4mysql-manager-0.57.tar.gz
[root@localhost opt]#cd mha4mysql-manager-0.57
[root@localhost mha4mysql-manager-0.57]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
- Time::HiRes ...loaded. (1.9725)
- Config::Tiny ...loaded. (2.14)
- Log::Dispatch ...loaded. (2.41)
- Parallel::ForkManager ...loaded. (1.18)
- MHA::NodeConst ...loaded. (0.57)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::manager
[root@localhost ~]#make && make install
#manager 组件安装后在/usr/local/bin 下面会生成几个工具,主要包括以下几个:
工具 | 说明 |
masterha_check_ssh | 检查 MHA 的 SSH 配置状况 |
masterha_check_repl | 检查 MySQL 复制状况 |
masterha_manger | 启动 manager的脚本 |
masterha_check_status | 检测当前 MHA 运行状态 |
masterha_master_monitor | 检测 master 是否宕机 |
masterha_master_switch | 控制故障转移(自动或者手动) |
masterha_conf_host | 添加或删除配置的 server 信息 |
masterha_stop | 关闭manager |
#node 组件安装后也会在/usr/local/bin 下面会生成几个脚本(这些工具通常由 MHAManager 的脚本触发,无需人为操作)主要如下:
工具 | 说明 |
save_binary_logs | 保存和复制 master 的二进制日志 |
apply_diff_relay_logs | 识别差异的中继日志事件并将其差异的事件应用于其他的 slave |
filter_mysqlbinlog | 去除不必要的 ROLLBACK 事件(MHA 已不再使用这个工具) |
purge_relay_logs | 清除中继日志(不会阻塞 SQL 线程) |
manager 节点上配置到所有数据库节点的无密码认证
[root@localhost ~]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:IdvcUwkcXV3OlWsNAvpBtQgLNEDT72LOVUiTx7B2i+4 root@localhost.localdomain
The key's randomart image is:
+---[RSA 2048]----+
| .+++ o===....=|
| ..o+*+ooo.+o|
| .o*++ +. .=|
| *o*.+ o.|
| ..S.= . |
| o.o . |
| + o. |
| o. |
| E |
+----[SHA256]-----+
[root@localhost ~]# ssh-copy-id 192.168.237.128
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.237.128 (192.168.237.128)' can't be established.
ECDSA key fingerprint is SHA256:NNSJ3YVr1bObxD3BqkJD3cpJlVQOwnCdNEvhOv7qBJA.
ECDSA key fingerprint is MD5:48:48:73:23:80:f8:f0:3b:7b:5d:f8:b6:36:ba:db:f6.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.128's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh '192.168.237.128'"
and check to make sure that only the key(s) you wanted were added.
[root@localhost ~]# ssh-copy-id 192.168.237.138
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.237.138 (192.168.237.138)' can't be established.
ECDSA key fingerprint is SHA256:f0A00lMrl1k5OMRihmFMJ3fMH04+r3n6brOeFAqZ2Tg.
ECDSA key fingerprint is MD5:b8:f6:fb:25:84:f8:f1:21:f7:43:9e:88:79:cb:8b:d2.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.138's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh '192.168.237.138'"
and check to make sure that only the key(s) you wanted were added.
[root@localhost ~]# ssh-copy-id 192.168.237.148
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.237.148 (192.168.237.148)' can't be established.
ECDSA key fingerprint is SHA256:xFapEICWsZOjdlUyVM3guOQECszsnmJgrnA0yyDIgNk.
ECDSA key fingerprint is MD5:ce:2e:10:1c:e9:43:ce:35:8c:66:26:36:d6:11:08:76.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.148's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh '192.168.237.148'"
and check to make sure that only the key(s) you wanted were added.
mysql1 上配置到数据库节点 mysql2 和 mysql3 的无密码认证
[root@localhost mha4mysql-node-0.57]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
ssh-copy-id 192.168.237.148. saved in ssh-copy-id 192.168.237.138
ssh-copy-id 192.168.237.148.pub.n ssh-copy-id 192.168.237.138
The key fingerprint is:
SHA256:k8bFOBGJ/ASGDYvCLFQeqZrvhz7DtoXld50G2naJc04 root@mysql1
The key's randomart image is:
+---[RSA 2048]----+
| ..oo=oooo |
|+ .ooo+ o+ |
|ooo.. oo o |
|.o ..+ |
|.. . S |
|o + + = o |
| o..o o * E |
| B... o B |
| ++= . |
+----[SHA256]-----+
[root@localhost mha4mysql-node-0.57]# ssh-copy-id 192.168.237.138
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.237.138 (192.168.237.138)' can't be established.
ECDSA key fingerprint is SHA256:f0A00lMrl1k5OMRihmFMJ3fMH04+r3n6brOeFAqZ2Tg.
ECDSA key fingerprint is MD5:b8:f6:fb:25:84:f8:f1:21:f7:43:9e:88:79:cb:8b:d2.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.138's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh '192.168.237.138'"
and check to make sure that only the key(s) you wanted were added.
The authenticity of host '192.168.237.148 (192.168.237.148)' can't be established.
ECDSA key fingerprint is SHA256:xFapEICWsZOjdlUyVM3guOQECszsnmJgrnA0yyDIgNk.
ECDSA key fingerprint is MD5:ce:2e:10:1c:e9:43:ce:35:8c:66:26:36:d6:11:08:76.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.148's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh '192.168.237.148'"
and check to make sure that only the key(s) you wanted were added.
mysql2 上配置到数据库节点 mysql1 和 mysql3 的无密码认证
[root@localhost mha4mysql-node-0.57]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:MqWrzYRPdoQVFH8BAxR1wkQqWjcNfwC7Q5f1Sq81pIs root@mysql2
The key's randomart image is:
+---[RSA 2048]----+
| oXXO.+ |
| O.B o |
| o O * + o |
| o O + + = |
| . = S o + |
| . = . . + . |
| . = . E o |
| O . |
| . + |
+----[SHA256]-----+
root@localhost mha4mysql-node-0.57]# ssh-copy-id 192.168.237.138
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.138's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh '192.168.237.138'"
and check to make sure that only the key(s) you wanted were added.
[root@localhost mha4mysql-node-0.57]# ssh-copy-id 192.168.237.128
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.237.128 (192.168.237.128)' can't be established.
ECDSA key fingerprint is SHA256:NNSJ3YVr1bObxD3BqkJD3cpJlVQOwnCdNEvhOv7qBJA.
ECDSA key fingerprint is MD5:48:48:73:23:80:f8:f0:3b:7b:5d:f8:b6:36:ba:db:f6.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.128's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh '192.168.237.128'"
and check to make sure that only the key(s) you wanted were added.
mysql3 上配置到数据库节点 mysql1 和 mysql2 的无密码认证
[root@localhost ~]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:810MExFH7yLmTI8NL2olxtUqF5jULMZ2A6WcFnLRG4Q root@mysql3
The key's randomart image is:
+---[RSA 2048]----+
| ..=X*+o |
| +E+Bo . |
| +==o= .|
| .o ++.. |
| S. .=o+ .|
| o=*+O . |
| ..=* + |
| .. . |
| .. |
+----[SHA256]-----+
[root@localhost ~]# ssh-copy-id 192.168.237.128
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.237.128 (192.168.237.128)' can't be established.
ECDSA key fingerprint is SHA256:NNSJ3YVr1bObxD3BqkJD3cpJlVQOwnCdNEvhOv7qBJA.
ECDSA key fingerprint is MD5:48:48:73:23:80:f8:f0:3b:7b:5d:f8:b6:36:ba:db:f6.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.128's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh '192.168.237.128'"
and check to make sure that only the key(s) you wanted were added.
[root@localhost ~]# ssh-copy-id 192.168.237.138
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.237.138 (192.168.237.138)' can't be established.
ECDSA key fingerprint is SHA256:f0A00lMrl1k5OMRihmFMJ3fMH04+r3n6brOeFAqZ2Tg.
ECDSA key fingerprint is MD5:b8:f6:fb:25:84:f8:f1:21:f7:43:9e:88:79:cb:8b:d2.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.138's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh '192.168.237.138'"
and check to make sure that only the key(s) you wanted were added.
MHA manager节点服务器
[root@localhost ~]# cp -rp /opt/mha4mysql-manager-0.57/samples/scripts /usr/local/bin
[root@localhost ~]# ll /usr/local/bin/scripts/
总用量 32
-rwxr-xr-x. 1 nginx nginx 3648 5月 31 2015 master_ip_failover
-rwxr-xr-x. 1 nginx nginx 9870 5月 31 2015 master_ip_online_change
-rwxr-xr-x. 1 nginx nginx 11867 5月 31 2015 power_manager
-rwxr-xr-x. 1 nginx nginx 1360 5月 31 2015 send_report
工具 | 说明 |
master_ip_failover | 自动切换时 VIP 管理的脚本 |
master_ip_online_change | 在线切换时 vip 的管理 |
power_manager | 故障发生后关闭主机的脚本 |
send_report | 因故障切换后发送报警的脚本 |
[root@localhost ~]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin
[root@localhost ~]# vim /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
#############################添加内容部分#########################################
my $vip = '192.168.237.200'; #指定vip的地址
my $brdc = '192.168.237.255'; #指定vip的广播地址
my $ifdev = 'ens33'; #指定vip绑定的网卡
my $key = '1'; #指定vip绑定的虚拟网卡序列号
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; #代表此变量值为ifconfig ens33:1 192.168.237.200
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down"; #代表此变量值为ifconfig ens33:1 192.168.237.200 down
my $exit_code = 0; #指定退出状态码为0
#my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
#my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";
##################################################################################
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
## A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
:wq
[root@localhost ~]# mkdir /etc/masterha
[root@localhost ~]# cp /opt/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha
[root@localhost ~]# vim /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/masterha/app1/manager.log #manager日志
manager_workdir=/var/log/masterha/app1.log #manager工作目录
master_binlog_dir=/usr/local/mysql/data/ #master保存binlog的位置,这里的路径要与master里配置的binlog的路径一致,以便MHA能找到
master_ip_failover_script=/usr/local/bin/master_ip_failover #设置自动failover时候的切换脚本,也就是上面的那个脚本
master_ip_online_change_script=/usr/local/bin/master_ip_online_change #设置手动切换时候的切换脚本
password=manager #设置mysql中root用户的密码,这个密码是前文中创建监控用户的那个密码
ping_interval=1 #设置监控主库,发送ping包的时间间隔,默认是3秒,尝试三次没有回应的时候自动进行failover
remote_workdir=/tmp #设置远端mysql在发生切换时binlog的保存位置
repl_password=123 #设置复制用户的密码
repl_user=myslave #设置复制用户的用户
report_script=/usr/local/send_report #设置发生切换后发送的报警的脚本
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.237.148 -s 192.168.237.138 #指定检查的从服务器IP地址
shutdown_script="" #设置故障发生后关闭故障主机脚本(该脚本的主要作用是关闭主机防止发生脑裂,这里没有使用)
ssh_user=root #设置ssh的登录用户名
user=mha #设置监控用户root
[server1]
hostname=192.168.237.128
port=3306
[server2]
hostname=192.168.237.148
port=3306
candidate_master=1
#设置为候选master,设置该参数以后,发生主从切换以后将会将此从库提升为主库,即使这个从库不是集群中最新的slave
check_repl_delay=0
#默认情况下如果一个slave落后master 超过100M的relay logs的话,MHA将不会选择该slave作为一个新的master,
因为对于这个slave的恢复需要花费很长时间;通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的
时候将会忽略复制延时,这个参数对于设置了candidate_master=1的主机非常有用,因为这个候选主在切换的过程中一定是新的master
[server3]
hostname=192.168.237.138
port=3306
:wq
master节点服务器
[root@localhost mha4mysql-node-0.57]# /sbin/ifconfig ens33:1 192.168.237.200/24
# manager 节点上测试 mysql 主从连接情况
[root@localhost mha4mysql-manager-0.57]# masterha_check_repl -conf=/etc/masterha/app1.cnf
Thu Jun 9 17:38:17 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 9 17:38:17 2022 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Thu Jun 9 17:38:17 2022 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Thu Jun 9 17:38:17 2022 - [info] MHA::MasterMonitor version 0.57.
Thu Jun 9 17:38:18 2022 - [info] GTID failover mode = 0
Thu Jun 9 17:38:18 2022 - [info] Dead Servers:
Thu Jun 9 17:38:18 2022 - [info] Alive Servers:
Thu Jun 9 17:38:18 2022 - [info] 192.168.237.128(192.168.237.128:3306)
Thu Jun 9 17:38:18 2022 - [info] 192.168.237.148(192.168.237.148:3306)
Thu Jun 9 17:38:18 2022 - [info] 192.168.237.138(192.168.237.138:3306)
Thu Jun 9 17:38:18 2022 - [info] Alive Slaves:
Thu Jun 9 17:38:18 2022 - [info] 192.168.237.148(192.168.237.148:3306) Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Thu Jun 9 17:38:18 2022 - [info] Replicating from 192.168.237.128(192.168.237.128:3306)
Thu Jun 9 17:38:18 2022 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Jun 9 17:38:18 2022 - [info] 192.168.237.138(192.168.237.138:3306) Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Thu Jun 9 17:38:18 2022 - [info] Replicating from 192.168.237.128(192.168.237.128:3306)
Thu Jun 9 17:38:18 2022 - [info] Current Alive Master: 192.168.237.128(192.168.237.128:3306)
Thu Jun 9 17:38:18 2022 - [info] Checking slave configurations..
Thu Jun 9 17:38:18 2022 - [info] read_only=1 is not set on slave 192.168.237.148(192.168.237.148:3306).
Thu Jun 9 17:38:18 2022 - [warning] relay_log_purge=0 is not set on slave 192.168.237.148(192.168.237.148:3306).
Thu Jun 9 17:38:18 2022 - [info] read_only=1 is not set on slave 192.168.237.138(192.168.237.138:3306).
Thu Jun 9 17:38:18 2022 - [warning] relay_log_purge=0 is not set on slave 192.168.237.138(192.168.237.138:3306).
Thu Jun 9 17:38:18 2022 - [info] Checking replication filtering settings..
Thu Jun 9 17:38:18 2022 - [info] binlog_do_db= , binlog_ignore_db=
Thu Jun 9 17:38:18 2022 - [info] Replication filtering check ok.
Thu Jun 9 17:38:18 2022 - [info] GTID (with auto-pos) is not supported
Thu Jun 9 17:38:18 2022 - [info] Starting SSH connection tests..
Thu Jun 9 17:38:20 2022 - [info] All SSH connection tests passed successfully.
Thu Jun 9 17:38:20 2022 - [info] Checking MHA Node version..
Thu Jun 9 17:38:21 2022 - [info] Version check ok.
Thu Jun 9 17:38:21 2022 - [info] Checking SSH publickey authentication settings on the current master..
Thu Jun 9 17:38:21 2022 - [info] HealthCheck: SSH to 192.168.237.128 is reachable.
Thu Jun 9 17:38:21 2022 - [info] Master MHA Node version is 0.57.
Thu Jun 9 17:38:21 2022 - [info] Checking recovery script configurations on 192.168.237.128(192.168.237.128:3306)..
Thu Jun 9 17:38:21 2022 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/usr/local/mysql/data --output_file=/tmp/save_binary_logs_test --manager_version=0.57 --start_file=master-bin.000001
Thu Jun 9 17:38:21 2022 - [info] Connecting to root@192.168.237.128(192.168.237.128:22)..
Creating /tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /usr/local/mysql/data, up to master-bin.000001
Thu Jun 9 17:38:22 2022 - [info] Binlog setting check done.
Thu Jun 9 17:38:22 2022 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Thu Jun 9 17:38:22 2022 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.237.148 --slave_ip=192.168.237.148 --slave_port=3306 --workdir=/tmp --target_version=5.7.20-log --manager_version=0.57 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx
Thu Jun 9 17:38:22 2022 - [info] Connecting to root@192.168.237.148(192.168.237.148:22)..
Checking slave recovery environment settings..
Opening /usr/local/mysql/data/relay-log.info ... ok.
Relay log found at /usr/local/mysql/data, up to relay-log-bin.000004
Temporary relay log file is /usr/local/mysql/data/relay-log-bin.000004
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Jun 9 17:38:22 2022 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.237.138 --slave_ip=192.168.237.138 --slave_port=3306 --workdir=/tmp --target_version=5.7.20-log --manager_version=0.57 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx
Thu Jun 9 17:38:22 2022 - [info] Connecting to root@192.168.237.138(192.168.237.138:22)..
Checking slave recovery environment settings..
Opening /usr/local/mysql/data/relay-log.info ... ok.
Relay log found at /usr/local/mysql/data, up to relay-log-bin.000004
Temporary relay log file is /usr/local/mysql/data/relay-log-bin.000004
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Jun 9 17:38:22 2022 - [info] Slaves settings check done.
Thu Jun 9 17:38:22 2022 - [info]
192.168.237.128(192.168.237.128:3306) (current master)
+--192.168.237.148(192.168.237.148:3306)
+--192.168.237.138(192.168.237.138:3306)
Thu Jun 9 17:38:22 2022 - [info] Checking replication health on 192.168.237.148..
Thu Jun 9 17:38:22 2022 - [info] ok.
Thu Jun 9 17:38:22 2022 - [info] Checking replication health on 192.168.237.138..
Thu Jun 9 17:38:22 2022 - [info] ok.
Thu Jun 9 17:38:22 2022 - [info] Checking master_ip_failover_script status:
Thu Jun 9 17:38:22 2022 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.237.128 --orig_master_ip=192.168.237.128 --orig_master_port=3306
IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.237.200===
Checking the Status of the script.. OK
Thu Jun 9 17:38:22 2022 - [info] OK.
Thu Jun 9 17:38:22 2022 - [warning] shutdown_script is not defined.
Thu Jun 9 17:38:22 2022 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
#后台启动
[root@localhost mha4mysql-manager-0.57]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
[root@localhost mha4mysql-manager-0.57]# ps -ef|grep masterha
root 44915 15765 0 17:40 pts/1 00:00:00 perl /usr/local/bin/masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover
#查看MHA状态
[root@localhost mha4mysql-manager-0.57]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:44915) is running(0:PING_OK), master:192.168.237.128
#查看MHA日志
[root@localhost mha4mysql-manager-0.57]# cat /var/log/masterha/app1/manager.log | grep "current master"
Thu Jun 9 17:40:43 2022 - [info] Checking SSH publickey authentication settings on the current master..
192.168.237.128(192.168.237.128:3306) (current master)
master节点服务器
[root@localhost mha4mysql-node-0.57]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.237.128 netmask 255.255.255.0 broadcast 192.168.237.255
inet6 fe80::af1c:3feb:e960:2d03 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:82:35:12 txqueuelen 1000 (Ethernet)
RX packets 152784 bytes 192828666 (183.8 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 53618 bytes 10649554 (10.1 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.237.200 netmask 255.255.255.0 broadcast 192.168.237.255
ether 00:0c:29:82:35:12 txqueuelen 1000 (Ethernet)
注:若要关闭 manager 服务,可以使用如下命令
masterha_stop --conf=/etc/masterha/app1.cnf
模拟故障
MHA manager节点服务器
[root@localhost mha4mysql-manager-0.57]# tail -f /var/log/masterha/app1/manager.log
IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.237.200===
Checking the Status of the script.. OK
Thu Jun 9 17:40:44 2022 - [info] OK.
Thu Jun 9 17:40:44 2022 - [warning] shutdown_script is not defined.
Thu Jun 9 17:40:44 2022 - [info] Set master ping interval 1 seconds.
Thu Jun 9 17:40:44 2022 - [info] Set secondary check script: /usr/local/bin/masterha_secondary_check -s 192.168.237.148 -s 192.168.237.138
Thu Jun 9 17:40:44 2022 - [info] Starting ping health check on 192.168.237.128(192.168.237.128:3306)..
Thu Jun 9 17:40:44 2022 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
master节点服务器
[root@localhost mha4mysql-node-0.57]# systemctl stop mysqld #关闭mysqld
[root@localhost mha4mysql-node-0.57]# systemctl status mysqld #检查mysqld状态
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since 四 2022-06-09 17:46:28 CST; 27s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 46910 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 46892 ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 46913 (code=exited, status=0/SUCCESS)
6月 09 16:23:33 mysql1 mysqld[46910]: 2022-06-09T08:23:33.817544Z 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
6月 09 16:23:33 mysql1 mysqld[46910]: 2022-06-09T08:23:33.817552Z 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
6月 09 16:23:33 mysql1 mysqld[46910]: 2022-06-09T08:23:33.820068Z 0 [Note] Event Scheduler: Loaded 0 events
6月 09 16:23:33 mysql1 mysqld[46910]: 2022-06-09T08:23:33.820328Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
6月 09 16:23:33 mysql1 mysqld[46910]: Version: '5.7.20-log' socket: '/usr/local/mysql/mysql.sock' port: 3306 Source distribution
6月 09 16:23:33 mysql1 mysqld[46910]: 2022-06-09T08:23:33.820341Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. ...ip this check.
6月 09 16:23:33 mysql1 mysqld[46910]: 2022-06-09T08:23:33.820343Z 0 [Note] Beginning of list of non-natively partitioned tables
6月 09 16:23:33 mysql1 systemd[1]: Started MySQL Server.
6月 09 17:46:17 mysql1 systemd[1]: Stopping MySQL Server...
6月 09 17:46:28 mysql1 systemd[1]: Stopped MySQL Server.
Hint: Some lines were ellipsized, use -l to show in full.
MHA manager节点服务器
----- Failover Report -----
app1: MySQL Master failover 192.168.237.128(192.168.237.128:3306) to 192.168.237.148(192.168.237.148:3306) succeeded
Master 192.168.237.128(192.168.237.128:3306) is down!
Check MHA Manager logs at localhost.localdomain:/var/log/masterha/app1/manager.log for details.
Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.237.128(192.168.237.128:3306)
The latest slave 192.168.237.148(192.168.237.148:3306) has all relay logs for recovery.
Selected 192.168.237.148(192.168.237.148:3306) as a new master.
192.168.237.148(192.168.237.148:3306): OK: Applying all logs succeeded.
192.168.237.148(192.168.237.148:3306): OK: Activated master IP address.
192.168.237.138(192.168.237.138:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.237.138(192.168.237.138:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.237.148(192.168.237.148:3306)
192.168.237.148(192.168.237.148:3306): Resetting slave info succeeded.
Master failover to 192.168.237.148(192.168.237.148:3306) completed successfully.
#可以看到master服务器宕机,slave1顶替成为master服务器
故障切换备选主库的算法
- 一般判断从库的是从(position/GTID)判断优劣,数据有差异,最接近于master的slave,成为备选主。
- 数据一致的情况下,按照配置文件顺序,选择备选主库。
- 设定有权重(candidate_master=1),按照权重强制指定备选主。
默认情况下如果一个slave落后master 100M的relay logs的话,即使有权重,也会失效。
如果check_repl_delay=0的话,即使落后很多日志,也强制选择其为备选主。
故障修复
原master节点服务器
[root@localhost mha4mysql-node-0.57]# systemctl restart mysqld
[root@localhost mha4mysql-node-0.57]# mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.237.148',master_user='myslave',master_password='123',master_log_file='master-bin.000002',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
现master节点服务器(原slave1)
#查看二进制文件和同步点
[root@localhost mha4mysql-node-0.57]# mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
原master节点服务器
#在原master服务器执行同步操作
mysql> change master to master_host='192.168.237.148',master_user='myslave',master_password='123',master_log_file='master-bin.000002',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
manager节点服务器
#manager节点上修改配置文件app1.cnf
[root@localhost mha4mysql-manager-0.57]# vi /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1
master_binlog_dir=/usr/local/mysql/data
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=manager
ping_interval=1
remote_workdir=/tmp
repl_password=123
repl_user=myslave
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.237.128 -s 192.168.237.138
shutdown_script=""
ssh_user=root
user=mha
[server1]
hostname=192.168.237.148
port=3306
[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.237.128
port=3306
[server3]
hostname=192.168.237.138
port=3306
:wq
原master节点服务器
#原master节点服务器设置为只读模式
set global read_only=1;
[root@localhost mha4mysql-manager-0.57]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
[1] 45607 #后台启动
[root@localhost mha4mysql-manager-0.57]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:45607) is running(0:PING_OK), master:192.168.237.148 #检查当前master节点服务器是原slave1节点服务器
总结:配置文件的修改尤其麻烦,需要认真核对,配置文件出现问题,后期的检查会报错,并且提示信息不全面,比较难排错。