MySQL 备份可视化巡检系统

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
日志服务 SLS,月写入数据量 50GB 1个月
简介: 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 即可。

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


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
9天前
|
存储 关系型数据库 MySQL
mysql数据库备份与恢复
MySQL数据库的备份与恢复是确保数据安全性和业务连续性的关键操作。
28 4
|
4天前
|
关系型数据库 Unix MySQL
MySQL是一种关系型数据库管理系统
MySQL是一种关系型数据库管理系统
12 2
|
7天前
|
数据采集 中间件 关系型数据库
Mac系统通过brew安装mysql5.7后,启动报错的解决办法
Mac系统通过brew安装mysql5.7后,启动报错的解决办法
20 2
|
2月前
|
关系型数据库 MySQL 应用服务中间件
win7系统搭建PHP+Mysql+Apache环境+部署ecshop项目
这篇文章介绍了如何在Windows 7系统上搭建PHP、MySQL和Apache环境,并部署ECShop项目,包括安装配置步骤、解决常见问题以及使用XAMPP集成环境的替代方案。
42 1
win7系统搭建PHP+Mysql+Apache环境+部署ecshop项目
|
2月前
|
数据可视化 关系型数据库 MySQL
Mysql8 如何在 Window11系统下完成跳过密钥校验、完成数据库密码的修改?
这篇文章介绍了如何在Windows 11系统下跳过MySQL 8的密钥校验,并通过命令行修改root用户的密码。
Mysql8 如何在 Window11系统下完成跳过密钥校验、完成数据库密码的修改?
|
2月前
|
关系型数据库 MySQL Linux
【Azure 应用服务】在创建Web App Service的时候,选Linux系统后无法使用Mysql in App
【Azure 应用服务】在创建Web App Service的时候,选Linux系统后无法使用Mysql in App
【Azure 应用服务】在创建Web App Service的时候,选Linux系统后无法使用Mysql in App
|
2月前
|
关系型数据库 MySQL 数据库连接
成功解决「MySQL问题1」启动mysql时:发生系统错误5拒绝访问
这篇文章介绍了如何解决启动MySQL服务时遇到的系统错误5(拒绝访问),通过管理员权限启动命令窗口并使用"net start mysql"和"net stop mysql"命令来控制服务。
|
2月前
|
SQL 关系型数据库 MySQL
在Linux中,如何备份和恢复MySQL数据库?
在Linux中,如何备份和恢复MySQL数据库?
|
2月前
|
缓存 关系型数据库 MySQL
分享一个实用的MySQL一键巡检脚本
分享一个实用的MySQL一键巡检脚本
24 0
|
2月前
|
存储 关系型数据库 MySQL
MySQL备份与恢复
MySQL备份与恢复
47 0
下一篇
无影云桌面