lvs+keepalived+mysql双主复制实现负载分担及高可用

不念不忘少年蓝@ 2022-12-06 04:03 340阅读 0赞

### lvs+keepalived+mysql双主复制实现负载分担及高可用 ###

*  mysql双主
 *  keepalived
 *  测试
 *   *  1.验证LVS负载均衡转发策略
     *  2.模拟lvs的master故障
     *  3. 模拟其中一方数据库挂了

Keepalived+lvs+mysql主主复制是比较常用的一种Mysql高可用方案，其中lvs 提供读负载均衡，Keepalived通过虚拟vip漂移实现故障自动转移，而Mysql虽然配置成主主复制，但是一般都是采用单点写入来保证数据一致性。

--------------------

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center]

<table> 
 <thead> 
  <tr> 
   <th>主机</th> 
   <th>IP</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>VIP</td> 
   <td>192.168.30.250</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>keep-master</td> 
   <td>192.168.30.243</td> 
   <td>lvs+keepalived(主)</td> 
  </tr> 
  <tr> 
   <td>keep-slave</td> 
   <td>192.168.30.244</td> 
   <td>lvs+keepalived(备)</td> 
  </tr> 
  <tr> 
   <td>master-master</td> 
   <td>192.168.30.245</td> 
   <td>mysql(主)</td> 
  </tr> 
  <tr> 
   <td>master-slave</td> 
   <td>1929.168.30.246</td> 
   <td>mysql(主)</td> 
  </tr> 
 </tbody> 
</table>

# mysql双主 #

mysql双主与主从配置差不多，不过加了下面的几个步骤

[root@mysql-master ~]# vim /etc/my.cnf
    [mysqld]
    basedir = /usr/local/mysql
    datadir = /opt/data
    socket = /tmp/mysql.sock
    port = 3306
    pid-file = /opt/data/mysql.pid
    user = mysql
    skip-name-resolve
    log_slave_updates = 1    ##双主需要开启log_slave_updates功能
    server-id = 10           ##不能相同
    log-bin = mysql_bin      ##双主两个都是log-bin
    auto_increment_offset = 1   ##表示自增长字段从1开始，他的取值范围是1 .. 65535
    auto_increment_increment = 2 ##表示自增长字段每次递增2，取值范围是2 .. 65535

重启服务  
   
   
在master主机上创建授权账户，允许slave（192.168.30.246）主机上连接

mysql> grant replication slave on *.* to 'repl'@'192.168.30.246' identified by 'repl';
    Query OK, 0 rows affected, 1 warning (0.00 sec)

查看master的当前binlog状态信息

在slave服务器上将master设为自己的主服务器并且开启slave功能

mysql> change master to
        -> master_host = '192.168.30.245',
        -> master_user = 'repl',
        -> master_password = 'repl',
        -> master_log_file = 'mysql_bin.000001',
        -> master_log_pos = 455 ;
    Query OK, 0 rows affected, 2 warnings (0.02 sec)
    
    
    
    mysql> start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    
    
    mysql> show slave status\G;
    *************************** 1. row ***************************
    
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

修改从库配置文件

[root@mysql-slave ~]# cat /etc/my.cnf
    [mysqld]
    basedir = /usr/local/mysql
    datadir = /opt/data
    socket = /tmp/mysql.sock
    port = 3306
    pid-file = /opt/data/mysql.pid
    user = mysql
    skip-name-resolve
    log_slave_updates = 1    ##双主需要开启log_slave_updates功能
    server-id = 20           ##不能相同
    log-bin = mysql_bin      ##双主两个都是log-bin
    auto_increment_offset = 1   ##表示自增长字段从1开始，他的取值范围是1 .. 65535
    auto_increment_increment = 2 ##表示自增长字段每次递增2，取值范围是2 .. 65535

在slave主机上创建授权账户，允许master（192.168.30.246）主机上连接

