keepalived+MySQL双主高可用

我们通常说的双机热备是指两台机器都在运行，但并不是两台机器都同时在提供服务。当提供服务的一台出现故障的时候，另外一台会马上自动接管并且提供服务，而且切换的时间非常短。MySQL双主复制，即互为Master-Slave(只有一个Master提供写操作)，可以实现数据库服务器的热备，但是Master宕机后不能实现动态切换。使用Keepalived，可以通过虚拟IP，实现双主对外的统一接口以及自动检查、失败切换机制，从而实现MySQL数据库的高可用方案。

过多内容在这里就不做详细介绍了，下面详细记录下Mysql+Keepalived双主热备的高可用方案实践

1）先实施ctcdb1->ctcdb2的主主同步。主主是数据双向同步，主从是数据单向同步。一般情况下，主库宕机后，需要手动将连接切换到从库上。（但是用keepalived就可以自动切换）

2）再结合Keepalived的使用，通过vip实现MySQL双主对外连接的统一接口。即客户端通过vip连接数据库；当其中一台宕机后，vip会漂移到另一台上，这个过程对于客户端的数据连接来说几乎无感觉，从而实现高可用。

环境描述：

Centos 7.5版本

ctcdb1：192.168.2.101 安装mysql和keepalived

ctcdb2:192.168.2.102 安装mysql和keepalived

vip： 192.168.2.103

一MySQL主主同步环境部署

---------------ctcdb1服务器操作记录---------------

在my.cnf文件的[mysqld]配置区域添加下面内容：

[root@ctcdb1~]# vi etc/my.cnf

server-id= 1 #唯一值

auto-increment-increment= 2 #自增值，从1开始，每次递增2.数值是1,3,5,7……

auto-increment-offset= 1 #第一次加载数值时的偏移值的个位值

[root@ctcdb1~]# /usr/local/mysql/bin/mysqld &

mysql>createuser repl@’192.168.2.101’ identified by ‘msyql123’;

mysql> grantreplication slave,replication client on *.* to repl@’192.168.2.101’identified by ‘msyql123’;

mysql>createuser repl@’192.168.2.102’ identified by ‘msyql123’;

mysql>grant replication slave,replication client on *.*to repl@’192.168.2.102’ identified by ‘msyql123’;

mysql>flush privileges;

（如由主从升级为主主，则由master端执行create user命令，否则会报数据不一致错）

---------------ctcdb2服务器操作记录---------------

在my.cnf文件的[mysqld]配置区域添加下面内容：

[root@ctcdb2~]# vi etc/my.cnf

server-id= 2 #唯一值

auto-increment-increment= 2 #自增值，从2开始，每次递增2.数值是2,4,6,8……

auto-increment-offset= 2 #第一次加载数值时的偏移值的个位值

[root@ctcdb2~]# /usr/local/mysql/bin/mysqld &

mysql>changemaster tomaster_host='192.168.2.101',master_user='repl',master_password='mysql123',master_auto_position=1;

mysql> startslave;

---查看同步状态，如下出现两个“Yes”，表明同步成功！---

mysql> showslave status G;

***************************1. row ***************************

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

---------------ctcdb1服务器做同步操作---------------

mysql> changemaster tomaster_host='192.168.2.102',master_user='repl',master_password='mysql123',master_auto_position=1;

mysql> startslave;

mysql> showslave status G;

***************************1. row ***************************

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

这样，ctcdb1就和ctcdb2实现了主主同步

下面开始进行数据验证：

-----------------主主同步效果验证---------------------

1）在ctcdb1数据库上写入新数据

mysql> createdatabase text1;

QueryOK, 1 row affected (0.01 sec)

然后在ctcdb2数据库上查看，发现数据已经同步过来了!

mysql> showdatabases;

+--------------------+

|Database |

+--------------------+

|information_schema |

|text1 |

|mysql |

|performance_schema |

+--------------------+

4 rowsin set (0.00 sec)

2）在ctcdb2数据库上写入新数据

