Lvs+keepalived realize MySQL load balancing from library read operation

Source: Internet
Author: User
Tags chmod mysql client mysql load balancing centos install perl iptables firewall
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日志信息

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.