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;

参考地址

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

简栈文化服务订阅号