KeepAlived保证Mysql主从自动切换

发表于 更新于 分类于 ，

环境准备

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

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
➜  ~  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）

1
2
3
4
5
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）

1
2
3
4
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的。

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 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脚本，并且赋值可以执行权限

1
chmod 755 shutdown.sh

内容如下：

1
2
#!/bin/bash
pkill keepalived

配置keepalived.conf文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
[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服务

1
2
3
4
5
6
7
8
[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脚本，并且赋值可以执行权限

1
chmod 755 shutdown.sh

内容如下：

1
2
#!/bin/bash
pkill keepalived

配置keepalived.conf文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
! 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是否已经起来

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[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
1
2
3
4
5
6
7
8
9
10
11
12
13
[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机器来登录数据库，下面显示登录成功。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@8f31266d08fc bin]# ./mysql -h 172.17.0.99 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 173
Server version: 5.6.45-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
1
2
3
4
5
6
7
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.02 sec)

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

杀掉Master的进程：

1
2
3
4
5
6
[root@a40a40c6bde7 mysql]# ps aux | grep mysql
root      2559  0.0  0.1  15268  2952 pts/2    S    10:13   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/a40a40c6bde7.pid
mysql     2859  0.2 23.0 1686996 471820 pts/2  Sl   10:13   0:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=a40a40c6bde7.err --pid-file=/usr/local/mysql/data/a40a40c6bde7.pid --socket=/tmp/mysql.sock --port=3306
root      2909  0.0  0.1  12532  2084 pts/2    S+   10:27   0:00 grep --color=auto mysql
[root@a40a40c6bde7 mysql]# kill -9 2559
[root@a40a40c6bde7 mysql]# kill -9 2859

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show variables like 'server_id';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> show variables like 'server_id';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    62
Current database: *** NONE ***

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

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

遇到问题

不能启动keepalived服务

1
2
3
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登录报错

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

参考地址