如何使用Mysqldump备份和还原MySQL数据库

本教程说明了如何使用mysqldump实用程序从命令行备份和还原MySQL或MariaDB数据库。

mysqldump实用程序创建的备份文件基本上是一组SQL语句,可用于重新创建原始数据库。 mysqldump命令还可以生成CSV和XML格式的文件。

您还可以使用mysqldump实用程序将您的MySQL数据库传输到另一个MySQL服务器。

如果不备份数据库,则软件错误或硬盘故障可能是灾难性的。 为了帮助您节省大量时间和沮丧,强烈建议您采取预防措施,以定期备份MySQL数据库。

Mysqldump命令语法#

在开始使用mysqldump命令之前,让我们先回顾一下基本语法。

mysqldump实用程序表达式采用以下形式:

mysqldump [options] > file.sql
  • options -mysqldump选项
  • file.sql -转储(备份)文件

要使用mysqldump命令,MySQL服务器必须可访问并正在运行。

备份单个MySQL数据库

mysqldump工具最常见的用例是备份单个数据库。

例如,创建名为的数据库的备份 database_name 使用用户 root 并将其保存到名为 database_name.sql 您将运行以下命令:

mysqldump -u root -p database_name > database_name.sql

系统将提示您输入root密码。 身份验证成功后,转储过程将开始。 根据数据库大小,该过程可能需要一些时间。

如果您以用于执行导出的同一用户身份登录,并且该用户不需要密码,则可以省略 -u-p 选项:

mysqldump database_name > database_name.sql

备份多个MySQL数据库

要使用一个命令备份多个MySQL数据库,您需要使用 --database 选项,然后是要备份的数据库列表。 每个数据库名称必须用空格分隔。

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

上面的命令将创建一个包含两个数据库的转储文件。

备份所有MySQL数据库

使用 --all-databases 备份所有MySQL数据库的选项:

mysqldump -u root -p --all-databases > all_databases.sql

与前面的示例相同,上​​面的命令将创建一个包含所有数据库的转储文件。

将所有MySQL数据库备份到单独的文件中

mysqldump 该实用程序没有提供将所有数据库备份到单独文件的选项,但是我们可以通过简单的bash轻松实现 FOR 循环:

for DB in $(mysql -e 'show databases' -s --skip-column-names); do
    mysqldump $DB > "$DB.sql";
done

上面的命令将使用数据库名称作为文件名为每个数据库创建一个单独的转储文件。

创建压缩的MySQL数据库备份#

如果数据库很大,则最好压缩输出。 为此,只需将输出通过管道传输到 gzip 实用程序,并将其重定向到文件,如下所示:

mysqldump database_name | gzip > database_name.sql.gz

使用时间戳创建备份

如果要在同一位置保留多个备份,则可以将当前日期添加到备份文件名中:

mysqldump  database_name > database_name-$(date +%Y%m%d).sql

上面的命令将创建以下格式的文件 database_name-20180617.sql

恢复MySQL转储

您可以使用还原MySQL转储 mysql 工具。 命令的一般语法如下:

mysql  database_name < file.sql

在大多数情况下,您需要创建一个要导入的数据库。 如果数据库已经存在,则首先需要将其删除。

在下面的示例中,第一个命令将创建一个名为 database_name 然后将导入转储 database_name.sql 进去:

mysql -u root -p -e "create database database_name";mysql -u root -p database_name < database_name.sql

从完整的MySQL转储还原单个MySQL数据库

如果您使用 -all-databases 选项,并且您要从包含多个数据库的备份文件中还原单个数据库,请使用 --one-database 选项如下图所示:

mysql --one-database database_name < all_databases.sql

在一个命令中导出和导入MySQL数据库

不用从一个数据库创建转储文件,然后将备份导入另一个MySQL数据库,您可以使用以下一种格式:

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

上面的命令会将输出通过管道传递到远程主机上的mysql客户端,并将其导入到名为 remote_database_name。 在运行命令之前,请确保数据库已在远程服务器上存在。

使用Cron#自动执行备份

自动化备份数据库的过程就像创建cron作业一样简单,它将在指定时间运行mysqldump命令。

要使用cronjob设置MySQL数据库的自动备份,请执行以下步骤:

  1. 创建一个名为 .my.cnf 在您的用户主目录中:

    sudo nano ~/.my.cnf

    将以下文本复制并粘贴到.my.cnf文件中。

    [client]
    user = dbuser
    password = dbpasswd

    别忘了更换 dbuserdbpasswd数据库用户和用户密码。

  2. 限制凭据文件的权限,以便只有您的用户可以访问它:

    chmod 600 ~/.my.cnf
  3. 创建一个目录来存储备份:

    mkdir ~/db_backups
  4. 打开您的用户crontab文件:

    crontab -e

    添加以下cron作业,该作业将创建数据库名称的备份 mydb 每天凌晨3点:

    0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql

    别忘了更换 username 使用您的实际用户名。 我们也在转义百分号(%),因为它们在crontab中具有特殊含义。

您还可以创建另一个cronjob来删除30天以上的所有备份:

find /path/to/backups -type f -name "*.sql" -mtime +30 -delete

当然,您需要根据备份位置和文件名来调整命令。 要了解有关find命令的更多信息,请查看我们的《如何使用命令行在Linux中查找文件》。

结论#

本教程仅介绍基础知识,但是对于任何想学习如何使用mysqldump实用工具从命令行创建和还原MySQL数据库的人来说,它都是一个不错的开始。

如果要从命令行了解有关使用MySQL的更多信息,请查看我们的“如何管理MySQL用户帐户和数据库”指南。

您还可以查看有关如何重置MySQL根密码的教程,以防万一忘记了它。

如果您有任何疑问或反馈,请随时发表评论。

mysql mariadb备份mysqldump

Sidebar