MHA 实现 Mysql 高可用配置

一、项目环境：

server1 : mha-manager
server2 : master
server3,4 : slave

首先，在mysql组的三台服务器上做如下配置：

 /etc/init.d/mysqld start
 rm -rf /var/lib/mysql/*
 /etc/init.d/mysqld restart

 vim /etc/my.cnf      
-------------------------------------------------
 23 server-id=X         ##(X=2-4）
 24 log-bin=mysql-bin
 25 gtid_node=ON           
 26 enforce_gtid_consistency=ON

启动mysql流程：

[root@server2 ~]# /etc/init.d/mysqld restart
[root@server2 ~]# cat /var/log/mysqld.log | grep password
2018-08-09T14:35:31.740629Z 1 [Note] A temporary password is generated for root@localhost: .u#P4subI.oy
[root@server2 ~]# mysql_secure_installation 

二、Master配置:

[root@server2 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
##授予slaves replication权限
mysql> grant replication slave on *.* to 'repl'@'172.25.18.%' identified by 'Vincen100+';

三、Slave配置

[root@server3 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12

mysql> change master to master_host='172.25.18.2',master_user='repl',master_password='Vincen100+', MASTER_AUTO_POSITION=1;

mysql> start slave;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.18.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 446
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 659
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes     ##表示mysql连接成功
            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: 446
              Relay_Log_Space: 868
              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: 

三、MHA manager配置

需要的安装包：

mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm perl-Config-Tiny-2.12-7.1.el6.noarch.rpm perl-Email-Date-Format-1.002-5.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-MIME-Lite-3.027-2.el6.noarch.rpm perl-MIME-Types-1.28-2.el6.noarch.rpm perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm

[root@server1 MHA]# yum install mha4mysql-* perl-* -y

mha高可用文件配置

[root@server1 ～]# mkdir /etc/masterha
[root@server1 ～]# vim /etc/masterha/mah.conf  
------------------------------------------------
[server default]
manager_workdir=/etc/masterha  
manager_log=/etc/masterha/mha.log
master_binlog_dir=/var/lib/mysql
user=root                              ##监控用户
password="Vincen100+"
ping_interval=1

remote_workdir=/tmp
repl_user=repl
repl_password='Vincen100+'
ssh_user=root

[server1]
hostname=172.25.2.2
port=3306

[server2]
hostname=172.25.2.3
port=3306
candidate_master=1 
check_repl_delay=0

[server3]
hostname=172.25.2.4
port=3306
#no_master=1  

配置ssh

各个节点都是基于SSH互信通信； 把MHA管理节点上生成的私钥文件分别复制到其它三个节点上，确保可无需验证登录。

[root@server1 ~]# ssh-keygen 
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:
82:1c:af:68:c6:c5:59:f4:0f:5e:af:8e:dc:6a:d0:af root@server1
The key's randomart image is:
+--[ RSA 2048]----+
|      .          |
|     . .         |
|    . . o .      |
|   o * . + .     |
|    * o.S . .    |
| . o ....  .     |
|  = .  . ..      |
| o     ..+.      |
|       .Eoo      |
+-----------------+
[root@server1 ~]# ssh-copy-id server0

[root@server1 ~]# ssh-copy-id server2
[root@server1 ~]# ssh-copy-id server3
[root@server1 ~]# ssh-copy-id server4
[root@server1 ~]# scp ~/.ssh/id_rsa server2:~/.ssh/
[root@server1 ~]# scp ~/.ssh/id_rsa  server3:~/.ssh/
[root@server1 ~]# scp ~/.ssh/id_rsa  server4:~/.ssh/

主节点授权：

mysql> grant all on *.* to root@'172.25.18.%' identified by 'Vincen100+' ;

四、测试：

##检测各节点间 ssh 互信通信
[root@server1 ～]# masterha_check_ssh --conf=/etc/masterha/mah.conf 
...
All SSH connection tests passed successfully.
##检查管理MySQL复制集群的连接
[root@server1 ～]# masterha_check_repl --conf=/etc/masterha/mah.conf 
...
MySQL Replication Health is OK.

高可用检测：

MHA manager

##启动 MHA
[root@server1 MHA]# nohup masterha_manager --conf=/etc/masterha/mah.conf --ignore_last_failover 2>&1 

中止主节点server2上mysql服务
[root@server2 ~]# /etc/init.d/mysqld stop

server3:
server3作为备份节点，主节点故障后会迅速替代其进行工作。
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 172.25.18.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: server3-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes

mysql> show slave status\G;
Empty set (0.00 sec)
ERROR: 
No query specified

server4:
mysql> show slave status\G;
                  Master_Host: 172.25.18.2
##切换将server3作为主节点
mysql> show slave status\G;
                  Master_Host: 172.25.18.3

手动完成在线主从节点切换：

[root@server1 ~]# masterha_master_switch --conf=/etc/master/mah.conf--master_state=alive --new_master_host=172.25.18.3 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=1000

故障切换：

[root@server1 ~]#masterha_master_switch --conf=/etc/master/mah.conf--master_state=dead --dead_master_host=172.25.18.2 --dead_master_port=3306 --new_master_host=172.25.18.3 --new_master_port=3306 --ignore_last_failover