mysql lvs+keepalived+mha_Keepalived+Lvs+Mysql主主复制

最新推荐文章于 2023-05-12 11:09:18 发布

猫本聪

最新推荐文章于 2023-05-12 11:09:18 发布

阅读量191

点赞数

文章标签： mysql lvs+keepalived+mha

本文链接：https://blog.csdn.net/weixin_42501077/article/details/113908648

版权

一简单介绍

Keepalived+lvs+mysql主主复制是比較经常使用的一种Mysql高可用方案，当中lvs 提供读负载均衡，Keepalived通过虚拟vip漂移实现故障自己主动转移，而Mysql尽管配置成主主复制。可是一般都是採用单点写入来保证数据一致性。

整个方案实现很easy，仅需两台server就可以，安装配置也相对简单，但须要自己写脚本实现keepalived服务的开启和关闭，适用于仅仅有两台数据库server而且未实现读写分离的场景。

系统总体结构框图例如以下

Center

二环境搭建

2.1 环境配置例如以下

Keepalived

Server

LVS

Server

LVS

Realserver

MySQL

Master-Master

Write VIP

Read VIP

MySQL

Master

10.0.11.243

10.0.11.190

10.0.11.191

Centos6

Percona5.6

Backup

10.0.11.244

10.0.11.190

10.0.11.191

Centos6

Percona5.6

2.2 mysql master-master复制配置

主主复制配置本质上就是配置两台server互相的主从，实现比較简单，大致过程例如以下

2.2.1 在Master上创建一个复制账户

mysql>grantreplication slave on *.* to

rep71@10.0.11.244identified by‘123456’;

mysql>flushprivileges;

2.2.2 改动两台mysql配置文件my.cnf例如以下

##Replication General Config (both master and slave)

server-id= 101

log-bin= mysql-log-bin

auto-increment-increment= 2

auto-increment-offset= 1

binlog_format= row

relay_log= mysql-relay-bin

2.2.3 在Masterserver上运行show master status，记录下File和Position

mysql>show master status\G;

***************************1. row ***************************

File:mysql-log-bin.000005

Position:327933

Binlog_Do_DB:

Binlog_Ignore_DB:test,mysql,information_schema,performance_schema

2.2.4 在Slave服务上运行

mysql> slave stop;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to

-> master_host='10.0.11.243',

-> master_user='rep71',

-> master_password='123456',

-> master_log_file=' mysql-log-bin.000005',

-> master_log_pos=327933;

Query OK, 0 rows affected (0.01 sec)

mysql> start slave;

2.2.5反复以上四个步骤。在Slaveserver上创建账户rep72，show master status。在Master上start slave，这样mysql的主主同步就配置好了，分别在两台server上执行show slave status，假设结果都显示Slave_IO_Running: Yes，Slave_SQL_Running: Yes。则证明同步正常。

2.3 Keepalived的安装

2.3.1 在 master、backup server都进行安装：

sudotar zxvf ipvsadm-1.24.tar.gz

cdipvsadm-1.24

sudoln -s /usr/src/kernels/2.6.32-358.el6.x86_64/ /usr/src/linux

make

sudomake install

2.4 Keepalived 的安装

2.4.1在 master、backup server都进行安装：

tarzxvf keepalived-1.1.19.tar.gz

cdkeepalived-1.1.19

yuminstall -y openssl openssl-devel

sudoyum install popt-devel –y

./configure--sysconf=/etc/ --with-kernel-dir=/usr/src/kernels/2.6.18-308.el5-x86_64/

make

sudomake install

ln -s /usr/local/sbin/keepalived /sbin/

2.4.2 Master上的Keepalived 的配置

global_defs{

router_id mysqlmha1

}

vrrp_scriptcheck_run {

script"/etc/keepalived/check_mysqll.sh"

interval 1

}

vrrp_instanceVI_1 {

state MASTER

interface em1

virtual_router_id 51

priority 100

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

check_run

}

virtual_ipaddress {

10.0.11.190 dev em1 label em1:1

10.0.11.191 dev em1 label em1:2

}

