数据库在整体平台设计中都有很重要的作用,由此也衍生出了很多骚操作。波波不去一一列举为解决业务场景所使用的各种骚操作,本篇笔记主要记录LVS为MySQL读提供负载均衡配置过程。不见得适合所有的人,大家看看就行。
一、YUM安装LVS和Keepalived
1、配置阿里云的YUM源
- #clean OS default repo
- mkdir /etc/yum.repos.d/old && mv /etc/yum.repos.d/C* /etc/yum.repos.d/old/
- #add local repo
- wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
- wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
2、安装
- yum install ipvsadm keepalived
二、配置Keepalived
备注:keepalived配置文件目录/etc/keepalived,从库状态检查脚本目录/etc/keepalived。
- ! Configuration File for keepalived
- global_defs {
- router_id MHA-A
- }
- # db Read
- vrrp_instance VI_1 {
- state MASTER
- interface eth0
- virtual_router_id 51
- priority 150
- advert_int 6
- authentication {
- auth_type PASS
- auth_pass 123qwe
- }
- virtual_ipaddress {
- 192.168.0.21/24
- }
- }
- # VIP 192.168.0.21
- virtual_server 192.168.0.21 3306 {
- delay_loop 10
- lb_algo rr
- lb_kind DR
- nat_mask 255.255.255.0
- protocol TCP
- #sorry_server 192.168.0.235 3306
- real_server 192.168.0.235 3306 {
- weight 1
- TCP_CHECK {
- connect_port 3306
- connect_timeout 10
- nb_get_retry 3
- delay_before_retry 5
- }
- MISC_CHECK {
- misc_path "/etc/keepalived/check_slave.pl 192.168.0.235 3306"
- misc_dynamic
- }
- }
- real_server 192.168.0.236 3306 {
- weight 1
- TCP_CHECK {
- connect_port 3306
- connect_timeout 10
- nb_get_retry 3
- delay_before_retry 5
- }
- MISC_CHECK {
- misc_path "/etc/keepalived/check_slave.pl 192.168.0.236 3306"
- misc_dynamic
- }
- }
- }
三、所有从库绑定读VIP
编写脚本/app/scripts/lvs-start-client-read.sh
- #!/bin/bash
- #real_server.sh
- vip=192.168.0.21
- open() {
- sudo ifconfig lo:Rvip ${vip}/32 up
- sudo sysctl -w net.ipv4.conf.lo.arp_announce=2
- sudo sysctl -w net.ipv4.conf.lo.arp_ignore=1
- sudo sysctl -w net.ipv4.conf.all.arp_announce=2
- sudo sysctl -w net.ipv4.conf.all.arp_ignore=1
- }
- close() {
- sudo ifconfig lo:Rvip down
- sudo sysctl -w net.ipv4.conf.lo.arp_announce=0
- sudo sysctl -w net.ipv4.conf.lo.arp_ignore=0
- sudo sysctl -w net.ipv4.conf.all.arp_announce=0
- sudo sysctl -w net.ipv4.conf.all.arp_ignore=0
- }
- case $1 in
- start)
- open
- ;;
- stop)
- close
- ;;
- *)
- echo "Usage: $0 need argument [start|stop]"
- ;;
- esac
启动Keepalived,观察连接情况。
四、查看LVS状态
- watch -n1 ipvsadmin -Ln
上传 /etc/keepalived/check_slave.pl 内容如下:
- #!/usr/bin/perl -w
- use DBI;
- use DBD::mysql;
- # CONFIG VARIABLES
- $SBM = 200;
- $db = "information_schema";
- $host = $ARGV[0];
- $port = $ARGV[1];
- $user = "repl";
- $pw = "replpassword";
- # SQL query
- $query = "show slave status";
- $dbh = DBI->connect("DBI:mysql:$db:$host:$port", $user, $pw, { RaiseError => 0,PrintError => 0 });
- #print "$db, $host , $port , $user, $pw";
- if (!defined($dbh)) {
- #print "connect fail.";
- 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'};
- }
- #print "Slave_IO_Running = $Slave_IO_Running\n";
- #print "Slave_SQL_Running = $Slave_SQL_Running\n";
- #print "Seconds_Behind_Master = $Seconds_Behind_Master\n";
- $sqlQuery->finish;
- $dbh->disconnect();
- if ( $Slave_IO_Running eq "No" || $Slave_SQL_Running eq "No" || $Slave_IO_Running eq "" || $Slave_SQL_Running eq "" || $Seconds_Behind_Master eq "NULL" || $Seconds_Behind_Master eq "" ) {
- #print "Slave_IO_Running = $Slave_IO_Running\n";
- #print "Slave_SQL_Running = $Slave_SQL_Running\n";
- #print "Seconds_Behind_Master = $Seconds_Behind_Master\n";
- exit 1;
- } else {
- if ( $Seconds_Behind_Master > $SBM ) {
- #print "Seconds_Behind_Master > SBM";
- exit 1;
- } else {
- #print "Seconds_Behind_Master < SBM";
- exit 0;
- }
- }