使用 MHA 和 HAProxy 部署高可用 MySQL-阿里云开发者社区

设置高度可用和可扩展的MySQL系统的问题已经研究了十多年，有许多可能的解决方案-MariaDBGaleraCluster、PerconaXtraDBCluster、MySQLClusterCGE和许多其他自定义设置，仅举几例。我今天要演示的涉及MHA[1]、HAProxy[2]（用于扩展读写）和keepalived来实现VRRP[3]。使用MHA的好处是您可以使用任何您想要的存储引擎，以及Oracle、Maria或PerconaSQL服务器。它也适用于传统复制或GTID。

MHA（MasterHighAvailabilityManagerandtoolsforMySQL）是一组用Perl编写的工具，由位于专用主机上的管理器和驻留在MySQL节点上的脚本集合组成。管理器监视集群的状态，当主服务器发生故障时，它会将当前的从服务器提升为新的主服务器，并执行脚本来处理故障转移（例如移动虚拟IP，或更改配置文件等））。这是通过管理器ssh-ing到MySQL节点，并运行脚本、scp-ing中继日志等来完成的。如果允许进程ssh到数据库服务器不适合您，那么此设置不是最好的选择。

在本例中，我们将有四台服务器-两台用于MHA管理器和HAProxy，两台用于主从MySQL服务器。我将使用基于GTID的复制[4]。

首先让我们开始在主服务器和从服务器之间设置GTID复制。

在两台数据库服务器上：

[root@mysql-n01 ~] cat /etc/mysql/conf.d/replication.cnf
[mysqld]
server_id = 1
report-host = 1
report-port = 1
read_only = 0
# binary logs
log_bin = /var/log/mysql/replica-1-bin
expire_logs_days = 3
max_binlog_size = 1G
log_slave_updates = 1
sync-binlog = 0
binlog_format = MIXED
# GTID
gtid_mode = ON
enforce-gtid-consistency
# Relay logs
relay_log = /var/log/mysql/replica-1-relay
relay_log_purge = 1
relay_log_recovery  = 1
relay_log_space_limit = 5G
[root@mysql-n01 ~] mysql
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicationuser'@'%' IDENTIFIED BY 'somepassword'
mysql> FLUSH PRIVILEGES; 
[root@mysql-n02 ~] cat /etc/mysql/conf.d/replication.cnf
[mysqld]
server_id = 2
report-host = 1
report-port = 1
read_only = 1
# binary logs
log_bin = /var/log/mysql/replica-2-bin
expire_logs_days = 3
max_binlog_size = 1G
log_slave_updates = 1
sync-binlog = 0
binlog_format = MIXED
# GTID
gtid_mode = ON
enforce-gtid-consistency
# Relay logs
relay_log = /var/log/mysql/replica-2-relay
relay_log_purge = 1
relay_log_recovery  = 1
relay_log_space_limit = 5G
[root@mysql-n02 ~] mysql
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicationuser'@'%' IDENTIFIED BY 'somepassword'
mysql> FLUSH PRIVILEGES;

要开始复制，请在当前从属设备上执行：

