监控共享高可用数据库主从

目录

一.准备工作

二.nfs服务端部署与配置

三.nfs客户端配置与部署

四.配置keepalived高可用

五.部署MySQL主从

配置主从数据库

测试数据库共享目录效果

六.模拟主节点宕机效果

七.zabbix监控主节点mysql服务宕机

---

一.准备工作

服务机说明	服务机名称	ip	系统
zabbix_server端	c83.example.com	192.168.187.134	centos8
NFS服务端	nfs.example.com	192.168.187.150	centos8
NFS客户端，数据库主机1	M1.example.com	192.168.187.128	centos8
NFS客户端，数据库主机2	M2.example.com	192.168.187.131	centos8
数据库从机	S1.example.com	192.168.187.129	centos8

所有服务机关闭防火墙与selinux 

二.nfs服务端部署与配置

# 1.安装nfs与rpc服务
[root@nfs ~]# yum -y install nfs-utils rpcbind

# 2.启动服务并设置开机自启
[root@nfs ~]# systemctl enable --now nfs-server rpcbind
Created symlink /etc/systemd/system/multi-user.target.wants/nfs-server.service → /usr/lib/systemd/system/nfs-server.service.

# 3.创建共享存储文件夹
[root@nfs ~]# mkdir -p /nfs/mysql

# 4.配置nfs
# 表示所有客户端可挂载
[root@nfs ~]# echo "/nfs/mysql *(rw,sync,no_root_squash)" >> /etc/exports
# 使nfs配置生效
[root@nfs ~]# exportfs -r
# 重启nfs服务
[root@nfs ~]# systemctl restart nfs-server

# 5.查看是否成功和可用的nfs地址
[root@nfs ~]# showmount -e localhost
Export list for localhost:
/nfs/mysql *

三.nfs客户端配置与部署

两台客户端部署同样的操作
# 1.安装nfs服务与rpc服务
[root@M1 ~]# yum -y install nfs-utils rpcbind

# 2.创建挂载目录
[root@M1 ~]# mkdir -p /opt/data

# 3.挂载共享目录为服务端的/nfs/mysql与本地的/opt/data
[root@M1 ~]# mount -t nfs 192.168.187.150:/nfs/mysql /opt/data

# 挂载成功，这时可以用df查看
[root@M1 nfs]# df -h
Filesystem                 Size  Used Avail Use% Mounted on
devtmpfs                   1.8G     0  1.8G   0% /dev
tmpfs                      1.8G     0  1.8G   0% /dev/shm
tmpfs                      1.8G   17M  1.8G   1% /run
tmpfs                      1.8G     0  1.8G   0% /sys/fs/cgroup
/dev/mapper/cs-root         17G  5.0G   12G  30% /
/dev/sda1                 1014M  227M  788M  23% /boot
tmpfs                      364M     0  364M   0% /run/user/0
192.168.187.150:/nfs/data   17G  2.0G   16G  12% /data/nfs

# 4.测试，这时往任一端上写入文件，另外的服务机都会同步
[root@nfs ~]# cd /nfs/mysql/
[root@nfs mysql]# ls
[root@nfs mysql]# touch 666
[root@nfs mysql]# ls
666
[root@M1 ~]# cd /data/nfs
[root@M1 nfs]# ls
666

四.配置keepalived高可用

# 1.配置epel源，两台MySQL主机上都要配置
[root@M1 ~]# yum install -y https://mirrors.aliyun.com/epel/epel-release-latest-8.noarch.rpm
[root@M1 ~]# sed -i 's|^#baseurl=https://download.example/pub|baseurl=https://mirrors.aliyun.com|' /etc/yum.repos.d/epel*
[root@M1 ~]# sed -i 's|^metalink|#metalink|' /etc/yum.repos.d/epel*
[root@M1 ~]# yum makecache

# 2.安装keepalived，两台MySQL主机上都要安装
[root@M1 ~]# yum -y install keepalived

# 3.配置主节点配置文件
[root@M1 ~]# vim /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

global_defs {
   router_id haproxy01
}

