MySQL数据备份与恢复之mysqldump和source命令

一、概述

  • 导入操作：支持本地/远程SQL文件快速导入，适配不同环境（同机 / 跨服务器），解决字符集一致性、大文件超时、权限适配等常见问题，确保数据无缝迁移至目标数据库。
  • 导出操作：基于mysqldump工具实现灵活备份，支持全库/多库/单表、结构/数据分离、条件过滤等多样化需求，同时覆盖存储过程、触发器、事件等数据库对象，适配日常备份、主从复制初始化、数据分析等场景。

二、数据库导入操作

2.1 本地SQL文件导入

-- 1. 切换到目标数据库（若SQL文件不含CREATE DATABASE语句，需先手动创建）
CREATE DATABASE IF NOT EXISTS dbname DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE dbname;

-- 2. 导入本地SQL文件（推荐绝对路径，避免中文/空格路径）
SOURCE /path/to/your/dbname.sql; -- 注意：路径不含空格时直接使用
-- 若路径含空格（Windows 环境），需用双引号包裹
SOURCE "C:/Program Files/MySQL/backup/dbname.sql";

2.2 远程数据库导入

# 直接通过命令行指定数据库导入，无需先登录MySQL
mysql -h 192.168.1.100 -u root -p dbname < /path/to/dbname.sql
# 说明：
# -h ：远程数据库IP
# 若SQL文件含CREATE DATABASE语句，可省略dbname参数（需加-B避免报错）
mysql -h 192.168.1.100 -u root -p -B < /path/to/dbname.sql

2.3 导入注意事项

字符集一致性：导入前确保目标数据库字符集与SQL文件一致，避免乱码：

-- 查看目标库字符集
SELECT DEFAULT_CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'dbname';
-- 若不一致，导入时指定字符集
mysql -h IP -u root -p --default-character-set=utf8mb4 dbname < dbname.sql

权限要求：执行导入的用户需具备CREATE TABLE、INSERT等权限（root权限最稳妥）。
大文件导入优化：若SQL文件超过1G，建议关闭日志临时加速：

SET SQL_LOG_BIN = 0; -- 导入完成后恢复：SET SQL_LOG_BIN = 1;

三、数据库导出操作

3.1 核心参数

  • -d/--no-data: 只导出表结构，不导出数据
  • -t/--no-create-info: 只导出数据，不导出CREATE TABLE语句
  • -n/--no-create-db: 不导出CREATE DATABASE语句（多库导出时常用）
  • -B/--databases: 导出多个数据库（自动添加CREATE DATABASE + USE语句）
  • -R/--routines: 导出存储过程、自定义函数
  • -E/--events: 导出事件调度器
  • --triggers: 导出触发器（默认开启，--skip-triggers关闭）
  • --single-transaction: 非锁定导出（InnoDB专用，保证数据一致性，替代-x）
  • --master-data=2: 记录binlog位置（主从复制场景必备，注释形式输出，不影响导入）
  • --ignore-table: 排除指定表（支持多表排除，需重复该参数）
  • --where/-w: 按条件导出数据（类似SELECT WHERE子句）
  • --default-character-set: 指定导出字符集（默认utf8，推荐utf8mb4兼容emoji）

3.2 示例

3.2.1 全库/多库

导出单个数据库（结构+数据+触发器+存储过程）

# 基础版（含结构+数据+触发器，默认不含存储过程/事件）
mysqldump -u root -p dbname > dbname_full.sql
# 完整版（含结构+数据+存储过程+事件+触发器，生产推荐）
mysqldump -u root -p -R -E dbname > dbname_full_with_routines.sql

导出多个数据库（结构+数据）

# 自动添加 CREATE DATABASE + USE 语句，导入时可直接执行
mysqldump -u root -p -B dbname1 dbname2 dbname3 > multi_db_full.sql
# 只导出多库结构，不导出数据
mysqldump -u root -p -B -d dbname1 dbname2 > multi_db_struct.sql

导出所有数据库（管理员备份场景）

# 导出所有库（含系统库如 mysql、information_schema）
mysqldump -u root -p -A -R -E --master-data=2 > all_db_backup.sql
# 排除系统库，只导出业务库（通过--ignore-table排除）
mysqldump -u root -p -A -R -E --ignore-table=mysql.user 
              --ignore-table=information_schema.% > business_db_backup.sql

3.2.2 单表/多表

