Mysql数据库备份与恢复笔记

MySQL 数据库备份与恢复 完整笔记这份笔记涵盖生产常用、简单易操作的备份 / 恢复方案包含命令行、可视化工具、自动备份直接复制即可使用。一、核心概念备份把数据库数据导出成文件SQL 文件 / 二进制文件恢复把备份文件重新导入 MySQL还原数据常用备份类型逻辑备份mysqldump导出 SQL 语句通用、跨版本、跨平台物理备份直接拷贝数据文件速度快适合大数据量自动定时备份生产环境必备防止数据丢失二、逻辑备份最常用mysqldump1. 备份全部数据库bash运行mysqldump -u用户名 -p --all-databases 备份文件路径.sql示例bash运行mysqldump -uroot -p --all-databases /backup/all_db_20260330.sql2. 备份单个数据库bash运行mysqldump -u用户名 -p 数据库名 备份文件.sql示例bash运行mysqldump -uroot -p testdb /backup/testdb_20260330.sql3. 备份单个表bash运行mysqldump -u用户名 -p 数据库名 表名 表备份文件.sql4. 只备份结构不备份数据bash运行mysqldump -u用户名 -p -d 数据库名 结构备份.sql5. 生产推荐备份命令带字符集 锁表优化bash运行mysqldump -u用户名 -p --default-character-setutf8mb4 --single-transaction --databases 数据库名 备份文件.sql参数说明--default-character-setutf8mb4指定字符集防止乱码--single-transactionInnoDB 引擎热备份不锁表--databases备份时包含CREATE DATABASE语句恢复更方便三、逻辑恢复方法 1系统命令行直接恢复最常用bash运行mysql -u用户名 -p 目标数据库名 备份文件.sql示例bash运行mysql -uroot -p testdb /backup/testdb_20260330.sql方法 2MySQL 内部执行恢复登录 MySQLbash运行mysql -uroot -p选择数据库sqluse testdb;执行恢复sqlsource /backup/testdb_20260330.sql;四、物理备份大数据量专用1. 停止 MySQL 服务bash运行# Linux systemctl stop mysqld # Windows net stop mysql2. 直接拷贝数据目录MySQL 默认数据目录Linux/var/lib/mysql/WindowsC:\ProgramData\MySQL\MySQL Server X.X\Data\直接复制整个 Data 文件夹即可完成备份。3. 物理恢复停止 MySQL → 替换 Data 目录 → 启动 MySQL五、可视化工具备份 / 恢复Navicat / DBeaver备份右键数据库 → 转储 SQL 文件 → 结构和数据选择保存路径 → 开始导出恢复右键目标库 → 运行 SQL 文件选择备份文件 → 开始导入六、自动定时备份Linux 生产必备1. 创建备份脚本mysql_backup.shbash运行#!/bin/bash # 备份目录 BACKUP_DIR/backup # 日期 DATE$(date %Y%m%d_%H%M%S) # 备份 mysqldump -uroot -p密码 --default-character-setutf8mb4 --single-transaction --databases testdb $BACKUP_DIR/testdb_$DATE.sql # 保留7天备份自动删除旧文件 find $BACKUP_DIR -name testdb_*.sql -mtime 7 -delete2. 添加执行权限bash运行chmod x /backup/mysql_backup.sh3. 设置定时任务crontabbash运行crontab -e添加plaintext# 每天凌晨2点自动备份 0 2 * * * /backup/mysql_backup.sh七、备份与恢复注意事项恢复前务必备份当前数据防止覆盖备份文件定期校验确认可正常恢复生产环境优先使用--single-transaction热备份不影响业务备份文件要异地存储云盘 / 另一台服务器字符集统一使用utf8mb4避免乱码恢复时关闭外键检查防止报错sqlSET FOREIGN_KEY_CHECKS0; source 备份文件.sql; SET FOREIGN_KEY_CHECKS1;八、常用命令速查表表格操作命令全库备份mysqldump -u root -p --all-databases all.sql单库备份mysqldump -u root -p dbname db.sql单库恢复mysql -u root -p dbname db.sql热备份InnoDBmysqldump -u root -p --single-transaction dbname db.sql定时备份crontab shell 脚本总结小型数据库 / 开发环境使用mysqldump逻辑备份简单安全大型生产环境物理备份 自动定时备份 异地存储恢复前必做备份现有数据、关闭外键检查核心原则能备份、能恢复、定期验证