haproxy+keepalived来实现mariadb galera cluster的高可用架构

iyufygfd

haproxy+keepalived来实现mariadb galera cluster的高可用架构

网络拓扑图如下：



mariadb galera cluster集群基于wsrep协议可以实现mysql多主复制架构，详细说明如下：
galera cluster复制：
galera cluster本身是基于wsrep协议工作在底层的文件复制通道而已，只需要指明复制的文件，它会自动在多个节点之间的检测文件状态并完成数据同步。并且galera cluster向上提供API，其他组件只需调用其API，即可完成所需的文件复制功能。

galera cluster向上提供API，mysql只需调用其API即可。所以原生的mysql版本是没有galera cluster功能的，需要下载附带galera的版本才可以，mysql官方也有提供支持galera的版本。

而haproxy是mariadb galera cluster前端的负载均衡组件，可以实现把mysql请求按照指定的调度算法分发给后端的mariadb galera cluster存储。而keepalived则可以实现haproxy的高可用。


详细的安排配置过程如下：

以下节点均为CentOS7.1，安装方式为“基础架构服务器”，配置好CentOS7的base和epel源，主机名，时间同步，关闭selinux和firewalld
192.168.43.201node1.c7.comnode1Mariadb-Galera-server1
192.168.43.202node2.c7.comnode2 Mariadb-Galera-server2
192.168.43.203node3.c7.comnode3 Mariadb-Galera-server3
192.168.43.204node4.c7.comnode4 haproxy1 + keepalived
192.168.43.205node5.c7.comnode5 haproxy2 + keepalived
192.168.43.206node6.c7.comnode6 mysql 客户端

一、配置mariadb galera cluster集群
1. 配置节点之间网络时间同步
[iyunv@node1 ~]# crontab -l
*/5 * * * * /usr/sbin/ntpdate -u 129.6.15.28 &> /dev/null
[iyunv@node2 ~]# crontab -l
*/5 * * * * /usr/sbin/ntpdate -u 129.6.15.28 &> /dev/null
[iyunv@node3 ~]# crontab -l
*/5 * * * * /usr/sbin/ntpdate -u 129.6.15.28 &> /dev/null

