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

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

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

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

原创文章,作者:牛奇网,如若转载,请注明出处:https://www.niuqi360.com/lamp-config/backup-restore-mysql-databases-mysqldump/

发表评论

登录后才能评论