mysql> grant replication slave on *.* to 'repl'@'192.168.30.245' identified by 'repl';
    Query OK, 0 rows affected, 1 warning (0.00 sec)

查看slave的当前binlog状态信息

在master服务器上将slave设为自己的主服务器并且开启slave功能

mysql> change master to
        -> master_host = '192.168.30.246',
        -> master_user = 'repl',
        -> master_password = 'repl',
        -> master_log_file = 'mysql_bin.000001',
        -> master_log_pos = 154 ;
    Query OK, 0 rows affected, 2 warnings (0.00 sec)
    
    
    mysql> show slave status\G;
    *************************** 1. row ***************************
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

# keepalived #

我这里就用yum安装  
主keepalived

[root@keep-master ~]# yum -y install keepalived ipvsadm
    
    [root@keep-master ~]# vim /etc/keepalived/keepalived.conf 
    ! Configuration File for keepalived
    
    global_defs {
       router_id lb01       
    }
    
    vrrp_instance VI_1 {
        state MASTER
        interface ens33
        virtual_router_id 20 
        priority 100
        advert_int 1
        authentication {
            auth_type PASS
            auth_pass zyy                 
        }
        virtual_ipaddress {
            192.168.30.250                ##VIP
        }
    }
    
    virtual_server 192.168.30.250 3306 {      # 定义虚拟服务器，地址与上面的virtual_ipaddress相同
        delay_loop 3                          # 健康检查时间间隔，3秒
        lb_algo rr                            # 负载均衡调度算法：rr|wrr|lc|wlc|sh|dh|lblc
        lb_kind DR                            # 负载均衡转发规则：NAT|DR|TUN
       # persistence_timeout 5 # 会话保持时间5秒，动态服务建议开启
        protocol TCP                          # 转发协议protocol，一般有tcp和udp两种
    
     #后端真实服务器，有几台就设置几个
        real_server 192.168.30.245 3306 {     
            weight 1
            TCP_CHECK {
                connect_port 3306
                connect_timeout 3
                nb_get_retry 3
                delay_before_retry 3
            }
        }
    
        real_server 192.168.30.246 3306 {
            weight 1
            TCP_CHECK {
                connect_port 3306
                connect_timeout 3
                nb_get_retry 3
                delay_before_retry 3
            }
        }
    }

从库上一样，优先级改一下，设置不抢占就行

[root@keep-slave ~]# yum -y install keepalived ipvsadm
    
    [root@keep-slave ~]# vim /etc/keepalived/keepalived.conf 
    ! Configuration File for keepalived
    
    global_defs {
       router_id lb02
    }
    
    vrrp_instance VI_1 {
        state BACKUP              ##BACKUP
        interface ens33
        virtual_router_id 20    
        priority 80             ##优先级要比master小
        nopreempt
        advert_int 1
        authentication {
            auth_type PASS
            auth_pass zyy        
        }
        virtual_ipaddress {
            192.168.30.250
        }
    }
    
    virtual_server 192.168.30.250 3306 {
        delay_loop 3
        lb_algo rr
        lb_kind DR
      # persistence_timeout 5
        protocol TCP
    
        real_server 192.168.30.245 3306 {
            weight 1
            TCP_CHECK {
                connect_port 3306
                connect_timeout 3
                nb_get_retry 3
                delay_before_retry 3
            }
        }
    
        real_server 192.168.30.246 3306 {
            weight 1
            TCP_CHECK {
                connect_port 3306
                connect_timeout 3
                nb_get_retry 3
                delay_before_retry 3
            }
        }
    }

开启keepalived服务

[root@keep-master ~]# systemctl start keepalived
    [root@keep-slave ~]# systemctl start keepalived

查看VIP  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 2]

此时查看LVS集群状态，可以看到集群下有两个RealServer，调度算法，权重等信息。ActiveConn代表当前RealServer的活跃连接数。