2. 配置集群节点互信
[iyunv@node1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.43.201        node1.c7.com        node1
192.168.43.202        node2.c7.com        node2
192.168.43.203        node3.c7.com        node3
192.168.43.204        node4.c7.com        node4
192.168.43.205        node5.c7.com        node5
[iyunv@node5 ~]# for i in {2..5}; do scp /etc/hosts node$i:/etc; done
[iyunv@node1 ~]# ssh-keygen -t rsa -P '' -f '/root/.ssh/id_rsa'
[iyunv@node1 ~]# cat .ssh/id_rsa.pub >> .ssh/authorized_keys
[iyunv@node1 ~]# chmod 600 .ssh/*
[iyunv@node1 ~]# ll .ssh/*
-rw------- 1 root root  399 Dec 18 10:10 .ssh/authorized_keys
-rw------- 1 root root 1675 Dec 18 10:09 .ssh/id_rsa
-rw------- 1 root root  399 Dec 18 10:09 .ssh/id_rsa.pub
[iyunv@node1 ~]# for i in {2..5};do ssh node$i "mkdir /root/.ssh -p" ;done
[iyunv@node1 ~]# for i in {2..5};do scp -p .ssh/{id_rsa,authorized_keys} \
node$i:/root/.ssh ;done
[iyunv@node1 ~]# for i in {1..5};do ssh node$i hostname;done
[iyunv@node2 ~]# for i in {1..5};do ssh node$i hostname;done
[iyunv@node3 ~]# for i in {1..5};do ssh node$i hostname;done

3. 安装配置mariadb galera cluster集群
1） 到http://yum.mariadb.org/5.5-galera/的其中一个目录下载下列软件包进行安装
（需要先配置好系统光盘yum源）
MariaDB-Galera-server
MariaDB-shared
MariaDB-client     
MariaDB-common           
galera
2） 配置http://yum.mariadb.org/5.5-galera/某目录为网络源进行安装，例如如下
vim galera.repo
[galera]
name=mysql_galera_centos7
baseurl=http://yum.mariadb.org/5.5-galera/centos7-amd64/
gpgcheck=0
[iyunv@node1 ~]# yum -y install MariaDB-Galera-server        (注意大小写)
[iyunv@node2 ~]# yum -y install MariaDB-Galera-server
[iyunv@node3 ~]# yum -y install MariaDB-Galera-server

[iyunv@node1 ~]# rpm -ql galera | grep -i smm.so
/usr/lib64/galera/libgalera_smm.so
[iyunv@node1 ~]# vim /etc/my.cnf.d/server.cnf
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.43.201,192.168.43.202,192.168.43.203"
binlog_format=row
        #虽然不依赖二进制日志来复制，但是不开启二进制日志，mysql的集群服务启动不了的
default_storage_engine=InnoDB
        #目前只支持InnoDB存储引擎
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name='mycluster'

[iyunv@node1 ~]# scp /etc/my.cnf.d/server.cnf node2:/etc/my.cnf.d/
[iyunv@node1 ~]# scp /etc/my.cnf.d/server.cnf node3:/etc/my.cnf.d/

mariadb galera cluster集群第一个节点启动方式
[iyunv@node1 ~]# /etc/rc.d/init.d/mysql start --wsrep-new-cluster
Starting MySQL..... SUCCESS!

mariadb galera cluster集群其他节点启动方式
[iyunv@node2 ~]# service mysql start
Starting MySQL....SST in progress, setting sleep higher. SUCCESS!
[iyunv@node3 ~]# service mysql start
Starting MySQL....SST in progress, setting sleep higher. SUCCESS!


mysql_secure_installation 对mysql做进一步安全设定，本文设定root密码为mysql


使用shell脚本不停的插入数据，测试mysql能否同步数据

#!/bin/bash
a=$1
first=0
while true
do
    if [ $# -eq 0 ];then
        echo "需要一个参数"
        exit 1
    fi

    if [ $first -eq 0 ];then
        mysql -uroot -pmysql -e "CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8;"

        mysql -uroot -pmysql testdb -e "CREATE TABLE IF NOT EXISTS students
        (StuID INT PRIMARY KEY,Name char(10) NOT NULL,Age TINYINT NOT NULL)
        ENGINE=InnoDB DEFAULT CHARSET=utf8;"

        first=1   
    fi

    mysql -uroot -pmysql testdb -e "INSERT INTO students (StuID,Name,Age) values
    ($a,'name$a',$a % 100);"
    a=$(($a+1))
    sleep 1
done

查看数据是否能同步


至此mariadb galera cluster集群就构建完成

授权root用户远程登陆（生产环境中建议使用普通用户，而不是root用户）
MariaDB [test]> grant all on *.* to 'root'@'192.168.43.%' identified by '';
建议每个访问IP单独授权  grant all on *.* to 'root'@'192.168.43.206' identified by 'mysql';



二、构建haproxy + keepalived高可用框架
[iyunv@node4 ~]# yum -y install haproxy keepalived
[iyunv@node5 ~]# yum -y install haproxy keepalived


[iyunv@node4 ~]# cd /etc/haproxy/
[iyunv@node4 haproxy]# ls
haproxy.cfg
[iyunv@node4 haproxy]# cp haproxy.cfg{,.bak}
[iyunv@node4 haproxy]# vim haproxy.cfg
defaults
......

listen mysql_proxy 0.0.0.0:3306
    mode tcp
    balance leastconn
        #leastconn可以把请求调度给连接最少的mysql服务器
    option tcpka
    option tcp-check
    server mysql1 192.168.43.201:3306 weight 1
    server mysql2 192.168.43.202:3306 weight 1
    server mysql3 192.168.43.203:3306 weight 1
    #文档中下面的内容删除即可

[iyunv@node4 haproxy]# systemctl start haproxy
[iyunv@node4 haproxy]# ss -tanlp | grep haproxy
LISTEN     0      128                       *:3306                     *:*      
users:(("haproxy",3684,5))

[iyunv@node4 haproxy]# scp /etc/haproxy/haproxy.cfg node5:/etc/haproxy/

[iyunv@node5 ~]# systemctl start haproxy
[iyunv@node5 ~]# ss -tanlp | grep haproxy
LISTEN     0      128                       *:3306                     *:*      
users:(("haproxy",3368,5))

[iyunv@node4 ~]# cd /etc/keepalived/
[iyunv@node4 keepalived]# ls
keepalived.conf
[iyunv@node4 keepalived]# cp keepalived.conf{,.bak}
[iyunv@node4 keepalived]# vim 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 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_script chk_haproxy {
    script "/etc/keepalived/check_haproxy.sh"
    interval 5
    weight -5
}

vrrp_instance VI_1 {
    state MASTER
    interface eno16777736
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 111199
    }
    virtual_ipaddress {
        192.168.43.50/24 dev eno16777736 label eno16777736:1
    }
    track_script {
        chk_haproxy
    }
}

