MySQL mysqldump工具使用

mysqldump 是 MySQL 最常用的逻辑备份工具，通过导出 SQL 语句实现数据备份。

基本语法

mysqldump [options] db_name [tables] > backup.sql
mysqldump [options] --databases db1 db2 > backup.sql
mysqldump [options] --all-databases > backup.sql

常用参数

备份示例

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

# 备份单个表
mysqldump -u root -p mydb users > users.sql

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

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

# 备份所有数据库
mysqldump -u root -p --all-databases > all.sql

一致性备份参数

# InnoDB 单事务一致性备份（推荐）
mysqldump -u root -p --single-transaction mydb > mydb.sql

# 记录binlog位置（用于增量恢复）
mysqldump -u root -p --single-transaction --master-data=2 mydb > mydb.sql
--master-data=1: 在备份中写入 CHANGE MASTER TO
--master-data=2: 写注释形式的 CHANGE MASTER TO

# 锁表备份（MyISAM使用）
mysqldump -u root -p --lock-tables mydb > mydb.sql

备份对象选项

# 备份表结构不含数据
mysqldump -u root -p --no-data mydb > schema.sql

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

# 备份存储过程和函数
mysqldump -u root -p --routines --no-data mydb > routines.sql

# 备份触发器
mysqldump -u root -p --triggers --no-data mydb > triggers.sql

# 备份全部对象
mysqldump -u root -p --single-transaction \
  --routines --triggers --events mydb > full.sql

恢复数据

# 恢复数据库
mysql -u root -p mydb < mydb.sql

# 恢复所有数据库
mysql -u root -p < all.sql

# 在MySQL命令行恢复
mysql> USE mydb;
mysql> SOURCE /path/to/mydb.sql;

远程备份

# 远程主机备份
mysqldump -u root -p -h 192.168.1.100 -P 3306 mydb > mydb.sql

# 远程备份到本地
mysqldump -u root -p -h remote_host mydb | mysql -u root -p local_db

增强备份工具

# mydumper 多线程备份（更快）
mydumper -u root -p -B mydb -o /backup/mydb -t 4

# myloader 多线程恢复
myloader -u root -p -B mydb -d /backup/mydb -t 4

自动备份脚本

#!/bin/bash
# backup.sh
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"

mysqldump -u root -p'password' \
  --single-transaction \
  --routines --triggers \
  $DB_NAME > $BACKUP_DIR/$DB_NAME_$DATE.sql

# 压缩备份
gzip $BACKUP_DIR/$DB_NAME_$DATE.sql

# 删除30天前的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete

生产环境备份建议使用 --single-transaction 保证一致性。

要点总结

• mysqldump 是逻辑备份工具，导出SQL文件
• --single-transaction 实现 InnoDB 一致性备份
• --master-data 记录binlog位置便于增量恢复
• --routines --triggers --events 备份完整对象
• mydumper/myloader 多线程备份更高效