使用LVS+Keepalived实现MySQL双主复制负载均衡高可用

准备工作：

主机名	操作系统	IP地址	担任角色
master1	CentOS7	192.168.1.1	MySQL-主
master2		192.168.1.2	MySQL-主
keep1		192.168.1.3	Keepalived-主，LVS
keep2		192.168.1.4	Keepalived-备，LVS
client		192.168.1.5	测试客户机

一、 部署 MySQL 双主复制

• 注意：未部署 MySQL 数据库需看：CentOS7 安装 MySQL 数据库 来进行安装。

1.配置时间同步

master1 上操作

[root@master1 ~]# yum -y install ntp
[root@master1 ~]# sed -i '/^server/s/^/#/g' /etc/ntp.conf 
[root@master1 ~]# cat <<END >> /etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8
END
[root@master1 ~]# systemctl restart ntpd       		#重启ntpd
[root@master1 ~]# systemctl enable ntpd         	#设置开机自启
[root@master1 ~]# netstat -anpu | grep ntp      	#查看状态

master 2上操作

[root@master2 ~]# yum -y install ntpdate
[root@master2 ~]# /usr/sbin/ntpdate 192.168.1.1

2.配置双主复制

1）master1 上操作

[root@master1 ~]# cat <<END >> /etc/my.cnf
log-bin=mysql-master1
server-id=1
log_slave_update=1
auto_increment_offset=1
auto_increment_increment=2
END
[root@master1 ~]# systemctl restart mysqld
[root@master1 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repl@'192.168.1.%' identified by '123456';
mysql> flush privileges;
mysql> exit

2）master2 上操作

[root@master2 ~]# cat <<END >> /etc/my.cnf
log-bin=mysql-master2
server-id=2
log_slave_update=1
auto_increment_offset=1
auto_increment_increment=2
END
[root@master2 ~]# systemctl restart mysqld
[root@master2 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repl@'192.168.1.%' identified by '123456';
mysql> flush privileges;
mysql> exit

3）建立双主复制

master1 上操作

[root@master1 ~]# mysql -uroot -p123456
mysql> change master to
master_host='192.168.1.2',
master_user='repl',
master_password='123456';
mysql> start slave;
mysql> show slave status\G;

master2 上操作

[root@master2 ~]# mysql -uroot -p123456
mysql> change master to
master_host='192.168.1.1',
master_user='repl',
master_password='123456';
mysql> start slave;
mysql> show slave status\G;

二、部署 LVS+Keepalived 实现 MySQL 双主复制高可用

1.配置主调度器

[root@keep1 ~]# yum -y install keepalived ipvsadm
[root@keep1 ~]# vim /etc/keepalived/keepalived.conf
global_defs {
   router_id 1
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.188
    }
}

virtual_server 192.168.1.188 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 0
    protocol TCP

    real_server 192.168.1.1 3306 {
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }

    real_server 192.168.1.2 3306 {
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}
[root@keep1 ~]# systemctl start keepalived
[root@keep2 ~]# ip a

2.配置备调度器

[root@keep2 ~]# yum -y install keepalived ipvsadm
[root@keep2 ~]# vim /etc/keepalived/keepalived.conf
global_defs {
   router_id 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.188
    }
}

virtual_server 192.168.1.188 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 0
    protocol TCP

    real_server 192.168.1.1 3306 {
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }

    real_server 192.168.1.2 3306 {
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}
[root@keep2 ~]# systemctl start keepalived

3.查看 LVS 集群状态

[root@keep1 ~]# ipvsadm -ln

4.编写 LVS 启动脚本

在 master1 master2 上操作

[root@master1 ~]# vim /etc/init.d/realserver
#!/bin/bash
VIP=192.168.1.188
. /etc/rc.d/init.d/functions

case "$1" in
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
    /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@master1 ~]# chmod +x /etc/init.d/realserver 							#添加可执行权限
[root@master1 ~]# /etc/init.d/realserver start								#启动脚本
[root@master1 ~]# echo "/etc/init.d/realserver" >> /etc/rc.d/rc.local		#设置开启自启动
[root@master1 ~]# ifconfig lo:0

三、验证

在任意一台 master 添加一个测试用户

[root@master1 ~]# mysql -uroot -p123456
mysql> grant all on *.* to test@'%' identified by '123123';
mysql> flush privileges;

1.验证 LVS 负载均衡是否正常

[root@client ~]# yum -y install mariadb
[root@client ~]# mysql -utest -p123123 -h192.168.1.188 -e "show variables like 'server_id'"
[root@client ~]# mysql -utest -p123123 -h192.168.1.188 -e "show variables like 'server_id'"

2.验证 Keepalived 双机热备是否正常

• 关闭 LVS 主调度器，查看客户机是否能够正常访问数据库。

3.验证 MySQL 数据库可用性

[root@master1 ~]# systemctl stop mysqld