如何使用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工具最常见的用例是备份单个数据库。

例如，要使用用户root创建名为database_name的数​​据库的备份并将其保存到名为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转储。命令的一般语法如下：

mysqld  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数据库的自动备份，请执行以下步骤：

在用户主目录中创建一个名为.my.cnf的文件：

sudo nano ~/.my.cnf

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

[client]
user = dbuser
password = dbpasswd

不要忘记用数据库用户和用户密码替换dbuser和dbpasswd。

限制凭据文件的权限，以便只有您的用户可以访问它：

chmod 600 ~/.my.cnf

创建目录以存储备份：

mkdir ~/db_backups

打开您的用户crontab文件：

crontab -e

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

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

不要忘记用您的实际用户名替换username。

您也可以创建另一个cronjob来删除30天之前的所有备份：

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

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

结论

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

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

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

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