mysql> change master to master_host=<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="62" data-index-1691639741453="1853" data-index-len-1691639741453="1853" class="character" style="max-width: 100%;">"10.188.50.124<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="62" data-index-1691639741453="1867" data-index-len-1691639741453="1867" class="character" style="max-width: 100%;">", master_user=<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="62" data-index-1691639741453="1882" data-index-len-1691639741453="1882" class="character" style="max-width: 100%;">"replicationuser<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="62" data-index-1691639741453="1898" data-index-len-1691639741453="1898" class="character" style="max-width: 100%;">", master_password=<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="62" data-index-1691639741453="1917" data-index-len-1691639741453="1917" class="character" style="max-width: 100%;">"somepassword<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="62" data-index-1691639741453="1930" data-index-len-1691639741453="1930" class="character" style="max-width: 100%;">", master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.14 sec)
mysql> start slave;
Query OK, 0 rows affected (0.06 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.188.50.124
                  Master_User: replicationuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: replica-2-bin.000004
          Read_Master_Log_Pos: 231
               Relay_Log_File: replica-1-relay.000002
                Relay_Log_Pos: 369
        Relay_Master_Log_File: replica-2-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 231
              Relay_Log_Space: 573
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: ebc53c30-5de6-11e4-ac82-0018518bc543
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: e443c439-5de6-11e4-ac82-0018511fca0b:1-6,
ebc53c30-5de6-11e4-ac82-0018518bc543:1-3
                Auto_Position: 1
1 row in set (0.00 sec)

现在复制已经设置完毕，让我们在两台数据库服务器上安装 MHA 工具：

[root@mysql-n01 ~] wget https://mysql-master-ha.googlecode.com/files/mha4mysql-node_0.54-0_all.deb
[root@mysql-n01 ~] apt-get install libdbd-mysql-perl
[root@mysql-n01 ~] dpkg --install mha4mysql-node_0.54-0_all.deb

接下来，在两个管理器节点上，安装MHA管理器、HAProxy和keepalived。管理节点还需要MHA节点包：

[root@manager-n01 ~] apt-get install libdbd-mysql-perl
[root@manager-n01 ~] apt-get install libconfig-tiny-perl
[root@manager-n01 ~] apt-get install liblog-dispatch-perl
[root@manager-n01 ~] apt-get install libparallel-forkmanager-perl
[root@manager-n01 ~] wget https://mysql-master-ha.googlecode.com/files/mha4mysql-manager_0.55-0_all.deb
[root@manager-n01 ~] wget https://mysql-master-ha.googlecode.com/files/mha4mysql-node_0.54-0_all.deb
[root@manager-n01 ~] dpkg --install mha4mysql-node_0.54-0_all.deb
[root@manager-n01 ~] dpkg --install mha4mysql-manager_0.55-0_all.deb
[root@manager-n01 ~] apt-get install haproxy keepalived

确保您拥有部署了ssh密钥的用户，MHA可以使用该用户在所有服务器之间进行ssh。MHA、HAProxy和keepalived的配置如下：

[root@manager-n01 ~] cat /etc/app1.cnf
[server default]
# User that will ssh from the manager nodes to the database nodes
user=mhamanager
password=somepassword
ssh_user=root
# working directory on the manager
manager_workdir=/var/log/masterha/app1
# working directory on MySQL servers
remote_workdir=/var/log/masterha/app1
master_ip_failover_script=/usr/bin/master_ip_failover
[server1]
hostname=10.188.49.114
[server2]
hostname=10.188.50.124
[root@manager-n01 ~] cat /usr/bin/master_ip_failover
#!/bin/bash
COMMAND=$1
OLD_MASTER_IP=$(echo $4 | cut -d<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="370" data-index-1691639741453="5753" data-index-len-1691639741453="5753" class="character" style="max-width: 100%;">"=<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="370" data-index-1691639741453="5755" data-index-len-1691639741453="5755" class="character" style="max-width: 100%;">" -f2)
NEW_MASTER_IP=$(echo $7 | cut -d<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="377" data-index-1691639741453="5793" data-index-len-1691639741453="5793" class="character" style="max-width: 100%;">"=<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="377" data-index-1691639741453="5795" data-index-len-1691639741453="5795" class="character" style="max-width: 100%;">" -f2)
if [ <span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="381" data-index-1691639741453="5806" data-index-len-1691639741453="5806" class="character" style="max-width: 100%;">"$(echo $COMMAND | grep start)<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="383" data-index-1691639741453="5836" data-index-len-1691639741453="5836" class="character" style="max-width: 100%;">" != <span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="385" data-index-1691639741453="5841" data-index-len-1691639741453="5841" class="character" style="max-width: 100%;">"<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="385" data-index-1691639741453="5842" data-index-len-1691639741453="5842" class="character" style="max-width: 100%;">" ]
then
  logger <span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="389" data-index-1691639741453="5858" data-index-len-1691639741453="5858" class="character" style="max-width: 100%;">"Failover detected. Changing HAProxy config file<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="389" data-index-1691639741453="5906" data-index-len-1691639741453="5906" class="character" style="max-width: 100%;">"
  logger <span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="391" data-index-1691639741453="5916" data-index-len-1691639741453="5916" class="character" style="max-width: 100%;">"Failed Master IP: $OLD_MASTER_IP, New Master IP: $NEW_MASTER_IP<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="395" data-index-1691639741453="5980" data-index-len-1691639741453="5980" class="character" style="max-width: 100%;">"
  MASTER_STANZA=$(cat /etc/haproxy/haproxy.cfg | grep <span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="397" data-index-1691639741453="6035" data-index-len-1691639741453="6035" class="character" style="max-width: 100%;">"server mysql-master<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="397" data-index-1691639741453="6055" data-index-len-1691639741453="6055" class="character" style="max-width: 100%;">")
  sed -i <span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="400" data-index-1691639741453="6066" data-index-len-1691639741453="6066" class="character" style="max-width: 100%;">"s/${MASTER_STANZA}/    server mysql-master ${NEW_MASTER_IP}:3306/<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="404" data-index-1691639741453="6132" data-index-len-1691639741453="6132" class="character" style="max-width: 100%;">" /etc/haproxy/haproxy.cfg
  logger <span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="407" data-index-1691639741453="6167" data-index-len-1691639741453="6167" class="character" style="max-width: 100%;">"Restarting HAProxy<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="407" data-index-1691639741453="6186" data-index-len-1691639741453="6186" class="character" style="max-width: 100%;">"
  /etc/init.d/haproxy restart
fi
[root@manager-n01 ~] cat /etc/haproxy/haproxy.cfg
global
    log 127.0.0.1 local1
    log-tag haproxy
    maxconn 4096
    user haproxy
    group haproxy
    daemon
    stats socket /var/run/haproxy.sock mode 600 level admin
    stats timeout 2m
    tune.ssl.default-dh-param 2048
defaults
    log global
    mode tcp
    timeout connect 5000ms
    timeout client 50000ms
    timeout server 50000ms
    option dontlognull
    option tcplog
    option logasap
frontend mysql_master
    bind 10.188.50.121:3306
    default_backend mysql_master
frontend mysql_slave
    bind 10.188.50.121:3307
    default_backend mysql_slaves
backend mysql_master
    server mysql-master 10.188.50.100:3306
backend mysql_slaves
    server mysql-slaves 10.188.50.110:3306 check
[root@manager-n01 ~] cat /etc/keepalived/keepalived.conf
vrrp_instance management_network {
  state MASTER
  interface eth0
  virtual_router_id 51
  priority 100
  virtual_ipaddress {
    10.188.50.121/20 dev eth0 label eth0:0
  }
  nopreempt
  notify /usr/local/bin/mha_manager.sh
}
[root@manager-n01 ~] cat /usr/local/bin/mha_manager.sh
#!/bin/bash
TYPE=$1
NAME=$2
STATE=$3
case $STATE in
        <span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="481" data-index-1691639741453="7331" data-index-len-1691639741453="7331" class="character" style="max-width: 100%;">"MASTER<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="481" data-index-1691639741453="7338" data-index-len-1691639741453="7338" class="character" style="max-width: 100%;">") /etc/init.d/haproxy start && /usr/bin/masterha_manager --conf=/etc/app1.cnf
                  exit 0
                  ;;
        <span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="488" data-index-1691639741453="7468" data-index-len-1691639741453="7468" class="character" style="max-width: 100%;">"BACKUP<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="488" data-index-1691639741453="7475" data-index-len-1691639741453="7475" class="character" style="max-width: 100%;">") /etc/init.d/haproxy stop && /usr/bin/masterha_stop --conf=/etc/app1.cnf
                  exit 0
                  ;;
        <span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="495" data-index-1691639741453="7601" data-index-len-1691639741453="7601" class="character" style="max-width: 100%;">"FAULT<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="495" data-index-1691639741453="7607" data-index-len-1691639741453="7607" class="character" style="max-width: 100%;">")  /etc/init.d/haproxy stop && /usr/bin/masterha_stop --conf=/etc/app1.cnf
                  exit 0
                  ;;
        *)        echo <span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="504" data-index-1691639741453="7749" data-index-len-1691639741453="7749" class="character" style="max-width: 100%;">"unknown state<span data-raw-text-1691639741453="" "="" data-textnode-index-1691639741453="504" data-index-1691639741453="7763" data-index-len-1691639741453="7763" class="character" style="max-width: 100%;">"
                  exit 1
                  ;;
esac

第一个配置用于MHA管理器。第11行指定了新master升级后要执行的脚本。该脚本仅更改HAProxy配置，但它可以是任何内容。第13-17行定义了MySQL服务器。MHA会决定哪一个是master，哪一个是slave。

关于keepalived配置的唯一有趣的部分是第88行，其中我们指定了当前MHA服务器发生故障时keepalived将触发的脚本-它基本上会在备用服务器上启动haproxy以及MHA管理器。

要手动启动，请检查状态并停止MHA管理器运行：

[root@manager-n01 ~] /usr/bin/masterha_manager --conf=/etc/app1.cnf
[root@manager-n01 ~] /usr/bin/masterha_check_status --conf=/etc/app1.cnf
[root@manager-n01 ~] /usr/bin/masterha_stop --conf=/etc/app1.cnf

要测试故障转移，请停止当前主服务器上的MySQL并观察MHA将从服务器升级为主服务器，然后更改HAProxy配置以反映这一点。

资源：

[1]。https://code.google.com/p/mysql-master-ha/

[2]。http://www.haproxy.org/

[3]。http://www.keepalived.org/

[4]。http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html

使用 MHA 和 HAProxy 部署高可用 MySQL

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像