MySQL 备份可视化巡检系统

hh真是个慢性子

已于 2023-11-20 14:36:15 修改

阅读量56

点赞数 1

文章标签： mysql 数据库数据可视化 grafana

于 2023-11-17 18:22:15 首次发布

本文链接：https://blog.csdn.net/weixin_45385457/article/details/134468592

版权

MySQL 备份可视化巡检系统

简介

工具

python3 + granfana

功能

可视化备份信息
方便定位数据增长量
存储备份信息
脚本及数据异常邮件报警

运行逻辑

通过定时任务，使用配置文件调用脚本，实现将备份信息写入到MySQL，在通过 granfana 将MySQL 数据使用 SQL 将备份信息展示在页面上。

注意事项

Granfana 和 MySQL 可以直接使用yum 或 docker 安装

docker 安装Granfana 注意配置文件中需要开放外网访问

docker exec --user=root  -it docker_id  cat /usr/share/grafana/conf/defaults.ini |grep http_addr
http_addr = 0.0.0.0

效果

在这里插入图片描述

实现

编写代码

vim  mysql_bak_info.py
import os
import sys
import configparser
import logging
from datetime import datetime, timedelta
import pymysql
import re
import smtplib
from email.mime.text import MIMEText

"""
CREATE TABLE `backup_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `host` varchar(50) NOT NULL COMMENT '备份主机IP',
  `port` varchar(10) NOT NULL COMMENT '备份主机端口',
  `master_host` varchar(50) DEFAULT NULL COMMENT 'MySQL master IP',
  `master_port` varchar(10) DEFAULT NULL COMMENT 'MySQL master port',
  `type` varchar(10) NOT NULL COMMENT '备份类型 full or inc',
  `filename` varchar(200) NOT NULL COMMENT '备份文件名',
  `size` varchar(200) NOT NULL COMMENT '备份大小',
  `save_host` varchar(50) NOT NULL COMMENT '保存备份的机器',
  `stop_time` datetime NOT NULL COMMENT '备份结束时间',
  `status` tinyint(4) NOT NULL COMMENT '简单判断备份信息是否正常',
  PRIMARY KEY (`id`),
  KEY `idx_time_host_port` (`stop_time`,`host`,`port`)
) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=utf8 COMMENT='数据库备份信息';
"""

