mysql高可用 keepalived+双主复制

最新推荐文章于 2022-07-20 08:50:57 发布

zuozhiji

最新推荐文章于 2022-07-20 08:50:57 发布

阅读量269

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/jolly10/article/details/80704973

版权

MYSQL 专栏收录该内容

108 篇文章 3 订阅

订阅专栏

环境如下，目的客户端连接VIP连接数据计数库，当复制环境中主服务器出现故障，客户端的连扫可以自动切换到另一台机器。

master server IP:172.17.61.131

slave server IP:172.17.61.132

virtual IP：172.17.61.135

mysql version: mysql-5.7.21-linux

一、安装双主复制：

配置过程如下：

https://blog.csdn.net/jolly10/article/details/79845202

二、安装keepalived

1.下载

[root@qht131 home]# wget http://www.keepalived.org/software/keepalived-1.2.2.tar.gz

[root@qht131 home]# tar -zxvf keepalived-1.2.2.tar.gz

[root@qht131 home]# cd keepalived-1.2.2

2.查看安装文档，安装keepalived之前，需要先安装Openssl和popt，gcc也是必须得安装的

[root@qht131 keepalived-1.2.2]# cat INSTALL
Kernel needing
==============

  Compile a kernel with the following options :

  Kernel/User netlink socket
  Network firewalls (for Kernel 2.2)
  LinuxVirtualServer

  Keepalived support all LVS code : including IPVS code for kernel 2.2
  and kernel 2.4

Libraries dependency
====================

  In order to compile Keepalived needs the following libraries :

  * OpenSSL, <www.openssl.org>
  * popt

安装所需要的依赖包

[root@qht131 keepalived-1.2.2]# yum -y install gcc openssl-devel popt-devel

3.安装keepalived

[root@qht131 keepalived-1.2.2]# ./configure --prefix=/ && make && make install

4.设置开机自启动

[root@qht131 keepalived-1.2.2]# chkconfig --add keepalived
[root@qht131 keepalived-1.2.2]# chkconfig keepalived on

5.修改keepalived的配置文件：

先查看一下当前用的哪个网络连接，当前是eth3

[root@qht131 keepalived-1.2.20]# cat /usr/local/mysql/bin/mysql.sh
#! /bin/sh
pkill keepalived
[root@qht131 keepalived-1.2.20]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:72:6b:4b brd ff:ff:ff:ff:ff:ff
    inet 172.17.61.131/24 brd 172.17.61.255 scope global eth3
    inet6 fe80::20c:29ff:fe72:6b4b/64 scope link
       valid_lft forever preferred_lft forever

说明：keepalived只有一个配置文件keepalived.conf，里面主要包括以下几个配置区域，分别是：

global_defs、vrrp_instance和virtual_server。
global_defs：主要是配置故障发生时的通知对象以及机器标识。
vrrp_instance：用来定义对外提供服务的VIP区域及其相关属性。

virtual_server：虚拟服务器定义

[root@qht131 keepalived-1.2.20]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
        }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id MYSQL-ha
#   vrrp_skip_check_adv_addr
#   vrrp_strict
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth3 #网络连接名
    virtual_router_id 51
    priority 100  #优先级，另一台改为50
    advert_int 1
    nopreempt   #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置 
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        172.17.61.135
    }
}

virtual_server 172.17.61.135    3306 {  #VIP地址
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

    real_server 172.17.61.131 3306 { #真实IP地址
        weight 3
         notify_down /usr/local/mysql/bin/mysql.sh  # run the scripts if mysql is down.  #检测到服务down后执行的脚本
         TCP_CHECK {
             connect_timeout 10    #
             nb_get_retry 3       #conect times to try to connect
             delay_before_retry 3   #interval of retry
             connect_port 3306   # check mysql port
        }
    }
}

配置文件两台机器根本相同，只有三处需要修改，优先级别，是否占用（优先级别高的需要设置nopreempt）以及真实的IP地址。

