一、总概：
本文介绍MySQL高可用性的实现方案MHA，MHA由Node和Manager组成，Node运行在每一台MySQL服务器上，不管是MySQL主 服务器，还是MySQL从服务器，都要安装Node。
二、环境
1、操作系统：centos 6.8 64位
2、数据库：MySQL-5.6.17-1.el6.x86_64.rpm-bundle.tar
3、MHA版本：
mha4mysql-node-0.54-0.el6.noarch.rpm,
mha4mysql-manager-0.54-0.el6.noarch.rpm
4、主机部署
manager机:10.101.26.100 master机:10.101.26.101 slave1机:10.101.26.103(备用master) slave2机:10.101.26.105
5、大致步骤：
(1)、安装MYSQL 5.6.
(2)、MYSQL配置主重复制.
(3)、首先用ssh-keygen实现四台主机之间相互免密钥登录.
(4)、安装MHAmha4mysql-node，mha4mysql-manager 软件包
(5)、在MHA配置master，slave的相关文件。
(6)、管理机manager上配置MHA文件
(7)、masterha_check_ssh工具验证ssh信任登录是否成功
(8)、masterha_check_repl工具验证mysql复制是否成功
(9)、启动MHA manager,并监控日志文件.
(10)、测试master宕机后，是否会自动切换。
三、mysql安装
1、下载MYSQL 5.6
wget http://cdn.mysql.com/Downloads/MySQL-5.6/MySQL-5.6.17-1.el6.x86_64.rpm-bundle.tar
tar -cf MySQL-5.6.17-1.el6.x86_64.rpm-bundle.tar
2、解压后文件有：
MySQL-client-5.6.17-1.el6.x86_64.rpm
MySQL-devel-5.6.17-1.el6.x86_64.rpm
MySQL-embedded-5.6.17-1.el6.x86_64.rpm
MySQL-server-5.6.17-1.el6.x86_64.rpm
MySQL-shared-5.6.17-1.el6.x86_64.rpm
MySQL-shared-compat-5.6.17-1.el6.x86_64.rpm
MySQL-test-5.6.17-1.el6.x86_64.rpm
**注意：其实只要安装，服务器端MySQL-server-5.6.17-1.el6.x86_64.rpm 客户端MySQL-client-5.6.17-1.el6.x86_64.rpm 及MySQL-shared-compat-5.6.17-1.el6.x86_64.rpm(可以解决一些兼容性)
3、安装（4台机器都要安装）
卸载系统原有的MYSQL-LIB*相关组件，并安装MYSQL5.6。
yum -y remove mysql-libs-5.1.71*
rpm -ivh MySQL-client-5.6.17-1.el6.x86_64.rpm
rpm -ivh MySQL-server-5.6.17-1.el6.x86_64.rpm
rpm -ivh MySQL-shared-compat-5.6.17-1.el6.x86_64.rpm
**注意：安装完MYSQL会在/root/.mysql_secret生成root的默认登录密码。(cat /root/.mysql_secret)
4、由于是RPM安装的所以在/etc/下没有MYSQL的my.cnf文件。解决方法：
cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
5、mysql启动、停止、重启。
service mysql start
service mysql stop
service mysql restart
四、MYSQL主从复制配置（manager、slave1、slave2）
**【本文采用半同步，半同步可防止事务丢失导致从机和主机数据不一致。】
1、主从服务器分别作以下操作：
1.1、版本一致
1.2、初始化表，并在后台启动mysql
1.3、修改root的密码
2、修改主服务器master:
#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[必须]启用二进制日志
server-id=222 //[必须]服务器唯一ID，默认是1，一般取IP最后一段
binlog-do-db=db_nameA //指定对db_nameA记录二进制日志
binlog-ignore-db=db_namB //指定不对db_namB记录二进制日志
3、修改从服务器slave:
#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[不是必须]启用二进制日志
server-id=226 //[必须]服务器唯一ID，默认是1，一般取IP最后一段
relay_log = mysql-relay-bin
4、重启两台服务器的mysql
/etc/init.d/mysql restart
5、在主服务器上建立帐户并授权slave:
#/usr/local/mysql/bin/mysql -uroot -pmttang
mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456'; //一般不用root帐号，'%'表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如10.101.26.101，加强安全。
6、登录主服务器的mysql，查询master的状态
mysql>show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 308 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化
7、配置从服务器Slave：(slave1 salve2)
mysql>change master to master_host='10.101.26.100',master_user='mysync',master_password='q123456',
master_log_file='mysql-bin.000004',master_log_pos=308; //注意不要断开，308数字前后无单引号。
mysql>start slave; //启动从服务器复制功能
8、检查从服务器复制功能状态：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.101.26.100 //主服务器地址
Master_User: mysync //授权帐户名，尽量避免使用root
Master_Port: 3306 //数据库端口，部分版本没有此行
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 600 //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos
Relay_Log_File: ddte-relay-bin.000003
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
......
注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。
9、添加半同步插件（master slave1 salve2）
master:
mysql>install plugin rpl_semi_sync_master soname 'semisync_master.so';
slave1:
mysql>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
slave2:
mysql>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
master:
mysql>set global rpl_semi_sync_master_enabled = 1;
mysql>set global rpl_semi_sync_master_timeout = 100;
slave1:
mysql>set global rpl_semi_sync_slave_enabled = ON;
slave2:
mysql>set global rpl_semi_sync_slave_enabled = ON;
在从库上重启IO线程(slave1 slave2)
stop slave IO_thread;
start slave IO_thread;
以上操作过程，主从服务器配置完成。
五、MHA配置。
//环境介绍
1、主机部署
manager机:10.101.26.100
master机:10.101.26.101
slave1机:10.101.26.103(备用master)
slave2机:10.101.26.105
2、大致步骤
A.首先用ssh-keygen实现四台主机之间相互免密钥登录
B.安装MHAmha4mysql-node，mha4mysql-manager 软件包
C.建立master，slave1，slave2之间主从复制
D.管理机manager上配置MHA文件
E.masterha_check_ssh工具验证ssh信任登录是否成功
F.masterha_check_repl工具验证mysql复制是否成功
G.启动MHA manager,并监控日志文件
H.测试master宕机后，是否会自动切换
//A.首先用ssh-keygen实现四台主机之间相互免密钥登录
[manager机]
1.
shell> ssh-keygen -t rsa -b 2048
shell> scp id_rsa.pub root@10.101.26.101:/root/.ssh/
shell> scp id_rsa.pub root@10.101.26.103:/root/.ssh/
shell> scp id_rsa.pub root@10.101.26.105:/root/.ssh/
2.在另外三台机子上用cat xxx >> authorized_keys导入公钥到/root/.ssh/authorized_keys文件中
3.在其它三台机子重复此步骤,使四台机子中的任何两台之间可以免密码登录
//B.安装MHAmha4mysql-node，mha4mysql-manager 软件包
1.安装MHAmha4mysql-node
[manager,master,slave1,slave2]
shell> yum update
shell> yum -y install perl-DBD-MySQL ncftp
shell> wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.53-0.noarch.rpm
sehll> rpm -ivh mha4mysql-node-0.53-0.noarch.rpm
2.安装mha4mysql-manager
[manager]
shell> yum install perl
shell> yum install cpan
shell> rpm -ivh mha4mysql-manager-0.53-0.el6.noarch.rpm
error:
perl(Config::Tiny) is needed by mha4mysql-manager-0.53-0.noarch
perl(Log::Dispatch) is needed by mha4mysql-manager-0.53-0.noarch
perl(Log::Dispatch::File) is needed by mha4mysql-manager-0.53-0.noarch
perl(Log::Dispatch::Screen) is needed by mha4mysql-manager-0.53-0.noarch
perl(Parallel::ForkManager) is needed by mha4mysql-manager-0.53-0.noarch
perl(Time::HiRes) is needed by mha4mysql-manager-0.53-0.noarch
[solution]
shell> wget ftp://ftp.muug.mb.ca/mirror/centos/5.10/os/x86_64/CentOS/perl-5.8.8-41.el5.x86_64.rpm
shell> wget ftp://ftp.muug.mb.ca/mirror/centos/6.5/os/x86_64/Packages/compat-db43-4.3.29-15.el6.x86_64.rpm
shell> wget http://downloads.naulinux.ru/pub/NauLinux/6x/i386/sites/School/RPMS/perl-Log-Dispatch-2.27-1.el6.noarch.rpm
shell> wget http://dl.fedoraproject.org/pub/epel/6/i386/perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
shell> wget http://dl.fedoraproject.org/pub/epel/6/i386/perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
shell> wget http://dl.fedoraproject.org/pub/epel/6/i386/perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
shell> wget http://mirror.centos.org/centos/6/os/x86_64/Packages/perl-Time-HiRes-1.9721-136.el6.x86_64.rpm
shell> rpm -ivh perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm perl-Log-Dispatch-2.27-1.el6.noarch.rpm perl-Mail-Sender-0.8.16-3.el6.noarch.rpm perl-Mail-Sendmail-0.79-12.el6.noarch.rpm perl-Time-HiRes-1.9721-136.el6.x86_64.rpm
shell> rpm -ivh mha4mysql-manager-0.53-0.el6.noarch.rpm
//C.管理机manager上配置MHA文件
[manager:100]
1.
shell> mkdir -p /masterha/app1
shell> mkdir /etc/masterha
shell> vim /etc/masterha/app1.cnf
[server default]
user=root ##mysql管理用戶名
password=123456
manager_workdir=/masterha/app1
manager_log=/masterha/app1/manager.log
remote_workdir=/masterha/app1
ssh_user=root #ssh免密钥登录的帐号名
repl_user=mysync #mysql复制帐号，用来在主从机之间同步二进制日志等
repl_password=q123456
ping_interval=1 #ping间隔，用来检测master是否正常
[server1]
hostname=10.101.26.101
#ssh_port=9999
master_binlog_dir=/data/ndb #mysql数据库目录
candidate_master=1 #master机宕掉后,优先启用这台作为新master
[server2]
hostname=10.101.26.103
#ssh_port=9999
master_binlog_dir=/data/ndb
candidate_master=1
[server3]
hostname=10.101.26.105
#ssh_port=9999
master_binlog_dir=/data/ndb
no_master=1 #设置no_master=1使主机不能成为新master
//D.masterha_check_ssh工具验证ssh信任登录是否成功
[manager:100]
shell> masterha_check_ssh --conf=/etc/masterha/app1.cnf
Sun Mar 2 17:45:38 2014 - [debug] ok.
Sun Mar 2 17:45:38 2014 - [info] All SSH connection tests passed successfully.
//E.masterha_check_repl工具验证mysql复制是否成功
[manager:154]
shell> masterha_check_repl --conf=/etc/masterha/app1.cnf
---------------------------------------------------------
Sun Mar 2 13:16:57 2014 - [info] Slaves settings check done.
Sun Mar 2 13:16:57 2014 - [info]
10.101.26.101 (current master)
+--10.101.26.103
+--10.101.26.105
...
MySQL Replication Health is OK.
---------------------------------------------------------------
//F.启动MHA manager,并监控日志文件
[manager:100]
shell> nohup masterha_manager --conf=/etc/masterha/app1.cnf > /tmp/mha_manager.log 2>&1
shell> tail -f /masterha/app1/manager.log
---------------------------------------------------------------
10.101.26.101 (current master)
+--10.101.26.103
+--10.101.26.105
...
Sun Mar 2 13:09:25 2014 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
-----------------------------------------------------------------
##manager.log表明MHA运行良好，正在监控master是否正常工作
//G.测试master(156)宕机后，是否会自动切换
[master:101]
1.shell> /etc/init.d/myqld stop
[manager:100]
2.当掉master后，/masterha/app1/manager.log文件显示：
[error]
-----------------------------------------------------------
Sun Mar 2 13:13:46 2014 - [error][/usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm, ln178] Got ERROR: Use of uninitialized value $msg in scalar chomp at /usr/share/perl5/vendor_perl/MHA/ManagerConst.pm line 90.
-----------------------------------------------------------
3.[slution]
在文件/usr/share/perl5/vendor_perl/MHA/ManagerConst.pm第90行(chomp $msg)前加入一行：
$msg = "" unless($msg);
4.再次检测:
shell> /etc/init.d/mysqld stop
tail -f tail -f /masterha/app1/manager.log
日志文件显示：
-----------------------------------------------------------
----- Failover Report -----
app1: MySQL Master failover 10.101.26.101 to 10.101.26.103 succeeded
Master 10.101.26.101 is down!
Check MHA Manager logs at mycentos4:/masterha/app1/manager.log for details.
Started automated(non-interactive) failover.
The latest slave 10.101.26.103(10.101.26.103:3306) has all relay logs for recovery.
Selected 10.101.26.103 as a new master.
10.101.26.103: OK: Applying all logs succeeded.
10.101.26.105: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
10.101.26.105: OK: Applying all logs succeeded. Slave started, replicating from 10.10.54.155.
10.101.26.103: Resetting slave info succeeded.
Master failover to 10.101.26.103(10.101.26.103:3306) completed successfully.
--------------------------------------------------------
5.
切换主机后，检查replication状态
[master:101]
shell> /etc/init.d/mysqld start
[manager:100]
shell> masterha_check_repl --conf=/etc/masterha/app1.cnf
--------------------------------------------------------------
Sun Mar 2 13:22:11 2014 - [info] Slaves settings check done.
Sun Mar 2 13:22:11 2014 - [info]
10.101.26.103 (current master)
+--10.101.26.101
+--10.101.26.105
...
MySQL Replication Health is OK.
---------------------------------------------------------------
###上面的"10.101.26.103 (current master)" 这句表明master成功切换到103机子上。