[iyunv@node4 keepalived]# vim check_haproxy.sh
#!/bin/bash
A=`ps -C haproxy --no-header | wc -l`
if [ $A -eq 0 ]; then
        systemctl restart haproxy
        echo "Start haproxy" &> /dev/null
        sleep 3
        if [ `ps -C haproxy --no-header | wc -l`  -eq 0 ];then
                systemctl stop keepalived
                echo "Stop keepalived" &> /dev/null
        fi
fi
[iyunv@node4 keepalived]# chmod +x check_haproxy.sh
[iyunv@node4 keepalived]# scp ./* node5:/etc/keepalived/
[iyunv@node4 keepalived]# systemctl start keepalived
[iyunv@node4 keepalived]# ip a
2: eno16777736:
    inet 192.168.43.204/24 brd 192.168.43.255 scope global eno16777736
    inet 192.168.43.50/24 scope global secondary eno16777736:1

[iyunv@node5 ~]# cd /etc/keepalived/
[iyunv@node5 keepalived]# ls
check_haproxy.sh  keepalived.conf  keepalived.conf.bak
[iyunv@node5 keepalived]# vim 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 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_script chk_haproxy {
    script "/etc/keepalived/check_haproxy.sh"
    interval 5
    weight -5
}

vrrp_instance VI_1 {
    state BACKUP
    interface eno16777736
    virtual_router_id 51
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 111199
    }
    virtual_ipaddress {
        192.168.43.50/24 dev eno16777736 label eno16777736:1
    }
    track_script {
        chk_haproxy
    }
}

[iyunv@node5 keepalived]# vim check_haproxy.sh
#!/bin/bash
A=`ps -C haproxy --no-header | wc -l`
if [ $A -eq 0 ]; then
        systemctl start haproxy
        echo "Start haproxy" &> /dev/null
        sleep 3
        if [ `ps -C haproxy --no-header | wc -l` -eq 0 ];then
                #systemctl stop keepalived
                echo "Stop keepalived" &> /dev/null
        fi
fi

[iyunv@node5 keepalived]# systemctl start keepalived
[iyunv@node5 keepalived]# ip a
2: eno16777736:
    inet 192.168.43.205/24 brd 192.168.43.255 scope global eno16777736
    （说明：没有192.168.43.50/24）


三、使用mysql客户端进行连接
[iyunv@node6 ~]# yum -y install mariadb
[iyunv@node6 ~]# mysql -uroot -h192.168.43.50
MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb1            |
| tb2            |
+----------------+
2 rows in set (0.01 sec)

MariaDB [test]> select * from tb2;
+----+-------+
| id | name  |
+----+-------+
|  2 | hi    |
|  5 | hello |
|  8 | Mike  |
| 11 | Jack  |
| 12 | what  |
| 15 | is    |
| 18 | that  |
+----+-------+
7 rows in set (0.01 sec)

MariaDB [test]> desc tb2;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: test

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(30)         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.06 sec)

MariaDB [test]> insert into tb2(name) values ('how'),('do'),('you'),('do');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from tb2;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: test

+----+-------+
| id | name  |
+----+-------+
|  2 | hi    |
|  5 | hello |
|  8 | Mike  |
| 11 | Jack  |
| 12 | what  |
| 15 | is    |
| 18 | that  |
| 20 | how   |
| 23 | do    |
| 26 | you   |
| 29 | do    |
+----+-------+
11 rows in set (0.04 sec)

此时，haproxy+keepalived来实现mariadb galera cluster的高可用架构完成。