【MySQL 技术专题】「问题实战系列」深入探索和分析 MySQL 数据库的数据备份和恢复实战开发指南（备份 + 恢复篇）
MySQL 数据库备份
MySQL 的数据库备份主要分为两种：全量备份和增量备份。全量备份是指对整个数据库进行完全备份，包括所有的数据和对象。这种备份方法适用于数据库规模较小或者对备份恢复速度要求较高的情况。
而增量备份是指在全量备份的基础上，只备份发生了变化的数据和对象。这种备份方法可以大大减少备份的时间和存储空间，适用于数据库规模较大或者对备份频率要求较高的情况。增量备份可以通过记录修改日志或者使用数据库的存储引擎来实现。
无论是全量备份还是增量备份，都可以提供可靠的数据保护，并在系统故障或数据损坏时进行快速恢复。因此，在进行数据库备份时，需要根据实际情况选择适合的备份策略，并确保备份文件的安全存储和定期测试恢复的可行性。
全量备份
全量备份是对数据库进行完全备份的过程，包括所有表、记录和对象。在 MySQL 中，可以使用不同的命令进行全量备份。
以使用 InnoDB 引擎的数据库为例，备份所有库中的全部数据的命令如下：
备份一个名为 dadong 的库中的所有数据的命令如下：
在执行备份命令时，常用的选项包括--master-data=2 和--single-transaction。--master-data=2 会在备份文件中添加主从复制所需的信息，以便在恢复时进行主从同步。--single-transaction 使用事务来确保备份数据的一致性。
为了提高备份效率和减少备份文件的大小，可以对备份文件进行压缩，如使用 gzip 命令将备份文件压缩至/opt/all.sql.gz。
全量备份是一种重要的数据保护手段，但也需要注意备份文件的安全存储和定期测试恢复的可行性，以确保在系统故障或数据损坏的情况下能够快速恢复数据。
全量备份应用场景
数据库迁移或升级：使用全量备份可以将原数据库的完整数据备份到目标服务器，确保数据的完整性和一致性。
增加从库：通过全量备份将主库的数据备份到从库，建立数据一致性和冗余备份，提高数据库的可用性和容错性。
数据库数据破坏时：如果执行 DDL 或 DML 语句破坏了数据完整性，主从库无法正常工作。可以使用全量备份将数据库还原到执行 DDL 或 DML 语句之前的状态，恢复数据完整性。
跨机房灾备：当数据库所在的机房遭受灾难或故障时，为了保证数据的安全和可用性，可使用全量备份将数据库的完整数据复制到具备异地容灾条件的机房，实现跨机房的数据备份和灾难恢复。
硬件故障或删除物理文件导致的数据故障处理：在这种情况下，可以关闭主库，配置从库的 VIP 等相关设置，然后启动从库提供服务，无需使用备份数据进行恢复。
增量备份
增量数据：指在上一次全量备份数据之后到下一次全备份之间的数据库更新数据。在使用 mysqldump 命令进行全量备份时，增量数据就是 MySQL 的二进制日志（binlog）。
在进行全量备份后，如果数据库发生了更新操作，比如插入、更新或删除记录，这些操作将被记录在 binlog 中。二进制日志包含了对数据库的逐个修改操作，通过解析二进制日志，可以提取出这些增量数据。这些增量数据可以用来恢复数据库到最新的状态，或者用于在备份之间复制数据以保持数据的一致性。
简而言之，增量数据就是在全量备份之后进行的数据更新操作所产生的数据库变化记录，通过解析和应用这些记录，可以实现数据库的增量恢复和数据同步。
binlog
binlog 是二进制日志的简称，是 MySQL 数据库引擎用于记录数据库更新操作的一种日志文件格式。binlog 文件记录了用户对数据库进行的更新操作，例如插入、修改、删除表记录等 SQL 语句信息，但不记录对数据库的查询操作。
binlog 以二进制格式存储，不能通过查看文本工具（如 cat、vi 等）直接查看，需要使用 mysqlbinlog 工具进行解析和查看。
简而言之，binlog 是 MySQL 数据库引擎用于记录用户对数据库进行的更新操作的二进制日志文件，它能够记录数据变化的 SQL 语句信息，但不包括查询语句。要查看 binlog 内容，需要使用专门的工具 mysqlbinlog 进行解析。
binlog 主要作用
binlog 的作用主要是用于数据库的主从复制和数据的增量恢复，它记录了数据库的新增、删除和修改操作，可以实现数据的备份、负载均衡和故障恢复等功能。
binlog 的作用主要有两个方面
主从复制：在数据库主从复制中，主数据库将生成的 binlog 文件传输给从数据库，从数据库通过解析和应用 binlog 文件中记录的 SQL 语句，保持与主数据库的数据一致性。这样可以实现数据的备份、负载均衡和故障恢复等功能。
数据恢复：当数据库发生故障时，可以使用 binlog 日志进行数据的增量恢复。通过解析 binlog 文件中的 SQL 语句，可以重新执行这些操作，将数据恢复到故障发生之前的状态，保证数据的完整性。
开启 binlog 日志功能
MySQL 的配置文件 my.cnf 中，可以通过增加 log_bin 参数来开启 binlog 日志，并可以通过赋值来指定 binlog 日志的文件名。
要开启 MySQL 的 binlog 日志步骤
打开 MySQL 的配置文件 my.cnf（路径通常为/etc/my.cnf 或/etc/mysql/my.cnf）。
在文件中找到或添加以下行：
将/path/to/binlog/file替换为你希望保存 binlog 日志的文件路径和名称。确保所指定的路径存在，并具有适当的权限。
保存并关闭 my.cnf 文件。
重启 MySQL 服务器，使配置生效。可以使用以下命令重启 MySQL 服务：
确认 binlog 日志已成功开启。可以使用以下命令登录到 MySQL 控制台：
注意要替换<username>为你的 MySQL 用户名。
在 MySQL 控制台中，运行以下命令查看 binlog 配置信息：
如果输出结果中的Value字段显示为ON，则表示 binlog 日志已成功开启。
mysqlbinlog
mysqlbinlog 是 MySQL 数据库中的一个命令行工具，用于解析和展示 binlog 文件中的日志事件。
使用 mysqlbinlog 可以完成以下任务：
查看 binlog 内容：可以通过 mysqlbinlog 命令直接读取和展示 binlog 文件中的日志事件，包括数据库的更改操作。
数据恢复：可以使用 mysqlbinlog 将 binlog 文件中的日志事件应用到数据库中，从而实现数据的恢复和还原。
数据审计和分析：可以通过解析 binlog 文件并将其输出为可读的文本格式，用于数据审计、故障排查和性能分析等。
数据同步和复制：可以使用 mysqlbinlog 将 binlog 文件中的日志事件传输到另一个 MySQL 实例，实现数据库之间的数据同步和复制。
mysqlbinlog 提供了丰富的参数选项，使其可以适应各种场景和需求。例如，可以指定要解析的 binlog 文件、指定输出格式、指定时间范围等。
mysqlbinlog 的使用案例
这个例子展示了如何利用 mysqlbinlog 命令将 binlog 文件解析到 bin.log 文件中。
mysqlbinlog 命令可以通过使用
-d：来指定要解析的数据库，例如指定解析 dadong 数据库。
-r：用于将解析后的 binlog 输出成 SQL 语句，并指定生成的文件名为 bin.log。
使用 mysqlbinlog 的-d 参数可以实现按数据库导出 binlog 的功能。需要注意的是，如果使用了-d 参数，更新数据时必须在 SQL 语句中包含 use 库名的语句，这样才能正确分出指定数据库的 binlog。
例如，插入数据的语句应采用以下格式：
全量备份与增量备份结合
下面以mysqldump命令和 binlog 日志增量数据为例，讲解企业中按天全备和按周全备的方法。
按天全备与增量备份
对于按天全备，可以使用mysqldump命令来导出整个数据库的备份。下面是一个示例命令：
这个命令会导出所有数据库的备份，并保存到backup.sql文件中。你需要将username和password替换为实际的 MySQL 数据库的用户名和密码。
对于按周全备，可以使用mysqldump命令结合 binlog 日志的增量备份。首先，执行全备份命令：
然后，使用mysqlbinlog命令将 binlog 日志解析出增量数据，追加到全备份文件中：
这个命令会将 binlog 日志文件解析为 SQL 语句，并通过管道传递给mysql命令执行。你需要将binlog-file替换为实际的 binlog 日志文件路径。
通过以上方法，可以实现按天全备和按周全备的数据库备份。这样可以确保数据备份的完整性和及时性，以应对潜在的数据丢失和灾难恢复情况。
按天全备的特点
优点：
恢复数据时所需的数据文件数量较少，因此恢复时间较短。
维护成本较低，不需要频繁处理差异数据。
缺点：
每天进行全备份，占用大量存储空间。
备份过程会消耗系统资源，可能会对用户体验造成影响。
对于中小型企业而言，按天全备是最常用的备份策略。根据存储空间的情况，可以保留一定数量的全量备份，例如仅保留最近 7 天的备份数据。对于重要的企业数据，还可以考虑使用磁带机等设备将备份数据存档至一年以上。
针对 binlog 增量数据的清理，可以在 my.cnf 配置文件中通过设置"过期清理天数"相关参数（例如 expire_logs_days=7）来实现。这样可以保留最近 7 天的 binlog 日志。理论上，如果每天进行全备份，那么只需保留一天的 binlog 日志即可满足需求。
按周全备与增量备份
按周全备特点
优点：
每周仅有一个完整备份，因此占用磁盘总空间大小较小。
备份次数较少，占用系统资源较少，提供更好的用户体验。
缺点：
恢复时需要处理的数据文件较多，增加了恢复的复杂度。
维护成本较高，恢复时间可能较长。
对于大型企业来说，由于数据量特大，每天进行全备可能需要很长时间。因此，采用按周全备的策略可以节省数据存储空间，并且不会对用户访问数据库的体验产生太大的影响。
MySQL 的备份方式
逻辑备份
逻辑备份是通过将 MySQL 数据库中的数据（包括库表）以 SQL 语句的形式直接输出或生成备份文件的方式进行备份，其中使用 mysqldump 命令实现。
逻辑备份具有如下特点
优点：
操作简单、方便和可靠。
备份的数据可以在不同的平台、版本、软件和操作系统之间迁移和恢复。
可实现对分库分表的备份。
缺点：
备份速度比物理备份慢。
恢复效率相对较低。
常用的工具包括
mysqldump：MySQL 官方自带的最常用逻辑备份工具，可实现对分表分库的备份。
mydumper：一个在 GPL 许可下发布的高性能 MySQL 备份和恢复工具集。
逻辑备份在以下企业应用场景中适用：
数据量不是特别大的场景，例如备份前的数据不超过 30GB。这个参考值主要考虑备份效率与管理员使用复杂度间的平衡。
然而，当存在跨版本、跨软件的升级或数据迁移时，物理备份通常无法使用。此时，通常会使用 mysqldump 工具进行数据处理，例如：
恢复数据到测试库。
通过执行 SQL 语句人为删除数据。
在主从复制中使用。
案例：备份多个表
可以使用以下语法来备份多个表：
例如，备份库 1 中的表 1、表 2 和表 3 可以使用以下命令：
同样地，备份库 2 中的表 1、表 2 和表 3 可以使用以下命令：
备份一个或多个表的语法如下：
备份一个或多个库的语法如下：
备份所有数据库的语法如下：
以上是备份多个表和多个库的语法和用法。你可以根据实际需求进行相应的调整和使用。
压缩和解压备份
以下是压缩备份和解压备份的代码示例：
压缩备份：
这将使用mysqldump工具备份数据库dadong并将结果压缩为/opt/t.sql.gz文件。
解压备份：
这将解压缩/opt/t.sql.gz文件并将结果保存为t1.sql文件。另外，你还可以使用以下命令直接解压缩并删除源文件：
这会将t.sql.gz解压缩为t.sql文件，并删除原始的压缩文件。
请确保在操作过程中，确认目标路径和文件名与你需要的一致，并根据需要调整命令行中的数据库名称和文件路径。
mysqldump 重要参数说明
下面是对 mysqldump 命令中一些重要参数的说明：
-B, --database:
这个参数会在备份的数据中包含建库（create）以及"use 库"的语句。可以同时备份多个库，只需要在参数后面直接添加多个库名。使用-B 参数可以在数据库恢复时自动创建建表语句。
-A, --all-databases:
备份所有的数据库。
-F, --flush-logs:
刷新 binlog 日志，生成新的 binlog 文件。在将来进行增量恢复时，可以从这个新的 binlog 文件开始。如果备份多个库，每个库都会刷新一次 binlog。如果只想刷新一次 binlog，可以加上--lock-all-tables 或--master-data 参数。
-x, --lock-all-tables:
在备份过程中，对所有数据库的表执行全局读取锁定操作，同时禁用-single-transaction 和--lock-tables 参数的功能。
-l, --lock-tables:
将所有的表锁定为只读。
--single-transaction:
备份 InnoDB 引擎的数据表时，通常会启用此选项，以获得一致性的数据快照备份。这个选项将设置备份会话的隔离级别为可重复读，并将整个备份过程放在一个事务中，以确保备份期间不会看到其他连接会话中已提交的数据。也就是说，备份开始时刻的数据是什么样的，备份结果就是什么样的。相当于对表进行加锁备份数据。但是，此参数允许备份期间进行写入操作，与使用-x 参数锁定表不同，后者在备份期间不允许写入操作。启用此参数会关闭--lock-tables 选项。
-R, --routines:
备份存储过程和函数数据。
-d, --no-data:
只备份库表结构（以 SQL 语句形式），不包含行数据。
-t, --no-create-info:
只备份表内行数据（以 SQL 语句形式），不包含表结构。
-T, --tab=name:
将库表和数据分离成不同的文件，行数据以纯文本形式存储，表结构以 SQL 语句形式存储。默认情况下，这个功能在 5.6 版本中是没有被授权的，需要修改 my.cnf 文件的参数。
--master-data={1|2}:
在备份结果中包含 binlog 日志文件名及对应的 binlog 位置点（即"change master"命令）。--master-data=2 会记录备份时刻的二进制日志位置，并将其注释掉。而--master-data=1 则不会注释掉。
--triggers:
备份触发器数据。
--compact:
只显示少量有用的输出，适用于学习和测试环境调试。
single-transaction
当使用 mysqldump 的--single-transaction 选项备份 InnoDB 表时，会开启一个事务并将整个备份过程放入其中。这样做可以确保在备份过程中不会看到其他连接已提交的数据变更。换句话说，在备份开始时刻的数据状态就是备份结果中的数据状态，就好像表被锁定一样。但需要注意的是，这个选项允许在备份期间进行数据写入，与使用-x 选项锁定表并禁止写入的情况不同。
以下是一个操作示例：
物理备份
冷备份
一种常见的物理备份方法是使用 cp、rsync、tar、scp 等复制工具将 MySQL 数据文件复制成多份。然而，直接复制的方式备份会引起数据丢失，因为在备份期间数据仍可以进行写入操作。
另外，在恢复数据库时，对新数据库的路径和配置也有要求，通常需要与原数据库的配置保持一致（包括版本、路径和配置选项），以确保数据正常恢复。通常情况下，人工停库或锁库后再进行物理复制是保证备份期间数据一致性的方法。然而，在生产环境中，通常不允许停机或锁表，除非事先申请停机或锁表时间。因此，使用传统的 Linux 命令拷贝工具进行冷备份是一种粗放的方式，应尽量避免使用。在进行大规模数据库迁移时，先停机然后进行物理迁移是一种高效的解决方案。
热备份
除了直接通过 Linux 命令行复制 MySQL 数据文件，还存在一些第三方的开源或商业物理热备份工具，如 xtrabackup。使用这些工具可以实现物理全备份和增量备份。热备份是在不影响用户体验的情况下进行的备份方法。这些工具通常提供了更灵活和可靠的备份机制，可以确保数据的一致性，并能够有效地进行增量备份，减少备份时间和存储空间的占用。
物理备份的特点
物理备份与逻辑备份相比，具有以下优点和缺点。在企业中，根据需求，可以互补使用这两种备份方法。
优点
速度快：物理备份通过直接复制数据文件，备份过程简单高效，可以快速完成备份任务。
效率高：由于物理备份是直接复制数据文件，不需要解析数据，备份和恢复的效率较高。
缺点：
不容易跨平台、跨版本、跨软件、跨操作系统：物理备份的数据文件格式通常与特定的数据库版本、软件和操作系统相关，无法直接在其他平台或软件上进行恢复操作。
恢复过程复杂：由于物理备份涉及到数据库的路径、配置等细节，恢复时需要确保路径和配置与原数据库一致，这增加了恢复的复杂度。
使用较复杂：物理备份可能需要借助第三方工具或命令行操作，相对于逻辑备份而言使用起来可能更复杂一些。
物理备份常用工具：
在 Linux 环境下，常用的物理备份工具有以下几种：
cp：是一个命令行工具，用于复制文件和目录。可以通过将数据库文件复制到备份目录来进行物理备份。
tar：是一个常用的压缩和打包工具，在备份时可以使用 tar 将数据库文件打包到备份文件中进行备份。
xtrabackup：是一个基于 InnoDB 存储引擎的开源热备份工具。与传统的冷备份工具不同，xtrabackup 可以在数据库运行时进行备份，不需要停止数据库服务。
优化建议
对于总数据量超过 30GB 的数据库，可以考虑使用 xtrabackup 进行热备份，以提高备份的效率。
对于不希望影响数据库服务的情况，可以选择在数据库的从库上进行备份。在备份时，可以停止 SQL 线程从应用中读取数据，然后使用 cp 或 tar 等工具将数据库文件打包备份。这种方案称为冷备份，可以保证备份过程不影响数据库的正常服务。
MySQL 数据库恢复
在进行数据库恢复时，需要注意以下事项：
字符集关联：字符集的正确设置对于数据恢复至关重要。如果字符集设置不正确，恢复的数据可能会出现乱码问题。确保在恢复过程中，使用正确的字符集配置，以避免乱码问题的发生。
使用 MySQL 命令和 source 命令进行恢复：MySQL 命令和 source 命令是常用的数据库恢复工具。它们的原理是将备份文件中的 SQL 语句重新执行到数据库中，从而实现数据恢复的过程。在使用这些命令时，需要确保备份文件的 SQL 语句正确无误，并且执行过程中没有错误或中断。
优化建议
在进行数据库恢复之前，确保备份文件的字符集与目标数据库的字符集一致。这样可以避免因字符集不匹配而导致的数据乱码问题。
在使用 MySQL 命令或 source 命令恢复数据库时，建议先进行备份文件的验证，确保备份文件完整且没有损坏。可以使用 checksum 等工具对备份文件进行校验，以确保数据的完整性。
在进行数据恢复之前，建议先在测试环境或备份数据库中进行恢复测试，以确保恢复过程的顺利进行，并且恢复后的数据符合预期。
利用 source 命令恢复数据库
要通过 source 命令来恢复数据库，请按照以下步骤执行：
首先，进入 MySQL 数据库控制台。在命令行中输入以下命令并按 Enter 键：
这将使用 root 用户和密码登录到 MySQL 数据库。
登录成功后，使用以下命令切换到目标数据库：
将"数据库"替换为实际的目标数据库名称。
接下来，使用 source 命令来执行脚本文件。脚本文件可以是一个 SQL 文件或者是一个压缩包。如果是压缩包，需要在 MySQL 登录前进入压缩包所在的文件夹。
将"back_db.sql"替换为实际的脚本文件名称。
请注意，如果脚本文件不在当前路径下，需要提供脚本文件的完整路径。
通过以上步骤，您将能够使用 source 命令成功恢复数据库。记得在执行任何数据库恢复操作之前，确保先备份您的数据以确保安全。
MySQLDump 恢复数据库
在执行使用 gzip 解压缩之前，请先确保已经备份了数据库以确保安全。以下是使用 gzip 解压缩的步骤：
备份数据库并创建测试数据。在终端中执行以下命令：
这将备份名为"dadong"的数据库并将其输出到/opt/backup.sql.gz 文件中。
解压缩压缩包。在终端中执行以下命令：
这将解压缩/opt/backup.sql.gz 文件，并生成一个名为 backup.sql 的解压缩后的文件。
恢复数据库。在终端中执行以下命令：
这将使用 mysql 命令将 backup.sql 文件中的数据库恢复到 MySQL 中。
请注意，gzip 命令的参数：
-c 将输出写入标准输出，并保留原始文件。
-d 解压缩压缩文件。
如果使用 mysqldump 导出数据库时指定了-B 参数，则在恢复时无需特定库名。因为-B 参数会生成包含"use backup"和"create database backup"的脚本，这类似于在恢复时执行"use backup"命令。
使用以上步骤，您将能够成功使用 gzip 解压缩并恢复数据库。请谨慎操作，并在执行任何数据库操作前先备份数据以确保安全。
System 指令
注：如果不想退出 MySQL，在执行恢复语句时可以使用系统命令。
在 MySQL 命令行中，执行以下命令将当前目录切换到/root 目录:
以上是执行在 MySQL 命令行中切换到指定目录、退出 MySQL、确认当前目录以及进行数据库备份并压缩的步骤。使用"--single-transaction"参数可以确保备份过程中不会锁定表，提高备份的稳定性和一致性。
使用 zcat 读取压缩包数据（直接导入数据库）
下节预告
综上所述基本上完成了 MySQL 数据库的数据备份和恢复实现，下一节会面向物理备份的恢复实战，对于“【MySQL 技术专题】「问题实战系列」深入探索和分析 MySQL 数据库的数据备份和恢复实战开发指南（xtrabackup 恢复指南）”。
🏆 InfoQ写作平台-签约作者 🏆 2020-03-25 加入
【出版书籍】《深入浅出Java虚拟机—JVM原理与实战》 【个人简介】酷爱计算机科学、醉心编程技术、热衷悬疑推理的“极客达人” 【技术范畴】Java领域、Spring生态、MySQL专项、微服务/分布式体系和算法设计等
评论