[root@keep-master ~]# ipvsadm -ln
    IP Virtual Server version 1.2.1 (size=4096)
    Prot LocalAddress:Port Scheduler Flags
      -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
    TCP  192.168.30.250:3306 rr
      -> 192.168.30.245:3306          Route   1      0          0         
      -> 192.168.30.246:3306          Route   1      0          0

编写RealServer的网络配置脚本  
192.168.30.245和192.168.30.246写的东西都是一样的，这里就演示一个

[root@mysql-master ~]# cat /etc/init.d/realserver 
    #!/bin/bash
    VIP=192.168.30.250
    . /etc/rc.d/init.d/functions
    
    case "$1" in
    # 禁用本地的ARP请求、绑定本地回环地址
    start)
        /sbin/ifconfig lo down
        /sbin/ifconfig lo up
        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
        /sbin/sysctl -p >/dev/null 2>&1
        /sbin/ifconfig lo:0 $VIP netmask 255.255.255.255 up # 在回环地址上绑定VIP，设定掩码，与Direct Server上自身的IP保持通信
        /sbin/route add -host $VIP dev lo:0
        echo "LVS-DR real server starts successfully.\n"
        ;;
    stop)
        /sbin/ifconfig lo:0 down
        /sbin/route del $VIP >/dev/null 2>&1
        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 "LVS-DR real server stopped.\n"
        ;;
    status)
        isLoOn=`/sbin/ifconfig lo:0 | grep "$VIP"`
        isRoOn=`/bin/netstat -rn | grep "$VIP"`
        if [ "$isLoON" == "" -a "$isRoOn" == "" ]; then
            echo "LVS-DR real server has run yet."
        else
            echo "LVS-DR real server is running."
        fi
        exit 3
        ;;
    *)
        echo "Usage: $0 {start|stop|status}"
        exit 1
    esac
    exit 0

[root@mysql-master ~]# chmod 755 realserver
    [root@mysql-master ~]# service realserver start

[root@mysql-slave ~]# chmod 755 realserver
    [root@mysql-slave ~]# service realserver start

设置为开机自启

echo "/etc/init.d/realserver" >> /etc/rc.d/rc.local

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 4]

# 测试 #

添加一个测试用户

mysql> grant all on *.* to 'zyy'@'%' identified by 'zyy' ;
    Query OK, 0 rows affected, 1 warning (0.00 sec)

## 1.验证LVS负载均衡转发策略 ##

MySQL客户端使用VIP连接数据库，并查看所连接的数据库服务器ID。可以看到，每次执行依次连接到192.168.30.246和192.168.30.245的MySQL，证明是轮询策略产生的结果。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 5]

## 2.模拟lvs的master故障 ##

**停掉master端的keepalived**

[root@keep-master ~]# systemctl stop keepalived

可以看到VIP已经漂移  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 6]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 7]

此时连接MySQL，负载均衡不受影响。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 8]

## 3. 模拟其中一方数据库挂了 ##

**关闭slave的数据库**

[root@mysql-slave ~]# service mysqld stop 
    Shutting down MySQL............ SUCCESS! 
    
    
    [root@mysql-slave ~]# ss -anlt
    State      Recv-Q Send-Q Local Address:Port               Peer Address:Port              
    LISTEN     0      128     *:22                  *:*                  
    LISTEN     0      128    :::22                 :::*

此时，只有mysql-master端数据库在工作，但可以保证业务的不中断  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 9]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/183f3403a9704364ba044e77d15edf4d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/7fe50baa423848c79660d6cc350ed7f4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/e8e6c0a389e44d57953a1ec36046f600.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221123/3e402ae85a9740ef822374d081f813ca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221123/76321c90cd9e4f3894587fb6836565e6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221123/4afd3b3df821483995bc04867e52ceeb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221123/f0ddfe8f1f904b64904e2090608187e1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221123/3f7939110c0346eba382b7119d1baf4b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20221123/6bfb756c3ce142158ac2c9d6ac57740a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eTEzMDk4OA_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20221123/e7a2209562f548faa10cf90093d87a0f.png