MySQL 数据库导出命令 mysqldump 简明用法

之前用 LNMP 一键包搭建环境时，习惯通过 phpMyAdmin 面板备份数据库。偶尔这样操作还行，如果需要经常备份就麻烦了。还是使用 shell 命令操作要好些，可以写个脚本自动完成。

MySQL 和其衍生版（如 MariaDB）包含了一个实用工具 mysqldump，可以用它转储数据库至标准输出，然后输出到文件保存，并在需要时用备份恢复数据库。下面就来介绍 mysqldump 的具体用法。

导出单个数据库

这是 mysqldump 导出数据库的基本语法。

mysqldump -u user_name -p db_name > dump-$(date +"%Y%m%d").sql

解释其中的各项参数：

• -u 用于指定数据库用户名，通常是 root，需要具备对目标数据库的读写权限。
• -p 指定数据库账号密码。基于安全考量，一般不指定密码，运行命令后以交互方式输入。若要指定密码，在 -p 后输入密码（注意是紧挨着没有空格）。
• db_name 备份数据库名称，这种写法不能同时备份多个数据库。如果用空格写了多个名称，则会被视为备份数据库下的表（第一名称是数据库，之后名称表示该数据库下的表名）。
• > dump-$(date +"%Y%m%d").sql 将数据库导出到文件，这里文件名加了时间戳。

导出多个数据库

导出多个数据库用 --databases 参数指定，语法如下。

mysqldump -u user_name -p --databases db1 db2 db3 > dump-$(date +"%Y%m%d").sql

当带了 --databases 和下面介绍的 --all-databases 参数，在转储输出前会写入 CREATE DATABASE 和 USE 语句。这样在恢复数据时，如果没有同名数据库，它会先创建数据库。并在恢复各个数据库时，通过 USE 语句设置默认数据库，以便将数据导入到同一数据库中。

导出所有数据库

备份所有数据库使用 --all-databases 参数。

mysqldump -u user_name -p --all-databases > dump-$(date +"%Y%m%d").sql

其它常用参数

上面提到过，因为安全原因，不建议在 -p 后面指定密码。但有的场景需要自动输入，那应该怎样操作比较好呢？这时可用选项文件存储密码凭据。

具体做法是先在用户目录下创建一个.my.cnf文件，内容格式如下。

[client]
user=root
password=password_here

接着设置文件权限，仅允许属主具读写权限。这步是必须的，MySQL 会忽略未设置权限的选项文件。

chmod 600 /root/.my.cnf

之后在命令里用 --defaults-extra-file 参数指定选项文件（绝对路径），这样就不用手动输入密码了。

mysqldump --defaults-extra-file=/root/.my.cnf --user=root db_name > dump-$(date +"%Y%m%d").sql

可能你留意到了，为什么上面命令中仍指定了用户名？这是一个安全做法，防止配置选项文件里的用户名被替换，这个不是必须。事实上如果选项文件用了.my.cnf文件名，并且在用户目录下，--defaults-extra-file 参数也可以省略掉，MySQL 程序会自动读取这个约定用户选项文件。

另外介绍几个实用参数，在机器内存较小或要备份大型数据库时很有用。

mysqldump -u user_name -p --single-transaction --quick --lock-tables=false db_name > dump-$(date +"%Y%m%d").sql

• --single-transaction 参数表示将备份会话的隔离级别设置为 REPEATABLE READ，以确保不会看到其它会话已经提交的数据。目的是为确保备份数据的一致性，它只对支持事务的表有效（只有 InnoDB 表支持事务）。在导出大表时与 --quick 结合使用。
• --quick 参数表示逐行转储表，避免将表存储在内存中可能带来的潜在问题。
• --lock-tables=false 参数表示不要为备份会话锁定表。

添加定时备份数据库任务

在掌握了数据库导出命令使用后，要定时备份数据库只需添加相应的 cron 任务。执行crontab -e添加计划任务，内容如下。

0 0 1 * * /usr/bin/mysqldump --defaults-extra-file=/root/.my.cnf --user=root db_name > /root/dump-$(date +"%Y%m%d").sql

这将每月备份一次数据库，文件存储在 /root 目录下。

用备份文件恢复数据库

导入恢复单个数据库：

mysql -u user_name -p db_name < dump-backup.sql

导入恢复单个数据表：

mysql -u user_name -p db_name < dump-table-backup.sql

导入单个数据库或数据表时，目标数据库必须是已存在。否则需要先创建数据库，创建语法如下。

mysqladmin -u user_name -p create db_name

当导入使用 --databases 或 --all-databases 参数导出的数据库文件，则不需要指定目标数据库，它会自动创建数据库。

mysql -u user_name -p < dump-full-backup.sql