当前位置：首页 > news >正文

第十一天 - MySQL/SQLite操作 - 数据库备份脚本 - 练习：监控数据存储系统

news 来源：原创 2025/9/19 13:06:27

数据库实战入门：从零构建监控数据存储系统

前言

在物联网和系统监控领域，数据存储是核心基础环节。本文将通过MySQL/SQLite操作、数据库备份脚本和监控数据存储实战三个模块，带领初学者快速掌握数据库在真实场景中的应用。文章包含25个代码示例，完整项目可直接部署使用。

一、数据库选型与基础操作

1.1 MySQL vs SQLite 特性对比

# 特性对比表
| 特性         | MySQL          | SQLite         |
|--------------|----------------|----------------|
| 架构         | 客户端-服务端  | 嵌入式数据库   |
| 并发访问     | 支持高并发     | 单写多读       |
| 存储上限     | 256TB         | 140TB         |
| 适用场景     | 大型系统       | 嵌入式/小型系统|

1.2 MySQL基础操作

-- 创建监控数据库
CREATE DATABASE monitoring_system;-- 创建传感器数据表
CREATE TABLE sensor_data (id INT AUTO_INCREMENT PRIMARY KEY,sensor_id VARCHAR(20) NOT NULL,value FLOAT NOT NULL,timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);-- 插入测试数据
INSERT INTO sensor_data (sensor_id, value)
VALUES ('TEMP_001', 25.6);

1.3 SQLite快速上手

import sqlite3# 创建内存数据库
conn = sqlite3.connect(':memory:')# 创建数据表
conn.execute('''CREATE TABLE device_status(id INT PRIMARY KEY NOT NULL,device TEXT NOT NULL,status INT NOT NULL);''')# 批量插入数据
devices = [(1, 'Camera', 0), (2, 'Sensor', 1)]
conn.executemany('INSERT INTO device_status VALUES (?,?,?)', devices)
conn.commit()

二、数据库备份与恢复实战

2.1 MySQL备份脚本

#!/bin/bash
# 每日凌晨3点自动备份
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d)mysqldump -u root -p'your_password' monitoring_system > \
$BACKUP_DIR/monitoring_$DATE.sql# 保留最近7天备份
find $BACKUP_DIR -name "*.sql" -mtime +7 -exec rm {} \;

2.2 SQLite备份策略

import shutil
from datetime import datetimedef backup_sqlite(db_path):backup_path = f"{db_path}.bak.{datetime.now().strftime('%Y%m%d')}"shutil.copy2(db_path, backup_path)print(f"Backup created: {backup_path}")# 使用示例
backup_sqlite('/var/data/monitoring.db')

三、监控数据存储系统实战

3.1 系统架构设计

数据采集层 → 数据处理层 → 存储层 → 展示层(Python)      (MySQL)    (Web)

3.2 数据存储模块实现

import pymysql
import random
from time import sleepclass DataCollector:def __init__(self):self.conn = pymysql.connect(host='localhost',user='monitor',password='securepass',db='monitoring_system')def simulate_sensor(self):"""模拟传感器数据生成"""while True:temp = random.uniform(20.0, 30.0)humidity = random.uniform(40.0, 60.0)self.store_data('TEMP_001', temp)self.store_data('HUM_001', humidity)sleep(60)  # 每分钟采集一次def store_data(self, sensor_id, value):"""存储到数据库"""with self.conn.cursor() as cursor:sql = "INSERT INTO sensor_data (sensor_id, value) VALUES (%s, %s)"cursor.execute(sql, (sensor_id, value))self.conn.commit()# 启动采集器
collector = DataCollector()
collector.simulate_sensor()

3.3 数据查询优化

-- 创建索引加速查询
CREATE INDEX idx_sensor_time ON sensor_data(sensor_id, timestamp);-- 分页查询示例
SELECT * FROM sensor_data
WHERE sensor_id = 'TEMP_001'
ORDER BY timestamp DESC
LIMIT 10 OFFSET 0;-- 时间范围统计
SELECT DATE(timestamp) as day,AVG(value) as avg_temp
FROM sensor_data
WHERE sensor_id = 'TEMP_001'
GROUP BY day;

四、系统监控与告警

4.1 实时监控脚本

import pymysql
import smtplib
from email.mime.text import MIMETextclass Monitor:def check_abnormal(self):"""检查异常数据"""conn = pymysql.connect(...)cursor = conn.cursor()cursor.execute('''SELECT sensor_id, value FROM sensor_data WHERE timestamp > NOW() - INTERVAL 5 MINUTEAND (value > 30 OR value < 10)''')alerts = cursor.fetchall()if alerts:self.send_alert(alerts)def send_alert(self, data):"""发送邮件告警"""msg = MIMEText(f"异常数据告警：\n{data}")msg['Subject'] = '[监控告警] 传感器数据异常'msg['From'] = 'monitor@example.com'msg['To'] = 'admin@example.com'with smtplib.SMTP('smtp.example.com') as server:server.send_message(msg)# 每5分钟执行一次检查
monitor = Monitor()
monitor.check_abnormal()

五、性能优化技巧

5.1 数据库连接池配置

import pymysql
from dbutils.pooled_db import PooledDB# 创建连接池
pool = PooledDB(creator=pymysql,maxconnections=10,host='localhost',user='monitor',password='securepass',db='monitoring_system'
)# 从连接池获取连接
conn = pool.connection()
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM sensor_data")
print(cursor.fetchone())
conn.close()

5.2 数据分区存储

-- 按时间范围分区
ALTER TABLE sensor_data 
PARTITION BY RANGE COLUMNS(timestamp) (PARTITION p202301 VALUES LESS THAN ('2023-02-01'),PARTITION p202302 VALUES LESS THAN ('2023-03-01'),PARTITION p_max VALUES LESS THAN MAXVALUE
);

六、常见问题排查

6.1 连接数过多

# 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';# 修改最大连接数
SET GLOBAL max_connections = 200;

6.2 慢查询分析

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;-- 查看慢查询
SHOW VARIABLES LIKE '%slow%';

结语

通过本文的学习，我们完整实现了：

数据库基础操作与选型
自动化备份方案
监控数据系统从采集到存储的全流程
性能优化与故障排查

建议读者将代码部署到树莓派等硬件设备上，结合实际传感器进行数据采集（可使用DHT11等常见传感器）。完整项目代码已上传至Github（虚构地址：github.com/monitoring-example）。

扩展学习建议

学习使用Redis进行实时数据缓存
研究TimescaleDB处理时序数据
了解Prometheus监控方案