主页 > 网络知识, 运维知识 > 支持高并发高可用的mysql集群(keepavlied+haproxy+mysql)[原创]

支持高并发高可用的mysql集群(keepavlied+haproxy+mysql)[原创]

2012年12月18日 发表评论 查看评论

 

明月采用了6个mysql数据库,其中两主四从,两个主之间互为主从,1个主带两个从。读写分离,主负责写,从负责读。如图:

使用haproxy实现读请求负载均衡以及故障切换。方法是通过xinted调用监控脚本返回一个页面,然后配置haproxy实现。这样做是因为用http_check要比haproxy自带的tcp检查要准确,并且haproxy自带的检验无法知道slave是否正常工作。可以通过脚本检查我们需要的。

使用keepalived实现写请求故障切换。单一个主发生故障的时候,另一个主自动替换。

配置方法:

一.安装好mysql,先配置1主5从。之后配置主主的时候记得先调用flush tables with read lock锁表,避免不必要的错误。
二.配置从服务器的负载均衡以及故障切换:
在安装haproxy的机器上配置haproxy.cfg,增加下面两个配置:

frontend proxy-3308
bind :3308
mode tcp
log global
maxconn 50000
timeout client 60s
timeout http-request 30s
default_backend proxy-3308
backend proxy-3308
mode tcp
balance roundrobin
option tcplog
timeout queue 50s
timeout connect 30s
timeout server 30s
timeout http-request 30s
option httpchk
server mysqlserver10 192.168.0.10:3308 check port 9201 inter 1500 rise 5 fall 3 weight 3
server mysqlserver11 192.168.0.11:3308 check port 9201 inter 1500 rise 5 fall 3 weight 3
server mysqlserver12 192.168.0.12:3308 check port 9201 inter 1500 rise 5 fall 3 weight 3
server mysqlserver13 192.168.0.13:3308 check port 9201 inter 1500 rise 5 fall 3 weight 3

其中check port 9201 是明月用xinted生成的监控页面端口。
在所有mysql-salve的机器上面安装xinted并配置如下,先用yum安装xinted,然后进入/etc/xinetd.d目录,新增下面文件:

[root@DB1 xinetd.d]# cat mysqlchk
service mysqlchk_read
{
flags = REUSE
socket_type = stream
port = 9201
wait = no
user = nobody
server = /opt/mysqlchk_replication.pl
log_on_failure += USERID
disable = no
only_from = 192.168.0.0/24 # recommended to put the IPs that need
}

服务名称为mysqlchk_read,记下,然后配置/etc/services,在最后增加一行:

mysqlchk_read 9201/tcp # mysql slave check

之后重启xinetd: service xinetd restart
之后编写脚本/opt/mysqlchk_replication.pl,内容如下:

[root@DB1 xinetd.d]# cat /opt/mysqlchk_replication.pl
#!/usr/bin/perl
use strict;
use Data::Dumper;

my $MYSQL_HOST="127.0.0.1";
my $MYSQL_PORT=3308;
my $MYSQL_USERNAME="xxxx";
my $MYSQL_PASSWORD="xxxx";

# We perform a simple query that should return a few results
my $ERROR_MSG = `/usr/local/percona/bin/mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e "show slave status \\G;" `;

# Check the output. If it is not empty then everything is fine and we return
# something. Else, we just do not return anything.
if ( $ERROR_MSG ne "" ){
if( $ERROR_MSG =~/Slave_IO_Running: Yes/i && $ERROR_MSG =~/Slave_SQL_Running: Yes/i){
# mysql is fine, return http 200
print "HTTP/1.1 200 OK\r\n";
print "Content-Type: Content-Type: text/plain\r\n";
print "\r\n";
print "MySQL is running.\r\n";
print "\r\n";
}else{
# mysql is fine, but slave not work ,return http 503
print "HTTP/1.1 500 Service Error\r\n";
print "Content-Type: Content-Type: text/plain\r\n";
print "\r\n";
print "MySQL slave *not work*.\r\n";
print "\r\n";
}
}else{
# mysql is down, return http 503
print "HTTP/1.1 503 Service Unavailable\r\n";
print "Content-Type: Content-Type: text/plain\r\n";
print "\r\n";
print "MySQL is *down*.\r\n";
print "\r\n";
}

