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

本教程将介绍如何通过mysqldump来备份和还原MySQL或MariaDB数据库。

mysqldump程序可以通过一组SQL语句，来备份现有数据库或者从备份恢复数据库。mysqldump命令可以操作SQL格式的数据库文件，还可以生成CSV和XML格式的文件。

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

如果不定期备份数据库，一旦遭遇软件错误或硬盘故障，可能会造成灾难性的后果。为了避免严重不利后果的产生，牛奇网强烈建议您定期备份MySQL数据库。

Mysqldump 基本语法

在开始之前，让我们先了解一下mysqldump的基本语法。

mysqldump程序表达式采用以下形式：

mysqldump [options] > /home/file.sql

• options – 参数
• file.sql – 要备份的数据库文件

要使用mysqldump命令来备份数据库，MySQL服务器必须正在运行并且是有访问权限的。

备份单个MySQL数据

我们最常使用的是利用mysqldump工具来备份单个数据库。

例如，要以MySQL用户root的身份，为名为database_name的数据库创建备份，并将备份文件保存到/home/文件夹内，我们可以执行以下命令：

mysqldump -u root -p database_name > /home/database_name.sql

系统将提示您输入root用户的密码。成功通过身份验证后，就将开始创建数据库备份文件。根据数据库大小的不同，备份过程可能需要一些时间。

如果您已经使用root用户身份登录了数据库，则可以直接执行备份，可以省略-u和-p参数：

mysqldump database_name > database_name.sql

备份多个MySQL数据库

要一次备份多个MySQL数据库，需要添加--databases选项，后面加上要备份的多个数据库名称。每个数据库名称之间需要用空格分隔：

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

执行上面的命令后，将生成一个名为“databases_a_b.sql”的数据库备份文件，这个文件中包含了上述两个数据库的数据

备份所有的MySQL数据库

要备份所有MySQL数据库，需要使用--all-databases选项：

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

执行上面的命令后，将创建一个包含了所有MySQL数据库的备份文件。

备份所有MySQL数据库为多个文件

mysqldump工具没有提供将所有数据库备份到多个单独文件的选项，但是我们可以通过一个简单的FOR循环来实现这一点：

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

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

创建压缩的MySQL数据库备份文件

如果数据库很大，则最好压缩备份。执行如下所示的命令：

mysqldump database_name | gzip > database_name.sql.gz

创建名称带有时间的备份

如果要在同一文件夹位置保留多个备份文件，则可以将当前日期添加到备份文件名中，这样可以识别和管理不同时间点的备份：

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

上面的命令，将创建如下包含日期的文件 database_name-20180617.sql。

还原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

从包含所有数据库的备份文件恢复其中一个

如果我们备份时，使用-all-databases选项，将所有MySQL数据库备份到了一个文件中，但是我们想要从这个备份文件中还原单个数据库，我们可以使用--one-database选项，如下所示：

mysql --one-database database_name < all_databases.sql

将一个数据库导入另一个数据库

一般情况下，我们先将一个MySQL数据库备份下来，然后再将其导入其他的数据库中。其实我们有更加简单的方法，可以直接将一个数据库导入另一个数据库：

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

上面的命令，会将备份的数据库，直接传到远程主机数据库上名为remote_database_name的数据库中。在执行上述命令之前，请确保远程服务器上已经存在该数据库。

使用Cron自动执行备份

自动备份数据库的过程其实非常简单，通过创建一个cron任务，可以在指定时间运行mysqldump备份命令。

要使用cron设置MySQL数据库自动备份，请按以下步骤执行：

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

sudo nano ~/.my.cnf

将以下内容，复制并粘贴到.my.cnf文件中：

[client]
user = dbuser
password = dbpasswd

将“dbuser”和“dbpasswd”分别替换为实际的数据库用户和密码。

02、设置.my.cnf的访问权限，确保只有你可以访问：

chmod 600 ~/.my.cnf

03、创建一个目录，来储存备份文件：

mkdir ~/db_backups

04、打开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替换为实际用户名。

还可以创建另一个cron任务，用来删除存储了30天以上的数据库备份：

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