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

---------------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主主同步环境已经实现。

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启动）

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架构无任何影响，也不需要停止数据库服务，完全联机操作即可。有一点需要注意，主从库的端口必须一样。

END