vrrp_instance VI_1 {
    state MASTER
    interface ens160
    virtual_router_id 99  ----- 这里主备一致
    priority 100     ----- 主节点比备用节点的优先级要高
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456789   ---- 密码一致
    }
    virtual_ipaddress {
        192.168.187.200      ----- 设置的同一网段的vip
    }
}

virtual_server 192.168.187.200 3306 {   --- 端口号改为3306
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP

    real_server 192.168.187.128 3306 {
        weight 1
        TCP_CHECK {
            connect_port 3306    ----- 下面都是3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }

    real_server 192.168.187.131 3306 {
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }

# 设置keepalived服务开机自启
[root@M1 ~]# systemctl enable --now keepalived

# 3.配置备用节点配置文件
[root@M2 ~]# vim /etc/keepalived/keepalived.conf 
[root@M2 ~]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived
 
global_defs {
   router_id haproxy01
}
 
vrrp_instance VI_1 {
    state BACKUP
    interface ens160
    virtual_router_id 99
    priority 90       ----- 比主节点低，其它一致
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456789
    }
    virtual_ipaddress {
        192.168.187.200
    }
}
 
virtual_server 192.168.187.200 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP
 
    real_server 192.168.187.128 3306 {
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
 
    real_server 192.168.187.131 3306 {
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
[root@M2 ~]# systemctl enable --now keepalived
Created symlink /etc/systemd/system/multi-user.target.wants/keepalived.service → /usr/lib/systemd/system/keepalived.service.

# 查看vip
[root@M1 scripts]# ip a
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:d8:60:da brd ff:ff:ff:ff:ff:ff
    altname enp3s0
    inet 192.168.187.128/24 brd 192.168.187.255 scope global dynamic noprefixroute ens160
       valid_lft 1652sec preferred_lft 1652sec
    inet 192.168.187.200/32 scope global ens160
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fed8:60da/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@M1 scripts]# 

五.部署MySQL主从

配置主从数据库

                     三台数据库的部署步骤一致
# 1.下载mysql5.7二进制软件包
[root@M1 ~]#  wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.42-el7-x86_64.tar.gz
[root@M1 ~]# ls
anaconda-ks.cfg  mysql-5.7.42-el7-x86_64.tar.gz 
[root@M1 ~]# tar xf mysql-5.7.42-el7-x86_64.tar.gz -C /usr/local
[root@M1 ~]# ls /usr/local/
bin  games    lib    libexec                  sbin   src
etc  include  lib64  mysql-5.7.42-el7-x86_64  share

# 安装依赖
[root@M1 ~]# yum -y install ncurses-compat-libs

# 2.创建mysql用户与组
[root@M1 ~]# groupadd -r -g 306 mysql
[root@M1 ~]# useradd -r -M -s /sbin/nologin -g 306 -u 306 mysql

# 做个软链接
[root@M1 ~]# cd /usr/local
[root@M1 local]# ln -sv mysql-5.7.42-el7-x86_64 mysql 
'mysql' -> 'mysql-5.7.42-el7-x86_64'

# 修改/usr/local/mysql的属主属组
[root@M1 local]# chown -R mysql.mysql /usr/local/mysql

# 设置环境变量
[root@M1 local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@c81 local]# . /etc/profile.d/mysql.sh 
[root@c81 local]# ldconfig

# 3.初始化数据库
[root@M1 local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
# 注意:这里指定数据库文件存放地点，待会要用来nfs挂载同步文件
# 记住初始化密码，下面用来登录

# 4.配置数据库
[root@M1 local]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
[root@M1 local]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@M1 local]# ldconfig

# 生成配置文件
[root@M1 local]# cat > /etc/my.cnf <<EOF
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
EOF

# 配置服务启动脚本
[root@M1 local]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@M1 local]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@M1 local]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld

# 启动服务
[root@M1 ~]# service mysqld start

# 5.用临时密码登录数据库并重新设置密码
[root@M1 ~]# /usr/local/mysql/bin/mysql -uroot -pFdW#Q_/kX52t
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 2
Server version: 5.7.42

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> set password = password("2664218545Z")
    -> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye

# 6.主数据库配置
# 创建一个授权用户repl从数据库使用，可以访问187网段的所有ip
# 只在一台上做
mysql> grant replication slave on *.* to 'repl'@'192.168.187.%' identified by '123456789';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

# 7.配置主数据库
[root@c81 ~]# vim /etc/my.cnf
[root@c81 ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql_bin   ---- 添加上这俩行
server-id=10        ---- 主数据库id小于从数据库，另外一台主数据库id可以设为10，也可以设为其它，但是要小于从数据库

# 重启mysql服务
[root@c81 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@c81 ~]# service mysqld start
Starting MySQL. SUCCESS! 

# 查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


# 8.配置从库
[root@S1 local]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=20              ---- 加上这俩行，从库id大于主库
relay-log=mysql-relay-bin

# 配置从库认主
[root@S1 local]# /usr/local/mysql/bin/mysql -uroot -p2664218545Z

mysql> change master to
    -> master_host='192.168.187.200',    ------- 这里是vip
    -> master_user='repl',
    -> master_password='123456789',
    -> master_log_file=' mysql_bin.000003',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)


# 启动主从复制
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

# 查看状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.187.200
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes       ---- 看到这两个yes表示成功
 

测试数据库共享目录效果

在主节点的主库上创建一个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database zz;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zz                 |
+--------------------+
5 rows in set (0.01 sec)

# 在从库上查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zz                 |
+--------------------+
5 rows in set (0.00 sec)

# 关闭主节点的数据库，在备用节点的主数据库查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zz                 |
+--------------------+

六.模拟主节点宕机效果

# 1.现在，主节点上keepalived与mysql服务都是开启的
# 编写脚本，当mysql服务停止时，关闭keepalived服务
[root@M1 ~]# mkdir /scripts
[root@M1 ~]# cd /scripts
[root@M1 scripts]# vim mysql.sh 
[root@M1 scripts]# cat mysql.sh 
#! /bin/bash
if [ `ps -ef | grep -Ev "grep|$0" | grep mysql | wc -l` -lt 2 ];then
        systemctl stop keepalived
fi

# 2.编辑keepalived服务配置文件
[root@M1 scripts]# vim /etc/keepalived/keepalived.conf 
[root@M1 scripts]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

global_defs {
   router_id haproxy01
}
vrrp_script mysql_check {       ---- mysql_check为事件名
    script "/scripts/mysql.sh"  ----- 时间内容为 interval 1 / 每秒执行一次脚本
    interval 1
}

vrrp_instance VI_1 {
    state MASTER
    interface ens160
    virtual_router_id 99
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456789
    }
    virtual_ipaddress {
        192.168.187.200
    }
    track_script {       --  追踪脚本
        mysql_check
    }
}


virtual_server 192.168.187.200 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP

    real_server 192.168.187.128 3306 {
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }

    real_server 192.168.187.131 3306 {
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }

# 重启keepalived服务
[root@M1 scripts]# systemctl restart keepalived

测试: 关闭主节点的mysql服务，keepalived服务是否关闭

[root@M1 scripts]# service mysqld stop
Shutting down MySQL............ SUCCESS! 
[root@M1 scripts]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor pres>
   Active: inactive (dead) since Sun 2023-10-15 07:19:36 EDT; 16s ago
  Process: 201683 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, >
 Main PID: 201684 (code=exited, status=0/SUCCESS)

# 关闭mysql服务，keepalived服务被脚本关闭

# 注意
# 另外一台主机虽然可以编写获得vip后启动mysql服务的脚本
# 但是不能够编写失去vip关闭mysqld服务的脚本，因为失去vip意味着主节点mysql服务启动，但是两台主机的mysql服务不能够同时启动

七.zabbix监控主节点mysql服务宕机

zabbix监控端部署详情参考前面的博客

zabbix服务部署

zabbix监控主节点mysql服务宕机可参考前面的博客

zabbix监控实战