mysql> createdatabase text2;

QueryOK, 1 row affected (0.00 sec)

然后在ctcdb1数据库上查看，发现数据也已经同步过来了!

mysql> showdatabases;

+--------------------+

|Database |

+--------------------+

|information_schema |

|text1 |

|text2 |

|mysql |

|performance_schema |

+--------------------+

5 rowsin set (0.00 sec)

至此，Mysql主主同步环境已经实现。

二配置MySQL+Keepalived故障转移的高可用环境（双节点部署，以单节点为示例）

1、下载keepalived

https://www.keepalived.org/

2、安装依赖包

[root@ctcdb1~]# yum install kernel-devel openssl-devel pdata/soft-devel gcc

3、解压

[root@ctcdb1~]#cd data/soft

[root@ctcdb1~]# tar zxvf data/soft/keepalived-2.0.16.tar.gz

4、部署keepalived

[root@ctcdb1~]# cd data/soft/keepalived-2.0.16 ./configure --prefix=/ &&make && make install

[root@ctcdb1~]# cp/data/soft/keepalived-2.0.16/keepalived/etc/init.d/keepalived /etc/init.d/

5、配置文件

[root@ctcdb1~]# cat etc/keepalived/keepalived.conf

!Configuration File for keepalived

global_defs{

notification_email{

873387660@qq.com

}

notification_email_from873387660@qq.com

smtp_server127.0.0.1

smtp_connect_timeout30

router_idctcdb1 #主机名

}

vrrp_scriptcheck_mysqld { #检测mysql服务是否在运行。比如进程，用脚本检测等等

script "/etc/keepalived/mysqlcheck/keepalived_check_mysql.sh"

interval 2 #脚本执行间隔，每2s检测一次

weight -5 #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级-5

fall 2 #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）

rise 1 #检测1次成功就算成功。但不修改优先级

}

vrrp_instanceVI_1 {

state MASTER #备节点为BACKUP

interface enp0s8 #指定虚拟ip的网卡接口

mcast_src_ip 192.168.2.101 #本机IP

virtual_router_id 51 #路由器标识，MASTER和BACKUP必须是一致的

priority 110 #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将vip资源再次抢回来

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.2.103 #vip

}

track_script{

check_mysqld

}

[root@ctcdb1~]# cat etc/keepalived/mysqlcheck/keepalived_check_mysql.sh

#!/bin/bash

counter=$(netstat-na|grep "LISTEN"|grep "3306"|wc -l)

if ["${counter}" -eq 0 ]; then

systemctl stopkeepalived

[root@ctcdb1~]# chmod 755/etc/keepalived/mysqlcheck/keepalived_check_mysql.sh

6、启动keepalived服务

[root@ctcdb1~]# systemctl start keepalived

（MySQL要先于keepalived启动）

三MySQL＋keepalived故障转移的高可用测试

1、利用Navicat通过vip连接，看是否连接成功。

（192.168.2.100为Navicat地址）

root@localhost[(none)]>show processlist;

+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----

+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----

| 10 |root |192.168.2.100:59777 | NULL | Sleep | 4| |NULL

+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----

9 rowsin set (0.00 sec)

2、查看vip

[root@ctcdb1~]# ip a

3:enp0s8:mtu 1500 qdisc fq_codel state UP group default qlen 1000

link/ether 08:00:27:de:5c:48brd ff:ff:ff:ff:ff:ff

inet 192.168.2.101/24brd 192.168.2.255 scope global enp0s8

valid_lft foreverpreferred_lft forever

inet 192.168.2.103/32scope global enp0s8 #这个32位子网掩码的vip地址表示该资源目前还在ctcdb1机器上

valid_lft foreverpreferred_lft forever

inet6 fe80::a00:27ff:fede:5c48/64scope link

valid_lft foreverpreferred_lft forever

[root@ctcdb2~]# ip a

3:enp0s8:mtu 1500 qdisc fq_codel state UP group default qlen 1000