导出单个表（结构+数据）

# 简化语法：单个表无需 --tables（原文档第11条可简化）
mysqldump -u root -p dbname tablename > tablename_full.sql
# 只导出表结构
mysqldump -u root -p -d dbname tablename > tablename_struct.sql
# 只导出表数据
mysqldump -u root -p -t dbname tablename > tablename_data.sql

导出多个表（结构+数据）

# 推荐语法：无需-B（-B是多库参数，单库多表无需加）
mysqldump -u root -p dbname table1 table2 table3 > multi_table_full.sql
# 只导出多表结构
mysqldump -u root -p -d dbname table1 table2 > multi_table_struct.sql
# 只导出多表数据（按条件过滤，例如导出2024年以后的数据）
mysqldump -u root -p -t dbname table1 table2 -w "create_time >= '2024-01-01'" > multi_table_data_filtered.sql

3.2.3 特殊场景

导出大表（分块导出，避免内存溢出）

# 按ID分块导出（例如每10万条导出一个文件）
mysqldump -u root -p dbname big_table -w "id between 1 and 100000" > big_table_1.sql
mysqldump -u root -p dbname big_table -w "id between 100001 and 200000" > big_table_2.sql

导出为CSV格式（便于Excel分析）

# 方法1：通过mysqldump --tab生成CSV（需指定目录，且MySQL服务有权限写入）
mysqldump -u root -p -T /tmp/db_backup dbname tablename --fields-terminated-by=',' 
                   --fields-enclosed-by='"' --lines-terminated-by='\n'
# 生成两个文件：tablename.sql（结构）+ tablename.txt（CSV数据）

# 方法2：直接导出为纯CSV（无结构）
mysql -u root -p -e "SELECT * FROM dbname.tablename INTO OUTFILE '/tmp/tablename.csv' 
         FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

主从复制场景导出（记录binlog位置）

# --master-data=2：注释形式记录binlog文件和偏移量，不影响导入
# --single-transaction：非锁定导出（InnoDB表），避免影响业务
mysqldump -u root -p -B dbname -R -E --master-data=2 --single-transaction > slave_backup.sql

3.2.4 存储过程&函数&事件

只导出存储过程和函数（不含结构/数据）

mysqldump -u root -p -R -ntd dbname > routines.sql
# 说明：-n（无CREATE DB）、-t（无CREATE TABLE）、-d（无数据）组合，只保留存储过程/函数

只导出事件（不含其他内容）

mysqldump -u root -p -E -ntd dbname > events.sql

导出结构+存储过程+事件（不含数据）

mysqldump -u root -p -d -R -E dbname > struct_routines_events.sql

排除触发器导出（默认导出，需手动关闭）

mysqldump -u root -p --skip-triggers dbname > no_triggers_backup.sql

3.3 高级参数详解

--single-transaction​

  • 适用场景：InnoDB表备份（非锁定）​
  • 示例命令：mysqldump -u root -p --single-transaction dbname > no_lock_backup.sql​

--lock-all-tables/-x​

  • 适用场景：MyISAM表备份（全局读锁，保证一致性）​
  • 示例命令：mysqldump -u root -p -x dbname > myisam_backup.sql​

--master-data=2​

  • 适用场景：主从复制备份（记录binlog位置）​
  • 示例命令：mysqldump -u root -p -B dbname --master-data=2 > slave_init.sql​

--ignore-table​

  • 适用场景：排除敏感表（如用户密码表）​
  • 示例命令：mysqldump -u root -p dbname --ignore-table=dbname.user_info > backup.sql​

--complete-insert/-c​

  • 适用场景：导出完整INSERT语句（含列名，便于调试）​
  • 示例命令：mysqldump -u root -p -c dbname tablename > complete_insert.sql​

--extended-insert/-e​

  • 适用场景：批量INSERT（减小文件体积，加速导入）​
  • 示例命令：mysqldump -u root -p -e dbname > compact_backup.sql（默认开启）​

--hex-blob​

  • 适用场景：导出二进制字段（BLOB/TEXT类型，避免乱码）​
  • 示例命令：mysqldump -u root -p --hex-blob dbname > blob_backup.sql​

--where/-w​

  • 适用场景：条件导出（如导出近30天数据）​
  • 示例命令：mysqldump -u root -p -t dbname orders -w "create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)" > recent_orders.sql​

