KeepAlived保证Mysql主从自动切换

环境准备

前面有几篇文章对于MySQL主从搭建做了一些铺垫:

文章一:MySQL中Binlog的常用设置

文章二:MySQL主从同步-原理&实践篇

先启动Master与Slave的2台mysql服务器,具体信息如下:

➜  ~  docker ps -a
CONTAINER ID        IMAGE                    COMMAND             CREATED             STATUS              PORTS                     NAMES  
8f31266d08fc        docker-mysql-master:v1   "/usr/sbin/init"    49 minutes ago      Up 49 minutes       0.0.0.0:33063->3306/tcp   docker-mysql-client  
a579aa381425        docker-mysql-slave:v1    "/usr/sbin/init"    19 hours ago        Up 19 hours         0.0.0.0:33062->3306/tcp   docker-mysql-slave  
a40a40c6bde7        docker-mysql-master:v1   "/usr/sbin/init"    19 hours ago        Up 19 hours         0.0.0.0:33061->3306/tcp   docker-mysql-master

#进入master
➜  ~  docker exec -it 8166c07dd6c7 bash
[root@8166c07dd6c7 /]#

#进入slave
➜  ~  docker exec -it 208c30295ec9 bash
[root@208c30295ec9 /]#

Master机器(172.17.0.2)

create user 'master_account'@'%' identified by '123456';  
grant replication slave on *.* to 'master_account'@'%';  
flush privileges;

change master to master_host='172.17.0.3',master_user='slave_account',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=120;  

Slave机器(172.17.0.3)

create user 'slave_account'@'%' identified by '123456';  
grant replication slave on *.* to 'slave_account'@'%';  
flush privileges;  
change master to master_host='172.17.0.2',master_user='master_account',master_password='123456',master_log_file='mysql-bin.000008',master_log_pos=862;  

分别在Master与Slave机器验证,必须是互相同步OK的。

#并且保证主从是同步的
mysql> show slave status\G;  
Slave_IO_Running: Yes  
Slave_SQL_Running: Yes  

SQL验证,分别在Master执行脚本需要在Slave上看到数据同步。

# Master
INSERT INTO test.person_01 (id, first_name, age, gender) VALUES (10, 'chenyuan', 20, 'M');  
# Slave
INSERT INTO test.person_01 (id, first_name, age, gender) VALUES (11, 'chenyuan11', 20, 'M');  
# 2边数据一致就OK
mysql> select * from person_01;  
+------+------------+------+--------+
| id   | first_name | age  | gender |
+------+------------+------+--------+
|    1 | Bob        |   25 | M      |
|    2 | Jane       |   20 | F      |
|    3 | Jack       |   30 | M      |
|    4 | Bill       |   32 | M      |
|    5 | Nick       |   22 | M      |
|    6 | Kathy      |   18 | F      |
|    7 | Steve      |   36 | M      |
|    8 | Anne       |   25 | F      |
|    1 | Vernon     |  300 | M      |
|   10 | chenyuan   |   20 | M      |
|   11 | chenyuan11 |   20 | M      |
+------+------------+------+--------+
11 rows in set (0.00 sec)  

安装KeepAlived

安装好gcc,gcc-c++,make

yum install gcc gcc-c++ autoconf automake  
yum install initscripts -y  

分别在Maste机器、Slave机器安装好keepalived

# master
[root@8166c07dd6c7 /]# yum install -y keepalived
...
Complete!  
[root@8166c07dd6c7 /]# keepalived -v
Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2  
Copyright(C) 2001-2017 Alexandre Cassen, <acassen@gmail.com>

# slave
[root@208c30295ec9 /]# yum install keepalived
...
Complete!  
[root@8166c07dd6c7 /]# keepalived -v
Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2  
Copyright(C) 2001-2017 Alexandre Cassen, <acassen@gmail.com>  

配置KeepAlived

配置Master机器keepalived

新增shutdown.sh脚本,并且赋值可以执行权限

chmod 755 shutdown.sh  

内容如下:

#!/bin/bash
pkill keepalived  

配置keepalived.conf文件

[root@8166c07dd6c7 keepalived]# vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {  
   router_id HA_MySQL
}

vrrp_instance VI_1 {  
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass chenyuan
    }
    virtual_ipaddress {
        172.17.0.4
    }
}