当整个mysql服务不能使用时,返回503错误;当主从同步异常时候返回500错误;当mysql正常时候返回200。
记得赋权限给该脚本。
之后到haproxy的监控页面,可以看到下图,但9021端口返回值不是200时候,haproxy会自动将该salve从负载均衡中标识成故障状态,保证请求不在发送到这个slave上面。

三.配置主服务器的故障切换:
1.在两台主库服务器上面都需要安装keepalived.下载可以到:www.keepalived.org上面下载。安装必要的ipvs,ipvsadm等,然后./configure => make => make install三部曲。
2.配置台服务器的keeplived,keepalived也分主从。配置如下:
master的配置:

vrrp_script chk_mysql_master {
script “/usr/local/keepalived/keepalived_check_mysql.pl”
interval 2
weight 2
}
vrrp_instance VI_1 {
interface eth0
state MASTER # 从为BACKUP
priority 100 # 从为99,数值大优先级高
virtual_router_id 11 #路由ID,可通过#tcpdump vrrp查看。
garp_master_delay 1 #主从切换时间,单位为秒。
advert_int 1 #检查间隔,默认1秒
#nopreempt #设置为不抢占 注:这个配置只能设置在backup主机上,而且这个主机优先级要比另外一台
authentication {
auth_type PASS
auth_pass 11235
}
virtual_ipaddress {
192.168.0.15
}
track_script {
chk_mysql_master
}
}

slave上面配置:

vrrp_script chk_mysql_master {
script “/usr/local/keepalived/keepalived_check_mysql.pl”
interval 2
weight 2
}
vrrp_instance VI_1 {
interface eth0
state SLAVE # 从为BACKUP
priority 99 # 主为100
virtual_router_id 11 #路由ID,可通过#tcpdump vrrp查看。
garp_master_delay 1 #主从切换时间,单位为秒。
advert_int 1 #检查间隔,默认1秒
nopreempt #设置为不抢占
authentication {
auth_type PASS #主从这里必须一致
auth_pass 11235 #主从这里必须一致
}
virtual_ipaddress {
192.168.0.15 #虚拟IP地址
}
track_script {
chk_mysql_master
}
}

keepalived是可以配置虚拟IP的LVS负载均衡的,但明月这里只需要keepalived实现故障时候自动切换。一部主数据库是用来作为备份使用。所以配置vrrp即可。

[root@gleasy keepalived]# cat keepalived_check_mysql.pl
#!/usr/bin/perl
my $MYSQL="/usr/local/percona/bin/mysql";
my $MYSQL_HOST="192.168.0.10";
my $MYSQL_PORT=3307;
my $MYSQL_USERNAME="xxxx";
my $MYSQL_PASSWORD="xxxx";
my $CHECK_TIME=5;
#mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
my $MYSQL_OK=1;
sub check_mysql_helth (){
my $ERROR_MSG = `$MYSQL --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e "show databases;" 2>/dev/null `;
if ( $ERROR_MSG ne "" ){
$MYSQL_OK=1;
}else{
$MYSQL_OK=0;
}
return $MYSQL_OK;
}
while($CHECK_TIME > 0){
check_mysql_helth();
$CHECK_TIME -= 1;
if($MYSQL_OK == 1){
$CHECK_TIME=0;
exit(0);
}
if($MYSQL_OK == 0 && $CHECK_TIME == 0 ){
system("service keepalived stop");
exit(1);
}
sleep(3);
}

验证:

上面配置完后明月开始做验证:
先做从库故障验证,当一个从库关闭,这个时候haproxy就会把对应的服务标识为故障状态。下图为故障状态报L7STS/503 in 21ms 报503错误。