link/ether 08:00:27:ab:4f:cbbrd ff:ff:ff:ff:ff:ff

inet 192.168.2.102/24brd 192.168.2.255 scope global enp0s8

valid_lft foreverpreferred_lft forever

inet6 fe80::a00:27ff:feab:4fcb/64scope link

valid_lft foreverpreferred_lft forever

（2节点未见vip）

3、高可用测试

停止ctcdb1机器上的mysql服务，根据配置中的脚本，mysql服务停了，keepalived也会停，从而vip资源将会切换到ctcdb2机器上。（mysql服务没有起来的时候，keepalived服务也无法顺利启动！）

---------------ctcdb1服务器操作记录---------------

root@localhost[(none)]>shutdown;

QueryOK, 0 rows affected (0.00 sec)

[root@ctcdb1~]# tail -f /var/log/messages

May 2519:46:46 ctcdb1 Keepalived[21086]: Stopping

May 2519:46:46 ctcdb1 systemd: Stopping LVS and VRRP High AvailabilityMonitor...

May 2519:46:46 ctcdb1 Keepalived_vrrp[21088]: (VI_1) sent 0 priority

May 2519:46:46 ctcdb1 Keepalived_vrrp[21088]: (VI_1) removingvips. #移除vip

May 2519:46:46 ctcdb1 avahi-daemon[3160]: Withdrawing address record for192.168.2.103 on enp0s8.

May 2519:46:47 ctcdb1 Keepalived_vrrp[21088]: Stopped - used 0.030909 usertime, 0.042863 system time

May 2519:46:47 ctcdb1 Keepalived[21086]: Stopped Keepalived v2.0.16(05/03,2019), git commit v2.0.15-96-g4d492740+

[root@ctcdb1~]# ip a

3:enp0s8:mtu 1500 qdisc fq_codel state UP group default qlen 1000

link/ether 08:00:27:de:5c:48brd ff:ff:ff:ff:ff:ff

inet 192.168.2.101/24brd 192.168.2.255 scope global enp0s8

valid_lft foreverpreferred_lft forever

inet6 fe80::a00:27ff:fede:5c48/64scope link

valid_lft foreverpreferred_lft forever

---------------ctcdb2服务器操作记录---------------

[root@ctcdb2~]# tail -f /var/log/messages

May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: (VI_1) Backup receivedpriority 0 advertisement

May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: (VI_1) Receive advertisementtimeout

May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: (VI_1) Entering MASTER STATE

May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: (VI_1) settingvips. #设置vip

May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: Sending gratuitous ARP onenp0s8 for 192.168.2.103

May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: (VI_1) Sending/queueinggratuitous ARPs on enp0s8 for 192.168.2.103

May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: Sending gratuitous ARP onenp0s8 for 192.168.2.103

May 2519:46:46 ctcdb2 avahi-daemon[3149]: Registering new address recordfor 192.168.2.103 on enp0s8.IPv4.

[root@ctcdb2~]# ip a

3:enp0s8:mtu 1500 qdisc fq_codel state UP group default qlen 1000

link/ether 08:00:27:ab:4f:cbbrd ff:ff:ff:ff:ff:ff

inet 192.168.2.102/24brd 192.168.2.255 scope global enp0s8

valid_lft foreverpreferred_lft forever

inet 192.168.2.103/32scope global enp0s8

valid_lft foreverpreferred_lft forever

inet6 fe80::a00:27ff:feab:4fcb/64scope link

valid_lft foreverpreferred_lft forever

root@localhost[(none)]>show processlist;

+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----

+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----

| 12 |root |192.168.2.100:59799 | NULL | Sleep | 4| |NULL

+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----

9 rowsin set (0.00 sec)

4、回切

将1节点MySQL+keepalived启动即可自动完成切换

四总结

本MySQL高可用方案配置简单，对现有MySQL架构无任何影响，也不需要停止数据库服务，完全联机操作即可。有一点需要注意，主从库的端口必须一样。