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

最新推荐文章于 2021-12-29 08:56:01 发布

weixin_34253539

最新推荐文章于 2021-12-29 08:56:01 发布

阅读量191

点赞数

文章标签：数据库开发工具运维

原文链接：http://blog.51cto.com/changeflyhigh/1886032

版权

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

if [ $# -eq 0 ];then

echo "需要一个参数"

exit 1

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

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

[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

[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

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

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

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

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

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

转载于:https://blog.51cto.com/changeflyhigh/1886032

weixin_34253539

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
haproxy+keepalived来实现mariadb galera cluster的高可用架构

haproxy+keepalived来实现mariadb galera cluster的高可用架构网络拓扑图如下：mariadb galera cluster集群基于wsrep协议可以实现mysql多主复制架构，详细说明如下：galera cluster复制：galera cluster本身是基于wsrep协议工作在底层的文件复制通道而已，只需要指明复制的文件，它会自动在多个...
复制链接

扫一扫