从库重新开启后恢复正常

然后做主库故障验证:把正在工作的mysql主数据库停掉。这个时候keepalived起作用,使用VIP访问master,没感觉任何影响。
可以看到keepalived从服务器上的日志如下:

Dec 7 10:48:31 DB1 Keepalived_vrrp[1821]: VRRP_Instance(VI_1) Entering MASTER STATE
Dec 7 10:48:31 DB1 Keepalived_vrrp[1821]: VRRP_Instance(VI_1) setting protocol VIPs.
Dec 7 10:48:31 DB1 Keepalived_vrrp[1821]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.0.13
Dec 7 10:48:31 DB1 Keepalived_healthcheckers[1820]: Netlink reflector reports IP 192.168.0.13 added
Dec 7 10:48:36 DB1 Keepalived_vrrp[1821]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.0.13
Dec 7 10:51:44 DB1 Keepalived_healthcheckers[1820]: TCP connection to [192.168.0.10]:3307 success.
Dec 7 10:51:44 DB1 Keepalived_healthcheckers[1820]: Adding service [192.168.0.10]:3307 to VS [192.168.0.13]:3307
Dec 7 10:51:44 DB1 Keepalived_healthcheckers[1820]: IPVS: Service not defined
Dec 7 10:51:44 DB1 Keepalived_healthcheckers[1820]: Remote SMTP server [0.0.0.0]:25 connected.
Dec 7 10:51:44 DB1 Keepalived_healthcheckers[1820]: SMTP alert successfully sent.

当数据库恢复后,开启主keepalived服务,这个时候keepalived从服务器上的日志如下:

Dec 7 10:58:35 DB1 Keepalived_vrrp[1821]: VRRP_Instance(VI_1) Received higher prio advert
Dec 7 10:58:35 DB1 Keepalived_vrrp[1821]: VRRP_Instance(VI_1) Entering BACKUP STATE
Dec 7 10:58:35 DB1 Keepalived_vrrp[1821]: VRRP_Instance(VI_1) removing protocol VIPs.
Dec 7 10:58:35 DB1 Keepalived_vrrp[1821]: VRRP_Group(VG1) Syncing instances to BACKUP state
Dec 7 10:58:35 DB1 Keepalived_healthcheckers[1820]: Netlink reflector reports IP 192.168.0.13 removed
Dec 7 11:01:38 DB1 Keepalived_healthcheckers[1820]: TCP connection to [192.168.0.10]:3307 failed !!!
Dec 7 11:01:38 DB1 Keepalived_healthcheckers[1820]: Removing service [192.168.0.10]:3307 from VS [192.168.0.13]:3307
Dec 7 11:01:38 DB1 Keepalived_healthcheckers[1820]: IPVS: Service not defined
Dec 7 11:01:38 DB1 Keepalived_healthcheckers[1820]: Remote SMTP server [0.0.0.0]:25 connected.
Dec 7 11:01:38 DB1 Keepalived_healthcheckers[1820]: SMTP alert successfully sent.

可以看到从keepalived服务再次进入backup状态,这个时候这个集群恢复到故障前状态。这一次对于客户端是不可见的,用户甚至不会察觉到后台数据出现过故障。

而且haproxy上两个对应的该master的从数据库的也会跟着变为故障状态,因为我们脚本里面实现了,slave同步失败的时候返回500错误不是之前的503状态。haproxy的http检测只要不是返回200,就会把这两个从库从负载均衡中移除。这样就保证了数据的一致性。

 

 

原创文章,转载请注明: 转载自肚腩照明月'blog

本文链接地址: 支持高并发高可用的mysql集群(keepavlied+haproxy+mysql)[原创]

文章的脚注信息由WordPress的wp-posturl插件自动生成


  1. 2016年3月2日10:20 | #1

    请教下,如何自动替换mysql里的字符串?

SEO Powered by Platinum SEO from Techblissonline