mysql+mha+keepalived实现MHA架构

来源:互联网 发布:少女前线数据获取失败 编辑:程序博客网 时间:2024/03/29 17:14

简介:

MHA(Master High Availability)目前在MySQL高可用方面是一个相对成熟的解决方案,它由日本DeNA公司youshimaton(现就职于Facebook公司)开发,是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在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服务器上,因此可以保证所有节点的数据一致性.

配置环境准备:


1节点主+2节点从
[root@master ~]# vi /etc/hosts
127.0.0.1   localhost localhost.localdomain
192.168.174.128 master
192.168.174.129 slave1
192.168.174.130 slave2
192.168.174.128 manager


搭建主从复制架构:
具体见http://blog.csdn.net/crpp0902/article/details/76136766
这里具体过程不再描述。






mha分为两个部分,一个是mha node(所有节点安装),一个是mha manager(管理节点安装)


安装mha node:
[root@master ~]# yum install perl-DBD-MySQL -y
Loaded plugins: aliases, changelog, downloadonly, fastestmirror, kabi, presto, refresh-packagekit, security, tmprepo, verify,
              : versionlock
Loading support for CentOS kernel ABI
Loading mirror speeds from cached hostfile
 * base: centos.ustc.edu.cn
 * epel: mirror.premi.st
 * extras: ftp.sjtu.edu.cn
 * updates: ftp.sjtu.edu.cn
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Running transaction check
---> Package mysql-libs.x86_64 0:5.1.73-8.el6_8 will be installed
--> Finished Dependency Resolution
Dependencies Resolved


====================================================================================================================================
 Package                            Arch                       Version                               Repository                Size
====================================================================================================================================
Installing:
 perl-DBD-MySQL                     x86_64                     4.013-3.el6                           base                     134 k
Installing for dependencies:
 mysql-libs                         x86_64                     5.1.73-8.el6_8                        base                     1.2 M


Transaction Summary
====================================================================================================================================
Install       2 Package(s)


Total download size: 1.4 M
Installed size: 4.4 M
Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 1.4 M
(1/2): mysql-libs-5.1.73-8.el6_8.x86_64.rpm                                                                  | 1.2 MB     00:01     
(2/2): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm                                                                 | 134 kB     00:00     
------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                               750 kB/s | 1.4 MB     00:01     
warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID c105b9de: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
Importing GPG key 0xC105B9DE:
 Userid : CentOS-6 Key (CentOS 6 Official Signing Key) <centos-6-key@centos.org>
 Package: centos-release-6-5.el6.centos.11.1.x86_64 (@anaconda-CentOS-201311272149.x86_64/6.5)
 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
Running rpm_check_debug
Running Transaction Test
Transaction Check Error:
  file /usr/share/mysql/charsets/Index.xml from install of mysql-libs-5.1.73-8.el6_8.x86_64 conflicts with file from package MySQL-server-5.6.37-1.el6.x86_64
 。。。。。。
Error Summary
-------------


解决:
安装
[root@master mysql]# rpm -ivh MySQL-shared-*
warning: MySQL-shared-5.6.37-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:MySQL-shared-compat    ########################################### [ 50%]
   2:MySQL-shared           ########################################### [100%]


root@master mysql]# yum install perl-DBD-MySQL -y                                                                                         
...................
Complete!


[root@master mha4mysql-node-0.56]# perl Makefile.PL 
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.609)
- DBD::mysql ...loaded. (4.013)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node


[root@master mha4mysql-node-0.56]# make && make install
cp lib/MHA/BinlogPosFinderElp.pm blib/lib/MHA/BinlogPosFinderElp.pm
..........................


node产生如下文件:
[root@slave1 mha4mysql-node-0.56]# cd /usr/local/bin
[root@slave1 bin]# ls
apply_diff_relay_logs  filter_mysqlbinlog  purge_relay_logs  save_binary_logs
[root@slave1 bin]# ls -alt
total 52
drwxr-xr-x.  2 root root  4096 Aug  2 00:05 .
-r-xr-xr-x.  1 root root  8261 Aug  2 00:05 purge_relay_logs
-r-xr-xr-x.  1 root root  7525 Aug  2 00:05 save_binary_logs
-r-xr-xr-x.  1 root root 16367 Aug  2 00:05 apply_diff_relay_logs
-r-xr-xr-x.  1 root root  4807 Aug  2 00:05 filter_mysqlbinlog
drwxr-xr-x. 12 root root  4096 Jul 31 19:38 ..






