Lvs+keepalived realize MySQL load balancing from library read operation

Description

Operating system: CentOS 5.X 64-bit

MySQL master server: 192.168.21.126

MySQL from server: 192.168.21.127,192.168.21.128

MySQL master-Slave synchronization of the database is: OSYUNWEIDB

To achieve the purpose:

Add two servers (primary standby) to realize MySQL load balancing from library read operation through lvs+keepalived

Architecture Planning:

Operating system: CentOS 5.X 64-bit

LVS master server: 192.168.21.129

LVS Standby server: 192.168.21.130

LVS Virtual Server (VIP): 192.168.21.254

After the deployment is completed, through vip:192.168.21.254, according to the LVS scheduling algorithm to access the backend real MySQL from the server to achieve load balancing.

Specific actions:

The first part: two MySQL from the server operation

First, close SELinux

Vi/etc/selinux/config

#SELINUX =enforcing #注释掉

#SELINUXTYPE =targeted #注释掉

Selinux=disabled #增加

: wq! #保存退出

Setenforce 0 #使配置立即生效

Second, configure the firewall, open 3306 ports

Vi/etc/sysconfig/iptables #编辑

-A rh-firewall-1-input-m state--state new-m tcp-p TCP--dport 3306-j ACCEPT #允许3306端口通过防火墙

: wq! #保存退出

/etc/init.d/iptables Restart #重启防火墙使配置生效

Third, binding LVS virtual Server (VIP): 192.168.21.254 to lo:0

Vi/etc/rc.d/init.d/realserver #编辑, add the following code

#################################################

#!/bin/sh

# Chkconfig:-80 90

# Description:realserver

# MYSQL_VIP Start Realserver

mysql_vip=192.168.21.254 #LVS虚拟服务器 (VIP)

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

Case "$" in

Start

Ifconfig lo:0 $mysql _vip netmask 255.255.255.255 broadcast $mysql _VIP

/sbin/route add-host $mysql _vip Dev lo:0

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

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

echo "Realserver Start OK"

;;

Stop

Ifconfig lo:0 Down

Route del $mysql _VIP >/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 "Realserver stoped"

;;

*)

echo "Usage: $ {start|stop}"

Exit 1

Esac

Exit 0

#################################################

chmod +x/etc/rc.d/init.d/realserver #添加脚本执行权限

Chkconfig Realserver on #添加开机启动

/etc/rc.d/init.d/realserver start #开启, argument stop is off

Four, adjust the server parameters, so that the LVS virtual server (VIP) ignore ARP broadcast packet

Vi/etc/sysctl.conf #编辑

Net.ipv4.ip_forward= 1 #修改0为1, turn on forwarding

Net.ipv4.conf.lo.arp_ignore= 1

Net.ipv4.conf.lo.arp_announce= 2

Net.ipv4.conf.all.arp_ignore= 1

Net.ipv4.conf.all.arp_announce= 2

: wq! #保存退出

/sbin/sysctl-p #使配置立即生效

Setting up a MySQL user and password that can remotely monitor MySQL master-slave synchronization status

Mysql-u root-p #进入MySQL控制台

Insert into Mysql.user (Host,user,password) VALUES (' localhost ', ' checkslave ', Password (' 123456 ')); #新建账户checkslave, Password 123456

Flush privileges; #刷新系统授权表

Grant all in *.* to ' checkslave ' @ ' 192.168.21.129 ' identified by ' 123456 ' with GRANT option; #允许账户 Checkslave from the LVS master server 192.168.21.129 to the database server

Grant all in *.* to ' checkslave ' @ ' 192.168.21.130' identified by ' 123456 ' with GRANT option; #允许账户checkslave从LVS从服务器192.168.21.130 connect to the database server

Part II: Operating on two LVS primary standby servers

First, close the SELinux, configure the firewall

1, Vi/etc/selinux/config

#SELINUX =enforcing #注释掉

#SELINUXTYPE =targeted #注释掉

Selinux=disabled #增加

: wq! #保存退出

Setenforce 0 #使配置立即生效

2, Vi/etc/sysconfig/iptables #编辑

-A rh-firewall-1-input-d 224.0.0.18-j ACCEPT #允许VRRP (Virtual Router Redundancy Protocol) multicast address communication

-A rh-firewall-1-input-m state--state new-m tcp-p TCP--dport 3306-j ACCEPT #允许3306端口通过防火墙

: wq! #保存退出

/etc/init.d/iptables Restart #重启防火墙使配置生效

Second, the installation of LVS software

Yum Install ipvsadm #Linux 2.6 Kernel has been integrated with the LVS software, which ipvsadm the LVS management tool

Modprobe Ip_vs #加载ip_vs

Iii. installation of keepalived

Yum install gcc gcc-c++ make openssl-devel kernel-devel ncurses-devel #安装编译工具包

Download keeplived:http://www.keepalived.org/software/keepalived-1.2.12.tar.gz

Upload keepalived-1.2.12.tar.gz to/usr/local/src directory

Cd/usr/local/src

Tar zxvf keepalived-1.2.12.tar.gz

CD keepalived-1.2.12

./configure #配置, you must see the following prompts that the configuration is correct to continue the installation

Use Ipvs Framework:yes

Ipvs Sync Daemon Support:yes

Use VRRP Framework:yes

Make #编辑

Make install #安装

cp/usr/local/etc/sysconfig/keepalived/etc/sysconfig/

Mkdir/etc/keepalived

cp/usr/local/etc/keepalived/keepalived.conf/etc/keepalived/

