使用mysqldump备份MySQL或MariaDB

在日常维护工作当中经常会需要对数据进行导出操作，而mysqldump是导出数据过程中使用非常频繁的一个工具。本文将简介MySQL如何使用mysqldump工具进行数据备份。

准备

• 一台已经设置好可以使用sudo命令的非root账号的Ubuntu服务器，并且已开启防火墙。没有服务器的同学可以在这里购买，不过我个人更推荐您使用免费的腾讯云开发者实验室进行试验，学会安装后在购买服务器。
• 您需要安装MySQL或MariaDB，以及运行备份的数据库用户。相关安装教程可以参考四步教你搭建保护MySQL服务器！这篇文章。

备份数据库

mysqldump命令的一般语法是：

mysqldump -u [username] -p [databaseName] > [filename]-$(date +%F).sql

• mysqldump在启动备份过程之前会提示输入密码。
• 根据数据库的大小，可能需要一段时间才能完成。
• 将在运行该命令的目录中创建数据库备份。
• -$(date +%F) 为文件名添加时间戳。

备份示例

• 创建整个数据库管理系统（DBMS）的备份：

mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root -p

• 备份特定数据库。替换db1为要备份的数据库的名称：

mysqldump -u username -p db1 --single-transaction --quick --lock-tables=false > db1-backup-$(date +%F).sql

• 从任何数据库备份单个表。在下面的示例中，table1从数据库导出db1：

mysqldump -u username -p --single-transaction --quick --lock-tables=false db1 table1 > db1-table1-$(date +%F).sql

以下是mysqldump上面使用的命令选项的细分：

• --single-transaction：在从服务器转储数据之前发出BEGIN SQL语句。
• --quick：逐行执行转储表。为具有少量RAM和/或大型数据库的系统增加了安全性，其中在内存中存储表可能会成为问题。
• --lock-tables=false：不要锁定备份会话的表。

使用cron自动备份

可以添加条目/etc/crontab以定期计划数据库备份。

创建一个文件来保存将要执行备份的MySQL root用户的登录凭据。请注意，存储此文件的主目录的系统用户与MySQL用户无关。

1 [client]
2 user = root
3 password = MySQL root user's password

限制凭证文件的权限：

chmod 600 /home/example_user/.mylogin.cnf

创建cron作业文件。下面是每天凌晨1点备份整个数据库管理系统的示例cron作业：

10 1 * * * /usr/bin/mysqldump mysqldump --defaults-extra-file=/home/example_user/.my.cnf -u root --single-transaction --quick --lock-tables=false --all-databases > full-backup-$(date +%F).sql

还原备份

恢复命令的一般语法是：

mysql -u [username] -p [databaseName] < [filename].sql

• 还原整个DBMS备份。系统将提示您输入MySQL root用户的密码：

这将覆盖MySQL数据库系统中的所有当前数据

mysql -u root -p < full-backup.sql

• 还原单个数据库转储。必须已存在空的或旧的目标数据库才能将数据导入，并且您运行该命令的MySQL用户必须具有对该数据库的写访问权限：

mysql -u [username] -p db1 < db1-backup.sql

• 恢复单个表，您必须准备好目标数据库以接收数据：

mysql -u dbadmin -p db1 < db1-table1.sql

更多信息

有关此主题的其他信息，您可能需要参考以下资源。虽然提供这些是希望它们有用，但请注意，我们无法保证外部托管材料的准确性或及时性。

• MySQL数据库备份方法页面
• mysqldump - 数据库备份程序，MySQL参考手册

自建服务器难免会遇到这样的问题，但是如果您在生产环境使用，我还是建议您直接使用云关系型数据库，云关系型数据库让您在云中轻松部署、管理和扩展的关系型数据库，提供安全可靠、伸缩灵活的按需云数据库服务。腾讯云关系型数据库提供 MySQL、SQL Server、MariaDB、PostgreSQL 数据库引擎，并针对数据库引擎的性能进行了优化。云关系型数据库是一种高度可用的托管服务，提供容灾、备份、恢复、监控、迁移等数据库运维全套解决方案，可将您从耗时的数据库管理任务中解放出来，让您有更多时间专注于您的应用和业务。更多Linux教程请前往腾讯云+社区学习更多知识。

参考文献：《Use mysqldump to Back Up MySQL or MariaDB》