6.启动keepalived(两台机器都需要）

[root@qht131 keepalived-1.2.20]# /etc/init.d/keepalived start
Starting keepalived:                                       [  OK  ]

[root@qht132 keepalived-1.2.2]#  /etc/init.d/keepalived start
Starting keepalived:                                       [  OK  ]

6.增加检查脚本(两台机器都需要）

[root@qht132 keepalived-1.2.2]# cat /usr/local/mysql/bin/mysql.sh
#! /bin/sh
pkill keepalived

[root@qht132 keepalived-1.2.2]# chmod +x /usr/local/mysql/bin/mysql.sh

#检测到服务down后执行的脚本

注：此脚本是上面配置文件notify_down选项所用到的，keepalived使用notify_down选项来检查real_server 的服务状态，当发现real_server服务故障时，便触发此脚本；我们可以看到，脚本就一个命令，通过pkill keepalived强制杀死keepalived进程，从而实现了MySQL故障自动转移。另外，我们不用担心两个MySQL会同时提供数据更新操作，因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP，而不是两台MySQL的IP+VIP

7.启动keepalived

[root@qht131 keepalived-1.2.20]# /etc/init.d/keepalived start
Starting keepalived:                                       [  OK  ]

[root@qht132 keepalived-1.2.20]# /etc/init.d/keepalived start
Starting keepalived:                                       [  OK  ]

启动之后查看网卡的情况：，eth3上多了一个ip地址172.17.61.135

[root@qht131 keepalived-1.2.20]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:72:6b:4b brd ff:ff:ff:ff:ff:ff
    inet 172.17.61.131/24 brd 172.17.61.255 scope global eth3
    inet 172.17.61.135/32 scope global eth3
    inet6 fe80::20c:29ff:fe72:6b4b/64 scope link
       valid_lft forever preferred_lft forever

用cmd命令拼一下虚拟IP,应该是连通的状态：

C:\Users\L5M>ping 172.17.61.135

正在 Ping 172.17.61.135 具有 32 字节的数据:
来自 172.17.61.135 的回复: 字节=32 时间<1ms TTL=64
来自 172.17.61.135 的回复: 字节=32 时间<1ms TTL=64
来自 172.17.61.135 的回复: 字节=32 时间<1ms TTL=64

连接到虚拟IP地址：

[root@qht132 keepalived-1.2.2]# mysql -uroot -p -h172.17.61.135 -P3306

mysql>  show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10000 |
+---------------+-------+
1 row in set (0.01 sec)

连接到虚拟IP地址发现连接真实的mysql是qht131的服务，也就是优先级比较高的mysql服务。

8.测试

由于qht131的服务等级比qht132高，所以默认连接keepalived服务都是连接的qht131,这个上面已测验过。

下面测试如果qht131的mysql挂掉，虚拟IP能否自动接管qht132的服务?

关闭qht131的mysql

[root@qht131 ~]# service mysql stop
Shutting down MySQL.............                           [  OK  ]

测试连接虚拟IP：

[root@qht131 ~]# mysql -uroot -p -h172.17.61.135 -P3306
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10001 |
+---------------+-------+
1 row in set (0.03 sec)

没有问题，虚拟IP自动接管了qht132的mysql服务。

接着测试qht131恢复连接后会怎么样：

手动启动mysql和keepalived服务（keepalived在mysql关闭后被自动kill了）

[root@qht131 ~]# service mysql start
Starting MySQL.....                                        [  OK  ]
[root@qht131 ~]# /etc/init.d/keepalived start
Starting keepalived:                                       [  OK  ]

[root@qht131 ~]# mysql -uroot -p -h172.17.61.135 -P3306
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10001 |
+---------------+-------+
1 row in set (0.01 sec)

mysql>

[root@qht131 ~]# ip addr show eth3
2: eth3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:72:6b:4b brd ff:ff:ff:ff:ff:ff
    inet 172.17.61.131/24 brd 172.17.61.255 scope global eth3
    inet6 fe80::20c:29ff:fe72:6b4b/64 scope link
       valid_lft forever preferred_lft forever

[root@qht132 keepalived-1.2.2]# ip addr show eth3
2: eth3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:35:30:c6 brd ff:ff:ff:ff:ff:ff
    inet 172.17.61.132/24 brd 172.17.61.255 scope global eth3
    inet 172.17.61.135/32 scope global eth3
    inet6 fe80::20c:29ff:fe35:30c6/64 scope link
       valid_lft forever preferred_lft forever

即便qht131的mysql服务和keepalived服务都重新开启了，qht132仍然是master了，qht131抢不回去master的权利了，说明我们设置的nopreempt配置生效了，在生产环境中就需要添加不抢占配置，保证了群集稳定性，只有当qht132的mysql服务坏掉的时候，qht131才会再次成为master，否则它永远只能当qht132的备份。（注：nopreempt一般是在优先级高的mysql上设置）

将qht132关闭后再看一下：

[root@qht132 keepalived-1.2.2]# service mysql  stop
Shutting down MySQL.............                           [  OK  ]

[root@qht132 keepalived-1.2.2]# ip addr show eth3
2: eth3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:35:30:c6 brd ff:ff:ff:ff:ff:ff
    inet 172.17.61.132/24 brd 172.17.61.255 scope global eth3
    inet6 fe80::20c:29ff:fe35:30c6/64 scope link
       valid_lft forever preferred_lft forever

qht132上面的虚拟IP地址没有了，漂移到了qht131上面

[root@qht131 ~]# ip addr show eth3
2: eth3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:72:6b:4b brd ff:ff:ff:ff:ff:ff
    inet 172.17.61.131/24 brd 172.17.61.255 scope global eth3
    inet 172.17.61.135/32 scope global eth3
    inet6 fe80::20c:29ff:fe72:6b4b/64 scope link
       valid_lft forever preferred_lft forever

连接的mysql自然也是qht131的

[root@qht132 keepalived-1.2.2]# mysql -uroot -p -h172.17.61.135 -P3306
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 353
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>  show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10000 |
+---------------+-------+
1 row in set (0.06 sec)

写在最后：

Keepalived+mysql双主一般来说，中小型规模的时候，采用这种架构是最省事的。在master节点发生故障后，利用keepalived的高可用机制实现快速切换到备用节点。

zuozhiji

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql高可用 keepalived+双主复制

环境如下，目的客户端连接VIP连接数据计数库，当复制环境中主服务器出现故障，客户端的连扫可以自动切换到另一台机器。master server IP:172.17.61.131slave server IP:172.17.61.132virtual IP：172.17.61.135mysql version: mysql-5.7.21-linux一、安装双主复制：配置过程如下：https://blo...
复制链接

扫一扫