virtual_server 172.17.0.99 3306 {  
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

    real_server 172.17.0.99 3306 {
        weight 3
        notify_down /etc/keepalived/bin/shutdown.sh
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}

启动好keepalived服务

[root@a40a40c6bde7 bin]# systemctl start keepalived.service
[root@a40a40c6bde7 bin]# ps aux | grep keepalived
root       494  0.0  0.1 123016  2104 ?        Ss   14:59   0:00 keepalived  
root       495  0.0  0.3 125268  7164 ?        S    14:59   0:00 keepalived  
root       496  0.0  0.2 125140  5700 ?        S    14:59   0:00 keepalived  
root       515  0.0  0.1  12532  2164 pts/1    S+   14:59   0:00 grep --color=auto keepalived

[root@a40a40c6bde7 bin]# systemctl stop keepalived.service
配置Slave机器keepalived

新增shutdown.sh脚本,并且赋值可以执行权限

chmod 755 shutdown.sh  

内容如下:

#!/bin/bash
pkill keepalived  

配置keepalived.conf文件

! Configuration File for keepalived

global_defs {  
   router_id HA_MySQL
}

vrrp_instance VI_1 {  
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 90
    advert_int 1
    # nopreempt
    authentication {
        auth_type PASS
        auth_pass chenyuan
    }
    virtual_ipaddress {
        172.17.0.99
    }
}

virtual_server 172.17.0.99 3306 {  
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

    real_server 172.17.0.3 3306 {
        weight 3
        notify_down /etc/keepalived/bin/shutdown.sh
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}

同样也是启动好keepalived服务。

查看虚拟IP是否已经起来

[root@a40a40c6bde7 mysql]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1  
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: tunl0@NONE: <NOARP> mtu 1480 qdisc noop state DOWN group default qlen 1  
    link/ipip 0.0.0.0 brd 0.0.0.0
3: ip6tnl0@NONE: <NOARP> mtu 1452 qdisc noop state DOWN group default qlen 1  
    link/tunnel6 :: brd ::
21: eth0@if22: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default  
    link/ether 02:42:ac:11:00:02 brd ff:ff:ff:ff:ff:ff link-netnsid 0
    inet 172.17.0.2/16 brd 172.17.255.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 172.17.0.99/32 scope global eth0
       valid_lft forever preferred_lft forever
[root@a579aa381425 support-files]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1  
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: tunl0@NONE: <NOARP> mtu 1480 qdisc noop state DOWN group default qlen 1  
    link/ipip 0.0.0.0 brd 0.0.0.0
3: ip6tnl0@NONE: <NOARP> mtu 1452 qdisc noop state DOWN group default qlen 1  
    link/tunnel6 :: brd ::
23: eth0@if24: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default  
    link/ether 02:42:ac:11:00:03 brd ff:ff:ff:ff:ff:ff link-netnsid 0
    inet 172.17.0.3/16 brd 172.17.255.255 scope global eth0
       valid_lft forever preferred_lft forever

由此可见,现在172.17.0.99/32`是在master节点上。

验证

通过docker-mysql-client机器来登录数据库,下面显示登录成功。

keepalived.conf

! Configuration File for keepalived

global_defs {  
   router_id HA_MySQL
}

vrrp_instance VI_1 {  
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 90
    advert_int 1
    # nopreempt
    authentication {
        auth_type PASS
        auth_pass chenyuan
    }
    virtual_ipaddress {
        172.17.0.99
    }
}

virtual_server 172.17.0.99 3306 {  
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

    real_server 172.17.0.3 3306 {
        weight 3
        notify_down /etc/keepalived/bin/shutdown.sh
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}

把mysql进程直接杀掉,类似于机器down的情况。然后再次查看server_id,短暂的失去联系,即可很快的恢复。

杀掉Master的进程:

[root@a40a40c6bde7 mysql]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1  
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: tunl0@NONE: <NOARP> mtu 1480 qdisc noop state DOWN group default qlen 1  
    link/ipip 0.0.0.0 brd 0.0.0.0
3: ip6tnl0@NONE: <NOARP> mtu 1452 qdisc noop state DOWN group default qlen 1  
    link/tunnel6 :: brd ::
21: eth0@if22: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default  
    link/ether 02:42:ac:11:00:02 brd ff:ff:ff:ff:ff:ff link-netnsid 0
    inet 172.17.0.2/16 brd 172.17.255.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 172.17.0.99/32 scope global eth0
       valid_lft forever preferred_lft forever

docker-mysql-client节点上继续查看server_id

[root@a579aa381425 support-files]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1  
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: tunl0@NONE: <NOARP> mtu 1480 qdisc noop state DOWN group default qlen 1  
    link/ipip 0.0.0.0 brd 0.0.0.0
3: ip6tnl0@NONE: <NOARP> mtu 1452 qdisc noop state DOWN group default qlen 1  
    link/tunnel6 :: brd ::
23: eth0@if24: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default  
    link/ether 02:42:ac:11:00:03 brd ff:ff:ff:ff:ff:ff link-netnsid 0
    inet 172.17.0.3/16 brd 172.17.255.255 scope global eth0
       valid_lft forever preferred_lft forever

最后还需要反过来验证一边,就是让Slave机器的mysql服务挂掉,让VIP切换到Master节点去。

遇到问题

不能启动keepalived服务

Failed to get D-Bus connection: Operation not permitted  
docker run -itd --name docker-mysql-slave --privileged -v /Users/chenyuan/Data/docker/mysql-data-slave:/usr/local/mysql -v /Users/chenyuan/Tools:/root/Tools -e MYSQL_ROOT_PASSWORD=root -p 33062:3306 docker-mysql-slave:v1 /usr/sbin/init  
注意这里的--privileged 与 /usr/sbin/init

通过vip登录报错

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;  
FLUSH PRIVILEGES;  

参考地址

如果大家喜欢我的文章,可以关注个人订阅号。欢迎随时留言、交流。

简栈文化服务订阅号