mysqldump 命令备份单库、多库、全库实操指南

在日常运维中，MySQL 数据备份是保障数据安全的核心操作，而mysqldump作为 MySQL 官方自带的备份工具，因其轻量、灵活、跨平台的特性，成为中小规模数据库备份的首选。

一、mysqldump 基础认知

1. 什么是 mysqldump？

mysqldump是 MySQL 提供的命令行工具，本质是一个 SQL 导出工具，它能将数据库/表结构和数据以 SQL 语句的形式导出到文本文件中，备份文件可直接用于数据恢复，兼容所有 MySQL 版本（5.0+）和 MariaDB。

2. 基础语法格式

mysqldump[连接参数][备份参数][数据库名][表名]>备份文件路径/文件名.sql

核心连接参数说明：

• -u：指定 MySQL 用户名（如-uroot）；
• -p：指定密码（建议只写-p不直接跟密码，回车后输入更安全）；
• -h：指定数据库服务器地址（本地可省略，远程需填 IP，如-h192.168.1.100）；
• -P：指定端口（默认 3306，非默认需指定，如-P3307）；

二、实操：mysqldump 备份单库（最常用）

1. 基础单库备份

适用于只备份某一个数据库（如业务库test_db），命令如下：

# 本地数据库单库备份（推荐写法，密码交互输入）mysqldump -uroot -p test_db>/backup/mysql/test_db_$(date+%Y%m%d).sql# 远程数据库单库备份（指定IP和端口）mysqldump -uroot -p -h192.168.1.100 -P3306 test_db>/backup/mysql/test_db_20260113.sql

关键说明：

• $(date +%Y%m%d)：自动拼接日期到备份文件名，避免覆盖（如test_db_20260113.sql）；
• /backup/mysql/：备份文件存放路径，需提前创建（mkdir -p /backup/mysql）；
• 执行后会提示Enter password:，输入 MySQL 密码即可开始备份。

2. 优化版单库备份（带压缩+忽略锁表）

生产环境中，建议开启压缩减少文件体积，同时添加参数避免锁表影响业务：

# 压缩备份（gzip）+ 忽略锁表 + 保留结构和数据mysqldump -uroot -p --single-transaction --skip-lock-tables test_db|gzip>/backup/mysql/test_db_$(date+%Y%m%d).sql.gz

参数解析：

• --single-transaction：适用于 InnoDB 引擎，保证备份数据的一致性，无锁；
• --skip-lock-tables：跳过表锁，避免备份时阻塞读写；
• | gzip：将备份内容通过管道压缩，生成.sql.gz文件，体积仅为原文件的 1/5 左右。

三、实操：mysqldump 备份多库

如果需要同时备份多个数据库（如test_db1、test_db2），无需多次执行命令，只需用--databases参数指定多个库名：

1. 基础多库备份

# 备份 test_db1 和 test_db2 两个库mysqldump -uroot -p --databases test_db1 test_db2>/backup/mysql/multi_db_$(date+%Y%m%d).sql

2. 多库备份（排除指定库）

如果需要备份大部分库，仅排除个别库（如排除mysql系统库），可结合grep筛选：

# 先获取所有库名，排除 mysql 后备份mysql -uroot -p -e"SHOW DATABASES;"|grep-Ev"Database|mysql|information_schema|performance_schema"|xargsmysqldump -uroot -p --databases>/backup/mysql/multi_db_exclude_$(date+%Y%m%d).sql

参数解析：

• -e "SHOW DATABASES;"：执行 SQL 查看所有库名；
• grep -Ev：排除系统库（Database 是表头，mysql/information_schema/performance_schema 是系统库）；
• xargs：将筛选后的库名传递给 mysqldump。

四、实操：mysqldump 备份全库

全库备份即备份 MySQL 服务器上所有数据库（含系统库），适合整机迁移、灾备场景，核心参数是--all-databases（可简写为-A）。

1. 基础全库备份

# 全库备份（最简洁写法）mysqldump -uroot -p --all-databases>/backup/mysql/full_db_$(date+%Y%m%d).sql# 简写形式（-A 等价于 --all-databases）mysqldump -uroot -p -A>/backup/mysql/full_db_$(date+%Y%m%d).sql

2. 生产级全库备份（压缩+一致性+跳过错误）

# 全库备份：压缩 + 一致性 + 跳过错误 + 记录备份时间mysqldump -uroot -p -A --single-transaction --skip-lock-tables --force|gzip>/backup/mysql/full_db_$(date+%Y%m%d_%H%M%S).sql.gz

参数解析：

• --force：备份过程中遇到错误不中断，继续备份其他库；
• %H%M%S：在文件名中添加时分秒，适合一天多次备份的场景。

五、mysqldump 备份注意事项

1. 引擎适配：--single-transaction仅对 InnoDB 引擎有效，MyISAM 引擎需用--lock-all-tables保证一致性（会锁全库，建议低峰期执行）；
2. 权限要求：执行备份的 MySQL 用户需有SELECT、LOCK TABLES、SHOW VIEW等权限，建议用 root 用户；
3. 备份验证：备份后务必验证文件有效性，可通过cat 备份文件.sql | grep "CREATE TABLE"查看是否有表结构，或直接导入测试库；
4. 定时备份：生产环境建议结合crontab实现定时备份，例如每天凌晨 2 点备份：

   # 编辑定时任务crontab-e# 添加以下内容（每天2点备份test_db，输出日志避免邮件告警）02* * * mysqldump -uroot -p'你的密码'--single-transaction test_db|gzip>/backup/mysql/test_db_$(date+%Y%m%d).sql.gz>/dev/null2>&1

   注意：定时任务中-p后可直接写密码（无空格），但需确保服务器权限安全。

六、备份恢复（补充）

备份的最终目的是恢复，以下是对应恢复命令：

# 恢复单库/多库mysql -uroot -p 目标库名<备份文件.sql# 恢复压缩的备份文件gzip-d 备份文件.sql.gz&&mysql -uroot -p 目标库名<解压后的文件.sql# 恢复全库mysql -uroot -p<全库备份文件.sql