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

Eric 教程 67 次浏览 抢沙发

之前用 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

发表评论

电邮地址用于 Gravatar 头像显示,不会被公开可见。