安装mha manager:
[root@master yum.repos.d]# yum install  perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y
Loaded plugins: aliases, changelog, downloadonly, fastestmirror, kabi, presto, refresh-packagekit, security, tmprepo, verify,
              : versionlock
Loading support for CentOS kernel ABI
Loading mirror speeds from cached hostfile
 * base: centos.ustc.edu.cn
 * epel: mirrors.ustc.edu.cn
 * extras: ftp.sjtu.edu.cn
 * updates: ftp.sjtu.edu.cn
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package perl-Config-Tiny.noarch 0:2.12-7.1.el6 will be installed
---> Package perl-Log-Dispatch.noarch 0:2.27-1.el6 will be installed
--> Processing Dependency: perl(Mail::Sendmail) for package: perl-Log-Dispatch-2.27-1.el6.noarch
--> Processing Dependency: perl(Mail::Sender) for package: perl-Log-Dispatch-2.27-1.el6.noarch
--> Processing Dependency: perl(Mail::Send) for package: perl-Log-Dispatch-2.27-1.el6.noarch
--> Processing Dependency: perl(MIME::Lite) for package: perl-Log-Dispatch-2.27-1.el6.noarch
---> Package perl-Parallel-ForkManager.noarch 0:0.7.9-1.el6 will be installed
---> Package perl-Time-HiRes.x86_64 4:1.9721-136.el6 will be updated
---> Package perl-Time-HiRes.x86_64 4:1.9721-144.el6 will be an update
。。。。。。。。


[root@master mha4mysql-manager-0.56]# perl Makefile.PL 
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies...
[Core Features]
- DBI                   ...loaded. (1.609)
- DBD::mysql            ...loaded. (4.013)
- Time::HiRes           ...loaded. (1.9721)
- Config::Tiny          ...loaded. (2.12)
- Log::Dispatch         ...loaded. (2.26)
- Parallel::ForkManager ...loaded. (0.7.9)
- MHA::NodeConst        ...loaded. (0.56)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::manager


