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 即可。
本文章仅供参考,请谨慎使用。