Python与MySQL数据备份增删改查实战指南

MySQL 数据备份策略与自动化实现

第1步：明确备份类型并执行逻辑导出

备份主要分为物理备份（直接复制底层文件，适合超大型库但无法跨平台）和逻辑备份（导出 SQL 建表与插入语句，适合中小型库且兼容异构环境）。日常运维与迁移多采用 mysqldump 进行逻辑备份。

# 基础语法：mysqldump -h 主机 -u用户名 -p密码 数据库名 > 备份文件.sql
# 单库备份（支持指定特定表）
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-tables.sql

# 多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql > multi_dbs.sql

# 全库备份
mysqldump -uroot -p123 --all-databases > all_dbs.sql

第2步：验证数据恢复与点时间修复

逻辑备份可通过两种主流方式还原。恢复至空库或覆盖现有数据前，请务必确认目标环境状态，避免误覆盖重要业务数据。

# 方法一：命令行重定向导入
mysql -uroot -p123 < all_dbs.sql

# 方法二：MySQL 客户端内部执行
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;
mysql> source /root/db1.sql

若需精准恢复到误操作前的瞬间（如误删表或误更新），需结合全备与 Binlog 日志。假设最后全备时间为 2014-02-13_all.sql，损坏发生前最后生成的 binlog 为 last_bin.log：

# 恢复全备
mysql> SET SQL_LOG_BIN=0;
mysql> source /backup/2014-02-13_all.sql
mysql> source /backup/last_bin.log

完成判断：执行 SHOW TABLES; 或核对核心数据行，确保业务数据完整且无异常报错。恢复期间 SET SQL_LOG_BIN=0 可防止历史操作被重复写入主从链路日志。

第3步：配置自动化定时备份

生产环境建议通过 Shell 脚本结合 cron 实现每日自动归档，并清理旧文件以节省磁盘空间。

#!/bin/bash
back_dir=/backup
back_file=$(date +%F)_all.sql
user=root
pass=your_secure_password

[ ! -d "$back_dir" ] && mkdir -p "$back_dir"

# 备份全库并保留事件计划
mysqldump -u${user} -p${pass} --events --all-databases > ${back_dir}/${back_file}
# 截断日志生成新的 binlog，便于增量恢复定位起点
mysql -u${user} -p${pass} -e 'FLUSH LOGS'

# 仅保留最近7天的备份文件
cd $back_dir
find . -mtime +7 -type f -name '*.sql' -exec rm -rf {} \;

赋予执行权限后配置定时任务：

chmod +x /path/to/mysql_back.sh
echo "0 2 * * * /path/to/mysql_back.sh" | crontab -

表数据导出导入与跨库迁移

第4步：处理文件导出限制并执行导入

使用 SELECT ... INTO OUTFILE 可将查询结果转为 CSV 等文本格式。需注意 secure_file_priv 的安全策略，默认可能禁止向非指定目录写入。

SELECT * FROM school.student1
INTO OUTFILE '/tmp/student1.txt'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

若遇到 Variable 'secure_file_priv' is a read only variable 错误，需在 my.cnf 的 [mysqld] 段追加有效路径（如 secure_file_priv='/tmp/'），重启服务后生效。对应导入使用 LOAD DATA INFILE。

第5步：实施跨服务器数据库迁移

同版本迁移可直接管道传输，保持结构完整：

mysqldump -h源IP -uroot -p123 --databases db1 | mysql -h目标IP -uroot -p456

Python 连接 MySQL 执行增删改查

安装依赖模块后，即可通过标准化接口管理数据库交互。推荐使用可视化客户端辅助调试建表语句，但生产脚本应严格遵循代码规范。

第6步：建立连接、初始化游标并处理返回结果

连接对象负责网络会话，游标负责 SQL 执行与结果集缓冲。默认返回元组，也可设为字典模式提升字段读取体验。

import pymysql

# 建立连接
conn = pymysql.connect(host='localhost', user='root', password='123', database='db_name', charset='utf8')
cursor = conn.cursor() # 默认 cursor=pymysql.cursors.DictCursor 可改为字典返回

sql = 'SELECT * FROM userinfo WHERE status=%s'
rows = cursor.execute(sql, ['active'])
print(f'匹配记录数: {rows}')

res1 = cursor.fetchone()       # 取下一行
res2 = cursor.fetchmany(2)     # 取接下来两行
res3 = cursor.fetchall()       # 取剩余所有行
print(res1, res2, res3)

插入数据后获取自增主键 ID，可在 execute 后立即调用：

insert_sql = "INSERT INTO users(name) VALUES(%s)"
cursor.execute(insert_sql, ("new_user",))
last_id = cursor.lastrowid
print(f'新记录ID: {last_id}')

第7步：安全执行增删改操作

涉及数据变更的操作必须显式提交事务。批量插入使用 executemany 可显著提升执行效率。

batch_sql = "INSERT INTO users(name, password) VALUES(%s, %s)"
data_list = [("alice", "pwd1"), ("bob", "pwd2"), ("charlie", "pwd3")]
affected_rows = cursor.executemany(batch_sql, data_list)
print(f'影响行数: {affected_rows}')

conn.commit()  # 必须执行事务提交，否则数据不会持久化
cursor.close()
conn.close()

安全防护与最佳实践

动态拼接 SQL 极易引发注入攻击（例如 ' OR 1=1 -- 绕过认证）。pymysql 原生支持参数化查询，底层会自动转义特殊字符，彻底阻断注入风险。

# 错误写法（存在注入风险）
# sql = f"SELECT * FROM users WHERE name='{username}' AND pass='{passwd}'"
# cursor.execute(sql)

# 正确写法（安全）
sql = "SELECT * FROM users WHERE name=%s AND pass=%s"
cursor.execute(sql, (username, passwd))

以上就是 Python 配合 MySQL 进行数据备份、迁移及安全增删改查的详细内容，更多关于 MySQL 运维管理与 Python 数据库交互的资料请关注本站其它相关文章！