[root@master mha4mysql-manager-0.56]# make && make install
cp lib/MHA/ManagerUtil.pm blib/lib/MHA/ManagerUtil.pm
cp lib/MHA/HealthCheck.pm blib/lib/MHA/HealthCheck.pm
cp lib/MHA/Config.pm blib/lib/MHA/Config.pm
cp lib/MHA/ManagerConst.pm blib/lib/MHA/ManagerConst.pm
cp lib/MHA/ServerManager.pm blib/lib/MHA/ServerManager.pm
cp lib/MHA/ManagerAdmin.pm blib/lib/MHA/ManagerAdmin.pm
cp lib/MHA/FileStatus.pm blib/lib/MHA/FileStatus.pm
cp lib/MHA/ManagerAdminWrapper.pm blib/lib/MHA/ManagerAdminWrapper.pm
cp lib/MHA/MasterFailover.pm blib/lib/MHA/MasterFailover.pm
cp lib/MHA/MasterRotate.pm blib/lib/MHA/MasterRotate.pm
cp lib/MHA/MasterMonitor.pm blib/lib/MHA/MasterMonitor.pm
cp lib/MHA/SSHCheck.pm blib/lib/MHA/SSHCheck.pm
cp lib/MHA/Server.pm blib/lib/MHA/Server.pm
cp lib/MHA/DBHelper.pm blib/lib/MHA/DBHelper.pm
cp bin/masterha_stop blib/script/masterha_stop
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_stop
cp bin/masterha_conf_host blib/script/masterha_conf_host
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_conf_host
cp bin/masterha_check_repl blib/script/masterha_check_repl
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_check_repl
cp bin/masterha_check_status blib/script/masterha_check_status
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_check_status
cp bin/masterha_master_monitor blib/script/masterha_master_monitor
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_master_monitor
cp bin/masterha_check_ssh blib/script/masterha_check_ssh
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_check_ssh
cp bin/masterha_master_switch blib/script/masterha_master_switch
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_master_switch
cp bin/masterha_secondary_check blib/script/masterha_secondary_check
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_secondary_check
cp bin/masterha_manager blib/script/masterha_manager
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_manager
Manifying blib/man1/masterha_stop.1
Manifying blib/man1/masterha_conf_host.1
Manifying blib/man1/masterha_check_repl.1
Manifying blib/man1/masterha_check_status.1
Manifying blib/man1/masterha_master_monitor.1
Manifying blib/man1/masterha_check_ssh.1
Manifying blib/man1/masterha_master_switch.1
Manifying blib/man1/masterha_secondary_check.1
Manifying blib/man1/masterha_manager.1
Installing /usr/local/share/perl5/MHA/ManagerConst.pm
Installing /usr/local/share/perl5/MHA/FileStatus.pm
Installing /usr/local/share/perl5/MHA/MasterMonitor.pm
Installing /usr/local/share/perl5/MHA/MasterFailover.pm
Installing /usr/local/share/perl5/MHA/SSHCheck.pm
Installing /usr/local/share/perl5/MHA/ServerManager.pm
Installing /usr/local/share/perl5/MHA/MasterRotate.pm
Installing /usr/local/share/perl5/MHA/HealthCheck.pm
Installing /usr/local/share/perl5/MHA/Server.pm
Installing /usr/local/share/perl5/MHA/ManagerUtil.pm
Installing /usr/local/share/perl5/MHA/ManagerAdmin.pm
Installing /usr/local/share/perl5/MHA/DBHelper.pm
Installing /usr/local/share/perl5/MHA/ManagerAdminWrapper.pm
Installing /usr/local/share/perl5/MHA/Config.pm
Installing /usr/local/share/man/man1/masterha_manager.1
Installing /usr/local/share/man/man1/masterha_check_repl.1
Installing /usr/local/share/man/man1/masterha_check_ssh.1
Installing /usr/local/share/man/man1/masterha_check_status.1
Installing /usr/local/share/man/man1/masterha_conf_host.1
Installing /usr/local/share/man/man1/masterha_stop.1
Installing /usr/local/share/man/man1/masterha_master_switch.1
Installing /usr/local/share/man/man1/masterha_master_monitor.1
Installing /usr/local/share/man/man1/masterha_secondary_check.1
Installing /usr/local/bin/masterha_manager
Installing /usr/local/bin/masterha_check_ssh
Installing /usr/local/bin/masterha_master_monitor
Installing /usr/local/bin/masterha_check_repl
Installing /usr/local/bin/masterha_secondary_check
Installing /usr/local/bin/masterha_conf_host
Installing /usr/local/bin/masterha_check_status
Installing /usr/local/bin/masterha_stop
Installing /usr/local/bin/masterha_master_switch
Appending installation info to /usr/lib64/perl5/perllocal.pod


manager安装完产生如下脚本文件:
[root@master mha4mysql-manager-0.56]# cd /usr/local/bin/
[root@master bin]# ls
apply_diff_relay_logs  masterha_check_ssh     masterha_manager         masterha_secondary_check  save_binary_logs
filter_mysqlbinlog     masterha_check_status  masterha_master_monitor  masterha_stop
masterha_check_repl    masterha_conf_host     masterha_master_switch   purge_relay_logs
[root@master bin]# ls -alt
total 92
drwxr-xr-x.  2 root root  4096 Aug  2 00:06 .
-r-xr-xr-x.  1 root root  2517 Aug  2 00:06 masterha_manager
-r-xr-xr-x.  1 root root  2373 Aug  2 00:06 masterha_master_switch
-r-xr-xr-x.  1 root root  5171 Aug  2 00:06 masterha_secondary_check
-r-xr-xr-x.  1 root root  1779 Aug  2 00:06 masterha_check_ssh
-r-xr-xr-x.  1 root root  1865 Aug  2 00:06 masterha_check_status
-r-xr-xr-x.  1 root root  2165 Aug  2 00:06 masterha_master_monitor
-r-xr-xr-x.  1 root root  1995 Aug  2 00:06 masterha_check_repl
-r-xr-xr-x.  1 root root  3201 Aug  2 00:06 masterha_conf_host
-r-xr-xr-x.  1 root root  1739 Aug  2 00:06 masterha_stop
-r-xr-xr-x.  1 root root  7525 Aug  2 00:03 save_binary_logs
-r-xr-xr-x.  1 root root 16367 Aug  2 00:03 apply_diff_relay_logs
-r-xr-xr-x.  1 root root  4807 Aug  2 00:03 filter_mysqlbinlog
-r-xr-xr-x.  1 root root  8261 Aug  2 00:03 purge_relay_logs




