mysqldump
mysqldump 是MySQL官方提供的逻辑备份工具，用于将数据库结构和数据导出为SQL脚本文件。支持全库备份、单库备份、单表备份，以及选择性备份（仅结构/仅数据）。生成的SQL文件可通过mysql命令重新导入，常用于数据迁移、备份恢复等场景。
mysqldump默认不属与热备，需要增加--single-transaction参数适用于InnoDB支持热备份
语法格式
bash
mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases选项
| 选项 | 描述 |
|---|---|
| 基本连接选项 | |
--user=user_name, -u user_name | MySQL用户名 |
--password[=password], -p[password] | MySQL密码 |
--host=host_name, -h host_name | 连接的主机地址 |
--port=port_num, -P port_num | 连接端口号 |
--socket=path, -S path | 套接字文件路径 |
--ssl-mode=mode | SSL连接模式（DISABLED/PREFERRED/REQUIRED等） |
| 备份范围控制 | |
--all-databases, -A | 备份所有数据库 |
--databases, -B | 备份多个指定数据库 |
--ignore-table=db_name.tbl_name | 排除指定表 |
--tables | 覆盖--databases选项，只备份指定表 |
--where='where_condition', -w 'where_condition' | 按条件导出数据 |
| 输出内容控制 | |
--no-create-db, -n | 不输出CREATE DATABASE语句 |
--no-create-info, -t | 不输出表创建语句 |
--no-data, -d | 只导出表结构 |
--add-drop-database | 添加DROP DATABASE语句 |
--add-drop-table | 添加DROP TABLE语句（默认） |
--skip-add-drop-table | 不添加DROP TABLE语句 |
--replace | 使用REPLACE代替INSERT语句 |
--insert-ignore | 使用INSERT IGNORE语句 |
--skip-extended-insert | 禁用多行INSERT语法 |
--complete-insert, -c | 使用完整列名的INSERT语句 |
--compact | 精简输出格式 |
--create-options | 包含表创建选项 |
--quote-names, -Q | 用反引号引用标识符（默认） |
--skip-quote-names | 禁用标识符引用 |
| 对象类型控制 | |
--routines, -R | 包含存储过程和函数 |
--events, -E | 包含事件 |
--triggers | 包含触发器（默认） |
--skip-triggers | 不包含触发器 |
--tz-utc | 添加时区设置（默认） |
--skip-tz-utc | 不添加时区设置 |
| 性能优化选项 | |
--single-transaction | 使用事务保证一致性（InnoDB） |
--lock-tables, -l | 锁定所有表（MyISAM） |
--skip-lock-tables | 不锁定表 |
--quick, -q | 不缓存查询结果 |
--opt | 等同于--add-drop-table --add-locks --create-options --quick --extended-insert --lock-tables |
--skip-opt | 禁用--opt选项 |
| 字符集与编码 | |
--default-character-set=charset | 设置默认字符集 |
--set-charset | 添加SET NAMES语句（默认） |
--skip-set-charset | 不添加SET NAMES语句 |
--hex-blob | 十六进制导出BLOB类型 |
| 输出格式控制 | |
--tab=dir_name, -T dir_name | 生成分隔文本文件 |
--fields-terminated-by=string | 字段分隔符 |
--fields-enclosed-by=string | 字段包围符 |
--fields-optionally-enclosed-by=string | 字段可选包围符 |
--fields-escaped-by=string | 转义字符 |
--lines-terminated-by=string | 行结束符 |
| 其他实用选项 | |
--result-file=file_name, -r file_name | 指定输出文件 |
--verbose, -v | 详细模式 |
--debug-info, -# | 输出调试信息 |
--force, -f | 遇到错误继续执行 |
--max_allowed_packet=size | 最大数据包大小 |
--net_buffer_length=size | TCP/IP缓冲区大小 |
--flush-logs, -F | 备份前刷新日志 |
--delete-master-logs | 备份后删除二进制日志 |
--dump-date | 添加备份日期注释（默认） |
--skip-dump-date | 不添加日期注释 |
--compress, -C | 压缩客户端/服务器协议 |
--version, -V | 显示版本信息 |
--help, -? | 显示帮助信息 |
备份案例
bash
# 备份整个数据库（包含结构和数据）
mysqldump -u root -p123456 --databases wordpress > wordpress_backup.sql
# 仅备份表结构
mysqldump -u root -p --no-data --databases inventory > schema_only.sql
# 备份特定表的数据（带条件）
mysqldump -u root -p --where="create_time>'2023-01-01'" sales orders > recent_orders.sql
# 使用事务保证一致性备份
mysqldump -u root -p --single-transaction --quick --all-databases > full_backup.sql
# 压缩备份文件
mysqldump -u root -p --all-databases | gzip > all_dbs.sql.gz
# 完整备份（含存储过程/触发器）
mysqldump -u root -p --single-transaction --routines --triggers --all-databases > full_backup.sql
# 关键参数：
# --single-transaction # 对InnoDB启用一致性备份
# --master-data=2 # 记录binlog位置(用于主从.1:不注释binlog位置、2注释binlog位置)
# --flush-logs # 备份前刷新日志
# --skip-add-drop-table # 避免恢复时先删除表
# 增大网络缓冲区
mysqldump --net_buffer_length=1M ...
# 关闭外键检查
mysqldump --skip-add-drop-table --skip-add-locks --skip-disable-keys ...
# 设置锁等待超时
mysqldump --lock-wait-timeout=30 ...
# 减少内存使用（适合大表）
mysqldump --quick --skip-opt ...
# 标准全库备份（推荐生产环境使用）
mysqldump --user=root --password --host=localhost --port=3306 \
--single-transaction --routines --events --triggers \
--opt --flush-logs --master-data=2 \
--all-databases > full_backup_$(date +%Y%m%d).sql基础备份脚本
shell
#!/bin/bash
MYSQL_USER="backup_user"
MYSQL_PASS="password"
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
BACKUP_DIR="/data/backups/mysql"
DBS=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -h"$MYSQL_HOST" -P"$MYSQL_PORT" -e"show databases;" 2>/dev/null | grep -Ev "Database|*schema|mysql|sys")
for db in $DBS; do
mysqldump -u"$MYSQL_USER" -p"$MYSQL_PASS" -h"$MYSQL_HOST" -P"$MYSQL_PORT" \
--single-transaction --routines --events --triggers --opt --flush-logs --master-data=2 \
-B "$db" | gzip > "$BACKUP_DIR/$db"_$(date +%Y_%m_%d_%H_%M).sql.gz
done恢复数据方法
完整恢复
bash
# 解压后恢复
gunzip < full_backup.sql.gz | mysql -u root -p
# 直接恢复压缩文件（MySQL 5.7+）
mysql -u root -p -e "SET GLOBAL innodb_compression_default=1"
zcat full_backup.sql.gz | mysql -u root -p恢复单个数据库
bash
# 从完整备份提取特定库
sed -n '/^-- Current Database: `mydb`/,/^-- Current Database: `/p' full_backup.sql > mydb.sql
mysql -u root -p mydb < mydb.sql恢复特定表
bash
mysql -u root -p mydb < mytable.sql定时备份脚本示例
shell
#!/bin/bash
BACKUP_DIR="/mysql_backups"
DATE=$(date +%Y%m%d_%H%M)
LOG_FILE="$BACKUP_DIR/backup_$DATE.log"
echo "Backup started at $(date)" >> $LOG_FILE
mysqldump -u backup_user -p'password' \
--single-transaction \
--routines \
--triggers \
--master-data=2 \
--databases important_db | gzip > $BACKUP_DIR/full_$DATE.sql.gz 2>> $LOG_FILE
# 保留最近7天备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete >> $LOG_FILE
echo "Backup completed at $(date)" >> $LOG_FILE注意事项
- 权限要求：执行用户至少需要SELECT权限（表数据）、SHOW VIEW权限（视图）、TRIGGER权限（触发器）
- 大表处理：备份大表时建议添加
--quick选项避免内存溢出 - MyISAM引擎：使用
--lock-tables会阻塞整个库的写操作，生产环境建议在低峰期执行 - 恢复测试：定期验证备份文件可用性（可通过
mysql -e "SOURCE backup.sql"测试） - 密码安全：避免在命令行直接暴露密码，建议使用
-p后交互式输入 - 版本兼容：高版本mysqldump备份的文件可能不兼容低版本MySQL服务器
- 性能影响：生产环境大表备份建议使用
--single-transaction --quick组合
常见问题解决方案
问题1：备份时内存不足
现象：ERROR 2013 (HY000) at line XXX: Lost connection to MySQL server during query
解决方案：
bash
# 使用--quick参数逐行获取数据
mysqldump --quick -u root -p mydb > backup.sql
# 增加超时时间
mysqldump --net_read_timeout=60 --net_write_timeout=120 ...问题2：大表备份中断
解决方案：
bash
# 使用where条件分片备份
mysqldump -u root -p mydb big_table --where="id<1000000" > part1.sql
mysqldump -u root -p mydb big_table --where="id>=1000000" > part2.sql问题3：GTID一致性警告
现象：Warning: (1681, 'GTID cannot work...')
解决方案：
bash
# 添加set-gtid-purged参数
mysqldump --set-gtid-purged=OFF -u root -p mydb > backup.sql