--max_allowed_packet​

  • 适用场景：导出大字段（如LONGTEXT，避免包溢出）​
  • 示例命令：mysqldump -u root -p --max_allowed_packet=4G dbname > big_field_backup.sql

四、实战优化技巧

4.1 导出文件压缩

# 导出并通过gzip压缩（推荐，压缩率70%+）
mysqldump -u root -p -R -E dbname | gzip > dbname_backup.sql.gz
# 解压后导入
gzip -d dbname_backup.sql.gz && mysql -u root -p dbname < dbname_backup.sql

# Windows环境（用7z压缩）
mysqldump -u root -p dbname > dbname_backup.sql && 7z a dbname_backup.sql.7z dbname_backup.sql

4.2 大数据库备份优化

  • 禁用索引构建（导入后重建，加速导入）：
# 导出时正常导出，导入前执行：
ALTER TABLE tablename DISABLE KEYS; -- 禁用非主键索引
# 导入完成后执行：
ALTER TABLE tablename ENABLE KEYS; -- 批量重建索引（比逐条插入快10倍+）
  • 分库分表导出：按业务模块拆分备份（如用户库、订单库分开导出），避免单文件过大。

4.3 备份自动化

# Linux下通过crontab定时备份（每天凌晨2点执行）
# 1. 创建备份脚本backup.sh
#!/bin/bash
BACKUP_DIR=/data/mysql/backup
DATE=$(date +%Y%m%d_%H%M%S)
mysqldump -u root -p'your_password' -R -E --single-transaction dbname | gzip > $BACKUP_DIR/dbname_$DATE.sql.gz
# 删除7天前的备份（避免磁盘占满）
find $BACKUP_DIR -name "dbname_*.sql.gz" -mtime +7 -delete

# 2. 给脚本加执行权限
chmod +x backup.sh

# 3. 添加到crontab
crontab -e
# 加入以下内容（每天2:00执行）
0 2 * * * /data/mysql/backup/backup.sh

4.4 备份校验

# 方法1：检查SQL文件语法合法性
mysql -u root -p -e "SOURCE /path/to/backup.sql" 2> error.log
# 若error.log无报错，说明语法正常

# 方法2：通过md5校验文件完整性（备份后记录md5值）
md5sum /path/to/backup.sql.gz > backup.md5
# 恢复前校验
md5sum -c backup.md5

五、常见问题排查

5.1 Access denied

导出时提示 Access denied; you need (at least one of) the PROCESS privilege(s) for this operation：

  • 原因：导出存储过程/函数时，用户缺少PROCESS权限。
  • 解决：给用户授权或用root账号导出：
GRANT PROCESS ON *.* TO 'your_user'@'localhost';

5.2 Duplicate key entry

导入时提示Duplicate key entry：

  • 原因：目标表已有数据，与导入数据主键冲突。
  • 解决：导入前清空表或使用--replace/--insert-ignore导出：
# 导出时添加--replace（替换冲突数据）
mysqldump -u root -p --replace dbname > backup.sql
# 或--insert-ignore（忽略冲突数据）
mysqldump -u root -p --insert-ignore dbname > backup.sql

5.3 大文件导入超时

  • 解决：修改MySQL配置文件my.cnf（Linux）/my.ini（Windows）：
[mysqld]
max_allowed_packet = 4G
wait_timeout = 3600 # 超时时间1小时

重启MySQL后重新导入。

六、总结

需求场景 推荐命令
本地单库全量备份（结构+数据+函数） mysqldump -u root -p -R -E dbname > dbname_full.sql
远程单表结构备份 mysqldump -h IP -u root -p -d dbname tablename > tablename_struct.sql
条件导出数据（近7天订单） mysqldump -u root -p -t dbname orders -w "create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)" > recent_orders.sql
主从复制初始化备份 mysqldump -u root -p -B dbname -R -E --master-data=2 --single-transaction > slave_init.sql
大文件压缩备份 mysqldump -u root -p dbname gzip > dbname_backup.sql.gz
本地SQL文件导入 mysql -u root -p --default-character-set=utf8mb4 dbname < dbname.sql

通过以上优化，该指南覆盖了从基础操作到生产级备份的全场景，补充了原文档缺失的避坑技巧、高级参数用法和自动化方案，更适合实际开发与运维使用。

posted @ 2022-04-22 18:31  夏尔_717  阅读(627)  评论(0)    收藏  举报