数据导入导出

引言

提出问题

在使用MySQL过程中，经常需要：

如何备份数据库？
如何将Excel数据导入MySQL？
如何将MySQL数据导出为Excel或CSV？
如何迁移数据到另一台服务器？
测试环境如何快速导入生产数据？

这就是数据导入导出（Import/Export）的核心技能！

mysqldump：数据备份工具

基础语法

mysqldump [options] database [tables] > backup.sql

备份单个数据库

# 备份整个数据库
mysqldump -u root -p mydb > mydb_backup.sql

# 执行后，输入密码
Enter password: ****

# 查看备份文件
ls -lh mydb_backup.sql
# -rw-r--r--  1 user  staff   1.2M Jan 15 10:30 mydb_backup.sql

备份单张表

# 备份指定表
mysqldump -u root -p mydb users > users_backup.sql

# 备份多张表
mysqldump -u root -p mydb users orders > tables_backup.sql

备份多个数据库

# 备份多个数据库
mysqldump -u root -p --databases db1 db2 db3 > multi_db_backup.sql

备份所有数据库

# 备份所有数据库（包括系统库）
mysqldump -u root -p --all-databases > all_databases_backup.sql

常用选项

# 完整备份命令（推荐）
mysqldump -u root -p \
  --single-transaction \  # 一致性快照（InnoDB）
  --routines \            # 包含存储过程和函数
  --triggers \            # 包含触发器
  --events \              # 包含事件
  --default-character-set=utf8mb4 \  # 字符集
  mydb > mydb_full_backup.sql

选项	作用
`--single-transaction`	不锁表，适合InnoDB
`--lock-tables`	锁表备份，适合MyISAM
`--no-data`	只备份结构，不备份数据
`--no-create-info`	只备份数据，不备份结构
`--where='条件'`	按条件备份数据

只备份表结构

# 只备份表结构（DDL）
mysqldump -u root -p --no-data mydb > mydb_schema.sql

只备份数据

# 只备份数据（不含表结构）
mysqldump -u root -p --no-create-info mydb > mydb_data.sql

按条件备份

# 备份最近30天的订单
mysqldump -u root -p mydb orders \
  --where="created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" \
  > orders_recent.sql

数据恢复（导入）

恢复数据库

# 方式1：命令行导入
mysql -u root -p mydb < mydb_backup.sql

# 方式2：登录后导入
mysql -u root -p
mysql> USE mydb;
mysql> SOURCE /path/to/mydb_backup.sql;

创建数据库后恢复

# 如果数据库不存在，先创建
mysql -u root -p -e "CREATE DATABASE mydb CHARACTER SET utf8mb4;"

# 再导入数据
mysql -u root -p mydb < mydb_backup.sql

恢复单张表

# 恢复单张表
mysql -u root -p mydb < users_backup.sql

进度显示

# 使用pv工具显示进度
pv mydb_backup.sql | mysql -u root -p mydb

# 输出示例：
# 1.2GB 0:05:30 [3.6MB/s] [=========>         ] 45% ETA 0:06:45

CSV/TXT文件导入导出

导出为CSV

-- 导出为CSV文件
SELECT * FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 执行结果：
Query OK, 1000 rows affected (0.02 sec)

注意事项：

导出路径必须在secure_file_priv指定的目录内
查看允许的导出路径：

SHOW VARIABLES LIKE 'secure_file_priv';
-- Value: /var/lib/mysql-files/

导入CSV文件

-- 从CSV导入数据
LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;  -- 忽略标题行

-- 执行结果：
Query OK, 1000 rows affected (0.03 sec)
Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0

指定列导入

-- 只导入指定列
LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(username, email, age);  -- 只导入这3列

导入时处理数据

-- 导入时转换数据
LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(username, email, @age)  -- @age是变量
SET age = IF(@age > 0, @age, 18);  -- 年龄为0时默认18

Excel数据导入

方式1：先转为CSV

在Excel中，另存为CSV格式
使用LOAD DATA INFILE导入

方式2：使用Navicat等工具

打开Navicat
右键表 → 导入向导
选择Excel文件
映射字段
开始导入

方式3：使用Python脚本

import pandas as pd
import pymysql

# 读取Excel
df = pd.read_excel('users.xlsx')