def setup_logging(log_directory, log_filename):
    # 配置日志
    log_file_path = os.path.join(log_directory, log_filename)
    logging.basicConfig(filename=log_file_path, level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

def load_config(config_file_path):
    # 加载配置文件
    config = configparser.ConfigParser()
    config.read(config_file_path)
    return config


def connect_to_database(config):

    # 连接MySQL数据库
    try:
        db_config = {
            'host': config['MYSQL']['host'],
            'port': int(config['MYSQL']['port']),
            'user': config['MYSQL']['user'],
            'password': config['MYSQL']['password'],
            'database': config['MYSQL']['database'],
        }
        return pymysql.connect(**db_config)
    except pymysql.Error as e:
        # 处理连接数据库错误，记录错误到日志
        error_message = f"Error connecting to MySQL database: {e}"
        logging.error(error_message)

        # 发送电子邮件通知
        if 'EMAIL' in config and all(key in config['EMAIL'] for key in ['sender_email', 'sender_password', 'receiver_email', 'smtp_server', 'smtp_port']):
            email_subject = f"备份检查-连接 MySQL 数据库错误在: {config.get('HOST', 'host_ip')}:{config.get('HOST', 'host_port')}"
            email_body = error_message
            send_email(config, email_subject, email_body)

        # 抛出异常终止程序
        raise RuntimeError(error_message)

def send_email(config, subject, body):
    # 配置电子邮件
    if 'EMAIL' not in config:
        logging.warning("Email configuration not found. Skipping email notification.")
        return

    if not all(key in config['EMAIL'] for key in ['sender_email', 'sender_password', 'receiver_email', 'smtp_server', 'smtp_port']):
        logging.warning("Incomplete or missing email configuration. Skipping email notification.")
        return

    sender_email = config['EMAIL']['sender_email']
    sender_password = config['EMAIL']['sender_password']
    receiver_email = config['EMAIL']['receiver_email']
    smtp_server = config['EMAIL']['smtp_server']
    smtp_port = int(config['EMAIL']['smtp_port'])

    # 构建邮件内容
    message = MIMEText(body)
    message['Subject'] = subject
    message['From'] = sender_email
    message['To'] = receiver_email

    # 连接到SMTP服务器并发送邮件
    with smtplib.SMTP(smtp_server, smtp_port) as server:
        server.starttls()
        server.login(sender_email, sender_password)
        server.sendmail(sender_email, receiver_email, message.as_string())

def get_files_within_seven_days(directory, pattern):
    current_date = datetime.now()
    seven_days_ago = current_date - timedelta(days=7)

    all_files = os.listdir(directory)

    seven_days_files = [f for f in all_files if re.match(pattern, f)]

    seven_days_files_full_path = [os.path.join(directory, f) for f in seven_days_files
                                   if datetime.fromtimestamp(os.path.getmtime(os.path.join(directory, f))) > seven_days_ago]

    return seven_days_files_full_path

def get_file_size(file_path):
    try:
        size_in_bytes = os.path.getsize(file_path)
        # 直接返回文件大小的整数表示
        return size_in_bytes
    except FileNotFoundError:
        raise FileNotFoundError(f"File not found: {file_path}")
    except Exception as e:
        raise RuntimeError(f"Error getting file size: {e}")

def get_file_modification_time(file_path):
    try:
        modified_timestamp = os.path.getmtime(file_path)
        modified_datetime = datetime.fromtimestamp(round(modified_timestamp))
        return modified_datetime
    except FileNotFoundError:
        raise FileNotFoundError(f"File not found: {file_path}")
    except Exception as e:
        raise RuntimeError(f"Error getting file modification time: {e}")

def check_file_size(file_path):
    try:
        size_in_bytes = os.path.getsize(file_path)
        size_in_kb = size_in_bytes / 1024.0
        if size_in_kb >= 1:
            return 1
        else:
            raise ValueError(f"File size is less than 1 KB: {file_path}")
    except FileNotFoundError:
        raise FileNotFoundError(f"File not found: {file_path}")
    except Exception as e:
        raise RuntimeError(f"Error checking file size: {e}")

def write_to_mysql(conn, config, size_kb, end_time, file_status, filename):
    try:
        with conn.cursor() as cursor:
            # 在执行插入之前先检查是否已经存在相同的记录
            check_query = ("SELECT COUNT(*) FROM backup_info "
                           "WHERE filename = %s AND stop_time = %s")
            cursor.execute(check_query, (filename, end_time))
            result = cursor.fetchone()

            if result[0] == 0:  # 如果没有相同记录，则执行插入操作
                insert_query = ("INSERT INTO backup_info "
                                "(host, port, master_host, master_port, type, filename, size, save_host, stop_time, status) "
                                "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)")
                # 修改size_kb的计算为字节，并将其转换为整数
                size_bytes = int(size_kb * 1024)
                data = (
                    config['HOST']['host_ip'],
                    config['HOST']['host_port'],
                    config['MASTER']['master_ip'],
                    config['MASTER']['master_port'],
                    config['BACKUP']['backup_type'],
                    filename,
                    str(size_bytes),  # 将size_kb转换为整数，并转换为字符串
                    config['BACKUP']['save_host_ip'],
                    end_time,
                    file_status
                )
                cursor.execute(insert_query, data)
                conn.commit()

                logging.info(
                    f"备份检查-写入MySQL的备份信息 - Size: {size_bytes} B, End Time: {end_time}, Status: {file_status}, Filename: {filename}")
            else:
                logging.info(f"相同的记录已存在，不执行插入操作 - Filename: {filename}, Stop Time: {end_time}")

    except pymysql.Error as err:
        raise RuntimeError(f"MySQL Error: {err}")


def main():
    if len(sys.argv) != 2:
        print("Usage: python backup_script.py <config_file_path>")
        sys.exit(1)

    config_file_path = sys.argv[1]
    config = load_config(config_file_path)

    log_directory = config['LOG']['log_directory']
    setup_logging(log_directory, config['LOG']['log_filename'])
    
    host_ip = config.get('HOST', 'host_ip')
    host_port = config.get('HOST', 'host_port')

    db_conn = connect_to_database(config)

    error_messages = []  # 用于收集错误消息

    try:
        # 获取文件列表
        directory = config.get('BACKUP', 'backup_directory')
        pattern = config.get('BACKUP', 'filename')
        file_paths = get_files_within_seven_days(directory, pattern)

        for file_path in file_paths:
            try:
                size_kb = get_file_size(file_path)
                end_time = get_file_modification_time(file_path)
                file_status = check_file_size(file_path)
                filename = f"{file_path}"

                write_to_mysql(db_conn, config, size_kb, end_time, file_status, filename)

            except (FileNotFoundError, ValueError, RuntimeError) as e:
                error_message = f"Error processing file {file_path}: {e}"
                logging.error(error_message)

                # 将错误消息添加到列表中
                error_messages.append(error_message)

    finally:
        db_conn.close()

    # 在文件遍历完成后，检查是否有错误消息，如果有则发送邮件
    if error_messages:
        # 发送电子邮件通知
        if 'EMAIL' in config and all(key in config['EMAIL'] for key in ['sender_email', 'sender_password', 'receiver_email', 'smtp_server', 'smtp_port']):
            email_subject = f"备份检查-错误在 : {host_ip}:{host_port}"
            email_body = "\n".join(error_messages)  # 将错误消息合并为一个字符串
            send_email(config, email_subject, email_body)

if __name__ == "__main__":
    main()

MySQL 建表语句

CREATE TABLE `backup_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `host` varchar(50) NOT NULL COMMENT '备份主机IP',
  `port` varchar(10) NOT NULL COMMENT '备份主机端口',
  `master_host` varchar(50) DEFAULT NULL COMMENT 'MySQL master IP',
  `master_port` varchar(10) DEFAULT NULL COMMENT 'MySQL master port',
  `type` varchar(10) NOT NULL COMMENT '备份类型 full or inc',
  `filename` varchar(200) NOT NULL COMMENT '备份文件名',
  `size` varchar(200) NOT NULL COMMENT '备份大小',
  `save_host` varchar(50) NOT NULL COMMENT '保存备份的机器',
  `stop_time` datetime NOT NULL COMMENT '备份结束时间',
  `status` tinyint(4) NOT NULL COMMENT '简单判断备份信息是否正常',
  PRIMARY KEY (`id`),
  KEY `idx_time_host_port` (`stop_time`,`host`,`port`)
) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=utf8 COMMENT='数据库备份信息';

编写配置文件

[HOST]
; 备份主机信息 仅用于在数据库显示备份信息
host_ip = 192.168.1.1
host_port = 3306

[MASTER]
; MySQL主服务器信息 仅用于在数据库显示备份信息
master_ip = 192.168.1.2
master_port = 3306

[BACKUP]
; 备份配置信息
; 配置备份文件保存目录
backup_directory = /tmp
; 正则匹配对应的文件 如 full_20231010.tar.gz >正则匹配 full_.*.tar.gz
filename = full_.*.tar.gz
; 备份类型 full 或 inc 全备或增倍
backup_type = full
; 保存备份的主机 仅用于在数据库显示备份信息
save_host_ip = 10.10.8.8

[LOG]
; 日志配置
log_directory = /tmp
; 日志名可修改为 mysql 实例的ip_port 方便区分
log_filename = backup_script.log

[MYSQL]
; MySQL连接信息 用于保存备份信息的MySQL 需要创建表
host = 127.0.0.1
port = 3306
user = root
password = 123456
database = test

[EMAIL]
; 配置邮箱可将报错信息发送至邮箱 注释邮箱选项可跳过发送邮箱
; 发件人邮箱
sender_email = test@test.com
; 发件人邮箱密码
sender_password = test123
; 收件人邮箱
receiver_email = test@test.com
; 发信服务器地址
smtp_server = mail.test.com 
; 发信服务器端口
smtp_port = 25

安装依赖

pip3 install configparser
pip3 install logging
pip3 install pymysql
pip3 install smtplib

测试执行如果缺少其他依赖，请自行安装

打包代码

一般情况下在linux 上运行，需要在一台安装 python3 的linux 主机将代码和依赖打包成一个文件方便运行，这样就不需要在运行的主机上单独安装python了

pyinstaller --onefile  --hidden-import=os,sys,configparser,logging,datetime,pymysql,re,smtplib,email.mime.text     mysql_bak_info.py

打包完成后会在当前目录下生成一个 dist 目录，目录内就是打包好的代码文件

[root@localhost ~]# ll dist/
-rwxr-xr-x. 1 root root 10370288 11月 15 14:38 mysql_bak_info

将打包好的文件拷贝到备份文件保存的主机，并编写配置文件即可运行。

每个实例的备份需要单独定义一个配置文件。

实战

以下是我在 centos 7 上打包好的文件可直接运行，及创建的多个目录和批量运行脚本，可直接食用。

目录结构

mysql_backinfo/
├── conf    # 配置文件保存路径 可将每个mysql实例的备份以host+port的形式命名为单独的配置文件。
│   └── test_192.168.0.3_3306.ini  # 示例配置文件
├── config_list.txt   # 将配置文件的全路径写入此文件，方便定时任务调用
├── crond.log         # 定时任务日志
├── crond.sh          # 定时任务脚本  作用是循环调用保存在 config_list.txt 文件中的配置文件
├── logs              # 所有实例的脚本执行日志写入到这个目录
│   └── bakinfo_192.168.0.3_3306.log  # 每个实例单独的日志
└── mysql_bak_info    # 打包的脚本程序

获取文件

cd /opt/
# 直接在网站附件中下载即可
wget  https://download.csdn.net/download/weixin_45385457/88547242?spm=1001.2101.3001.9499

解压文件

tar xf mysql_backinfo.tar.gz

编写配置文件

vim /opt/mysql_backinfo/conf/dnr_172.18.222.85_3306.ini
[HOST]
; 备份主机信息 仅用于在数据库显示备份信息
host_ip = 192.168.1.1
host_port = 3306

[MASTER]
; MySQL主服务器信息 仅用于在数据库显示备份信息
master_ip = 192.168.1.2
master_port = 3306

[BACKUP]
; 备份配置信息
; 配置备份文件保存目录
backup_directory = /tmp
; 正则匹配对应的文件 如 full_20231010.tar.gz >正则匹配 full_.*.tar.gz
filename = full_.*.tar.gz
; 备份类型 full 或 inc 全备或增倍
backup_type = full
; 保存备份的主机 仅用于在数据库显示备份信息
save_host_ip = 10.10.8.8

[LOG]
; 日志配置
log_directory = /tmp
; 日志名可修改为 mysql 实例的ip_port 方便区分
log_filename = backup_script.log

[MYSQL]
; MySQL连接信息 用于保存备份信息的MySQL 需要创建表
host = 127.0.0.1
port = 3306
user = root
password = 123456
database = test

[EMAIL]
; 配置邮箱可将报错信息发送至邮箱 注释邮箱选项可跳过发送邮箱
; 发件人邮箱
sender_email = test@test.com
; 发件人邮箱密码
sender_password = test123
; 收件人邮箱
receiver_email = test@test.com
; 发信服务器地址
smtp_server = mail.test.com 
; 发信服务器端口
smtp_port = 25

单个执行

以下方式是手动调用脚本+配置文件，将数据同步到MySQL

cd /opt/mysql_backinfo/
./mysql_bak_info ./conf/test_192.168.0.3_3306.ini

执行时根据提示修改配置文件即可

使用脚本执行

将需要获取备份信息的实例，的配置文件路径写入到此文件

vim /opt/mysql_backinfo/config_list.txt
# test 业务 生产备份检查
/opt/mysql_backinfo/conf/test_192.168.0.3_3306.ini
# 某某业务 可以将所有备份实例
# /opt/mysql_backinfo/conf/test_192.168.0.4_3307.ini

运行脚本即可执行所有配置文件

sh  /tmp/mysql_backinfo/crond.sh

定时任务执行

使用定时任务，每天将备份信息推送到MySQL中。

[root@test mysql_backinfo]# crontab -e
##################备份检查###########################
# 每天十点检查备份信息 并推送到MySQL数据库，在granfa 中展示数据
0 10 * * *  /bin/sh /opt/mysql_backinfo/crond.sh >>/opt/mysql_backinfo/crond.log 2>&1

经验之谈：

尽量选取所有备份全部执行完毕的时间，防止漏掉某些实例导致报错。

尽量选择上班时间执行定时任务，方便及时处理故障，避免遗忘。

配置granfana

安装

略。。。

配置数据源

这一步的配置需要在MySQL 中创建连接的账号密码，并在 granfana 中配置可以外网访问。

测试网络是否通畅：

在 granfana 机器中使用 telnet MySQL 的 IP + Port 测试是否可以访问MySQL 的端口。

telnet 192.168.0.3 3306
Escape character is '^]'. # 显示这个表示通畅

测试是否可以连接MYSQL：

在 granfana 机器安装MySQL 客户端，使用mysql 命令测试是否可以登录MySQL。

yum install -y mysql
mysql -h192.168.0.3 -ubackup_info -p123456 -P3306

如以上步骤没有问题，依然连接不上granfana 说明 granfnan的配置存在问题可修改配置文件解决，具体方法看简介。

在这里插入图片描述

配置条形图

在这里插入图片描述

SQL 语句详解

select stop_time,size/1024 as szie  FROM mysql_backup.backup_info  where $__timeFilter(stop_time) AND    host='192.168.0.3' and port='3306'    ORDER BY  stop_time;

# 选择时间数据量字段作为 x y 轴
#  $__timeFilter(stop_time) 是 granfana的变量 用来配合筛选需要展示的时间段
# host + port 筛选 每个备份实例 
#  ORDER BY  stop_time; 使用 stop_time 进行排序。

其他系统图形请复制当前图形，修改SQL中的host 个 port 即可。

本文章仅供参考，请谨慎使用。

hh真是个慢性子

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 备份可视化巡检系统

可视化备份信息方便定位数据增长量存储备份信息脚本及数据异常邮件报警通过定时任务，使用配置文件调用脚本，实现将备份信息写入到MySQL，在通过 granfana 将MySQL 数据使用 SQL 将备份信息展示在页面上。Granfana 和 MySQL 可以直接使用yum 或 docker 安装docker 安装Granfana 注意配置文件中需要开放外网访问。
复制链接

扫一扫