创建监控用户:
[root@master bin]# su - mysql
-bash-4.1$ mysql -p -u root 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.6.37-log MySQL Community Server (GPL)


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 all privileges on *.* to 'root'@'192.168.174.%' identified by '12345';
Query OK, 0 rows affected (0.33 sec)


mysql> 
mysql> 
mysql> flush privileges;
Query OK, 0 rows affected (0.24 sec)




配置mha:
[root@master mha]# cp /root/mysql/mhatools/mha4mysql-manager-0.56/samples/conf/app1.cnf /etc/mha/
[root@master mha]# cd /etc/mha
[root@master mha]# ls
app1.cnf
[root@master mha]# ls -alt
total 20
-rw-r--r--.   1 root root   257 Aug  2 00:16 app1.cnf
drwxr-xr-x.   2 root root  4096 Aug  2 00:16 .
drwxr-xr-x. 164 root root 12288 Aug  2 00:14 ..
[root@master mha]# vi app1.cnf
[server default]
manager_workdir=/var/log/mha              //设置manager的工作目录
manager_log=/var/log/mha/manager.log          //设置manager的日志
master_binlog_dir=/u02/mysql                         //设置master 保存binlog的位置,以便MHA可以找到master的日志,我这里的也就是mysql的数据目录
master_ip_failover_script= /usr/local/bin/master_ip_failover    //设置自动failover时候的切换脚本
master_ip_online_change_script= /usr/local/bin/master_ip_online_change  //设置手动切换时候的切换脚本
password=12345         //设置mysql中root用户的密码,这个密码是前文中创建监控用户的那个密码
user=root               设置监控用户root
ping_interval=1         //设置监控主库,发送ping包的时间间隔,默认是3秒,尝试三次没有回应的时候自动进行railover
remote_workdir=/tmp     //设置远端mysql在发生切换时binlog的保存位置
repl_password=repluser    //设置复制用户的密码
repl_user=repluser          //设置复制环境中的复制用户名
report_script=/usr/local/send_report    //设置发生切换后发送的报警的脚本
secondary_check_script= /usr/local/bin/masterha_secondary_check -s slave1 -s slave2            
shutdown_script=""      //设置故障发生后关闭故障主机脚本(该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用)
ssh_user=root           //设置ssh的登录用户名


[server1]
hostname=192.168.174.128
port=3306


[server2]
hostname=192.168.174.129
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.174.130
port=3306


设置两从节点为只读(这个只修改全局变量,不写入MY.CNF):
[root@slave2 mha4mysql-node-0.56]# su - mysql
-bash-4.1$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.37 MySQL Community Server (GPL)


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>  set global read_only=1;
Query OK, 0 rows affected (0.14 sec)


mysql> set global relay_log_purge=0;
Query OK, 0 rows affected (0.06 sec)






配置ssh互信:
[root@master mha]# 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:
43:be:75:a2:7d:27:80:63:66:ee:bb:98:34:a9:e2:23 root@master
The key's randomart image is:
+--[ RSA 2048]----+
|                 |
|                 |
|        .        |
|       o .       |
|        S + .    |
|       * B +     |
|      + + . o .  |
|E o  o =   . o   |
| o.o. o +o       |
+-----------------+
[root@master mha]# cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
[root@master mha]# ssh slave1 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
root@slave1's password: 
[root@master mha]# ssh slave2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
root@slave2's password: 
[root@master mha]# 


scp ~/.ssh/authorized_keys slave1:~/.ssh/authorized_keys
scp ~/.ssh/authorized_keys slave2:~/.ssh/authorized_keys




[root@master mha]# ssh slave1 date
Wed Aug  2 00:33:16 CST 2017
[root@master mha]# ssh slave2 date
Wed Aug  2 00:33:20 CST 2017
[root@master mha]# 
[root@master mha]# 
[root@master mha]# 


配置完成.