# 连接MySQL
conn = pymysql.connect(host='localhost', user='root', password='password', database='mydb')

# 导入数据
df.to_sql('users', conn, if_exists='append', index=False)

conn.close()

跨服务器数据迁移

方式1：导出后导入

# 步骤1：在服务器A导出
mysqldump -u root -p -h server_a mydb > mydb.sql

# 步骤2：传输到服务器B
scp mydb.sql user@server_b:/tmp/

# 步骤3：在服务器B导入
mysql -u root -p -h server_b mydb < /tmp/mydb.sql

方式2：管道直接传输

# 一条命令完成迁移（推荐）
mysqldump -u root -p -h server_a mydb | mysql -u root -p -h server_b mydb

# 压缩传输（节省带宽）
mysqldump -u root -p -h server_a mydb | gzip | ssh user@server_b "gunzip | mysql -u root -p mydb"

方式3：使用mydumper/myloader

# 安装mydumper（多线程备份工具）
apt install mydumper

# 导出（多线程，速度快）
mydumper -u root -p password -h server_a -B mydb -o /backup/mydb

# 导入
myloader -u root -p password -h server_b -B mydb -d /backup/mydb

定时备份脚本

自动备份脚本

#!/bin/bash
# mysql_backup.sh

# 配置
DB_USER="root"
DB_PASS="password"
DB_NAME="mydb"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 备份（压缩）
mysqldump -u $DB_USER -p$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  $DB_NAME | gzip > $BACKUP_FILE

# 检查备份是否成功
if [ $? -eq 0 ]; then
  echo "[$(date)] Backup successful: $BACKUP_FILE"
else
  echo "[$(date)] Backup failed!"
  exit 1
fi

# 删除7天前的备份
find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete

echo "[$(date)] Old backups cleaned up"

设置定时任务

# 编辑crontab
crontab -e

# 添加定时任务（每天凌晨2点备份）
0 2 * * * /root/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

大数据量导入优化

问题

导入百万级数据时，速度很慢。

优化方案

1. 临时禁用索引和约束

-- 导入前
ALTER TABLE users DISABLE KEYS;  -- 禁用非主键索引
SET FOREIGN_KEY_CHECKS = 0;  -- 禁用外键检查
SET UNIQUE_CHECKS = 0;  -- 禁用唯一性检查

-- 导入数据
LOAD DATA INFILE '/path/to/users.csv' INTO TABLE users ...;

-- 导入后
ALTER TABLE users ENABLE KEYS;  -- 启用索引
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

2. 批量插入

-- ❌ 慢：单条插入
INSERT INTO users VALUES (1, 'user1');
INSERT INTO users VALUES (2, 'user2');
INSERT INTO users VALUES (3, 'user3');

-- ✅ 快：批量插入（快10倍+）
INSERT INTO users VALUES
  (1, 'user1'),
  (2, 'user2'),
  (3, 'user3');

3. 调整缓冲区大小

-- 增大批量插入缓冲区
SET bulk_insert_buffer_size = 256 * 1024 * 1024;  -- 256MB

-- 增大InnoDB缓冲池
SET GLOBAL innodb_buffer_pool_size = 2 * 1024 * 1024 * 1024;  -- 2GB

4. 使用LOAD DATA而非INSERT

# LOAD DATA比INSERT快20倍+
LOAD DATA INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

实战案例：电商数据迁移

需求

将测试环境的订单数据（100万条）迁移到生产环境。

方案

# 1. 导出测试环境数据（只导出最近30天）
mysqldump -u root -p test_db orders \
  --where="created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" \
  --single-transaction \
  > orders_recent.sql

# 2. 压缩（节省传输时间）
gzip orders_recent.sql

# 3. 传输到生产服务器
scp orders_recent.sql.gz user@prod_server:/tmp/

# 4. 在生产服务器解压
ssh user@prod_server
cd /tmp
gunzip orders_recent.sql.gz

# 5. 导入前准备（提升性能）
mysql -u root -p prod_db << EOF
ALTER TABLE orders DISABLE KEYS;
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
EOF

# 6. 导入数据
mysql -u root -p prod_db < orders_recent.sql

# 7. 导入后恢复
mysql -u root -p prod_db << EOF
ALTER TABLE orders ENABLE KEYS;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;
EOF

