keepalived+haproxy實現mysql負載均衡高可用

 

環境準備

作業系統CentOS 6.9

Haproxy+keepalived(h1)                   192.168.20.135

Haproxy+keepalived(h2)                   192.168.20.136

Mysql主                                    192.168.20.139

Mysql備                                   192.168.20.138

以上四臺都用虛擬機器測試,全部關閉防火牆service iptables stop.

 

yum安裝MySQ並配置主主複製

兩臺MySQL分別執行:yum -y install mysql-server

 

MySQL(m1)修改配置檔案

Vim /etc/my.cnf

#[mysqld]下新增
server-id = 1
auto_increment_offset = 1
log-bin = mysql-bin
auto-increment-increment = 1
skip-name-resolve
binlog-ignore = mysql   #忽略mysql和information_schema
binlog-ignore = information_schema

重啟MySQL:service mysqld restart

 

MySQL(m2)修改配置檔案

Vim /etc/my.cnf

​
#[mysqld]下新增
server-id = 2
auto_increment_offset = 2
log-bin = mysql-bin
auto-increment-increment = 2
skip-name-resolve
binlog-ignore = mysql   #忽略mysql和information_schema
binlog-ignore = information_schema
​

重啟MySQL:service mysqld restart

注：二都只有server-id不同和 auto-increment- offset不同

 

MySQL(m1)建立授權複製使用者

mysql> grant replication slave on *.* to 'repuser'@'192.168.20.139' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql>FLUSH PRIVILEGES;

 

MySQL(m2)建立授權複製使用者

mysql> grant replication slave on *.* to 'repuser'@'192.168.20.138' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql>FLUSH PRIVILEGES;

 

MySQL(m1)檢視master狀態

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000007 |      311 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

 

MySQL(m2)檢視master狀態

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000007 |      106 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

 

 

MySQL(m1)執行同步命令

mysql> change master to master_host='192.168.20.139',master_port=3306,master_user='repuser',master_password='123456',master_log_file='mysql-bin.000007',master_log_pos=106;

Query OK, 0 rows affected (0.14 sec)

mysql> stop slave;
Query OK, 0 rows affected (2.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

 

MySQL(m2)執行同步命令

mysql> change master to master_host='192.168.20.138',master_port=3306,master_user='repuser',master_password='123456',master_log_file='mysql-bin.000007',master_log_pos=311;

Query OK, 0 rows affected (0.14 sec)

mysql> stop slave;
Query OK, 0 rows affected (2.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

 

 

主主同步測試

檢視slave狀態：show slave status\G

當Slave_IO_Running和Slave_SQL_Running均為Yes時，表示同步成功。

 

 

小記:有一次正好虛擬機器不小心關機了,重新開啟再次看出slave狀態的時候發現如下報錯,最後查到是防火牆隨著虛擬機器重啟後自己又開啟了,service iptables stop把防火牆關掉重新起MySQL就好.

 

 

H1和h2用Yum安裝haproxy

Yum -y install haproxy

配置haproxy

Vim /etc/haproxy/haproxy.cfg

​

#預設的defaults模組以上不動,以下部分替換成如下內容,兩臺haproxy配置一致.

listen mysql_proxy
        bind 0.0.0.0:3306
        mode tcp

        balance source    #定義負載均衡演算法

        server mysqldb1 192.168.20.139:3306 weight 1  check  inter 1s rise 2 fall 2        #master mysql
        server mysqldb2 192.168.20.138:3306 weight 2  check  inter 1s rise 2 fall 2 backup         #slave mysql

listen stats     #監控
       mode http
       bind 0.0.0.0:8888                    #web監控登入埠
       stats enable
       stats uri /dbs                        #we監控端登入地址http:ip:8888/dbs
       stats realm haproxy\ statistics
       stats auth admin:admin                #web監控登端錄使用者和密碼

修改日誌系統

Vim /etc/rsyslog.conf

#在centos6.x系統中，系統日誌的配置檔案原來的/etc/syslog.conf已經變為/etc/rsyslog.conf

###Provides UDP syslog reception               //去掉下面兩行註釋，開啟UDP監聽

$ModLoad imudp
$UDPServerRun 514

local2.* /var/log/haproxy.log           #新增此行

修改/etc/sysconfig/syslog

vim/etc/sysconfig/rsyslog，新增如下內容：

SYSLOGD_OPTIONS=”-c 2 -r -m 0″          

#註釋：-c 2 使用相容模式，預設是 -c 5,-r開啟遠端日誌,-m 0標記時間戳。單位是分鐘，為0時，表示禁用該功能

兩臺haproxy都修改.

service rsyslog restart

 

兩臺都啟動haproxy

/etc/init.d/haproxy start

 

開啟網頁檢視haproxy監控mysql的情況

http://192.168.20.136:8888/dbs

測試關掉一臺MySQL這邊監控頁面就會即時改變顏色提示其down掉.

 

 

 

兩臺haproxy安裝keepalived

Yum -y install keepalived

配置keepalived

Vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {          
notification_email {          # 忽略
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 172.0.0.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_script chk_haproxy {
    script "/etc/keepalived/chk.sh"     # 檢查haproxy的指令碼
    interval 2                          # 每兩秒檢查一次
}

vrrp_instance VI_1 {
    state BACKUP                        # 定義為BACKUP節點
    nopreempt                           # 開啟不搶佔，另一個不寫
    interface eth0
    virtual_router_id 51
    priority 100          # 開啟了不搶佔，所以此處優先順序必須高於另一臺，另一個寫99
    advert_int 1

    authentication {
        auth_type PASS
        auth_pass 1111
    }

    virtual_ipaddress {
        192.168.20.140                  # 配置VIP
    }

    track_script {
        chk_haproxy                     # 呼叫檢查指令碼
    }

    notify_backup "/etc/init.d/haproxy restart"
    notify_fault "/etc/init.d/haproxy stop"

}

注意:Master和Backup不同的地方只有nopreempt和priority兩處.

此處兩臺主機均配置為BACKUP，因此哪臺先執行keepalived，VIP就在哪臺上

在兩臺機器上建立chk.sh檔案：

vim /etc/keepalived/chk.sh

#!/bin/bash

if [ $(ps -C haproxy --no-header | wc -l) -eq 0 ]; then
       /etc/init.d/keepalived stop
fi

chmod +x /etc/keepalived/chk.sh

 

兩臺haproxy啟動keepalived

/etc/init.d/Keepalievd start

 

測試mysql負載均衡

即根據haproxy反向代理相應的策略用VIP訪問MySQL.

 

MySQL(m1)建立一個沒有許可權的使用者

CREATE USER 'haproxy'@'%' IDENTIFIED BY '';

Flush privileges;

然後找一臺別的不相干的伺服器192.168.20.133用VIP連線mysql;

 

關閉其中一臺MySQL依然可以連線,

關閉其中一臺haproxy同樣可以連線;

即搭建成功.

 

下面忽略

#tcpdump -nn -i ens33 vrrp # 抓包檢視