Skip to main content

4.使用mysqldump进行备份

提示：

考虑使用MySQL Shell 转储实用程序，它提供多线程并行转储、文件压缩和进度信息显示，使用MySQL Shell 负载转储实用程序可以轻松地将转储导入到 MySQL 服务器实例或 MySQL HeatWave 服务数据库系统中。

内容概述​

本节介绍如何使用mysqldump生成转储文件，以及如何重新加载转储文件。转储文件可以通过多种方式使用：

  • 作为备份，以便在数据丢失时恢复数据。
  • 作为设置副本的数据源。
  • 作为实验数据来源：
    • 制作数据库的副本，可以在不更改原始数据的情况下使用该副本。
    • 测试潜在的升级不兼容性。

mysqldump产生两种类型的输出，具体取决于是否 --tab给出该选项：

  • 如果没有 --tab， myqldump会将 SQL 语句写入标准输出。此输出包含创建转储对象（数据库、表、存储例程等）的 CREATE语句和将数据加载到表中的 INSERT语句。输出可以保存在文件中，稍后使用 mysql重新加载以重新创建转储的对象。可以使用选项来修改 SQL 语句的格式，并控制转储哪些对象。
  • 使用 mysqldump --tab 为每个转储表生成两个输出文件。服务器将一个文件写入制表符分隔的文本，每个表行一行。

使用 mysqldump 以 SQL 格式转储数据​

默认情况下，mysqldump将信息作为 SQL 语句写入标准输出。可以将输出保存在文件中：

语法：

mysqldump [arguments] > file_name

要转储所有数据库​

使用 --all-databases

mysqldump --all-databases > dump.sql		#全部数据库

要仅转储特定数据库​

使用 --databases

mysqldump --databases test > dump.sql		#单个数据库
mysqldump --databases db1 db2 db3 > dump.sql	#多个数据库

提示：

该 --databases选项导致命令行上的所有名称都被视为数据库名称。如果没有此选项，mysqldump将第一个名称视为数据库名称，将后面的名称视为表名称。

使用 --all-databases或 --databases， mysqldump 在每个数据库的转储输出之前写入 CREATE DATABASE和 USE 语句。这确保了当重新加载转储文件时，它会创建每个不存在的数据库，并将其设为默认数据库，以便将数据库内容加载到它们来自的同一数据库中。

如果想让转储文件在重新创建数据库之前强制删除每个数据库， 也可以使用 --add-drop-database该选项。在这种情况下，mysqldump 在每个 CREATE DATABASE语句之前写入一个 DROP DATABASE 语句 。

从数据库中转储特定表​

要仅从数据库中转储特定表，请在命令行上的数据库名称后命名它们：

mysqldump test t1 t3 t7 > dump.sql	#转储test数据库中的t1,t3,t7 3张表

制作数据库的副本​

mysqldump db1 > dump.sql		#导出db1备份
mysqladmin create db2
mysql db2 < dump.sql			#将db1导入到db2

导出时不要使用--databases选项，因为这会导致 USE db1 包含在转储文件中，覆盖掉命令行上的 db2。

将数据库从一台服务器复制到另一台服务器​

  1. 在服务器 1 上制作转储文件：

    mysqldump --databases db1 > dump.sql

  2. 将转储文件从服务器 1 复制到服务器 2。

  3. 在服务器 2 上导入转储文件：

    mysql < dump.sql

 --databases会导致转储文件包含 CREATE DATABASE 和 USE 创建数据库的语句，并使其成为重新加载数据的默认数据库。

如果导致转储文件后，想手动创建数据库的话，可按以下操作：

  1. 服务器 1 上操作：

    mysqldump db1 > dump.sql

  2. 拷贝dump文件

  3. 服务器 2 上操作：

    mysqladmin create db1
    mysql db1 < dump.sql

转储存储的程序​

有几个选项控制如何 mysqldump 处理存储程序（存储过程和函数、触发器和事件）：

  • --events：转储事件调度程序事件
  • --routines：转储存储过程和函数
  • --triggers：表的转储触发器

默认情况下启用该 --triggers选项，以便在转储表时，它们会附带它们拥有的任何触发器。 其他选项默认禁用，必须显式指定才能转储相应的对象。

要显式禁用任何这些选项，请使用其跳过形式： --skip-events、 --skip-routines或 --skip-triggers。

分别转储表定义和内容​

某些情况下，数据库和表结构都已存在，只是需要除此之外的表数据，可使用以下操作。

  • --no-data 选项 mysqldump不要转储表数据，导致转储文件仅包含创建表的语句。
  • --no-create-info 选项 mysqldump抑制 CREATE输出中的语句，以便转储文件仅包含表数据。

示例：

要分别转储数据库的表定义和数据 test，请使用以下命令：

mysqldump --no-data test > dump-defs.sql		#只存表结构
mysqldump --no-create-info test > dump-data.sql		#只存表数据

对于仅定义转储，添加 --routines 和 --events 选项以还包括存储的例程和事件定义：

mysqldump --no-data --routines --events test > dump-defs.sql

使用 mysqldump 测试升级不兼容性​

当考虑升级 MySQL 时，明智的做法是与当前的生产版本分开安装较新的版本。然后，从生产服务器转储数据库和数据库对象定义，并将它们加载到新服务器中，以验证它们是否得到正确处理。（这对于测试降级也很有用。）

在生产服务器上：

mysqldump --all-databases --no-data --routines --events > dump-defs.sql

在升级后的服务器上：

mysql < dump-defs.sql

由于转储文件不包含表数据，因此可以快速处理。这使您能够发现潜在的不兼容性，而无需等待冗长的数据加载操作。在处理转储文件时查找警告或错误。

验证定义已正确处理后，转储数据并尝试将其加载到升级后的服务器中。

在生产服务器上：

mysqldump --all-databases --no-create-info > dump-data.sql

在升级后的服务器上：

mysql < dump-data.sql

现在检查表内容并运行一些测试查询。