# 8. 验证数据
mysql -u root -p prod_db -e "SELECT COUNT(*) FROM orders;"

最佳实践

备份策略

备份类型	频率	保留时间	工具
全量备份	每周	4周	mysqldump
增量备份	每天	7天	binlog
实时备份	实时	-	主从复制

命名规范

# 推荐命名格式
database_YYYYMMDD_HHMMSS.sql.gz

# 示例
mydb_20240115_020000.sql.gz
mydb_20240116_020000.sql.gz

安全建议

不要在命令行明文写密码：

# ❌ 不安全
mysqldump -u root -pPassword123 mydb > backup.sql

# ✅ 安全：使用配置文件
# ~/.my.cnf
[client]
user=root
password=Password123

# 命令行不带密码
mysqldump mydb > backup.sql

备份文件加密：

# 加密备份
mysqldump -u root -p mydb | gzip | openssl enc -aes-256-cbc -salt -out backup.sql.gz.enc

# 解密恢复
openssl enc -aes-256-cbc -d -in backup.sql.gz.enc | gunzip | mysql -u root -p mydb

异地备份：

# 自动上传到云存储
mysqldump -u root -p mydb | gzip | aws s3 cp - s3://my-backup-bucket/mydb_$(date +%Y%m%d).sql.gz

常见问题（FAQ）

Q1：mysqldump备份会锁表吗？

A：

InnoDB + --single-transaction：不锁表
MyISAM或不用--single-transaction：会锁表

Q2：如何恢复单张表？

A：

# 从完整备份中提取单表
sed -n '/CREATE TABLE `users`/,/UNLOCK TABLES/p' mydb_backup.sql > users.sql
mysql -u root -p mydb < users.sql

Q3：LOAD DATA报错"secure_file_priv"？

A：

-- 查看允许的目录
SHOW VARIABLES LIKE 'secure_file_priv';

-- 将文件放到该目录，或修改配置文件：
[mysqld]
secure_file_priv = /path/to/import/

Q4：导入大文件报错"max_allowed_packet"？

A：

-- 增大数据包大小
SET GLOBAL max_allowed_packet = 1024 * 1024 * 1024;  -- 1GB

总结

核心要点

mysqldump：
- 备份：mysqldump -u root -p mydb > backup.sql
- 恢复：mysql -u root -p mydb < backup.sql
- 选项：--single-transaction（不锁表）
CSV导入导出：
- 导出：SELECT ... INTO OUTFILE
- 导入：LOAD DATA INFILE
性能优化：
- 批量插入代替单条插入
- 临时禁用索引和约束
- 使用LOAD DATA
定时备份：
- cron定时任务
- 自动清理旧备份
- 异地存储

记忆口诀

备份三步走：导、压、传

导：mysqldump导出数据
压：gzip压缩文件
传：scp传输到远程

下一步学习

💡 本文是 “MySQL从入门到精通” 系列的第 9 篇（共86篇）。
📚 建议编写一个自动备份脚本并设置定时任务。

引言#

提出问题#

mysqldump：数据备份工具#

基础语法#

备份单个数据库#

备份单张表#

备份多个数据库#

备份所有数据库#

常用选项#

只备份表结构#

只备份数据#

按条件备份#

数据恢复（导入）#

恢复数据库#

创建数据库后恢复#

恢复单张表#

进度显示#

CSV/TXT文件导入导出#

导出为CSV#

导入CSV文件#

指定列导入#

导入时处理数据#

Excel数据导入#

方式1：先转为CSV#

方式2：使用Navicat等工具#

方式3：使用Python脚本#

跨服务器数据迁移#

方式1：导出后导入#

方式2：管道直接传输#

方式3：使用mydumper/myloader#

定时备份脚本#

自动备份脚本#

设置定时任务#

大数据量导入优化#

问题#

优化方案#

1. 临时禁用索引和约束#

2. 批量插入#

3. 调整缓冲区大小#

4. 使用LOAD DATA而非INSERT#

实战案例：电商数据迁移#

需求#

方案#

最佳实践#

备份策略#

命名规范#

安全建议#

常见问题（FAQ）#

总结#

核心要点#

记忆口诀#

下一步学习#

参考资料#

引言