search

Home >Database >Mysql Tutorial >Keepalived+Lvs+Mysql主主复制_MySQL

Keepalived+Lvs+Mysql主主复制_MySQL

WBOY
WBOYOriginal
2016-06-01 13:01:261077browse

一简介

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

整个方案实现非常简单，仅需两台服务器即可，安装配置也相对简单，但需要自己写脚本实现keepalived服务的开启和关闭，适用于只有两台数据库服务器并且未实现读写分离的场景。系统整体结构框图如下

\

二环境搭建

2.1 环境配置如下

Keepalived

Server

LVS

Server

LVS

Realserver

MySQL

Master-Master

Write VIP

Read VIP

OS

MySQL

Master

10.0.11.243

10.0.11.243

10.0.11.243

10.0.11.243

10.0.11.190

10.0.11.191

Centos6

Percona5.6

Backup

10.0.11.244

10.0.11.244

10.0.11.244

10.0.11.244

10.0.11.190

10.0.11.191

Centos6

Percona5.6

 

 

2.2 mysql master-master复制配置

主主复制配置本质上就是配置两台服务器互相的主从，实现比较简单，大致步骤如下

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 在Master服务器上执行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重复以上四个步骤，在Slave服务器上创建账户rep72，show master status，在Master上start slave，这样mysql的主主同步就配置好了，分别在两台服务器上运行show slave status，如果结果都显示Slave_IO_Running: Yes，Slave_SQL_Running: Yes，则证明同步正常。

 

2.3 Keepalived的安装

2.3.1 在 master、backup 服务器都进行安装：

wget http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.24.tar.gz

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 服务器都进行安装：

wget http://www.keepalived.org/software/keepalived-1.1.19.tar.gz

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: ___FCKpd___7 {start|stop}"

exit1

esac

exit0

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

大体意思是只要检测到mysql服务停止keepalived服务也停止 ，因为keepalived是通过组播方式告诉本网段自己还活着当mysql服务停止后keepalived还依然运行 这时就需要停止keepalived让另一个主机获得虚拟IP，可以在后台运行这个脚本 也可以在keepalived配置文件加入这个脚本，master和slave节点都需要，只是数据库用户名和密码有区别
 

[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
fi
return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
CHECK_TIME=0
exit 0
fi
if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]
then
pkill keepalived
exit 1
fi
sleep 1
done

该脚本需要有执行权限，通过以下命令加上执行权限
# chmod +x check_mysql.sh

2.7 测试Keepalived自动故障转移

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

\

Slave上执行 ip a

\

从上可以看到masteMySQL上虚拟IP绑定成功，backup MySQL上keepalived服务正常运行，但无虚拟IP(这属于正常)

客户端能通过vip连接上mysql

\

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

\

这时候slave运行ip a发现绑定了虚拟ip，并且从库mysql运行正常，而客户端依旧能通过vip连上数据库

\

\

重启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可看到如下信息

\

 

在客户端通过read vip连接到mysql并执行几次select,可考到inaction发生了变化，证明实现了负载均衡

\

 

Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：MySQL主从复制资料汇总_MySQLNext article：mysql explain解析_MySQL

Related articles

See more