Lvs+keepalived realizing MySQL load balancing from library read operations

http://www.osyunwei.com/archives/7464.html (Learning operation and maintenance Knowledge good station)

Description

Operating system: CentOS 5.X 64-bit

MySQL master server: 192.168.21.126

MySQL slave server: 192.168.21.127,192.168.21.128

MySQL master-Slave synchronization database is: OSYUNWEIDB

Purpose of implementation:

Two additional servers (master standby) for MySQL read-from-Library load balancing via 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 complete, through vip:192.168.21.254, according to the LVS scheduling algorithm to access the backend real MySQL slave server, to achieve load balancing.

Specific operation:

Part one: operate on two MySQL slave servers, respectively

First, close SELinux

Vi/etc/selinux/config

#SELINUX =enforcing #注释掉

#SELINUXTYPE =targeted #注释掉

Selinux=disabled #增加

: wq! #保存退出

Setenforce 0 #使配置立即生效

Second, configure the firewall, open 3306 port

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 #重启防火墙使配置生效

System operation and maintenance www.osyunwei.com warm reminder: qihang01 original content copyright, reproduced please indicate the source and the original link

Third, bound 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 #开启, parameter stop is off

Four, adjust the server parameters, so that the LVS virtual server (VIP) ignore the 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 #使配置立即生效

V. Set up MySQL user and password to 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 on * * to ' checkslave ' @ ' 192.168.21.129 ' identified by ' 123456 ' with GRANT option; #允许账户checkslave从LVS主服务器192.168.21.129 connecting to the database server

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

The second part: operate on two LVS main and standby servers respectively

One, turn off SELinux, configure firewalls

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 #重启防火墙使配置生效

Ii. Installing the LVS software

Yum Install ipvsadm #Linux 2.6 kernel has integrated LVS software, Ipvsadm as LVS management tool here

Modprobe Ip_vs #加载ip_vs

Third, installation 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 prompt, stating 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 #重启

Install 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 Succeeded

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

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

V. Configuration keepalived

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

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

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

! Configuration File for Keepalived

Global_defs {

Notification_email {

[Email protected]

[Email protected]

[Email protected]

}

Notification_email_from [email protected]

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! #保存退出

System operation and maintenance www.osyunwei.com warm reminder: qihang01 original content copyright, reproduced please indicate the source and the original link

Vi. setting up MySQL master/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 #添加脚本执行权限

Viii. test whether the lvs+keepalived is running properly

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

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

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

#可以看到VIP: 192.168.21.254 is now pointing to the LVS master server

1. Close the keepalived service of the LVS master server and see if the LVS standby server is properly taken over keepalived

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

At this point, the IP addr show command is executed on both LVS primary and standby servers, as shown in

System operation and maintenance www.osyunwei.com warm reminder: qihang01 original content copyright, reproduced please indicate the source and the original link

You can see that vip:192.168.21.254 is now pointing to the LVS standby server, indicating that the LVS standby 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; #停止同步

In the LVS standby server Execution ipvsadm-l command, you can see that MySQL from the server 192.168.21.127 has been removed from the Load Balancer list, the test was successful.

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

Telnet 192.168.21.254 3306 #执行此命令, as shown, indicates the test was successful.

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

At this point, the lvs+keepalived implementation of MySQL from the library read operation load balancer configuration is complete.

» This article link: http://www.osyunwei.com/archives/7464.html» subscribe to this site: http://www.osyunwei.com/feed» reprint Please indicate the Source: system operation and Maintenance»lvs+ Keepalived realizing MySQL load balancing from library read operations

Lvs+keepalived realizing MySQL load balancing from library read operations