MySQL 零基础教程

MySQL 数据库备份

在管理 MySQL 数据库时，最关键的任务之一就是定期创建备份。无论是由于硬件故障、意外删除还是恶意攻击导致的数据丢失，其后果都可能是灾难性的。备份是你的安全网，允许你将数据库恢复到之前已知的良好状态。

mysqldump 实用程序是用于创建 MySQL 数据库逻辑备份 (logical backups) 的标准命令行工具。它会生成 SQL 语句，这些语句可以被重新执行以重建数据库、表及其数据。

1. 深入理解 mysqldump

mysqldump 的工作原理是连接到你的 MySQL 服务器，读取数据库结构（schema/模式）及其数据，然后写入一个包含 CREATE TABLE、INSERT 以及其他 SQL 语句的纯文本文件。这使得备份具有高度的可移植性——你几乎可以在任何 MySQL 服务器上恢复它们，甚至可以用文本编辑器检查其内容。

mysqldump 的基本语法如下：

mysqldump -u [username] -p [database_name] > [backup_file_name].sql

让我们拆解一下这些组件：

  • -u [username]: 指定用于连接的 MySQL 用户。该用户需要有足够的权限来读取数据库。
  • -p: 提示你输入指定用户的密码。让 mysqldump 提示你输入密码通常比直接在命令行中包含密码更安全，因为命令行历史记录可能会存储它。
  • [database_name]: 你想要备份的数据库的名称。
  • >: 这是 Shell 的重定向操作符。它将 mysqldump 命令的输出（即 SQL 语句流）发送到指定的文件中。
  • [backup_file_name].sql: 你的备份文件的路径和名称。.sql 扩展名是 SQL 转储文件的标准格式。

2. 备份单个数据库

要备份我们熟悉的 world 数据库，你可以执行类似如下的命令：

mysqldump -u root -p world > world_backup_20231027.sql

运行此命令后，系统会提示你输入 root 用户的密码。一旦认证通过，mysqldump 将连接到 world 数据库，读取其结构和数据，并将所有必要的 SQL 命令写入 world_backup_20231027.sql 文件中。

你可以通过查看备份文件的前几行来验证其内容：

head -n 20 world_backup_20231027.sql

你会看到注释、CREATE TABLE 语句和 INSERT 语句。

-- MySQL dump 10.13  Distrib 8.0.34, for Linux (x86_64)
-- ... (省略部分注释头) ...
-- Table structure for table `city`
--
DROP TABLE IF EXISTS `city`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `city` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `city`
--

请注意 DROP TABLE IF EXISTS 和 CREATE TABLE 语句。这些语句确保在进行恢复时，表结构会被正确地重新创建。

3. 备份多库与特定表

3.1 备份多个数据库

如果你需要备份几个特定的数据库，可以使用 --databases 选项，后跟数据库的名称：

mysqldump -u root -p --databases world sys mysql > multi_db_backup.sql

这将创建一个单一的 .sql 文件，包含 world、sys 和 mysql 数据库的模式和数据。输出文件还将为每个数据库包含 CREATE DATABASE 和 USE 语句，以确保能够正确恢复。

3.2 备份所有数据库

要备份 MySQL 服务器上的每个数据库（除了通常不以此方式备份的内部性能模式数据库 performance schema 外），请使用 --all-databases 选项：

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

这通常用于全服务器备份。请注意，备份所有数据库可能会创建一个非常大的文件，具体取决于数据量。

3.3 备份特定表

有时，你只需要备份数据库中的某些表。你可以指定数据库名称，后跟表名：

mysqldump -u root -p world city country > world_cities_countries_backup.sql

此命令仅备份 world 数据库中的 city 和 country 表。

4. mysqldump 的关键高级选项

mysqldump 提供了许多选项来微调你的备份。以下是一些最常用的选项：

  • --no-data: 此选项创建一个仅包含数据库模式（表结构、视图、存储过程等）的备份，不包含任何数据。这对于创建模式模板或迁移数据库结构非常有用。
mysqldump -u root -p --no-data world > world_schema_only.sql
  • --no-create-info (或 -t): 此选项仅转储数据，不包含 CREATE TABLE 语句。这假设在恢复时该表已经存在。
