haproxy+keepalived来实现mariadb galera cluster的高可用架构
网络拓扑图如下：
![wKiom1hfysqALMaCAACFtpfI3dc181.png]( "haproxy+galera.png")
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的高可用架构完成。
本文转自 zhuhc1988 51CTO博客，原文链接：http://blog.51cto.com/changeflyhigh/1886032，如需转载请自行联系原作者
