首页 > 代码库 > haproxy+keepalived来实现mariadb galera cluster的高可用架构

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


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. 配置节点之间网络时间同步

[root@node1 ~]# crontab -l

*/5 * * * * /usr/sbin/ntpdate -u 129.6.15.28 &> /dev/null

[root@node2 ~]# crontab -l

*/5 * * * * /usr/sbin/ntpdate -u 129.6.15.28 &> /dev/null

[root@node3 ~]# crontab -l

*/5 * * * * /usr/sbin/ntpdate -u 129.6.15.28 &> /dev/null


2. 配置集群节点互信

[root@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

[root@node5 ~]# for i in {2..5}; do scp /etc/hosts node$i:/etc; done

[root@node1 ~]# ssh-keygen -t rsa -P ‘‘ -f ‘/root/.ssh/id_rsa‘

[root@node1 ~]# cat .ssh/id_rsa.pub >> .ssh/authorized_keys

[root@node1 ~]# chmod 600 .ssh/*

[root@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

[root@node1 ~]# for i in {2..5};do ssh node$i "mkdir /root/.ssh -p" ;done

[root@node1 ~]# for i in {2..5};do scp -p .ssh/{id_rsa,authorized_keys} \

node$i:/root/.ssh ;done

[root@node1 ~]# for i in {1..5};do ssh node$i hostname;done

[root@node2 ~]# for i in {1..5};do ssh node$i hostname;done

[root@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

[root@node1 ~]# yum -y install MariaDB-Galera-server (注意大小写)

[root@node2 ~]# yum -y install MariaDB-Galera-server

[root@node3 ~]# yum -y install MariaDB-Galera-server


[root@node1 ~]# rpm -ql galera | grep -i smm.so

/usr/lib64/galera/libgalera_smm.so

[root@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‘


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

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


mariadb galera cluster集群第一个节点启动方式

[root@node1 ~]# /etc/rc.d/init.d/mysql start --wsrep-new-cluster

Starting MySQL..... SUCCESS! 


mariadb galera cluster集群其他节点启动方式

[root@node2 ~]# service mysql start

Starting MySQL....SST in progress, setting sleep higher. SUCCESS! 

[root@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高可用框架

[root@node4 ~]# yum -y install haproxy keepalived

[root@node5 ~]# yum -y install haproxy keepalived



[root@node4 ~]# cd /etc/haproxy/

[root@node4 haproxy]# ls

haproxy.cfg

[root@node4 haproxy]# cp haproxy.cfg{,.bak}

[root@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

    #文档中下面的内容删除即可

    

[root@node4 haproxy]# systemctl start haproxy

[root@node4 haproxy]# ss -tanlp | grep haproxy

LISTEN     0      128                       *:3306                     *:*      

users:(("haproxy",3684,5))


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


[root@node5 ~]# systemctl start haproxy

[root@node5 ~]# ss -tanlp | grep haproxy

LISTEN     0      128                       *:3306                     *:*      

users:(("haproxy",3368,5))


[root@node4 ~]# cd /etc/keepalived/

[root@node4 keepalived]# ls

keepalived.conf

[root@node4 keepalived]# cp keepalived.conf{,.bak}

[root@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

    }

}


[root@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

[root@node4 keepalived]# chmod +x check_haproxy.sh 

[root@node4 keepalived]# scp ./* node5:/etc/keepalived/

[root@node4 keepalived]# systemctl start keepalived

[root@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


[root@node5 ~]# cd /etc/keepalived/

[root@node5 keepalived]# ls

check_haproxy.sh  keepalived.conf  keepalived.conf.bak

[root@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

    }

}


[root@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


[root@node5 keepalived]# systemctl start keepalived

[root@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客户端进行连接

[root@node6 ~]# yum -y install mariadb

[root@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的高可用架构完成。


本文出自 “change” 博客,请务必保留此出处http://changeflyhigh.blog.51cto.com/8317626/1886032

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