mysqldump -u root -p --no-create-info world city > world_city_data_only.sql
  • --single-transaction: 对于 InnoDB 表（默认且推荐的存储引擎），此选项对于保证一致性备份至关重要。它在一个单一事务内执行转储，确保备份中的数据反映了单一时间点的状态，即使在备份过程中数据库正在被频繁写入。这是通过为事务设置一致性读取视图来实现的。注意：此选项对 MyISAM 表无效。
mysqldump -u root -p --single-transaction world > world_consistent_backup.sql
  • --add-drop-database: 在转储中的每个 CREATE DATABASE 语句之前添加一个 DROP DATABASE IF EXISTS 语句。这对于在恢复期间完全重建数据库很有用，但请谨慎使用，因为它会永久删除同名的现有数据库。
mysqldump -u root -p --add-drop-database --databases world > world_recreate_backup.sql
  • --routines (或 -R): 将存储例程（存储过程和函数）包含在备份中。默认情况下，mysqldump 仅备份表和视图。
mysqldump -u root -p --routines world > world_with_routines.sql
  • --events (或 -E): 将 MySQL 事件调度器 (Event Scheduler) 的事件包含在备份中。
mysqldump -u root -p --events world > world_with_events.sql

5. 组合选项与 Gzip 压缩

你经常会将 mysqldump 与其他命令行实用程序结合使用，特别是为了压缩。使用 gzip 是一种创建更小备份文件的常见做法：

mysqldump -u root -p --single-transaction world | gzip > world_backup_compressed.sql.gz

在这里，|（管道）操作符将 mysqldump 的输出直接发送到 gzip 命令，后者对其进行压缩并将其写入 .gz 文件。这可以节省大量的磁盘空间，并且如果磁盘 I/O 是瓶颈，这可以使备份速度更快。

6. 自动化备份实战 (Linux Cron)

在生产环境中，你不会每次都手动运行 mysqldump。相反，你会使用作业调度程序来实现自动化，例如 Linux/Unix 系统上的 cron 或 Windows 上的任务计划程序。

每日备份的典型 cron 条目可能如下所示（需要添加到 crontab -e 中）：

0 3 * * * /usr/bin/mysqldump -u backup_user -p'YourSecurePassword' --single-transaction world | gzip > /var/backups/mysql/world_$(date +\%Y\%m\%d_\%H\%M\%S).sql.gz 2>> /var/log/mysql_backup.log

让我们拆解这个 cron 命令：

  • 0 3 * * *: 指定调度时间：在每天凌晨 3 点 0 分。
  • /usr/bin/mysqldump ...: mysqldump 可执行文件的绝对路径。
  • -u backup_user -p'YourSecurePassword': 使用指定密码以 backup_user 身份连接。
    • 安全提示： 虽然由于历史记录的原因，通常不鼓励直接在命令行上传递密码，但这对于无法进行交互式密码提示的自动 cron 作业来说往往是必要的。请确保你的 cron 文件具有极其严格的权限。自动化脚本的一个更好的替代方案是使用包含凭据的 .my.cnf 文件。
  • --single-transaction world: 一致地转储 world 数据库。
  • | gzip: 将输出通过管道传输到 gzip 进行压缩。
  • > /var/backups/mysql/world_$(date +\%Y\%m\%d_\%H\%M\%S).sql.gz: 将压缩的输出重定向到特定目录中的文件。$(date +\%Y\%m\%d_\%H\%M\%S) 部分为文件名动态生成时间戳，使每个备份都是唯一的。注意 % 字符前面的反斜杠 \；这是必要的，因为 cron 对 % 的解释不同。
  • 2>> /var/log/mysql_backup.log: 将来自 mysqldump 或 gzip 命令的任何错误消息（标准错误 stderr）重定向到一个日志文件中，并以追加（>>）方式写入。这对于监控自动化任务至关重要。

使用 mysqldump 创建定期、一致的备份是数据库管理的基础部分。请始终确保你的备份用户仅拥有需要备份的数据库的必要 SELECT、LOCK TABLES 和 EVENT 权限，遵循最小特权原则。