keepalived+MySQL双主高可用
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
fi
[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;
+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----
| Id |User |Host |db | Command |Time |State |Info
+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----
| 2| root |localhost |NULL | Query | 0| starting |show processlist |
| 3| system user | |NULL | Connect | 121| Waiting for master to send event |NULL |
| 4| system user | |NULL | Connect | 121| Slave has read all relay log; waiting for more updates |NULL |
| 5| system user | |NULL | Connect | 121| Waiting for an event from Coordinator |NULL |
| 6| system user | |NULL | Connect | 121| Waiting for an event from Coordinator |NULL |
| 7| system user | |NULL | Connect | 121| Waiting for an event from Coordinator |NULL |
| 8| system user | |NULL | Connect | 121| Waiting for an event from Coordinator |NULL |
| 9| repl |192.168.2.102:56970 | NULL | Binlog Dump GTID | 111 |Master has sent all binlog to slave; waiting for more updates |NULL |
| 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 Keepalived_vrrp[20346]: Sending gratuitous ARP onenp0s8 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 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;
+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----
| Id |User |Host |db | Command |Time |State |Info
+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----
| 4| root |localhost |NULL | Query | 0| starting |show processlist |
| 5| repl |192.168.2.101:38742 | NULL | Binlog Dump GTID | 231 |Master has sent all binlog to slave; waiting for more updates |NULL |
| 6| system user | |NULL | Connect | 221| Reconnecting after a failed master event read |NULL |
| 7| system user | |NULL | Connect | 221| Slave has read all relay log; waiting for more updates |NULL |
| 8| system user | |NULL | Connect | 221| Waiting for an event from Coordinator |NULL |
| 9| system user | |NULL | Connect | 221| Waiting for an event from Coordinator |NULL |
| 10 |system user | |NULL | Connect | 221| Waiting for an event from Coordinator |NULL |
| 11 |system user | |NULL | Connect | 221| Waiting for an event from Coordinator |NULL |
| 12 |root |192.168.2.100:59799 | NULL | Sleep | 4| |NULL
+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----
9 rowsin set (0.00 sec)
4、回切
将1节点MySQL+keepalived启动即可自动完成切换
四总结
本MySQL高可用方案配置简单，对现有MySQL架构无任何影响，也不需要停止数据库服务，完全联机操作即可。有一点需要注意，主从库的端口必须一样。
