Keepalived+MySQL 高可用集群

Keepalived+MySQL 高可用集群

1.环境配置

角色	IP地址	主机名	说明
mysql-master/slave	192.168.73.128	mysql1	keepalived master /mysql-master/slave
mysql-master/slave	192.168.73.129	mysql2	keepalived backup /mysql-master/slave
vip	192.168.73.100
client	192.168.73.130	client	测试机安装mysql客户端通过vip登录

2.不会配置mysql双主的可以看我以前的帖子

mysql双主

3.授权用户远程登录

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4.配置keepalived

#安装应用
[root@mysql1 ~]# yum install -y keepalived

#mysq1配置
[root@mysql1 ~]# mv /etc/keepalived/keepalived.conf{,.bak} #备份原文件
[root@mysql1 keepalived]# cat keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id mysql1
}

vrrp_script check_run {
   script "/etc/keepalived/mysql.sh"
   interval 5
}

vrrp_instance VI_1 {
    state MASTER  #主服务
    interface ens33  
    virtual_router_id 88
    priority 100  #优先级
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass wh2024
    }

    track_script {
        check_run
    }

    virtual_ipaddress {
        192.168.73.100
    }
}

#写一个简单的判断mysql是否正常运行的脚本
[root@mysql1 ~]# cat /etc/keepalived/mysql.sh 
#!/bin/bash
/usr/bin/mysql -uroot -p'Huawei@123' -e "show status;" &>/dev/null 
if [ $? -ne 0 ] ;then 
        /usr/bin/systemctl stop keepalived 
fi 

#脚本加上执行权限
[root@mysql1 ~]# chmod +x /etc/keepalived/mysql.sh


#mysql2配置
[root@mysql1 ~]# mv /etc/keepalived/keepalived.conf{,.bak} #备份原文件
[root@mysql2 keepalived]# cat keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id mysql2
}

vrrp_script check_run {
   script "/etc/keepalived/mysql.sh"
   interval 5
}

vrrp_instance VI_1 {
    state BACKUP  #热备
    interface ens33
    virtual_router_id 88
    priority 50   #优先级
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass wh2402
    }

    track_script {
        check_run
    }

    virtual_ipaddress {
        192.168.73.100
    }
}

[root@mysql2 ~]# cat /etc/keepalived/mysql.sh 
#!/bin/bash
/usr/bin/mysql -uroot -p'Huawei@123' -e "show status;" &>/dev/null 
if [ $? -ne 0 ] ;then 
        /usr/bin/systemctl stop keepalived 
fi 
#脚本加上执行权限
[root@mysql2 ~]# chmod +x /etc/keepalived/mysql.sh

3.启动mysql，keepalived

#启动mysql1
[root@mysql1 ~]# systemctl start mysqld
[root@mysql1 ~]# systemctl status keepalived.service 
● keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
   Active: active (running) since 五 2024-09-27 14:57:07 CST; 42s ago
  Process: 6968 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 6969 (keepalived)
   CGroup: /system.slice/keepalived.service
           ├─6969 /usr/sbin/keepalived -D
           ├─6970 /usr/sbin/keepalived -D
           └─6971 /usr/sbin/keepalived -D
#可以看到 vip192.168.73.100会优先漂到 priority 100的
[root@mysql1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:cb:1e:ed brd ff:ff:ff:ff:ff:ff
    inet 192.168.73.128/24 brd 192.168.73.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.73.100/32 scope global ens33
       valid_lft forever preferred_lft forever
       
#启动mysql2      
[root@mysql2 ~]# systemctl start mysqld
[root@mysql2 ~]# systemctl status keepalived.service 
● keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
   Active: active (running) since 五 2024-09-27 14:55:54 CST; 4min 5s ago
  Process: 19851 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 19852 (keepalived)
   CGroup: /system.slice/keepalived.service
           ├─19852 /usr/sbin/keepalived -D
           ├─19853 /usr/sbin/keepalived -D
           └─19854 /usr/sbin/keepalived -D
[root@mysql2 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
       link/ether 00:0c:29:01:7e:cd brd ff:ff:ff:ff:ff:ff
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
valid_lft 1746sec preferred_lft 1746sec
    inet 192.168.73.129/24 brd 192.168.73.255 scope global secondary noprefixroute ens33

4.远程登录检验

[root@client ~]# mysql -uroot -p'123456' -h192.168.73.100
#可以成功的登录上去
#如果登录不上去，请确认是否关闭防火墙，是否创建了远程登录的权限用户
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 86
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

#关闭mysql1
[root@mysql1 ~]# systemctl stop mysqld
#可以看到vim飘到了mysql2
[root@mysql2 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:01:7e:cd brd ff:ff:ff:ff:ff:ff     
     valid_lft 1538sec preferred_lft 1538sec
    inet 192.168.73.100/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.73.129/24 brd 192.168.73.255 scope global secondary noprefixroute ens33
    
#在试着查询库，可以看到已经跳转到mysql2里
mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    886
Current database: *** NONE ***

+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

#再次启动mysql1
[root@mysql1 ~]# systemctl start mysqld
[root@mysql1 ~]# systemctl restart keepalived.service 
#vip又重新跳回mysql1
[root@mysql1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:cb:1e:ed brd ff:ff:ff:ff:ff:ff
    inet 192.168.73.128/24 brd 192.168.73.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.73.100/32 scope global ens33

#重新查询库
mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    12
Current database: *** NONE ***

+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.06 sec)