测试:
[root@master mha]# masterha_check_ssh --conf=/etc/mha/app1.cnf 
Wed Aug  2 00:38:04 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Aug  2 00:38:04 2017 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Wed Aug  2 00:38:04 2017 - [info] Reading server configuration from /etc/mha/app1.cnf..
Wed Aug  2 00:38:04 2017 - [info] Starting SSH connection tests..
Wed Aug  2 00:38:14 2017 - [debug] 
Wed Aug  2 00:38:04 2017 - [debug]  Connecting via SSH from root@192.168.174.128(192.168.174.128:22) to root@192.168.174.129(192.168.174.129:22)..
Warning: Permanently added '192.168.174.128' (RSA) to the list of known hosts.
Wed Aug  2 00:38:09 2017 - [debug]   ok.
Wed Aug  2 00:38:09 2017 - [debug]  Connecting via SSH from root@192.168.174.128(192.168.174.128:22) to root@192.168.174.130(192.168.174.130:22)..
Wed Aug  2 00:38:14 2017 - [debug]   ok.
Wed Aug  2 00:38:15 2017 - [debug] 
Wed Aug  2 00:38:05 2017 - [debug]  Connecting via SSH from root@192.168.174.129(192.168.174.129:22) to root@192.168.174.128(192.168.174.128:22)..
Warning: Permanently added '192.168.174.128' (RSA) to the list of known hosts.
Wed Aug  2 00:38:10 2017 - [debug]   ok.
Wed Aug  2 00:38:10 2017 - [debug]  Connecting via SSH from root@192.168.174.129(192.168.174.129:22) to root@192.168.174.130(192.168.174.130:22)..
Warning: Permanently added '192.168.174.130' (RSA) to the list of known hosts.
Wed Aug  2 00:38:15 2017 - [debug]   ok.
Wed Aug  2 00:38:16 2017 - [debug] 
Wed Aug  2 00:38:06 2017 - [debug]  Connecting via SSH from root@192.168.174.130(192.168.174.130:22) to root@192.168.174.128(192.168.174.128:22)..
Warning: Permanently added '192.168.174.128' (RSA) to the list of known hosts.
Wed Aug  2 00:38:11 2017 - [debug]   ok.
Wed Aug  2 00:38:11 2017 - [debug]  Connecting via SSH from root@192.168.174.130(192.168.174.130:22) to root@192.168.174.129(192.168.174.129:22)..
Warning: Permanently added '192.168.174.129' (RSA) to the list of known hosts.
Wed Aug  2 00:38:16 2017 - [debug]   ok.
Wed Aug  2 00:38:16 2017 - [info] All SSH connection tests passed successfully.
[root@master mha]# 


mha架构中加入keepalived实现VIP的漂移:
在主节点及备用从节点上安装KEEPALIVED,具体KEEPALIVED安装配置见:
http://blog.csdn.net/crpp0902/article/details/76223833


修改 /usr/local/bin/master_ip_failover文件:
[root@master mha]# vim /usr/local/bin/master_ip_failover


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.174.140';
my $ssh_start_vip = "/etc/init.d/keepalived start";
my $ssh_stop_vip = "/etc/init.d/keepalived stop";


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";
        #`ssh $ssh_user\@cluster1 \" $ssh_start_vip \"`;
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}


