Keepalived+mysql高可用

最新推荐文章于 2024-08-19 16:28:14 发布

老王笔记

最新推荐文章于 2024-08-19 16:28:14 发布

阅读量630

点赞数

分类专栏： MYSQL 文章标签： keepalived mysql

本文链接：https://blog.csdn.net/JSWANGCHANG/article/details/81569413

版权

MYSQL 专栏收录该内容

79 篇文章 4 订阅

订阅专栏

DB1： 192.168.10.13:3358

DB2： 192.168.10.14：3358

VIP： 192.168.10.100：3358

注意： /etc/my.cnf中，bind_address注销掉，或者改为0.0.0.0

（mysql已安装完毕，并配置了主-主关系）

安装环境：

yum install -y keepalived

yum install -y ipvsadm

10.13上配置keepalived /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc #加上自己邮箱地址
   }
   notification_email_from keepalive.mysql@firewall.loc #发件人
   smtp_server 127.0.0.1  
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.10.100
    }
}

virtual_server 192.168.10.100 3358 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP

    real_server 192.168.10.13 3358 {
        weight 1
	notify_down /root/shutdown.sh  #当3358端口检测失败的时候执行脚本
	TCP_CHECK {
	    connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3358
	}
    }
}

10.14上配置keepalived /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from keepalive.mysql@firewall.loc
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.10.100
    }
}

virtual_server 192.168.10.100 3358 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP

    real_server 192.168.10.14 3358 {
        weight 1
	notify_down /root/shutdown.sh
	TCP_CHECK {
	    connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3358
	}
    }
}

注意： 2台只是real_server不一样，其他的都一样；

2台的/root/下防止shutdown.sh脚本，用于在3358端口无法访问的时候停掉keepalived，并授予可执行权限

[root@192.168.10.13 ~]#cat shutdown.sh
#!/bin/bash
pkill keepalived
[root@192.168.10.13 ~]#ll shutdown.sh
-rwxr-xr-x 1 root root 29 Aug  9 09:43 shutdown.sh

新建用户用于测试连接用，因为是主主架构，任意一台DB执行下面即可：

mysql> create user wang@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to  wang@'%';
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)
mysql> show grants for wang@'%';
+-------------------------------------------+
| Grants for wang@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wang'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

确认主主：

mysql> select @@hostname;
+---------------+
| @@hostname    |
+---------------+
| 192.168.10.13 |
+---------------+
1 row in set (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.14
                  Master_User: wang
                  Master_Port: 3358
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 154
               Relay_Log_File: 192-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
                   .........................

mysql> select @@hostname;
+---------------+
| @@hostname    |
+---------------+
| 192.168.10.14 |
+---------------+
1 row in set (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.13
                  Master_User: wang
                  Master_Port: 3358
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 1051
               Relay_Log_File: 192-relay-bin.000005
                Relay_Log_Pos: 1264
        Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
               ............................

启动2台DB的keepalived

[root@192.168.10.13 ~]#service keepalived start
Starting keepalived:                                       [  OK  ]

查看2台DB的lvs信息：

[root@192.168.10.13 ~]#ipvsadm -L -n
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.10.100:3358 rr persistent 50
  -> 192.168.10.13:3358           Local   1      1          0

[root@192.168.10.14 ~]#ipvsadm -L -n
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.10.100:3358 rr persistent 50
  -> 192.168.10.14:3358           Local   1      1          0

验证：

在Client上连接VIP 192.168.10.100

[root@192.168.10.12 ~]#mysql -h 192.168.10.100 -uwang -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> select @@hostname;
+---------------+
| @@hostname    |
+---------------+
| 192.168.10.14 |
+---------------+
1 row in set (0.00 sec)

mysql>

通过VIP 192.168.10.100地址，成功连接到了192.168.10.14；

加入现在192.168.10.14发生了故障，我们手动把192.168.10.14的mysql服务停止；然后再次启动一个mysql连接

[root@192.168.10.14 ~]#
[root@192.168.10.14 ~]#service mysql stop
Shutting down MySQL............ SUCCESS!
[root@192.168.10.14 ~]#

[root@192.168.10.12 ~]#mysql -h 192.168.10.100 -uwang -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 66
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> select @@hostname;
+---------------+
| @@hostname    |
+---------------+
| 192.168.10.13 |
+---------------+
1 row in set (0.01 sec)

mysql>

看到现在连在了10.13上；

加入10.14故障修复了，启动mysql服务和keepalived 服务

[root@192.168.10.14 ~]#service mysql start
Starting MySQL.. SUCCESS!
[root@192.168.10.14 ~]#service keepalived start
Starting keepalived:                                       [  OK  ]
[root@192.168.10.14 ~]#

然后我们再吧192.168.10.13模拟故障，手动停止，可以看到又连到了14上

[root@192.168.10.13 ~]#service mysql stop
Shutting down MySQL............ SUCCESS!
[root@192.168.10.13 ~]#

[root@192.168.10.12 ~]#mysql -h 192.168.10.100 -uwang -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> select @@hostname;
+---------------+
| @@hostname    |
+---------------+
| 192.168.10.14 |
+---------------+
1 row in set (0.00 sec)

outlook也收到了对应的告警邮件