cp/usr/local/sbin/keepalived/usr/sbin/

cp/usr/local/etc/rc.d/init.d/keepalived/etc/rc.d/init.d/

chmod +x/etc/rc.d/init.d/keepalived #添加执行权限

Chkconfig keepalived on #设置开机启动

Service keepalived Start #启动

Service keepalived Stop #关闭

Service keepalived Restart #重启

Iv. Installing Perl, PERL-DBI, dbd-mysql modules, and MySQL client #perl脚本连接MySQL数据库需要

Yum Install Perl perl-dbi perl-dbd-mysql MySQL #执行此命令安装

vi/tmp/test_perl.pl

#!/usr/bin/perl

Print "Hello, world!n";

: wq! #保存退出

perl/tmp/test_perl.pl #运行测试脚本, if present: hello,world! Description Perl installation Successful

Perldoc DBI #查看DBI模块是否安装

Perldoc dbd::mysql #查看DBD:: MySQL module is installed

V. Configuration of keepalived

Cp/etc/keepalived/keepalived.conf/etc/keepalived/keepalived.conf-bak

Vi/etc/keepalived/keepalived.conf #编辑, modified to the following code

##################################################################################################

! 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_instance Vi_1 {

State MASTER #LVS备机修改为BACKUP

Interface eth0

VIRTUAL_ROUTER_ID 51

Priority #LVS备机修改为80

Advert_int 1

Authentication {

Auth_type Pass

Auth_pass 1111

}

virtual_ipaddress {

192.168.21.254

}

Notify_master "/etc/keepalived/clean_arp.sh" #LVS主备机都为notify_master

}

Virtual_server 192.168.21.254 3306 {

Delay_loop 30

Lb_algo WLC

Lb_kind DR

#nat_mask 255.255.255.0

Persistence_timeout 120

Protocol TCP

Real_server 192.168.21.127 3306 {

Weight 1

Misc_check {

Misc_path "/etc/keepalived/check_slave.pl 192.168.21.127"

Misc_dynamic

}

}

Real_server 192.168.21.128 3306 {

Weight 1

Misc_check {

Misc_path "/etc/keepalived/check_slave.pl 192.168.21.128"

Misc_dynamic

}

}

##################################################################################################

: wq! #保存退出

Vi. set up MySQL master and slave monitoring script check_slave.pl

vi/etc/keepalived/check_slave.pl #编辑, add the following code

##################################################################################################

#!/usr/bin/perl-w

Use DBI;

Use Dbd::mysql;

# CONFIG VARIABLES

$SBM = 120;

$db = "Osyunweidb";

$host = $ARGV [0];

$port = 3306;

$user = "Checkslave";

$PW = "123456";

# SQL Query

$query = "show slave status";

$DBH = Dbi->connect ("Dbi:mysql: $db: $host: $port", $user, $PW, {raiseerror => 0,printerror 0});

if (!defined ($DBH)) {

Exit 1;

}

$sqlQuery = $dbh->prepare ($query);

$sqlQuery->execute;

$Slave _io_running = "";

$Slave _sql_running = "";

$Seconds _behind_master = "";

while (my $ref = $sqlQuery->fetchrow_hashref ()) {

$Slave _io_running = $ref->{' slave_io_running '};

$Slave _sql_running = $ref->{' slave_sql_running '};

$Seconds _behind_master = $ref->{' Seconds_behind_master '};

}

$sqlQuery->finish;

$DBH->disconnect ();

if ($Slave _io_running eq "no" | | $Slave _sql_running eq "no") {

Exit 1;

} else {

if ($Seconds _behind_master > $SBM) {

Exit 1;

} else {

Exit 0;

}

}

##################################################################################################

: wq! #保存退出

chmod +x/etc/keepalived/check_slave.pl #添加脚本执行权限

Vii. setting up an ARP record to update the LVS virtual Server (VIP) address to the gateway script

Vi/etc/keepalived/clean_arp.sh #编辑, add the following code

#!/bin/sh

vip=192.168.21.254

gateway=192.168.21.2

/sbin/arping-i eth0-c 5-s $VIP $GATEWAY &>/dev/null

: wq! #保存退出

chmod +x/etc/keepalived/clean_arp.sh #添加脚本执行权限

Eight, test whether the lvs+keepalived is normal operation

Service keepalived Restart #在两台LVS主备服务器上重启keepalived

Ipvsadm-l #在两台LVS主备服务器上执行此命令, see the following figure

IP addr Show #在两台LVS主备服务器上执行此命令, see the following figure

#从图中可以看到VIP: 192.168.21.254 now points to the LVS master server

1. Turn off the keepalived service of the LVS master server to see if the LVS standby server is properly taking over keepalived

Service keepalived Stop #在LVS主服务器上运行

At this point, the IP addr show command is executed on the two LVS primary standby servers, as shown in the following illustration

You can see that vip:192.168.21.254 is now pointing to the LVS standby server, stating that the LVS server has taken over the keepalived and tested successfully.

2, stop MySQL from the server 192.168.21.127 above the master-slave synchronization Service

Mysql-u root-p #进入MySQL控制台

Slave start; #停止同步

The ipvsadm-l command is executed on the LVS standby server and you can see that MySQL has been removed from the load balancing list from the server 192.168.21.127 and the test has been successful.

3, from the Client connection LVs virtual Server (VIP): 192.168.21.254

Telnet 192.168.21.254 3306 #执行此命令, as shown in the following illustration, to illustrate the success of the test.

Tail-f/var/log/messages #查看LVS日志信息