Blog

Appearance

mysqldump 教程 ​

1. 什么是 mysqldump？ ​

mysqldump 是 MySQL 自带的一个命令行客户端工具，用于实现数据库的逻辑备份。 它能生成一个包含一系列 SQL 语句的文本文件（通常是 .sql 文件），这些 SQL 语句可以用来重建数据库的原始对象定义和表数据。 你可以利用它来备份数据库、将数据迁移到另一个 SQL 服务器，或者为开发和测试克隆数据库。

逻辑备份的优点：

  • 灵活性高： 备份文件是可读的 SQL 语句，你可以在恢复前查看甚至编辑它。
  • 跨版本和平台： 生成的 SQL 文件通常具有很好的兼容性，可以在不同版本的 MySQL 服务器甚至不同的数据库系统上（经过修改后）使用。
  • 简单易用： 基本的备份和恢复操作命令非常简单。

逻辑备份的缺点：

  • 恢复速度慢： 对于非常大的数据库，重新执行所有 SQL 语句来恢复数据会比物理备份慢很多。
  • 备份期间的资源消耗： 对正在运行的服务器执行 mysqldump 可能会占用相当多的 CPU 和 I/O 资源。

2. 先决条件：所需权限 ​

要成功运行 mysqldump，执行操作的数据库用户需要具备一些特定的权限。 基本上，你需要一个至少拥有完全读取权限的用户。

  • SELECT：对于要导出的所有表都是必需的。
  • SHOW VIEW：如果需要导出视图的定义。
  • TRIGGER：如果需要导出触发器。
  • LOCK TABLES：如果未使用 --single-transaction 选项，则需要此权限来锁定表以保证数据一致性。
  • PROCESS：如果未使用 --no-tablespaces 选项。

3. 基本语法 ​

mysqldump 的命令结构通常遵循以下模式：

bash
mysqldump -u [用户名] -p [选项] [数据库名] [表名...] > [备份文件名.sql]
  • -u [用户名]：指定连接数据库的用户名。
  • -p：提示输入用户密码。为了安全，建议使用 -p 而不是 -p[密码] 的形式，避免在命令行历史中留下密码。
  • [选项]：用于控制备份过程的各种参数（下文会详细介绍）。
  • [数据库名]：要备份的数据库名称。
  • [表名...]：可选参数，如果指定，则只备份该数据库中的特定表。
  • >：这是标准的 shell 输出重定向符，将 mysqldump 生成的 SQL 语句输出到指定的文件中。

核心操作：备份与恢复 ​

4. 备份数据库（常见场景） ​

场景一：备份单个数据库 ​

这是最常见的用例。此命令将备份 mydb 数据库的完整结构和数据。

bash
mysqldump -u root -p mydb > mydb_backup.sql
场景二：备份多个数据库 ​

使用 --databases 选项，后面跟上所有需要备份的数据库名称，用空格隔开。

bash
mysqldump -u root -p --databases mydb1 mydb2 > multiple_db_backup.sql```
这个命令创建的备份文件中会包含 `CREATE DATABASE IF NOT EXISTS` 和 `USE` 语句，这在恢复时非常方便。

##### 场景三：备份所有数据库

使用 `--all-databases` 选项可以一次性备份服务器上的所有数据库。

```bash
mysqldump -u root -p --all-databases > all_databases_backup.sql
场景四：仅备份特定的数据表 ​

如果你只需要备份某个数据库中的一两个表，可以在数据库名后面直接跟上表名。

bash
mysqldump -u root -p mydb table1 table2 > tables_backup.sql
场景五：仅备份数据库结构（Schema） ​

使用 --no-data 选项，可以只导出数据库的结构（表、视图、触发器等），不包含任何行数据。这对于创建测试环境或复制表结构非常有用。

bash
mysqldump -u root -p --no-data mydb > mydb_schema_only.sql
场景六：仅备份数据 ​

与上一个场景相反，使用 --no-create-info 选项可以只导出数据，而不包含 CREATE TABLE 语句。

bash
mysqldump -u root -p --no-create-info mydb > mydb_data_only.sql

5. 从备份文件恢复数据库 ​

恢复数据库使用的是 mysql 命令行工具，而不是 mysqldump。你需要使用输入重定向符 < 将备份文件导入。

重要提示： 在恢复之前，通常需要手动创建一个空的数据库（除非你的备份文件中包含了 CREATE DATABASE 语句，例如使用了 --databases 或 --all-databases 选项进行备份）。

步骤：

  1. （如果需要）登录 MySQL 并创建数据库：

    sql
    mysql -u root -p
CREATE DATABASE newdb;
exit;

  2. 执行恢复命令： 将 mydb_backup.sql 文件中的数据导入到 newdb 数据库中。

    bash
    mysql -u root -p newdb < mydb_backup.sql

高级选项与最佳实践 ​

6. 保证数据一致性：--single-transaction ​

对于使用 InnoDB 等事务性存储引擎的数据库，强烈建议在备份时使用 --single-transaction 选项。它可以在不锁定表的情况下，利用事务来获取备份开始时的数据快照，从而保证数据的一致性，且不会阻塞正在进行的应用读写操作。

bash
mysqldump -u root -p --single-transaction mydb > mydb_consistent_backup.sql

7. 包含存储过程和函数：--routines ​

默认情况下，mysqldump 不会备份存储过程和函数。如果你需要备份它们，必须明确使用 --routines 选项。

bash
mysqldump -u root -p --routines --single-transaction mydb > mydb_with_routines.sql

8. 处理大型数据库 ​

  • 压缩备份： 对于大型数据库，生成的 .sql 文件可能会非常大。你可以通过管道直接将其压缩。

    bash
    mysqldump -u root -p --single-transaction mydb | gzip > mydb_backup.sql.gz

    恢复时也需要先解压：

    bash
    gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

  • 使用 --quick 选项： 这个选项让 mysqldump 逐行从服务器获取数据，而不是一次性获取全部数据并缓存到内存中。这对于备份大表至关重要，可以防止因内存耗尽而导致的备份失败。 值得注意的是，--opt 选项（默认开启）已经包含了 --quick。

9. 自动化备份：使用 Cron Job ​

在 Linux 或 macOS 系统上，你可以使用 cron 来定时执行备份任务。

  1. 创建备份脚本 (/home/user/backup.sh):

    bash
    #!/bin/bash
mysqldump -u dbuser -p'yourpassword' mydb | gzip > /home/user/backups/mydb-$(date +%Y-%m-%d).sql.gz

    注意： 直接在脚本中写密码存在安全风险。更安全的方法是使用 MySQL 的配置文件 .my.cnf。

  2. 设置 Cron Job： 运行 crontab -e 并添加一行，表示每天凌晨 3 点执行备份。

    crontab
    0 3 * * * /bin/bash /home/user/backup.sh

10. 安全地存储凭证 ​

为了避免在脚本中明文存储密码，可以在你的用户主目录下创建一个 .my.cnf 文件。

ini
# ~/.my.cnf
[mysqldump]
user=dbuser
password=yourpassword

[mysql]
user=dbuser
password=yourpassword

设置文件权限为只有所有者可读写：chmod 600 ~/.my.cnf。 之后，你在执行 mysqldump 或 mysql 命令时就不需要再输入 -u 和 -p 参数了。

Released under the MIT License.

Copyright © 2024-present LoftyShadow