virtual_server10.0.11.190 3306 {

delay_loop6

lb_algorr

lb_kindDR

persistence_timeout20

protocolTCP

sorry_server10.0.11.244 3306

real_server10.0.11.243 3306 {

weight3

TCP_CHECK{

connect_timeout3

nb_get_retry3

delay_before_retry3

connect_port3306

}

virtual_server10.0.11.191 3306 {

delay_loop6

lb_algorr

lb_kindDR

#persistence_timeout20

protocolTCP

real_server10.0.11.243 3306 {

weight3

TCP_CHECK{

connect_timeout3

nb_get_retry3

delay_before_retry3

connect_port3306

}

real_server10.0.11.244 3306 {

weight3

TCP_CHECK{

connect_timeout3

nb_get_retry3

delay_before_retry3

connect_port3306

}

2.4.3 Backup 的 keepalived 的配置

global_defs {

router_id mysqlmha1

}

vrrp_script check_run {

script "/etc/keepalived/check_mysqll.sh"

interval 1

}

vrrp_instance VI_1 {

state BACKUP

interface em1

virtual_router_id 51

priority 90

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

check_run

}

virtual_ipaddress {

10.0.11.190 dev em1 label em1:1

10.0.11.191 dev em1 label em1:2

}

virtual_server 10.0.11.190 3306 {

delay_loop 6

lb_algo rr

lb_kind DR

persistence_timeout 20

protocol TCP

sorry_server 10.0.11.244 3306

real_server 10.0.11.243 3306 {

weight 3

TCP_CHECK {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

virtual_server 10.0.11.191 3306 {

delay_loop 6

lb_algo rr

lb_kind DR

#persistence_timeout 20

protocol TCP

real_server 10.0.11.243 3306 {

weight 3

TCP_CHECK {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

real_server 10.0.11.244 3306 {

weight 3

TCP_CHECK {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

2.5 Master 和 backup 的 realserver 的配置

对于 realserver 的配置 master 和 backup 是一致的，脚本内容例如以下：

#!/bin/bash

#description: Config realserver lo and apply noarp

SNS_VIP=10.1.11.190

SNS_VIP2=10.0.11.191

/etc/rc.d/init.d/functions

case"$1" in

start)

ifconfiglo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP

ifconfiglo:1 $SNS_VIP2 netmask 255.255.255.255 broadcast $SNS_VIP2

/sbin/routeadd -host $SNS_VIP dev lo:0

/sbin/routeadd -host $SNS_VIP2 dev lo:1

echo"1" >/proc/sys/net/ipv4/conf/lo/arp_ignore

echo"2" >/proc/sys/net/ipv4/conf/lo/arp_announce

echo"1" >/proc/sys/net/ipv4/conf/all/arp_ignore

echo"2" >/proc/sys/net/ipv4/conf/all/arp_announce

echo"1" >/proc/sys/net/ipv4/conf/em1/arp_ignore

echo"2" >/proc/sys/net/ipv4/conf/em1/arp_announce

sysctl-p >/dev/null 2>&1

echo"RealServer Start OK"

;;

stop)

ifconfiglo:0 down

ifconfiglo:1 down

routedel $SNS_VIP >/dev/null 2>&1

routedel $SNS_VIP2 >/dev/null 2>&1

echo"0" >/proc/sys/net/ipv4/conf/lo/arp_ignore

echo"0" >/proc/sys/net/ipv4/conf/lo/arp_announce

echo"0" >/proc/sys/net/ipv4/conf/all/arp_ignore

echo"0" >/proc/sys/net/ipv4/conf/all/arp_announce

echo"0" >/proc/sys/net/ipv4/conf/em1/arp_ignore

echo"0" >/proc/sys/net/ipv4/conf/em1/arp_announce

echo"RealServer Stoped"

;;

echo"Usage: $0 {start|stop}"

exit1

esac

exit0

2.6 编辑脚本控制Keepalived的开启和停止

大体意思是仅仅要检測到mysql服务停止keepalived服务也停止，由于keepalived是通过组播方式告诉本网段自己还活着当mysql服务停止后keepalived还依旧执行这时就须要停止keepalived让还有一个主机获得虚拟IP，能够在后台执行这个脚本也能够在keepalived配置文件增加这个脚本，master和slave节点都须要，仅仅是数据库username和password有差别[root@244 ~]#more /etc/keepalived/check_mysql.sh

#20140722

#!/bin/bash

MYSQL=/usr/bin/mysql

MYSQL_HOST=10.0.11.243

MYSQL_USER=root

MYSQL_PASSWORD=mysql

CHECK_TIME=3

#mysql isworking MYSQL_OK is 1 , mysql down MYSQL_OK is 0

MYSQL_OK=1

function check_mysql_helth (){

$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "showstatus;" >/dev/null 2>&1

if [ $?

= 0 ] ;then

MYSQL_OK=1

else

MYSQL_OK=0

return $MYSQL_OK

}

while [ $CHECK_TIME -ne 0 ]

let "CHECK_TIME -= 1"

check_mysql_helth

if [ $MYSQL_OK = 1 ] ; then

CHECK_TIME=0

exit 0

if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]

then

pkill keepalived

exit 1

sleep 1

done

该脚本须要有运行权限。通过下面命令加上运行权限# chmod +x check_mysql.sh

2.7測试Keepalived自己主动故障转移

Master和slave上运行sudo servicekeepalived start，在master上运行ip

Center

Slave上运行ip a

从上能够看到masteMySQL上虚拟IP绑定成功，backup

MySQL上keepalived服务正常执行，但无虚拟IP(这属于正常)

client能通过vip连接上mysql

停止master上的mysql服务，可发现Keepalived服务也会停止

这时候slave执行ip a发现绑定了虚拟ip，而且从库mysql执行正常，而client依然能通过vip连上数据库

Center

重新启动master的mysql和Keepalived以后，并在slave库执行start

slave，master执行ip a发现恢复之前的服务

2.8測试lvs的负载均衡效果

启动 master 和 backup 的 mysql 以后，再在 master 和 backup 运行例如以下命令启

动 keepalived 和 realserver 脚本：

/etc/rc.d/init.d/realserver.sh start

/etc/rc.d/init.d/keepalived start

执行master和backup上的sudo ipvsadm -ln可看到例如以下信息

在client通过read vip连接到mysql并运行几次select,可考到inaction发生了变化。证明实现了负载均衡

猫本聪

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql lvs+keepalived+mha_Keepalived+Lvs+Mysql主主复制

一简单介绍Keepalived+lvs+mysql主主复制是比較经常使用的一种Mysql高可用方案，当中lvs 提供读负载均衡，Keepalived通过虚拟vip漂移实现故障自己主动转移，而Mysql尽管配置成主主复制。可是一般都是採用单点写入来保证数据一致性。整个方案实现很easy，仅需两台server就可以，安装配置也相对简单，但须要自己写脚本实现keepalived服务的开启和关闭，适用于仅...
复制链接

扫一扫