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
Add two servers (primary standby) to realize MySQL load balancing from library read operation through lvs+keepalived
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.
The first part: two MySQL from the server operation
#SELINUXTYPE =targeted #注释掉
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端口通过防火墙
/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
#################################################
# MYSQL_VIP Start Realserver
mysql_vip=192.168.21.254 #LVS虚拟服务器 (VIP)
. /etc/rc.d/init.d/functions
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
echo "Realserver Start OK"
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 "Usage: $ {start|stop}"
#################################################
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
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
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
#SELINUXTYPE =targeted #注释掉
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端口通过防火墙
/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
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
Tar zxvf keepalived-1.2.12.tar.gz
./configure #配置, you must see the following prompts that the configuration is correct to continue the installation
Ipvs Sync Daemon Support:yes
cp/usr/local/etc/sysconfig/keepalived/etc/sysconfig/
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 #执行此命令安装
perl/tmp/test_perl.pl #运行测试脚本, if present: hello,world! Description Perl installation Successful
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
Notification_email_from Alexandre.Cassen@firewall.loc
Smtp_server 192.168.200.1
State MASTER #LVS备机修改为BACKUP
Notify_master "/etc/keepalived/clean_arp.sh" #LVS主备机都为notify_master
Virtual_server 192.168.21.254 3306 {
Real_server 192.168.21.127 3306 {
Misc_path "/etc/keepalived/check_slave.pl 192.168.21.127"
Real_server 192.168.21.128 3306 {
Misc_path "/etc/keepalived/check_slave.pl 192.168.21.128"
##################################################################################################
Vi. set up MySQL master and slave monitoring script check_slave.pl
vi/etc/keepalived/check_slave.pl #编辑, add the following code
##################################################################################################
$query = "show slave status";
$DBH = Dbi->connect ("Dbi:mysql: $db: $host: $port", $user, $PW, {raiseerror => 0,printerror 0});
$sqlQuery = $dbh->prepare ($query);
$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 '};
if ($Slave _io_running eq "no" | | $Slave _sql_running eq "no") {
if ($Seconds _behind_master > $SBM) {
##################################################################################################
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
/sbin/arping-i eth0-c 5-s $VIP $GATEWAY &>/dev/null
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控制台
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日志信息