树莓派传感器数据转存至局域网内笔记本MySQL服务器方案咨询

刚好之前帮朋友搞定过一模一样的场景——树莓派采集传感器数据，本地存不下转存局域网笔记本的MySQL。咱们一步步来，保证能解决：

一、先搞定笔记本MySQL的远程访问权限（最关键的一步） #

默认MySQL只允许本地连接，得先开权限才能让树莓派访问：

• 编辑MySQL配置文件：
  Ubuntu/Debian系统是/etc/mysql/mysql.conf.d/mysqld.cnf，Windows系统找my.ini（通常在MySQL安装目录的data文件夹附近）
  找到bind-address这一行，把它改成你的笔记本局域网IP（比如192.168.1.100），或者临时改成0.0.0.0（允许所有IP访问，测试用，之后记得改回特定IP更安全）
• 重启MySQL服务：
  Ubuntu/Debian：sudo systemctl restart mysql
  Windows：在系统服务里找到MySQL，右键重启即可
• 登录MySQL创建专用远程用户：
  打开终端/CMD登录MySQL：mysql -u root -p，输入密码后执行以下SQL：

  -- 先创建存储数据的数据库（如果还没建）
  CREATE DATABASE IF NOT EXISTS sensor_db;
  -- 创建允许树莓派子网访问的用户，密码自己设
  CREATE USER 'pi_sensor_user'@'192.168.1.%' IDENTIFIED BY 'your_strong_password';
  -- 给这个用户授权操作sensor_db的所有权限
  GRANT ALL PRIVILEGES ON sensor_db.* TO 'pi_sensor_user'@'192.168.1.%';
  -- 刷新权限生效
  FLUSH PRIVILEGES;
  

  这里192.168.1.%表示允许整个192.168.1.x子网的设备连接，也可以直接指定树莓派的固定IP，安全性更高。

二、树莓派上安装MySQL客户端依赖 #

假设你用Python采集数据（树莓派最常用的方式），安装连接MySQL的库：

sudo apt update && sudo apt install python3-mysql.connector
# 或者用pip安装最新版
pip3 install mysql-connector-python

三、修改采集代码，把数据写到远程MySQL #

把你原来的本地存储代码替换成以下逻辑，核心是建立远程连接并插入数据：

import mysql.connector
from time import sleep
import datetime

# 替换成你的传感器读取函数
def get_sensor_data():
    # 示例：读取温湿度，实际改成你的传感器驱动逻辑
    temp = 24.7  # 比如从DHT11/DHT22读取
    humi = 58.3
    current_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    return {"temp": temp, "humi": humi, "time": current_time}

# 远程MySQL配置，替换成你的笔记本IP、用户名、密码
DB_CONFIG = {
    "host": "192.168.1.100",
    "user": "pi_sensor_user",
    "password": "your_strong_password",
    "database": "sensor_db"
}

# 初始化数据表（只需要执行一次）
def init_sensor_table():
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        # 创建存储传感器数据的表，根据你的数据字段调整
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS sensor_data (
                id INT AUTO_INCREMENT PRIMARY KEY,
                temperature DECIMAL(4,1),
                humidity DECIMAL(4,1),
                capture_time DATETIME NOT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """)
        conn.commit()
        print("数据表初始化完成")
    except Exception as e:
        print(f"初始化表出错: {str(e)}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

# 写入数据到远程MySQL
def save_to_mysql(data):
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        # 插入数据的SQL语句
        insert_sql = """
            INSERT INTO sensor_data (temperature, humidity, capture_time)
            VALUES (%s, %s, %s);
        """
        cursor.execute(insert_sql, (data["temp"], data["humi"], data["time"]))
        conn.commit()
        print(f"数据已写入: {data}")
    except Exception as e:
        print(f"写入数据失败: {str(e)}")
        # 可选：如果断网，临时存到本地文件，之后再补传
        with open("/home/pi/sensor_backup.txt", "a") as f:
            f.write(f"{data['time']},{data['temp']},{data['humi']}\n")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

# 主循环
if __name__ == "__main__":
    init_sensor_table()
    while True:
        sensor_data = get_sensor_data()
        save_to_mysql(sensor_data)
        sleep(30)  # 每30秒采集一次，根据需求调整

四、排坑指南 #

• 网络连通性问题：先在树莓派上ping笔记本的IP，比如ping 192.168.1.100，如果不通，检查两台设备是否在同一个WiFi/局域网，笔记本是否开了防火墙
• 防火墙拦截：笔记本要允许3306端口（MySQL默认端口）的入站连接。Ubuntu用sudo ufw allow 3306，Windows在防火墙高级设置里添加允许3306端口的规则
• 权限问题：如果提示“Access denied”，检查MySQL用户的IP范围是否正确，或者直接用'pi_sensor_user'@'%'测试（但不要长期用，不安全）
• 数据丢失风险：建议保留代码里的本地缓存逻辑，避免临时断网导致数据丢失

优化建议 #

• 给树莓派设置固定局域网IP，避免DHCP分配的IP变动导致连接失败
• 定期清理MySQL里的旧数据，比如用DELETE FROM sensor_data WHERE capture_time < DATE_SUB(NOW(), INTERVAL 30 DAY);删除30天前的数据
• 高频采集场景可以用连接池代替每次创建新连接，提升写入效率

内容的提问来源于stack exchange，提问作者Mayank Jain