# A simple system call that enable the VIP on the new master
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() {
     return 0  unless  ($ssh_user);
    `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";
}


架构脚本测试:
[root@master bin]# masterha_check_repl --conf=/etc/mha/app1.cnf 
Wed Aug  2 19:49:09 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Aug  2 19:49:09 2017 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Wed Aug  2 19:49:09 2017 - [info] Reading server configuration from /etc/mha/app1.cnf..
Wed Aug  2 19:49:09 2017 - [info] MHA::MasterMonitor version 0.56.
Wed Aug  2 19:49:10 2017 - [info] GTID failover mode = 0
Wed Aug  2 19:49:10 2017 - [info] Dead Servers:
Wed Aug  2 19:49:10 2017 - [info] Alive Servers:
Wed Aug  2 19:49:10 2017 - [info]   192.168.174.128(192.168.174.128:3306)
Wed Aug  2 19:49:10 2017 - [info]   192.168.174.129(192.168.174.129:3306)
Wed Aug  2 19:49:10 2017 - [info]   192.168.174.130(192.168.174.130:3306)
Wed Aug  2 19:49:10 2017 - [info] Alive Slaves:
Wed Aug  2 19:49:10 2017 - [info]   192.168.174.129(192.168.174.129:3306)  Version=5.6.37-log (oldest major version between slaves) log-bin:enabled
Wed Aug  2 19:49:10 2017 - [info]     Replicating from 192.168.174.128(192.168.174.128:3306)
Wed Aug  2 19:49:10 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Wed Aug  2 19:49:10 2017 - [info]   192.168.174.130(192.168.174.130:3306)  Version=5.6.37 (oldest major version between slaves) log-bin:disabled
Wed Aug  2 19:49:10 2017 - [info]     Replicating from 192.168.174.128(192.168.174.128:3306)
Wed Aug  2 19:49:10 2017 - [info] Current Alive Master: 192.168.174.128(192.168.174.128:3306)
Wed Aug  2 19:49:10 2017 - [info] Checking slave configurations..
Wed Aug  2 19:49:10 2017 - [info]  read_only=1 is not set on slave 192.168.174.129(192.168.174.129:3306).
Wed Aug  2 19:49:10 2017 - [warning]  relay_log_purge=0 is not set on slave 192.168.174.129(192.168.174.129:3306).
Wed Aug  2 19:49:10 2017 - [info]  read_only=1 is not set on slave 192.168.174.130(192.168.174.130:3306).
Wed Aug  2 19:49:10 2017 - [warning]  relay_log_purge=0 is not set on slave 192.168.174.130(192.168.174.130:3306).
Wed Aug  2 19:49:10 2017 - [warning]  log-bin is not set on slave 192.168.174.130(192.168.174.130:3306). This host cannot be a master.
Wed Aug  2 19:49:10 2017 - [info] Checking replication filtering settings..
Wed Aug  2 19:49:10 2017 - [info]  binlog_do_db= , binlog_ignore_db= 
Wed Aug  2 19:49:10 2017 - [info]  Replication filtering check ok.
Wed Aug  2 19:49:10 2017 - [info] GTID (with auto-pos) is not supported
Wed Aug  2 19:49:10 2017 - [info] Starting SSH connection tests..
Wed Aug  2 19:49:18 2017 - [info] All SSH connection tests passed successfully.
Wed Aug  2 19:49:18 2017 - [info] Checking MHA Node version..
Wed Aug  2 19:49:22 2017 - [info]  Version check ok.
Wed Aug  2 19:49:22 2017 - [info] Checking SSH publickey authentication settings on the current master..
Wed Aug  2 19:49:23 2017 - [info] HealthCheck: SSH to 192.168.174.128 is reachable.
Wed Aug  2 19:49:24 2017 - [info] Master MHA Node version is 0.56.
Wed Aug  2 19:49:24 2017 - [info] Checking recovery script configurations on 192.168.174.128(192.168.174.128:3306)..
Wed Aug  2 19:49:24 2017 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/u02/mysql --output_file=/tmp/save_binary_logs_test --manager_version=0.56 --start_file=master-bin.000002 
Wed Aug  2 19:49:24 2017 - [info]   Connecting to root@192.168.174.128(192.168.174.128:22).. 
  Creating /tmp if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /u02/mysql, up to master-bin.000002
Wed Aug  2 19:49:26 2017 - [info] Binlog setting check done.
Wed Aug  2 19:49:26 2017 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Wed Aug  2 19:49:26 2017 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.174.129 --slave_ip=192.168.174.129 --slave_port=3306 --workdir=/tmp --target_version=5.6.37-log --manager_version=0.56 --relay_log_info=/u02/mysql/relay-log.info  --relay_dir=/u02/mysql/  --slave_pass=xxx
Wed Aug  2 19:49:26 2017 - [info]   Connecting to root@192.168.174.129(192.168.174.129:22).. 
  Checking slave recovery environment settings..
    Opening /u02/mysql/relay-log.info ... ok.
    Relay log found at /u02/mysql, up to slave-relay.000005
    Temporary relay log file is /u02/mysql/slave-relay.000005
    Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Wed Aug  2 19:49:28 2017 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.174.130 --slave_ip=192.168.174.130 --slave_port=3306 --workdir=/tmp --target_version=5.6.37 --manager_version=0.56 --relay_log_info=/u02/mysql/relay-log.info  --relay_dir=/u02/mysql/  --slave_pass=xxx
Wed Aug  2 19:49:28 2017 - [info]   Connecting to root@192.168.174.130(192.168.174.130:22).. 
  Checking slave recovery environment settings..
    Opening /u02/mysql/relay-log.info ... ok.
    Relay log found at /u02/mysql, up to slave-relay.000005
    Temporary relay log file is /u02/mysql/slave-relay.000005
    Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Wed Aug  2 19:49:30 2017 - [info] Slaves settings check done.
Wed Aug  2 19:49:30 2017 - [info] 
192.168.174.128(192.168.174.128:3306) (current master)
 +--192.168.174.129(192.168.174.129:3306)
 +--192.168.174.130(192.168.174.130:3306)
Wed Aug  2 19:49:30 2017 - [info] Checking replication health on 192.168.174.129..
Wed Aug  2 19:49:30 2017 - [info]  ok.
Wed Aug  2 19:49:30 2017 - [info] Checking replication health on 192.168.174.130..
Wed Aug  2 19:49:30 2017 - [info]  ok.
Wed Aug  2 19:49:30 2017 - [info] Checking master_ip_failover_script status:
Wed Aug  2 19:49:30 2017 - [info]   /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.174.128 --orig_master_ip=192.168.174.128 --orig_master_port=3306 
IN SCRIPT TEST====/etc/init.d/keepalived stop==/etc/init.d/keepalived start===
Checking the Status of the script.. OK 
Wed Aug  2 19:49:30 2017 - [info]  OK.
Wed Aug  2 19:49:30 2017 - [warning] shutdown_script is not defined.
Wed Aug  2 19:49:30 2017 - [info] Got exit code 0 (Not master dead).


MySQL Replication Health is OK.


配置测试完成。




MHA Manager 端日常主要操作步骤
① 检查是否有下列文件,有则删除。
  发生主从切换后,MHAmanager服务会自动停掉,且在manager_workdir目录下面生成文件app1.failover.complete,若要启动MHA,必须先确保无此文件)
   # ll /etc/mha/app1.failover.complete
   # ll /etc/mha/app1.failover.error
  
② 检查MHA当前状态:
   # masterha_check_repl --conf=/etc/masterha/app1.cnf
  
③ 启动MHA:
   # nohup masterha_manager --conf=/etc/mha/app1.cnf > /etc/mha/mha_manager.log 2>&1 &
  
   当有slave 节点宕掉时,默认是启动不了的,加上 --ignore_fail_on_start 即使有节点宕掉也能启动MHA,如下:
   # nohup masterha_manager --conf=/etc/mha/app1.cnf --ignore_fail_on_start >/etc/mha/mha_manager.log 2>&1 &
  
④ 检查状态:
   # masterha_check_status --conf=/etc/mha/app1/app1.cnf
   
   [root@master bin]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
[root@master bin]# nohup masterha_manager --conf=/etc/mha/app1.cnf --ignore_fail_on_start >/etc/mha/mha_manager.log 2>&1 &
[2] 8755
[root@master bin]# 
[root@master bin]# 
[root@master bin]# 
[root@master bin]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 monitoring program is now on initialization phase(10:INITIALIZING_MONITOR). Wait for a while and try checking again.
[root@master bin]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 monitoring program is now on initialization phase(10:INITIALIZING_MONITOR). Wait for a while and try checking again.
[root@master bin]# more /etc/mha/mha_manager.log 
nohup: ignoring input
Wed Aug  2 20:17:10 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Aug  2 20:17:10 2017 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Wed Aug  2 20:17:10 2017 - [info] Reading server configuration from /etc/mha/app1.cnf..
[root@master bin]# more /etc/mha/mha_manager.log 
nohup: ignoring input
Wed Aug  2 20:17:10 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Aug  2 20:17:10 2017 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Wed Aug  2 20:17:10 2017 - [info] Reading server configuration from /etc/mha/app1.cnf..
[root@master bin]# more /etc/mha/mha_manager.log 
nohup: ignoring input
Wed Aug  2 20:17:10 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Aug  2 20:17:10 2017 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Wed Aug  2 20:17:10 2017 - [info] Reading server configuration from /etc/mha/app1.cnf..
[root@master bin]# more /etc/mha/mha_manager.log 
nohup: ignoring input
Wed Aug  2 20:17:10 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Aug  2 20:17:10 2017 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Wed Aug  2 20:17:10 2017 - [info] Reading server configuration from /etc/mha/app1.cnf..
[root@master bin]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:8755) is running(0:PING_OK), master:192.168.174.128
[root@master bin]# 
 
⑤ 检查日志:
   #tail -f /etc/masterha/app1/manager.log
  
⑥ 主从切换后续工作
   主库切换后,把原主库修复成新从库,然后重新执行以上5步。原主库数据文件完整的情况下,可通过以下方式找出最后执行的CHANGE MASTER命令:
   # grep "CHANGE MASTER TO MASTER" /etc/mha/manager.log | tail -1
   CHANGE MASTER TO MASTER_HOST='192.168.174.129',MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=120,MASTER_USER='repluser', MASTER_PASSWORD='xxx';
   --最后启动新从库
   # start slave;
   # show slave status\G
原创粉丝点击