MySQL8 中文参考（三十五）

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

原文：dev.mysql.com/doc/refman/8.0/en/selinux-context-mysqld-tcp-port.html

8.7.5.1 为 mysqld 设置 TCP 端口上下文

默认的 TCP 端口mysqld是3306；而使用的 SELinux 上下文类型是mysqld_port_t。

如果您配置mysqld使用不同的 TCP port，您可能需要为新端口设置上下文。例如，为非默认端口（如端口 3307）定义 SELinux 上下文：

semanage port -a -t mysqld_port_t -p tcp 3307

确认端口已添加：

$> semanage port -l | grep mysqld
mysqld_port_t                  tcp      3307, 1186, 3306, 63132-63164

原文：dev.mysql.com/doc/refman/8.0/en/selinux-context-mysql-feature-ports.html

8.7.5.2 设置 MySQL 功能的 TCP 端口上下文

如果启用了某些 MySQL 功能，您可能需要为这些功能使用的额外端口设置 SELinux TCP 端口上下文。如果 MySQL 功能使用的端口没有正确的 SELinux 上下文，这些功能可能无法正常运行。

以下各节描述了如何为 MySQL 功能设置端口上下文。通常，可以使用相同的方法为任何 MySQL 功能设置端口上下文。有关 MySQL 功能使用的端口信息，请参考 MySQL 端口参考。

从 MySQL 8.0.14 到 MySQL 8.0.17，必须将 mysql_connect_any SELinux 布尔值设置为 ON。从 MySQL 8.0.18 开始，不再需要或建议启用 mysql_connect_any。

setsebool -P mysql_connect_any=ON

设置 Group Replication 的 TCP 端口上下文

如果启用了 SELinux，您必须为 Group Replication 使用的通信端口设置端口上下文，该端口由 group_replication_local_address 变量定义。mysqld 必须能够绑定到 Group Replication 通信端口并在那里监听。InnoDB Cluster 依赖于 Group Replication，因此这同样适用于集群中使用的实例。要查看当前由 MySQL 使用的端口，请执行：

semanage port -l | grep mysqld

假设 Group Replication 通信端口为 33061，请通过以下方式设置端口上下文：

semanage port -a -t mysqld_port_t -p tcp 33061

设置 Document Store 的 TCP 端口上下文

如果启用了 SELinux，您必须为 X Plugin 使用的通信端口设置端口上下文，该端口由 mysqlx_port 变量定义。mysqld 必须能够绑定到 X Plugin 通信端口并在那里监听。

假设 X Plugin 通信端口为 33060，请通过以下方式设置端口上下文：

semanage port -a -t mysqld_port_t -p tcp 33060

设置 MySQL Router 的 TCP 端口上下文

如果启用了 SELinux，您必须为 MySQL Router 使用的通信端口设置端口上下文。假设 MySQL Router 使用的额外通信端口是默认的 6446、6447、64460 和 64470，在每个实例上通过以下方式设置端口上下文：

semanage port -a -t mysqld_port_t -p tcp 6446
semanage port -a -t mysqld_port_t -p tcp 6447
semanage port -a -t mysqld_port_t -p tcp 64460
semanage port -a -t mysqld_port_t -p tcp 64470

8.7.6 故障排除 SELinux

原文：dev.mysql.com/doc/refman/8.0/en/selinux-troubleshooting.html

诊断 SELinux 通常涉及将 SELinux 置于宽容模式，重新运行有问题的操作，在 SELinux 审计日志中检查访问拒绝消息，并在问题解决后将 SELinux 放回强制模式。

为了避免使用 setenforce 将整个系统置于宽容模式，您可以通过使用 semanage 命令将其 SELinux 域（mysqld_t）置于宽容模式，只允许 MySQL 服务以宽容模式运行：

semanage permissive -a mysqld_t

在完成故障排除后，请使用以下命令将 mysqld_t 域放回强制模式：

semanage permissive -d mysqld_t

SELinux 将拒绝操作的日志写入 /var/log/audit/audit.log。您可以通过搜索“denied”消息来检查拒绝。

grep "denied" /var/log/audit/audit.log

以下部分描述了可能遇到与 SELinux 相关问题的几个常见领域。

文件上下文

如果 MySQL 目录或文件具有不正确的 SELinux 上下文，则可能会拒绝访问。如果 MySQL 配置为从非默认目录或文件读取或写入，则可能会出现此问题。例如，如果您配置 MySQL 使用非默认数据目录，则该目录可能没有预期的 SELinux 上下文。

尝试在具有无效 SELinux 上下文的非默认数据目录上启动 MySQL 服务会导致以下启动失败。

$> systemctl start mysql.service
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.

在这种情况下，将“拒绝”消息记录到 /var/log/audit/audit.log 中：

$> grep "denied" /var/log/audit/audit.log
type=AVC msg=audit(1587133719.786:194): avc:  denied  { write } for  pid=7133 comm="mysqld"
name="mysql" dev="dm-0" ino=51347078 scontext=system_u:system_r:mysqld_t:s0
tcontext=unconfined_u:object_r:default_t:s0 tclass=dir permissive=0

有关为 MySQL 目录和文件设置正确的 SELinux 上下文的信息，请参见 第 8.7.4 节，“SELinux 文件上下文”。

端口访问

SELinux 期望诸如 MySQL 服务器之类的服务使用特定端口。更改端口而不更新 SELinux 策略可能会导致服务失败。

mysqld_port_t 端口类型定义了 MySQL 监听的端口。如果将 MySQL 服务器配置为使用非默认端口，例如端口 3307，并且不更新策略以反映更改，则 MySQL 服务无法启动：

$> systemctl start mysqld.service
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.

在这种情况下，拒绝消息被记录到 /var/log/audit/audit.log 中：

$> grep "denied" /var/log/audit/audit.log
type=AVC msg=audit(1587134375.845:198): avc:  denied  { name_bind } for  pid=7340
comm="mysqld" src=3307 scontext=system_u:system_r:mysqld_t:s0
tcontext=system_u:object_r:unreserved_port_t:s0 tclass=tcp_socket permissive=0

有关为 MySQL 设置正确的 SELinux 端口上下文的信息，请参见 第 8.7.5 节，“SELinux TCP 端口上下文”。启用使用未定义所需上下文的端口的 MySQL 功能时，可能会出现类似的端口访问问题。有关更多信息，请参见 第 8.7.5.2 节，“为 MySQL 功能设置 TCP 端口上下文”。

应用程序更改

SELinux 可能不知道应用程序的更改。例如，新版本、应用程序扩展或新功能可能以 SELinux 不允许的方式访问系统资源，导致访问被拒绝。在这种情况下，您可以使用audit2allow实用程序创建自定义策略以允许必要的访问。创建自定义策略的典型方法是将 SELinux 模式更改为宽容模式，识别 SELinux 审计日志中的访问拒绝消息，并使用audit2allow实用程序创建自定义策略以允许访问。

有关使用audit2allow实用程序的信息，请参考您发行版的 SELinux 文档。

如果您遇到 MySQL 的访问问题，认为应该由标准 MySQL SELinux 策略模块处理，请在您发行版的错误跟踪系统中提交错误报告。

8.8 FIPS 支持

原文：dev.mysql.com/doc/refman/8.0/en/fips-mode.html

MySQL 支持 FIPS 模式，如果使用 OpenSSL 3.0 或 OpenSSL 1.0.2 进行编译，并且在运行时可用 OpenSSL 库和 FIPS 对象模块。

服务器端的 FIPS 模式适用于服务器执行的加密操作。这包括复制（源/副本和组复制）和运行在服务器内部的 X 插件。FIPS 模式还适用于客户端尝试连接到服务器。

以下各节描述了 FIPS 模式以及如何在 MySQL 中利用它：

• FIPS 概述

• MySQL 中 FIPS 模式的系统要求

• 在 MySQL 中配置 FIPS 模式

FIPS 概述

联邦信息处理标准 140-2（FIPS 140-2）描述了一种安全标准，联邦（美国政府）机构可能要求用于保护敏感或有价值信息的加密模块。要被视为适用于此类联邦用途，加密模块必须获得 FIPS 140-2 认证。如果旨在保护敏感数据的系统缺乏适当的 FIPS 140-2 证书，联邦机构将无法购买该系统。

诸如 OpenSSL 之类的产品可以在 FIPS 模式下使用，尽管 OpenSSL 库本身未经 FIPS 验证。相反，OpenSSL 库与 OpenSSL FIPS 对象模块一起使用，以使基于 OpenSSL 的应用程序能够在 FIPS 模式下运行。

对于 FIPS 及其在 OpenSSL 中的实现的一般信息，这些参考资料可能会有所帮助：

• 国家标准与技术研究所 FIPS PUB 140-2

• OpenSSL FIPS 140-2 安全策略

• fips_module 手册页面

重要

FIPS 模式对加密操作施加条件，例如对可接受的加密算法的限制或对更长密钥长度的要求。对于 OpenSSL，确切的 FIPS 行为取决于 OpenSSL 版本。

MySQL 中 FIPS 模式的系统要求

要使 MySQL 支持 FIPS 模式，必须满足以下系统要求：

• 在构建时，MySQL 必须使用 OpenSSL 进行编译。如果编译使用与 OpenSSL 不同的 SSL 库，则无法在 MySQL 中使用 FIPS 模式。

  此外，MySQL 必须使用经过 FIPS 认证的 OpenSSL 版本进行编译。OpenSSL 1.0.2 和 OpenSSL 3.0 已经获得认证，但 OpenSSL 1.1.1 没有。最近版本的 MySQL 的二进制发行版在某些平台上使用 OpenSSL 3.0.9 编译，这意味着它们未经 FIPS 认证。这会导致根据系统和 MySQL 配置的不同而产生可用 MySQL 功能的权衡：

  • 使用具有 OpenSSL 1.0.2 和所需的 FIPS 对象模块的系统。在这种情况下，如果您使用使用 OpenSSL 1.0.2 编译的二进制发行版，或者使用 OpenSSL 1.0.2 从源代码编译 MySQL，则可以为 MySQL 启用 FIPS 模式。但是，在这种情况下，您不能使用需要 OpenSSL 1.1.1 的 TLSv1.3 协议或密码套件。此外，您正在使用于 2019 年底达到生命周期终点的 OpenSSL 版本。

  • 使用具有 OpenSSL 1.1.1 或更高版本的系统。在这种情况下，您可以使用二进制包安装 MySQL，并且可以使用 TLSv1.3 协议和密码套件，以及其他已支持的 TLS 协议。但是，您不能为 MySQL 启用 FIPS 模式。

  • 使用具有 OpenSSL 3.0 和所需的 FIPS 对象模块的系统。在这种情况下，如果您使用使用 OpenSSL 3.0 编译的二进制发行版，或者使用 OpenSSL 3.0 从源代码编译 MySQL，则可以为 MySQL 启用 FIPS 模式。可以直接通过 OpenSSL 3.0 配置文件处理 FIPS 模式，而不是使用服务器端系统变量和客户端选项（自 MySQL 8.0.34 起已弃用）。当使用 OpenSSL 3.0 编译 MySQL，并且在运行时可用 OpenSSL 库和 FIPS 对象模块时，服务器会读取 OpenSSL 配置文件，并尊重设置使用 FIPS 提供程序的首选项。

    有关升级到 OpenSSL 3.0 的一般信息，请参阅 OpenSSL 3.0 迁移指南。

• 在运行时，必须将 OpenSSL 库和 OpenSSL FIPS 对象模块作为共享（动态链接）对象可用。可以构建静态链接的 OpenSSL 对象，但 MySQL 无法使用它们。

FIPS 模式已在 EL7 上对 MySQL 进行了测试，但可能也适用于其他系统。

如果您的平台或操作系统提供 OpenSSL FIPS 对象模块，则可以使用它。否则，您可以从源代码构建 OpenSSL 库和 FIPS 对象模块。请参阅 fips_module 手册中的说明（参见 FIPS 概述）。

在 MySQL 中配置 FIPS 模式

注意

从 MySQL 8.0.34 开始，本节中描述的服务器端和客户端选项已弃用。

MySQL 在服务器端和客户端启用 FIPS 模式的控制：

• ssl_fips_mode 系统变量控制服务器是否在 FIPS 模式下运行。

• --ssl-fips-mode客户端选项控制特定 MySQL 客户端是否在 FIPS 模式下运行。

ssl_fips_mode系统变量和--ssl-fips-mode客户端选项允许这些值：

• OFF：禁用 FIPS 模式。

• ON：启用 FIPS 模式。

• STRICT：启用“严格” FIPS 模式。

在服务器端，数值型ssl_fips_mode的值为 0、1 和 2 分别等同于OFF、ON和STRICT。

重要

一般来说，STRICT比ON施加更多限制，但 MySQL 本身除了向 OpenSSL 指定 FIPS 模式值外，没有 FIPS 特定的代码。对于ON或STRICT的 FIPS 模式的确切行为取决于 OpenSSL 版本。有关详细信息，请参考fips_module手册页（参见 FIPS 概述）。

注意

如果 OpenSSL FIPS 对象模块不可用，则ssl_fips_mode和--ssl-fips-mode的唯一允许值为OFF。尝试将 FIPS 模式设置为其他值会导致错误。

服务器端的 FIPS 模式适用于服务器执行的加密操作。这包括复制（源/副本和组复制）和运行在服务器内部的 X 插件。

FIPS 模式也适用于客户端尝试连接到服务器的情况。当启用时，在客户端或服务器端，它限制了可以选择的支持加密密码。然而，启用 FIPS 模式并不要求必须使用加密连接，或者必须加密用户凭据。例如，如果启用了 FIPS 模式，就需要更强的加密算法。特别是，MD5 被限制，因此尝试使用像RC4-MD5这样的加密密码建立加密连接是行不通的。但是，FIPS 模式并不阻止建立非加密连接。 （为此，您可以为特定用户帐户使用CREATE USER或ALTER USER的REQUIRE子句，或设置require_secure_transport系统变量以影响所有帐户。）

第九章 备份和恢复

原文：dev.mysql.com/doc/refman/8.0/en/backup-and-recovery.html

目录

9.1 备份和恢复类型

9.2 数据库备份方法

9.3 备份和恢复策略示例

9.3.1 制定备份策略

9.3.2 使用备份进行恢复

9.3.3 备份策略摘要

9.4 使用 mysqldump 进行备份

9.4.1 使用 mysqldump 以 SQL 格式导出数据

9.4.2 重新加载 SQL 格式备份

9.4.3 使用 mysqldump 以分隔文本格式导出数据

9.4.4 重新加载分隔文本格式备份

9.4.5 mysqldump 提示

9.5 时间点（增量）恢复

9.5.1 使用二进制日志进行时间点恢复

9.5.2 使用事件位置进行时间点恢复

9.6 MyISAM 表维护和崩溃恢复

9.6.1 使用 myisamchk 进行崩溃恢复

9.6.2 如何检查 MyISAM 表中的错误

9.6.3 如何修复 MyISAM 表

9.6.4 MyISAM 表优化

9.6.5 设置 MyISAM 表维护计划

重要的是备份您的数据库，以便在出现问题时可以恢复数据并重新运行，例如系统崩溃、硬件故障或用户错误删除数据。在升级 MySQL 安装之前备份也是必不可少的保障，它们可以用于将 MySQL 安装转移到另一个系统或设置复制服务器。

MySQL 提供了多种备份策略供您选择，以选择最适合您安装要求的方法。本章讨论了几个备份和恢复主题，您应该熟悉：

• 备份类型：逻辑备份与物理备份，完整备份与增量备份等。

• 创建备份的方法。

• 包括时间点恢复在内的恢复方法。

• 备份调度，压缩和加密。

• 表维护，以便恢复损坏的表。

其他资源

与备份或维护数据可用性相关的资源包括以下内容：

• MySQL Enterprise Edition 的客户可以使用 MySQL Enterprise Backup 产品进行备份。有关 MySQL Enterprise Backup 产品的概述，请参阅 第 32.1 节，“MySQL Enterprise Backup 概述”。

• 专门讨论备份问题的论坛位于forums.mysql.com/list.php?28。

• mysqldump的详细信息可以在第六章，MySQL 程序中找到。

• 此处描述的 SQL 语句的语法在第十五章，SQL 语句中给出。

• 有关InnoDB备份程序的更多信息，请参见第 17.18.1 节，“InnoDB 备份”。

• 复制功能使您能够在多个服务器上保持相同的数据。这有几个好处，比如可以将客户端查询负载分布到多个服务器上，即使某个服务器被下线或失败，数据也是可用的，并且可以通过使用副本进行备份而不影响源数据。参见第十九章，复制。

• MySQL InnoDB 集群是一组产品，它们共同提供高可用性解决方案。可以配置一组 MySQL 服务器以使用 MySQL Shell 创建集群。服务器集群具有一个称为主服务器的单一源，充当读写源。多个次要服务器是源的副本。至少需要三个服务器才能创建高可用性集群。客户端应用程序通过 MySQL Router 连接到主服务器。如果主服务器失败，一个次要服务器将自动晋升为主服务器角色，并且 MySQL Router 将请求路由到新的主服务器。

• NDB 集群提供了适用于分布式计算环境的高可用性、高冗余性版本的 MySQL。请参见第二十五章，MySQL NDB 集群 8.0，其中提供了关于 MySQL NDB 集群 8.0 的信息。

9.1 备份和恢复类型

原文：dev.mysql.com/doc/refman/8.0/en/backup-types.html

本节描述了不同类型备份的特点。

物理（原始）与逻辑备份

物理备份由存储数据库内容的目录和文件的原始副本组成。这种备份适用于需要在出现问题时快速恢复的大型重要数据库。

逻辑备份保存的信息表示为逻辑数据库结构（CREATE DATABASE，CREATE TABLE语句）和内容（INSERT语句或分隔文本文件）。这种类型的备份适用于数据量较小的情况，您可能需要编辑数据值或表结构，或者在不同的机器架构上重新创建数据。

物理备份方法具有以下特点：

• 备份由数据库目录和文件的精确副本组成。通常这是 MySQL 数据目录的全部或部分副本。

• 物理备份方法比逻辑备份更快，因为它们只涉及文件复制而不涉及转换。

• 输出比逻辑备份更紧凑。

• 由于备份速度和紧凑性对于繁忙、重要的数据库至关重要，MySQL Enterprise Backup 产品执行物理备份。有关 MySQL Enterprise Backup 产品的概述，请参见第 32.1 节，“MySQL Enterprise Backup 概述”。

• 备份和恢复的粒度范围从整个数据目录的级别到单个文件的级别。这可能或可能不提供表级粒度，这取决于存储引擎。例如，InnoDB表可以分别存储在单独的文件中，或与其他InnoDB表共享文件存储；每个MyISAM表对应于一组文件。

• 除了数据库，备份还可以包括任何相关文件，如日志或配置文件。

• 通过这种方式备份MEMORY表的数据有些棘手，因为它们的内容不存储在磁盘上。（MySQL Enterprise Backup 产品具有一个功能，可以在备份过程中检索MEMORY表的数据。）

• 备份只能在具有相同或类似硬件特征的其他机器之间进行移植。

• 可以在 MySQL 服务器未运行时执行备份。如果服务器正在运行，则需要执行适当的锁定，以确保服务器在备份过程中不更改数据库内容。对于需要的表，MySQL Enterprise Backup 会���动执行此锁定。

• 物理备份工具包括 MySQL Enterprise Backup 的mysqlbackup用于InnoDB或任何其他表，或文件系统级别的命令（如cp，scp，tar，rsync）用于MyISAM表。

• 恢复时：

  • MySQL Enterprise Backup 可以恢复备份的InnoDB和其他表。

  • ndb_restore 用于恢复NDB表。

  • 在文件系统级别复制的文件可以使用文件系统命令复制回其原始位置。

逻辑备份方法具有以下特点：

• 备份是通过查询 MySQL 服务器获取数据库结构和内容信息完成的。

• 备份比物理方法慢，因为服务器必须访问数据库信息并将其转换为逻辑格式。如果输出写在客户端端，服务器还必须将其发送给备份程序。

• 输出比物理备份大，特别是以文本格式保存时。

• 备份和恢复的粒度可在服务器级别（所有数据库）、数据库级别（特定数据库中的所有表）或表级别进行。这对于存储引擎都是适用的。

• 备份不包括日志或配置文件，或其他不属于数据库的数据库相关文件。

• 存储在逻辑格式中的备份是与机器无关且高度可移植的。

• 逻辑备份是在 MySQL 服务器运行时执行的。服务器不会被停机。

• 逻辑备份工具包括mysqldump程序和SELECT ... INTO OUTFILE语句。这些适用于任何存储引擎，甚至MEMORY。

• 要恢复逻辑备份，可以使用mysql客户端处理 SQL 格式的转储文件。要加载分隔文本文件，请使用LOAD DATA语句或mysqlimport客户端。

在线与离线备份

在线备份是在 MySQL 服务器运行时进行的，以便从服务器获取数据库信息。离线备份是在服务器停止时进行的。这种区别也可以描述为“热”与“冷”备份；“温暖”备份是指服务器保持运行但被锁定以防止修改数据，同时您可以在外部访问数据库文件。

在线备份方法具有以下特点：

• 备份对其他客户端的干扰较小，这些客户端可以在备份期间连接到 MySQL 服务器，并根据需要执行的操作访问数据。

• 必须谨慎地施加适当的锁定，以防止发生会损害备份完整性的数据修改。MySQL Enterprise Backup 产品会自动执行此类锁定。

离线备份方法具有以下特点：

• 客户端可能受到不利影响，因为服务器在备份期间不可用。因此，这种备份通常是从可以脱机而不影响可用性的副本中进行的。

• 备份过程更简单，因为不会受到客户端活动的干扰。

在恢复操作中也存在在线和离线的区别，并且具有类似的特征。然而，客户端更有可能受到在线恢复的影响，而不是在线备份，因为恢复需要更强的锁定。在备份期间，客户端可能能够读取数据，而在恢复数据时，客户端必须被阻止访问数据，因为恢复会修改数据而不仅仅是读取数据。

本地与远程备份

本地备份是在运行 MySQL 服务器的同一主机上执行的，而远程备份是从不同主机执行的。对于某些类型的备份，即使输出是在服务器本地写入的，备份也可以从远程主机启动。

• mysqldump可以连接到本地或远程服务器。对于 SQL 输出（CREATE和INSERT语句），可以进行本地或远程转储，并在客户端生成输出。对于分隔文本输出（使用--tab选项），数据文件将在服务器主机上创建。

• SELECT ... INTO OUTFILE可以从本地或远程客户端主机启动，但输出文件将在服务器主机上创建。

• 物理备份方法通常在 MySQL 服务器主机上本地启动，以便服务器可以脱机，尽管复制文件的目的地可能是远程的。

快照备份

一些文件系统实现允许进行“快照”操作。这些提供了文件系统在特定时间点的逻辑副本，而无需对整个文件系统进行物理复制。（例如，实现可能使用写时复制技术，因此只需要复制快照时间后修改的文件系统的部分。）MySQL 本身不提供进行文件系统快照的功能。可以通过第三方解决方案（如 Veritas、LVM 或 ZFS）来实现。

完全与增量备份

完全备份包括在特定时间点上由 MySQL 服务器管理的所有数据。增量备份包括在给定时间段内对数据所做的更改（从一个时间点到另一个时间点）。MySQL 有不同的方法来执行完全备份，比如在本节中早些时候描述的那些。通过启用服务器的二进制日志，可以实现增量备份，服务器使用该日志记录数据更改。

完全备份与时间点（增量）恢复

完全恢复会从完全备份中恢复所有数据。这将使服务器实例恢复到备份时的状态。如果该状态不够及时，可以在完全恢复后恢复自完全备份以来制作的增量备份，将服务器带到更加及时的状态。

增量恢复是在给定时间段内恢复所做更改。这也被称为按时间点恢复，因为它使服务器的状态保持到给定时间。按时间点恢复基于二进制日志，通常在从备份文件进行完全恢复后进行，将服务器恢复到备份时的状态。然后，二进制日志文件中写入的数据更改将作为增量恢复应用，重新执行数据修改，将服务器带到所需的时间点。

表维护

如果表损坏，数据完整性可能会受到损害。对于InnoDB表，这不是一个典型问题。要检查MyISAM表并在发现问题时修复它们的程序，请参阅第 9.6 节，“MyISAM 表维护和崩溃恢复”。

备份调度、压缩和加密

备份调度对于自动化备份程序非常有价值。备份输出的压缩可以减少空间需求，输出的加密可以提供更好的安全性，防止未经授权访问备份数据。MySQL 本身不提供这些功能。MySQL 企业备份产品可以压缩InnoDB备份，使用文件系统工具可以实现备份输出的压缩或加密。也可以使用其他第三方解决方案。

9.2 数据库备份方法

原文：dev.mysql.com/doc/refman/8.0/en/backup-methods.html

这一部分总结了一些制作备份的一般方法。

使用 MySQL 企业备份进行热备份

MySQL 企业版的客户可以使用 MySQL 企业备份产品对整个实例或选定的数据库、表或两者进行物理备份。该产品包括增量备份和压缩备份的功能。备份物理数据库文件比逻辑技术（如mysqldump命令）快得多。InnoDB表使用热备份机制进行复制。（理想情况下，InnoDB表应该占据大部分数据。）其他存储引擎的表使用温备份机制进行复制。有关 MySQL 企业备份产品的概述，请参见第 32.1 节，“MySQL 企业备份概述”。

使用 mysqldump 进行备份

mysqldump程序可以进行备份。它可以备份所有类型的表。（参见第 9.4 节，“使用 mysqldump 进行备份”。）

对于InnoDB表，可以使用--single-transaction选项在不锁定表的情况下执行在线备份，使用mysqldump。参见第 9.3.1 节，“建立备份策略”。

通过复制表文件进行备份

可以通过复制表文件（*.MYD、*.MYI文件和相关的*.sdi文件）备份 MyISAM 表。为了获得一致的备份，停止服务器或锁定并刷新相关表：

FLUSH TABLES *tbl_list* WITH READ LOCK;

您只需要一个读锁；这使得其他客户端可以继续查询表，同时您可以复制数据库目录中的文件。刷新是必要的，以确保在开始备份之前将所有活动索引页写入磁盘。参见第 15.3.6 节，“LOCK TABLES 和 UNLOCK TABLES 语句”和第 15.7.8.3 节，“FLUSH 语句”。

您还可以通过简单地复制表文件来创建二进制备份，只要服务器没有更新任何内容。（但请注意，如果您的数据库包含InnoDB表，则表文件复制方法不起作用。此外，即使服务器没有活动更新数据，InnoDB可能仍然在内存中缓存修改的数据，而未刷新到磁盘。）

有关此备份方法的示例，请参考 Section 15.2.6, “IMPORT TABLE Statement”中的导出和导入示例。

创建分隔文本文件备份

要创建包含表数据的文本文件，可以使用SELECT * INTO OUTFILE '*file_name*' FROM *tbl_name*。该文件在 MySQL 服务器主机上创建，而不是客户端主机。对于此语句，输出文件不能已经存在，因为允许文件被覆盖构成安全风险。请参阅 Section 15.2.13, “SELECT Statement”。此方法适用于任何类型的数据文件，但仅保存表数据，而不保存表结构。

另一种创建文本数据文件（以及包含用于备份表的CREATE TABLE语句的文件）的方法是使用mysqldump与--tab选项。请参阅 Section 9.4.3, “Dumping Data in Delimited-Text Format with mysqldump”。

要重新加载分隔文本数据文件，请使用LOAD DATA或mysqlimport。

通过启用二进制日志进行增量备份

MySQL 支持使用二进制日志进行增量备份。二进制日志文件提供了您需要的信息，以便复制在您执行备份之后对数据库所做的更改。因此，为了允许服务器恢复到某个时间点，必须在其上启用二进制日志记录，这是 MySQL 8.0 的默认设置；请参阅 Section 7.4.4, “The Binary Log”。

当你想要进行增量备份（包含自上次完整或增量备份以来发生的所有更改）时，你应该通过使用FLUSH LOGS来旋转二进制日志。完成这一步后，你需要将从上次完整或增量备份时刻到倒数第二个时刻的所有二进制日志复制到备份位置。这些二进制日志就是增量备份；在恢复时，你需要按照第 9.5 节，“时间点（增量）恢复” Recovery")中的说明应用它们。下次进行完整备份时，你也应该使用FLUSH LOGS或mysqldump --flush-logs来旋转二进制日志。参见第 6.5.4 节，“mysqldump — 数据库备份程序”。

使用副本进行备份

如果在备份时服务器出现性能问题，一个有助于解决问题的策略是设置复制并在副本上执行备份，而不是在源服务器上执行。参见第 19.4.1 节，“使用复制进行备份”。

如果你正在备份一个副本，你应该在备份副本数据库时备份其连接元数据存储库和应用程序元数据存储库（参见第 19.2.4 节，“中继日志和复制元数据存储库”），无论你选择哪种备份方法，这些信息总是需要的，以便在恢复副本数据后恢复复制。如果你的副本正在复制LOAD DATA语句，你还应该备份副本用于此目的的任何SQL_LOAD-*文件。副本需要这些文件来恢复任何中断的LOAD DATA操作的复制。此目录的位置是系统变量replica_load_tmpdir（从 MySQL 8.0.26 开始）或slave_load_tmpdir（MySQL 8.0.26 之前）的值。如果服务器未使用该变量启动，则目录位置是系统变量tmpdir的值。

恢复损坏的表

如果必须恢复已损坏的 MyISAM 表，请尝试首先使用 REPAIR TABLE 或 myisamchk -r 进行恢复。在 99.9% 的情况下，这应该有效。如果 myisamchk 失败，请参阅 第 9.6 节，“MyISAM 表维护和崩溃恢复”。

使用文件系统快照进行备份。

如果你使用的是 Veritas 文件系统，可以这样进行备份：

1. 从客户端程序执行 FLUSH TABLES WITH READ LOCK。

2. 从另一个 shell 中执行 mount vxfs snapshot。

3. 从第一个客户端执行 UNLOCK TABLES。

4. 从快照中复制文件。

5. 卸载快照。

其他文件系统（如 LVM 或 ZFS）可能具有类似的快照功能。

9.3 备份和恢复策略示例

原文：dev.mysql.com/doc/refman/8.0/en/backup-strategy-example.html

9.3.1 建立备份策略

9.3.2 使用备份进行恢复

9.3.3 备份策略摘要

本节讨论了一种备份执行程序，使您能够在几种崩溃后恢复数据：

• 操作系统崩溃

• 电源故障

• 文件系统崩溃

• 硬件问题（硬盘、主板等）

示例命令不包括像--user和--password这样的选项，用于mysqldump和mysql客户端程序。您应根据需要包含这些选项，以使客户端程序能够连接到 MySQL 服务器。

假设数据存储在支持事务和自动崩溃恢复的InnoDB存储引擎中。同时假设 MySQL 服务器在崩溃时处于负载状态。如果不是这样，就永远不需要恢复。

对于操作系统崩溃或电源故障的情况，我们可以假设 MySQL 的磁盘数据在重新启动后是可用的。由于崩溃，InnoDB数据文件可能不包含一致的数据，但InnoDB会读取其日志，并在其中找到未刷新到数据文件的待处理已提交和未提交事务列表。InnoDB会自动回滚那些未提交的事务，并将已提交的事务刷新到其数据文件中。关于此恢复过程的信息通过 MySQL 错误日志传达给用户。以下是一个示例日志摘录：

InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections

对于文件系统崩溃或硬件问题的情况，我们可以假设在重新启动后 MySQL 磁盘数据不可用。这意味着 MySQL 无法成功启动，因为一些磁盘数据块不再可读。在这种情况下，需要重新格式化磁盘，安装新的磁盘，或者以其他方式纠正潜在问题。然后需要从备份中恢复我们的 MySQL 数据，这意味着备份必须已经制作好。为确保情况如此，设计并实施一个备份策略。

9.3.1 建立备份策略

原文：dev.mysql.com/doc/refman/8.0/en/backup-policy.html

为了有用，备份必须定期安排。在 MySQL 中可以使用几种工具进行完整备份（在某个时间点的数据快照）。例如，MySQL 企业版备份可以执行整个实例的物理备份，并进行优化以最小化开销并在备份InnoDB数据文件时避免中断；mysqldump提供在线逻辑备份。本讨论使用mysqldump。

假设我们在星期日下午 1 点，负载较低时，使用以下命令对所有数据库中的所有InnoDB表进行完全备份：

$> mysqldump --all-databases --master-data --single-transaction > backup_sunday_1_PM.sql

由mysqldump生成的.sql文件包含一组 SQL INSERT语句，可用于在以后重新加载转储的表。

此备份操作在转储开始时（使用FLUSH TABLES WITH READ LOCK）获取所有表的全局读锁。一旦获得此锁，就会读取二进制日志坐标并释放锁。如果在发出FLUSH语句时正在运行长时间的更新语句，则备份操作可能会停顿，直到这些语句完成。之后，转储变为无锁状态，不会干扰表的读写操作。

之前假设要备份的表是InnoDB表，因此--single-transaction使用一致性读取，并保证mysqldump看到的数据不会更改。（其他客户端对InnoDB表所做的更改不会被mysqldump进程看到。）如果备份操作包括非事务表，一致性要求在备份期间它们不会更改。例如，在mysql数据库中的MyISAM表，备份期间不能对 MySQL 帐户进行管理更改。

完整备份是必要的，但并不总是方便创建。它们产生大型备份文件并需要时间生成。从优化的角度来看，每次连续的完整备份都包含所有数据，即使是自上次完整备份以来未更改的部分。更有效的方法是进行初始完整备份，然后进行增量备份。增量备份更小，生成时间更短。权衡之处在于，在恢复时，你不能仅通过重新加载完整备份来恢复数据。你还必须处理增量备份以恢复增量更改。

要进行增量备份，我们需要保存增量更改。在 MySQL 中，这些更改在二进制日志中表示，因此 MySQL 服务器应始终使用--log-bin选项启动以启用该日志。启用二进制日志记录后，服务器在更新数据时将每个数据更改写入文件。查看运行了一些天的 MySQL 服务器的数据目录，我们会发现这些 MySQL 二进制日志文件：

-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem  guilhem        79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem  guilhem       508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem  guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem  guilhem       361 Nov 14 10:07 gbichot2-bin.index

每次重新启动时，MySQL 服务器都会使用序列中的下一个数字创建一个新的二进制日志文件。在服务器运行时，您还可以告诉它通过发出FLUSH LOGS SQL 语句或使用mysqladmin flush-logs命令手动关闭当前的二进制日志文件并开始一个新的。mysqldump还有一个选项来刷新日志。数据目录中的.index文件包含目录中所有 MySQL 二进制日志的列表。

MySQL 二进制日志对于恢复很重要，因为它们形成了增量备份集。如果确保在进行完整备份时刷新日志，那么之后创建的二进制日志文件将包含自备份以来进行的所有数据更改。让我们稍微修改之前的mysqldump命令，以便在完整备份时刷新 MySQL 二进制日志，并使转储文件包含新当前二进制日志的名称：

$> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases > backup_sunday_1_PM.sql

执行此命令后，数据目录包含一个新的二进制日志文件，gbichot2-bin.000007，因为--flush-logs选项导致服务器刷新其日志。--master-data选项导致mysqldump将二进制日志信息写入其输出，因此生成的.sql转储文件包含这些行：

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

因为mysqldump命令进行了完整备份，这些行意味着两件事：

• 转储文件包含在写入gbichot2-bin.000007二进制日志文件或更高版本之前所做的所有更改。

• 备份后记录的所有数据更改都不包含在转储文件中，但包含在gbichot2-bin.000007二进制日志文件或更高版本中。

在星期一下午 1 点，我们可以通过刷新日志来开始一个新的二进制日志文件进行增量备份。例如，执行mysqladmin flush-logs命令会创建gbichot2-bin.000008。在星期日下午 1 点全量备份和星期一下午 1 点之间的所有更改都写入gbichot2-bin.000007。这个增量备份很重要，所以最好将其复制到一个安全的地方。（例如，将其备份到磁带或 DVD 上，或将其复制到另一台机器上。）在星期二下午 1 点，执行另一个mysqladmin flush-logs命令。在星期一下午 1 点和星期二下午 1 点之间的所有更改都写入gbichot2-bin.000008（也应该将其复制到安全的地方）。

MySQL 二进制日志占用磁盘空间。为了释放空间，定期清理它们。一种方法是删除不再需要的二进制日志，例如当我们进行全量备份时：

$> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases --delete-master-logs > backup_sunday_1_PM.sql

注意

使用mysqldump --delete-master-logs删除 MySQL 二进制日志可能会很危险，如果您的服务器是一个复制源服务器，因为副本可能尚未完全处理二进制日志的内容。PURGE BINARY LOGS语句的描述解释了在删除 MySQL 二进制日志之前应该验证的内容。请参阅 Section 15.4.1.1, “PURGE BINARY LOGS Statement”。

9.3.2 使用备份进行恢复

原文：dev.mysql.com/doc/refman/8.0/en/recovery-from-backups.html

现在，假设我们在周三早上 8 点发生了灾难性的意外退出，需要从备份中恢复。为了恢复，首先我们恢复我们拥有的最后一个完整备份（即周日下午 1 点的备份）。完整备份文件只是一组 SQL 语句，因此恢复它非常容易：

$> mysql < backup_sunday_1_PM.sql

此时，数据已恢复到周日下午 1 点的状态。要恢复自那时以来所做的更改，我们必须使用增量备份；也就是说，gbichot2-bin.000007和gbichot2-bin.000008二进制日志文件。如有必要，从备份位置获取文件，然后像这样处理它们的内容：

$> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

现在，我们已将数据恢复到周二下午 1 点的状态，但仍然缺少从那天到崩溃日期的更改。为了不丢失它们，我们需要让 MySQL 服务器将其 MySQL 二进制日志存储到一个安全位置（RAID 磁盘，SAN，...），与存储数据文件的位置不同，以便这些日志不在被破坏的磁盘上。（也就是说，我们可以使用--log-bin选项启动服务器，指定一个与数据目录所在的物理设备不同的位置。这样，即使包含目录的设备丢失，日志也是安全的。）如果我们这样做了，我们将手头上有gbichot2-bin.000009文件（以及任何后续文件），我们可以使用mysqlbinlog和mysql应用它们，恢复最近的数据更改，直到崩溃时刻，而无需丢失：

$> mysqlbinlog gbichot2-bin.000009 ... | mysql

有关使用mysqlbinlog处理二进制日志文件的更多信息，请参阅第 9.5 节，“时间点（增量）恢复” Recovery")。

9.3.3 备份策略摘要

原文：dev.mysql.com/doc/refman/8.0/en/backup-strategy-summary.html

在操作系统崩溃或断电的情况下，InnoDB本身会完成所有数据恢复的工作。但为了确保您能安心入睡，请遵守以下准则：

• 始终以启用二进制日志记录的方式运行 MySQL 服务器（这是 MySQL 8.0 的默认设置）。如果您有这样的安全介质，这种技术也可以用于磁盘负载平衡（从而提高性能）。

• 做定期完整备份，使用之前在第 9.3.1 节，“建立备份策略”中展示的mysqldump命令，进行在线、非阻塞备份。

• 通过使用FLUSH LOGS或mysqladmin flush-logs来进行定期增量备份。

9.4 使用 mysqldump 进行备份

dev.mysql.com/doc/refman/8.0/en/using-mysqldump.html

9.4.1 使用 mysqldump 以 SQL 格式导出数据

9.4.2 重新加载 SQL 格式备份

9.4.3 使用 mysqldump 以分隔文本格式导出数据

9.4.4 重新加载分隔文本格式备份

9.4.5 mysqldump 技巧

提示

考虑使用 MySQL Shell dump 工具，提供多线程并行导出、文件压缩、进度信息显示，以及云功能，如 Oracle Cloud Infrastructure Object Storage 流式传输，以及 MySQL HeatWave Service 兼容性检查和修改。导出的数据可以轻松导入到 MySQL Server 实例或 MySQL HeatWave Service DB System 中，使用 MySQL Shell load dump 工具。MySQL Shell 的安装说明可在这里找到。

本节描述了如何使用mysqldump生成导出文件，以及如何重新加载导出文件。导出文件可以以多种方式使用：

• 作为备份，以便在数据丢失时进行数据恢复。

• 作为设置副本的数据源。

• 作为实验的数据源：

  • 复制数据库的副本，可以在不更改原始数据的情况下使用。

  • 用于测试潜在的升级不兼容性。

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

• 没有--tab选项时，mysqldump将 SQL 语句写入标准输出。这些输出包括用于创建导出对象（数据库、表、存储过程等）的CREATE语句，以及用于将数据加载到表中的INSERT语句。输出可以保存在文件中，并使用mysql稍后重新加载，以重新创建导出的对象。有选项可修改 SQL 语句的格式，并控制导出哪些对象。

• 使用--tab，mysqldump为每个转储的表生成两个输出文件。服务器将一个文件写为制表符分隔的文本，每行一个表行。该文件在输出目录中命名为*tbl_name*.txt。服务器还向mysqldump发送一个CREATE TABLE语句，该语句被写入一个名为*tbl_name*.sql的文件中。

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

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-sql-format.html

本节描述如何使用mysqldump创建 SQL 格式的转储文件。有关重新加载此类转储文件的信息，请参见 Section 9.4.2, “Reloading SQL-Format Backups”。

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

$> mysqldump [*arguments*] > *file_name*

要转储所有数据库，请使用--all-databases选项调用mysqldump：

$> mysqldump --all-databases > dump.sql

要仅转储特定数据库，请在命令行上命名它们并使用--databases选项：

$> 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 --databases test > dump.sql

在单个数据库的情况下，可以省略--databases选项：

$> mysqldump test > dump.sql

两个前述命令之间的区别在于，没有--databases，转储输出不包含CREATE DATABASE或USE语句。这有几个影响：

• 当重新加载转储文件时，必须指定一个默认数据库名称，以便服务器知道要重新加载哪个数据库。

• 对于重新加载，可以指定与原始名称不同的数据库名称，这使您可以将数据重新加载到不同的数据库中。

• 如果要重新加载的数据库不存在，必须首先创建它。

• 因为输出不包含CREATE DATABASE语句，--add-drop-database选项没有效果。如果使用它，将不会生成DROP DATABASE语句。

为了仅从数据库中导出特定表，需要在命令行中跟随数据库名称命名这些表：

$> mysqldump test t1 t3 t7 > dump.sql

默认情况下，如果在创建转储文件的服务器上使用了 GTIDs（gtid_mode=ON），mysqldump在输出中包含一个SET @@GLOBAL.gtid_purged语句，将源服务器上的gtid_executed集合中的 GTIDs 添加到目标服务器上的gtid_purged集合中。如果仅转储特定数据库或表，重要的是要注意由mysqldump包含的值包括源服务器上gtid_executed集合中的所有事务的 GTIDs，即使这些事务更改了数据库的被抑制部分，或者服务器上未包含在部分转储中的其他数据库。如果您只在目标服务器上重放一个部分转储文件，额外的 GTIDs 不会对该服务器的未来操作造成任何问题。但是，如果在目标服务器上重放包含相同 GTIDs 的第二个转储文件（例如，来自同一源服务器的另一个部分转储），第二个转储文件中的任何SET @@GLOBAL.gtid_purged语句将失败。为避免此问题，要么将mysqldump选项--set-gtid-purged设置为OFF或COMMENTED，以在输出第二个转储文件时不包含活动的SET @@GLOBAL.gtid_purged语句，要么在重放转储文件之前手动删除该语句。

9.4.2 重新加载 SQL 格式备份

原文：dev.mysql.com/doc/refman/8.0/en/reloading-sql-format-dumps.html

要重新加载由mysqldump编写的包含 SQL 语句的转储文件，请将其用作mysql客户端的输入。如果转储文件是由mysqldump使用--all-databases或--databases选项创建的，则其中包含CREATE DATABASE和USE语句，因此无需指定默认数据库来加载数据：

$> mysql < dump.sql

或者，从mysql内部使用source命令：

mysql> source dump.sql

如果文件是一个不包含CREATE DATABASE和USE语句的单个数据库转储文件，请首先创建数据库（如果需要）：

$> mysqladmin create db1

然后在加载转储文件时指定数据库名称：

$> mysql db1 < dump.sql

或者，从mysql内部创建数据库，将其选择为默认数据库，并加载转储文件：

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

注意

对于 Windows PowerShell 用户：由于"<"字符在 PowerShell 中保留供将来使用，因此需要另一种方法，例如使用引号cmd.exe /c "mysql < dump.sql"。

9.4.3 使用 mysqldump 以分隔文本格式转储数据

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-delimited-text.html

本节描述了如何使用mysqldump创建分隔文本转储文件。有关重新加载此类转储文件的信息，请参见 Section 9.4.4, “Reloading Delimited-Text Format Backups”。

如果使用--tab=*dir_name*选项调用mysqldump，它将使用*dir_name*作为输出目录，并在该目录中单独转储表，每个表使用两个文件。表名是这些文件的基本名称。对于名为t1的表，文件名为t1.sql和t1.txt。.sql文件包含表的CREATE TABLE语句。.txt文件包含表数据，每行一个表行。

以下命令将db1数据库的内容转储到/tmp数据库中：

$> mysqldump --tab=/tmp db1

包含表数据的.txt文件由服务器编写，因此它们由用于运行服务器的系统帐户拥有。服务器使用SELECT ... INTO OUTFILE来写入文件，因此您必须具有FILE权限才能执行此操作，如果给定的.txt文件已经存在，则会发生错误。

服务器将转储表的CREATE定义发送给mysqldump，后者将它们写入.sql文件。因此，这些文件的所有者是执行mysqldump的用户。

最好只在本地服务器上使用--tab进行转储。如果在远程服务器上使用它，那么--tab目录必须同时存在于本地和远程主机上，并且.txt文件由服务器在远程目录（在服务器主机上）中写入，而.sql文件由mysqldump在本地目录（在客户端主机上）中写入。

对于mysqldump --tab，服务器默认将表数据写入.txt文件，每行一个行，列值之间用制表符分隔，列值周围没有引号，换行符作为行终止符。（这些是与SELECT ... INTO OUTFILE相同的默认值。）

为了使数据文件以不同格式编写，mysqldump支持以下选项：

• --fields-terminated-by=*str*

  用于分隔列值的字符串（默认：制表符）。

• --fields-enclosed-by=*char*

  用于封装列值的字符（默认：无字符）。

• --fields-optionally-enclosed-by=*char*

  用于封装非数字列值的字符（默认：无字符）。

• --fields-escaped-by=*char*

  用于转义特殊字符的字符（默认：不转义）。

• --lines-terminated-by=*str*

  行终止字符串（默认：换行符）。

根据您为这些选项中的任何一个指定的值，可能需要在命令行上适当地引用或转义该值。或者，使用十六进制表示值。假设您希望mysqldump在双引号内引用列值。为此，请将双引号指定为--fields-enclosed-by选项的值。但是，此字符通常对命令解释器特殊，必须特殊处理。例如，在 Unix 上，您可以这样引用双引号：

--fields-enclosed-by='"'

在任何平台上，您都可以以十六进制指定值：

--fields-enclosed-by=0x22

通常会一起使用几个数据格式选项。例如，要以逗号分隔值格式转储表，并以回车/换行对（\r\n）终止行，请使用以下命令（在一行上输入）：

$> mysqldump --tab=/tmp --fields-terminated-by=,
         --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1

如果您使用任何数据格式选项来转储表数据，则在以后重新加载数据文件时，需要指定相同的格式，以确保正确解释文件内容。

9.4.4 重新加载分隔文本格式备份

原文：dev.mysql.com/doc/refman/8.0/en/reloading-delimited-text-dumps.html

对于使用mysqldump --tab生成的备份，每个表在输出目录中由一个包含表的CREATE TABLE语句的.sql文件和一个包含表数据的.txt文件表示。要重新加载表，首先进入输出目录。然后使用mysql处理.sql文件以创建空表，并处理.txt文件以将数据加载到表中：

$> mysql db1 < t1.sql
$> mysqlimport db1 t1.txt

除了使用mysqlimport加载数据文件之外，还可以在mysql客户端内部使用LOAD DATA语句：

mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;

如果在最初转储表时使用了任何数据格式化选项mysqldump，则必须在使用mysqlimport或LOAD DATA时使用相同的选项，以确保正确解释数据文件内容：

$> mysqlimport --fields-terminated-by=,
         --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt

或：

mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1
       FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"'
       LINES TERMINATED BY '\r\n';

9.4.5 mysqldump 提示

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-tips.html

9.4.5.1 复制数据库

9.4.5.2 从一个服务器复制数据库到另一个服务器

9.4.5.3 转储存储程序

9.4.5.4 分别转储表定义和内容

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

本节介绍了使用mysqldump解决特定问题的技术：

• 如何复制数据库

• 如何从一个服务器复制数据库到另一个服务器

• 如何转储存储程序（存储过程和函数，触发器和事件）

• 如何分别转储定义和数据

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-copying-database.html

9.4.5.1 复制数据库

$> mysqldump db1 > dump.sql
$> mysqladmin create db2
$> mysql db2 < dump.sql

在 mysqldump 命令行中不要使用 --databases，因为这会导致在转储文件中包含 USE db1，这会覆盖在 mysql 命令行上命名 db2 的效果。

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-copying-to-other-server.html

9.4.5.2 从一个服务器复制数据库到另一个服务器

在服务器 1 上：

$> mysqldump --databases db1 > dump.sql

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

在服务器 2 上：

$> mysql < dump.sql

使用--databases选项与mysqldump命令行一起使用，导致转储文件包含CREATE DATABASE和USE语句，如果数据库存在则创建数据库，并将其设置为重新加载数据的默认数据库。

或者，您可以从mysqldump命令中省略--databases。然后，您需要在服务器 2 上创建数据库（如果需要），并在重新加载转储文件时将其指定为默认数据库。

在服务器 1 上：

$> mysqldump db1 > dump.sql

在服务器 2 上：

$> mysqladmin create db1
$> mysql db1 < dump.sql

在这种情况下，您可以指定不同的数据库名称，因此从mysqldump命令中省略--databases使您能够从一个数据库转储数据并加载到另一个数据库中。

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-stored-programs.html

9.4.5.3 存储程序的导出

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

• --events: 导出事件调度器事件

• --routines: 导出存储过程和函数

• --triggers: 为表导出触发器

--triggers选项默认启用，因此在导出表时，它们将附带任何触发器。其他选项默认禁用，必须明确指定以导出相应的对象。要明确禁用这些选项中的任何一个，请使用其跳过形式：--skip-events，--skip-routines，或--skip-triggers。

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-definition-data-dumps.html

9.4.5.4 分别转储表定义和内容

--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

原文：dev.mysql.com/doc/refman/8.0/en/mysqldump-upgrade-testing.html

9.4.5.5 使用 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

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

9.5 时间点（增量）恢复

原文：dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.html

9.5.1 使用二进制日志进行时间点恢复

9.5.2 使用事件位置进行时间点恢复

时间点恢复指的是恢复数据更改直到特定时间点。通常，在恢复将服务器恢复到备份制作时的状态的完整备份之后执行此类型的恢复。（完整备份可以通过多种方式进行，比如第 9.2 节“数据库备份方法”中列出的方式。）然后，时间点恢复从完整备份的时间逐步将服务器更新到更近的时间点。

9.5.1 使用二进制日志进行时间点恢复

原文：dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery-binlog.html

本节解释了使用二进制日志执行时间点恢复的一般思路。下一节，第 9.5.2 节，“使用事件位置进行时间点恢复”，通过示例详细解释了操作。

注意

本节和下一节中的许多示例使用mysql客户端来处理mysqlbinlog生成的二进制日志输出。如果您的二进制日志包含\0（空）字符，则除非使用--binary-mode选项调用，否则mysql无法解析该输出。

时间点恢复的信息来源是在完全备份操作之后生成的一组二进制日志文件。因此，为了允许服务器恢复到某个时间点，必须在其上启用二进制日志记录，这是 MySQL 8.0 的默认设置（参见 第 7.4.4 节，“二进制日志”）。

要从二进制日志中恢复数据，必须知道当前二进制日志文件的名称和位置。默认情况下，服务器在数据目录中创建二进制日志文件，但可以使用--log-bin选项指定路径名以将文件放在不同位置。要查看所有二进制日志文件的列表，请使用以下语句：

mysql> SHOW BINARY LOGS;

要确定当前二进制日志文件的名称，请执行以下语句：

mysql> SHOW MASTER STATUS;

mysqlbinlog 实用程序将二进制日志文件中的事件从二进制格式转换为文本，以便查看或应用。mysqlbinlog 有选项可根据事件时间或日志中事件位置选择二进制日志的部分。请参阅 第 6.6.9 节，“mysqlbinlog — 用于处理二进制日志文件的实用程序”。

从二进制日志应用事件会导致它们所代表的数据修改重新执行。这使得可以恢复给定时间段的数据更改。要从二进制日志应用事件，请使用mysql客户端处理mysqlbinlog输出：

$> mysqlbinlog *binlog_files* | mysql -u root -p

如果二进制日志文件已加密，从 MySQL 8.0.14 开始可以执行此操作，mysqlbinlog 无法像上面的示例那样直接读取它们，但可以使用 --read-from-remote-server (-R) 选项从服务器读取它们。例如：

$> mysqlbinlog --read-from-remote-server --host=*host_name* --port=3306  --user=root --password --ssl-mode=required  *binlog_files* | mysql -u root -p

在这里，选项 --ssl-mode=required 被用来确保从二进制日志文件中传输的数据在传输过程中受到保护，因为它以未加密的格式发送给 mysqlbinlog。

重要

VERIFY_CA 和 VERIFY_IDENTITY 比 REQUIRED 更好的选择作为 SSL 模式，因为它们有助于防止中间人攻击。要实施这些设置之一，您必须首先确保服务器的 CA 证书可靠地提供给所有在您的环境中使用它的客户端，否则将导致可用性问题。请参阅加密连接的命令选项。

查看日志内容在需要确定事件时间或位置以选择执行事件之前的部分日志内容时很有用。要查看日志中的事件，请将 mysqlbinlog 输出发送到分页程序：

$> mysqlbinlog *binlog_files* | more

或者，将输出保存在文件中，并在文本编辑器中查看文件：

$> mysqlbinlog *binlog_files* > tmpfile
$> ... *edit tmpfile* ...

编辑文件后，应用内容如下：

$> mysql -u root -p < tmpfile

如果您有多个要应用于 MySQL 服务器的二进制日志，则可以使用单个连接来应用要处理的所有二进制日志文件的内容。以下是一种方法：

$> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

另一种方法是将整个日志写入单个文件，然后处理该文件：

$> mysqlbinlog binlog.000001 >  /tmp/statements.sql
$> mysqlbinlog binlog.000002 >> /tmp/statements.sql
$> mysql -u root -p -e "source /tmp/statements.sql"

9.5.2 使用事件位置进行时间点恢复

原文：dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery-positions.html

最后一节，第 9.5.1 节，“使用二进制日志进行时间点恢复”，解释了使用二进制日志执行时间点恢复的一般思路。该部分通过示例详细解释了操作。

例如，假设在 2020 年 3 月 11 日 20:06:00 左右执行了一个删除表的 SQL 语句。您可以执行时间点恢复，将服务器恢复到表删除之前的状态。以下是一些实现这一目标的示例步骤：

1. 恢复在感兴趣时间点之前创建的最后一个完整备份（我们的示例中是 2020 年 3 月 11 日 20:06:00 的t[p]）。完成后，记录已恢复服务器的二进制日志位置以供以后使用，并重新启动服务器。

   注意

   虽然 InnoDB 在恢复和服务器重新启动后也会显示最后一个恢复的二进制日志位置，但这不是获取恢复结束日志位置的可靠方法，因为在显示位置之后可能发生了 DDL 事件和非 InnoDB 更改。您的备份和恢复工具应为您提供用于恢复的最后一个二进制日志位置：例如，如果您正在使用mysqlbinlog执行任务，请检查二进制日志重放的停止位置；如果您正在使用 MySQL 企业版备份，则最后一个二进制日志位置已保存在您的备份中。请参阅时间点恢复。

2. 找到与您想要恢复数据库的时间点对应的精确二进制日志事件位置。在我们的示例中，假设我们知道表删除发生的大致时间（t[p]），我们可以通过使用mysqlbinlog实用程序来检查该时间周围的日志内容，找到日志位置。使用--start-datetime和--stop-datetime选项来指定围绕t[p]的短时间段，然后在输出中查找事件。例如：

   $> mysqlbinlog --start-datetime="2020-03-11 20:05:00" \
                      --stop-datetime="2020-03-11 20:08:00" --verbose \
            /var/lib/mysql/bin.123456 | grep -C 15 "DROP TABLE"
   
   /*!80014 SET @@session.original_server_version=80019*//*!*/;
   /*!80014 SET @@session.immediate_server_version=80019*//*!*/;
   SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
   # at 232
   #200311 20:06:20 server id 1  end_log_pos 355 CRC32 0x2fc1e5ea 	Query	thread_id=16	exec_time=0	error_code=0
   SET TIMESTAMP=1583971580/*!*/;
   SET @@session.pseudo_thread_id=16/*!*/;
   SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
   SET @@session.sql_mode=1168113696/*!*/;
   SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
   /*!\C utf8mb4 *//*!*/;
   SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
   SET @@session.lc_time_names=0/*!*/;
   SET @@session.collation_database=DEFAULT/*!*/;
   /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
   DROP TABLE `pets`.`cats` /* generated by server */
   /*!*/;
   # at 355
   #200311 20:07:48 server id 1  end_log_pos 434 CRC32 0x123d65df 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no	original_committed_timestamp=1583971668462467	immediate_commit_timestamp=1583971668462467	transaction_length=473
   # original_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT)
   # immediate_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT)
   /*!80001 SET @@session.original_commit_timestamp=1583971668462467*//*!*/;
   /*!80014 SET @@session.original_server_version=80019*//*!*/;
   /*!80014 SET @@session.immediate_server_version=80019*//*!*/;
   SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
   # at 434
   #200311 20:07:48 server id 1  end_log_pos 828 CRC32 0x57fac9ac 	Query	thread_id=16	exec_time=0	error_code=0	Xid = 217
   use `pets`/*!*/;
   SET TIMESTAMP=1583971668/*!*/;
   /*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
   CREATE TABLE dogs
   

   从mysqlbinlog的输出中，可以在二进制日志的段中找到DROP TABLE pets.cats``语句，该语句位于# at 232和# at 355之间，这意味着该语句发生在日志位置 232 之后，并且在DROP TABLE语句之后的位置 355 处。

   注意

   仅使用--start-datetime和--stop-datetime选项来帮助您找到感兴趣的实际事件位置。不建议使用这两个选项来指定要应用的二进制日志段的范围：使用这些选项时更容易错过二进制日志事件。请改用--start-position和--stop-position。

3. 将二进制日志文件中的事件应用到服务器上，从您在第 1 步中找到的日志位置开始（假设为 155），直到您在第 2 步中找到的在您感兴趣的时间点之前的位置（即 232）：

   $> mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456 \
            | mysql -u root -p
   

   该命令从起始位置恢复所有事务，直到停止位置之前。因为mysqlbinlog的输出在每个记录的 SQL 语句之前包含SET TIMESTAMP语句，恢复的数据和相关的 MySQL 日志反映了事务执行的原始时间。

   您的数据库现在已经恢复到感兴趣的时间点t[p]，就在表pets.cats被删除之前。

4. 完成了点时间恢复后，如果您还想重新执行您感兴趣的时间点之后的所有语句，请再次使用mysqlbinlog来将t[p]之后的所有事件应用到服务器上。我们在第 2 步中指出，在我们想要跳过的语句之后，日志位于位置 355；我们可以将其用于--start-position选项，以便包括位置之后的任何语句：

   $> mysqlbinlog --start-position=355 /var/lib/mysql/bin.123456 \
            | mysql -u root -p
   

   您的数据库已经恢复到二进制日志文件中记录的最新语句，但跳过了选定的事件。

9.6 MyISAM 表维护和崩溃恢复

原文：dev.mysql.com/doc/refman/8.0/en/myisam-table-maintenance.html

9.6.1 使用 myisamchk 进行崩溃恢复

9.6.2 如何检查 MyISAM 表中的错误

9.6.3 如何修复 MyISAM 表

9.6.4 MyISAM 表优化

9.6.5 设置 MyISAM 表维护计划

本节讨论如何使用myisamchk来检查或修复MyISAM表（用于存储数据和索引的.MYD和.MYI文件的表）。有关一般myisamchk背景，请参阅 Section 6.6.4, “myisamchk — MyISAM Table-Maintenance Utility”。其他表修复信息可在 Section 3.14, “Rebuilding or Repairing Tables or Indexes”找到。

您可以使用myisamchk来检查、修复或优化数据库表。以下各节描述了如何执行这些操作以及如何设置表维护计划。有关使用myisamchk获取有关您的表的信息，请参阅 Section 6.6.4.5, “Obtaining Table Information with myisamchk”。

即使使用myisamchk进行表修复是相当安全的，但在进行修复或任何可能对表进行大量更改的维护操作之前，始终最好先备份数据。

myisamchk影响索引的操作可能导致MyISAM FULLTEXT索引使用与 MySQL 服务器使用的值不兼容的全文参数进行重建。为避免此问题，请遵循 Section 6.6.4.1, “myisamchk General Options”中的指南。

MyISAM表维护也可以使用执行类似于myisamchk的操作的 SQL 语句来完成：

• 要检查MyISAM表，请使用CHECK TABLE。

• 要修复MyISAM表，请使用REPAIR TABLE。

• 要优化MyISAM表，请使用OPTIMIZE TABLE。

• 要分析MyISAM表，请使用ANALYZE TABLE。

有关这些语句的更多信息，请参阅 Section 15.7.3, “Table Maintenance Statements”。

这些语句可以直接使用，也可以通过mysqlcheck客户端程序使用。这些语句相对于myisamchk的一个优点是服务器完成所有工作。使用myisamchk时，您必须确保服务器不同时使用表，以避免myisamchk和服务器之间发生不必要的交互。

9.6.1 使用 myisamchk 进行崩溃恢复

原文：dev.mysql.com/doc/refman/8.0/en/myisam-crash-recovery.html

这一部分描述了如何检查和处理 MySQL 数据库中的数据损坏。如果您的表经常损坏，您应该尝试找出原因。请参阅第 B.3.3.3 节，“如果 MySQL 经常崩溃该怎么办”。

有关MyISAM表如何变得损坏的解释，请参阅第 18.2.4 节，“MyISAM 表问题”。

如果您使用外部锁定禁用（默认情况下）运行mysqld，您不能可靠地使用myisamchk来检查一个表，当mysqld正在使用相同的表时。如果您可以确定没有人可以在您运行myisamchk检查表时使用mysqld访问表，您只需在开始检查表之前执行mysqladmin flush-tables。如果您无法保证这一点，您必须在检查表时停止mysqld。如果您运行myisamchk来检查mysqld同时正在更新的表，即使表没有损坏，您可能会收到警告。

如果服务器启用了外部锁定，您可以随时使用myisamchk来检查表。在这种情况下，如果服务器尝试更新一个正在使用的表，服务器会等待myisamchk完成后才继续。

如果您使用myisamchk来修复或优化表，您必须始终确保mysqld服务器未使用该表（如果禁用外部锁定也适用）。如果不停止mysqld，您至少应在运行myisamchk之前执行mysqladmin flush-tables。如果服务器和myisamchk同时访问表，您的表可能会损坏。

在执行崩溃恢复时，重要的是要理解数据库中每个MyISAM表*tbl_name*对应于数据库目录中的三个文件，如下表所示。

文件	目的
*tbl_name*.MYD	数据文件
*tbl_name*.MYI	索引文件

这三种文件类型中的每一种都可能以各种方式损坏，但问题最常发生在数据文件和索引文件中。

myisamchk 通过逐行创建.MYD数据文件的副本来工作。它通过删除旧的.MYD文件并将新文件重命名为原始文件名来结束修复阶段。如果使用--quick，myisamchk 不会创建临时的.MYD文件，而是假设.MYD文件是正确的，并且仅生成一个新的索引文件而不触及.MYD文件。这是安全的，因为myisamchk会自动检测.MYD文件是否损坏，并在损坏时中止修复。您还可以两次指定--quick选项给myisamchk。在这种情况下，myisamchk不会在某些错误（如重复键错误）上中止，而是尝试通过修改.MYD文件来解决这些错误。通常只有在磁盘空间不足以执行正常修复时，才有必要使用两个--quick选项。在这种情况下，您至少应在运行myisamchk之前备份表。

9.6.2 如何检查 MyISAM 表中的错误

原文：dev.mysql.com/doc/refman/8.0/en/myisam-check.html

要检查一个MyISAM表，请使用以下命令：

• myisamchk tbl_name

  这可以找到 99.99%的所有错误。它无法找到仅涉及数据文件的损坏（这是非常不寻常的）。如果要检查一个表，通常应该运行myisamchk而不带选项或带上-s（静默）选项。

• myisamchk -m tbl_name

  这可以找到 99.999%的所有错误。它首先检查所有索引条目是否有错误，然后逐行阅读。它为行中所有键值计算校验和，并验证校验和是否与索引树中键的校验和匹配。

• myisamchk -e tbl_name

  这会对所有数据进行完整彻底的检查（-e表示“扩展检查”）。它对每一行的每个键进行检查读取，以验证它们确实指向正确的行。对于具有许多索引的大表，这可能需要很长时间。通常，myisamchk在找到第一个错误后就会停止。如果要获取更多信息，可以添加-v（详细）选项。这会导致myisamchk继续进行，最多检查 20 个错误。

• myisamchk -e -i tbl_name

  这类似于先前的命令，但-i选项告诉myisamchk打印额外的统计信息。

在大多数情况下，只需简单运行myisamchk命令，除了表名之外不带其他参数就足以检查一个表。

9.6.3 如何修复 MyISAM 表

原文：dev.mysql.com/doc/refman/8.0/en/myisam-repair.html

本节讨论了如何在MyISAM表（扩展名为.MYI和.MYD）上使用myisamchk。

您还可以使用CHECK TABLE和REPAIR TABLE语句来检查和修复MyISAM表。请参阅 Section 15.7.3.2, “CHECK TABLE Statement”和 Section 15.7.3.5, “REPAIR TABLE Statement”。

损坏表的症状包括意外中止的查询和可观察到的错误，例如：

• 找不到文件*tbl_name*.MYI（错误代码：nnn）

• 文件意外结束

• 记录文件已崩溃

• 从表处理程序得到错误*nnn*

要获取有关错误的更多信息，请运行perror nnn，其中*nnn*是错误编号。以下示例显示如何使用perror查找指示表存在问题的最常见错误编号的含义：

$> perror 126 127 132 134 135 136 141 144 145
MySQL error code 126 = Index file is crashed
MySQL error code 127 = Record-file is crashed
MySQL error code 132 = Old database file
MySQL error code 134 = Record was already deleted (or record file crashed)
MySQL error code 135 = No more room in record file
MySQL error code 136 = No more room in index file
MySQL error code 141 = Duplicate unique key or constraint on write or update
MySQL error code 144 = Table is crashed and last repair failed
MySQL error code 145 = Table was marked as crashed and should be repaired

请注意，错误 135（记录文件中没有更多空间）和错误 136（索引文件中没有更多空间）不是简单修复的错误。在这种情况下，您必须使用ALTER TABLE来增加MAX_ROWS和AVG_ROW_LENGTH表选项值：

ALTER TABLE *tbl_name* MAX_ROWS=*xxx* AVG_ROW_LENGTH=*yyy*;

如果您不知道当前的表选项值，请使用SHOW CREATE TABLE。

对于其他错误，您必须修复您的表。myisamchk通常可以检测和修复大多数出现的问题。

修复过程包括最多三个阶段，这里进行描述。在开始之前，您应该切换到数据库目录并检查表文件的权限。在 Unix 上，请确保它们可被mysqld运行的用户（以及您，因为您需要访问正在检查的文件）读取。如果您需要修改文件，它们也必须可被您写入。

本节适用于表检查失败的情况（例如 Section 9.6.2, “How to Check MyISAM Tables for Errors”中描述的情况），或者您想要使用myisamchk提供的扩展功能。

用于表维护的myisamchk选项在第 6.6.4 节“myisamchk — MyISAM Table-Maintenance Utility”中有描述。myisamchk还有一些变量，您可以设置以控制可能改善性能的内存分配。请参阅第 6.6.4.6 节“myisamchk 内存使用”。

如果要从命令行修复表，必须先停止mysqld服务器。请注意，当在远程服务器上执行mysqladmin shutdown时，mysqld服务器在mysqladmin返回后仍可用一段时间，直到所有语句处理停止并所有索引更改已刷新到磁盘。

第一阶段：检查您的表

运行myisamchk *.MYI或myisamchk -e *.MYI如果时间充裕。使用-s（静默）选项以抑制不必要的信息。

如果mysqld服务器已停止，应使用--update-state选项告诉myisamchk将表标记为“已检查”。

只需修复myisamchk报告错误的那些表。对于这样的表，继续进行第二阶段。

如果在检查时遇到意外错误（例如内存不足错误），或者myisamchk崩溃，请转到第三阶段。

第二阶段：简单安全修复

首先尝试myisamchk -r -q tbl_name（-r -q表示“快速恢复模式”）。这将尝试修复索引文件而不触及数据文件。如果数据文件包含应有的所有内容，并且删除链接指向数据文件内的正确位置，则应该可以工作，并且表已修复。开始修复下一个表。否则，请使用以下过程：

1. 在继续之前备份数据文件。

2. 使用myisamchk -r tbl_name（-r表示“恢复模式”）。这将从数据文件中删除不正确的行和已删除的行，并重建索引文件。

3. 如果前面的步骤失败，请使用myisamchk --safe-recover tbl_name。安全恢复模式使用一种旧的恢复方法，处理一些常规恢复模式无法处理的情况（但速度较慢）。

注意

如果您希望修复操作更快完成，应将sort_buffer_size和key_buffer_size变量的值分别设置为可用内存的约 25%，当运行myisamchk时。

如果在修复过程中遇到意外错误（如内存不足错误），或者myisamchk崩溃，请进入第三阶段。

第三阶段：困难修复

只有在索引文件中的第一个 16KB 块被破坏或包含不正确信息，或者索引文件丢失时，才应该达到这个阶段。在这种情况下，需要创建一个新的索引文件。操作如下：

1. 将数据文件移至安全位置。

2. 使用表描述文件创建新的（空）数据和索引文件：

   $> mysql *db_name*
   

   mysql> SET autocommit=1;
   mysql> TRUNCATE TABLE *tbl_name*;
   mysql> quit
   

3. 将旧数据文件复制回新创建的数据文件。（不要只是将旧文件移回新文件。您希望保留一份副本以防出现问题。）

重要提示

如果您正在使用复制功能，应在执行上述过程之前停止复制，因为这涉及文件系统操作，而 MySQL 不会记录这些操作。

返回到第二阶段。myisamchk -r -q应该可以工作。（这不应该是一个无限循环。）

您还可以使用REPAIR TABLE *tbl_name* USE_FRM SQL 语句，该语句会自动执行整个过程。由于服务器在使用REPAIR TABLE时会完成所有工作，因此不会出现工具与服务器之间的意外交互。请参阅 Section 15.7.3.5, “REPAIR TABLE Statement”。

9.6.4 MyISAM 表优化

原文：dev.mysql.com/doc/refman/8.0/en/myisam-optimization.html

为了合并碎片化的行并消除由于删除或更新行而导致的空间浪费，以恢复模式运行myisamchk：

$> myisamchk -r *tbl_name*

你可以通过使用OPTIMIZE TABLE SQL 语句以相同的方式优化表。OPTIMIZE TABLE 进行表修复和关键分析，并对索引树进行排序，以使关键查找更快。使用OPTIMIZE TABLE时，没有可能发生工具与服务器之间的不良交互，因为当您使用OPTIMIZE TABLE时，服务器会完成所有工作。参见 Section 15.7.3.4, “OPTIMIZE TABLE Statement”。

myisamchk 还有许多其他选项，可用于提高表的性能：

• --analyze 或 -a：执行关键分布分析。这通过使连接优化器更好地选择连接表的顺序和应该使用的索引来提高连接性能。

• --sort-index 或 -S：对索引块进行排序。这样可以优化查找并使使用索引的表扫描更快。

• --sort-records=*index_num* 或 -R *index_num*：根据给定的索引对数据行进行排序。这样可以使您的数据更加局部化，并可能加快使用该索引的基于范围的SELECT和ORDER BY操作。

有关所有可用选项的完整描述，请参见 Section 6.6.4, “myisamchk — MyISAM Table-Maintenance Utility”。

9.6.5 设置 MyISAM 表维护计划

原文：dev.mysql.com/doc/refman/8.0/en/myisam-maintenance-schedule.html

定期执行表检查是一个好主意，而不是等到问题发生时再处理。检查和修复MyISAM表的一种方法是使用CHECK TABLE和REPAIR TABLE语句。参见第 15.7.3 节，“表维护语句”。

另一种检查表的方法是使用myisamchk。为了维护目的，您可以使用myisamchk -s。-s 选项（简写为--silent）会导致myisamchk以静默模式运行，仅在发生错误时打印消息。

启用自动的MyISAM表检查也是一个好主意。例如，每当机器在更新过程中重新启动时，通常需要在进一步使用之前检查可能受影响的每个表。（这些是“预期崩溃的表”）。要使服务器自动检查MyISAM表，请使用设置myisam_recover_options系统变量来启动。参见第 7.1.8 节，“服务器系统变量”。

在正常系统运行期间，您还应定期检查您的表。例如，您可以运行一个cron作业，每周检查一次重要的表，可以在crontab文件中使用以下行：

35 0 * * 0 */path/to/myisamchk* --fast --silent */path/to/datadir*/*/*.MYI

这将打印出有关崩溃表的信息，以便您可以根据需要检查和修复它们。

首先，在所有在过去 24 小时内更新过的表上每晚执行myisamchk -s。当您发现问题很少发生时，您可以将检查频率减少到每周一次或类似频率。

通常，MySQL 表需要很少的维护。如果您对具有动态大小行（具有VARCHAR、BLOB或TEXT列的MyISAM表进行了许多更新，或者有许多已删除行的表，您可能希望不时地对表进行碎片整理/回收空间。您可以通过对相关表使用OPTIMIZE TABLE来实现这一点。或者，如果您可以暂停mysqld服务器一段时间，切换到数据目录并在服务器停止时使用以下命令：

$> myisamchk -r -s --sort-index --myisam_sort_buffer_size=16M */*.MYI

第十章 优化

原文：dev.mysql.com/doc/refman/8.0/en/optimization.html

目录

10.1 优化概述

10.2 优化 SQL 语句

10.2.1 优化 SELECT 语句

10.2.2 优化子查询、派生表、视图引用和公共表表达式

10.2.3 优化 INFORMATION_SCHEMA 查询

10.2.4 优化性能模式查询

10.2.5 优化数据更改语句

10.2.6 优化数据库权限

10.2.7 其他优化技巧

10.3 优化和索引

10.3.1 MySQL 如何使用索引

10.3.2 主键优化

10.3.3 空间索引优化

10.3.4 外键优化

10.3.5 列索引

10.3.6 多列索引

10.3.7 验证索引使用

10.3.8 InnoDB 和 MyISAM 索引统计收集

10.3.9 B-Tree 和 Hash 索引比较

10.3.10 索引扩展的使用

10.3.11 生成列索引优化器使用

10.3.12 隐藏索引

10.3.13 降序索引

10.3.14 从 TIMESTAMP 列进行索引查找

10.4 优化数据库结构

10.4.1 优化数据大小

10.4.2 优化 MySQL 数据类型

10.4.3 优化多表查询

10.4.4 MySQL 中内部临时表的使用

10.4.5 数据库和表数量限制

10.4.6 表大小限制

10.4.7 表列数和行大小限制

10.5 优化 InnoDB 表

10.5.1 优化 InnoDB 表的存储布局

10.5.2 优化 InnoDB 事务管理

10.5.3 优化 InnoDB 只读事务

10.5.4 优化 InnoDB 重做日志记录

10.5.5 InnoDB 表的批量数据加载

10.5.6 优化 InnoDB 查询

10.5.7 优化 InnoDB DDL 操作

10.5.8 优化 InnoDB 磁盘 I/O

10.5.9 优化 InnoDB 配置变量

10.5.10 为具有多个表的系统优化 InnoDB

10.6 为 MyISAM 表优化

10.6.1 优化 MyISAM 查询

10.6.2 MyISAM 表的批量数据加载

10.6.3 优化 REPAIR TABLE 语句

10.7 为 MEMORY 表优化

10.8 理解查询执行计划

10.8.1 使用 EXPLAIN 优化查询

10.8.2 EXPLAIN 输出格式

10.8.3 扩展 EXPLAIN 输出格式

10.8.4 获取命名连接的执行计划信息

10.8.5 估算查询性能

10.9 控制查询优化器

10.9.1 控制查询计划评估

10.9.2 可切换的优化

10.9.3 优化器提示

10.9.4 索引提示

10.9.5 优化器成本模型

10.9.6 优化器统计信息

10.10 缓冲和缓存

10.10.1 InnoDB 缓冲池优化

10.10.2 MyISAM 键缓存

10.10.3 缓存准备语句和存储程序

10.11 优化锁定操作

10.11.1 内部锁定方法

10.11.2 表锁定问题

10.11.3 并发插入

10.11.4 元数据锁定

10.11.5 外部锁定

10.12 优化 MySQL 服务器

10.12.1 优化磁盘 I/O

10.12.2 使用符号链接

10.12.3 优化内存使用

10.13 测量性能（基准测试）

10.13.1 测量表达式和函数的速度

10.13.2 使用自己的基准

10.13.3 使用 performance_schema 测量性能

10.14 检查服务器线程（进程）信息

10.14.1 访问进程列表

10.14.2 线程命令值

10.14.3 一般线程状态

10.14.4 复制源线程状态

10.14.5 复制 I/O（接收器）线程状态

10.14.6 复制 SQL 线程状态

10.14.7 复制连接线程状态

10.14.8 NDB 集群线程状态

10.14.9 事件调度器线程状态

本章介绍了如何优化 MySQL 性能并提供了示例。优化涉及在多个级别配置、调整和衡量性能。根据您的工作角色（开发人员、数据库管理员或两者兼而有之），您可能会在单个 SQL 语句、整个应用程序、单个数据库服务器或多个网络化数据库服务器的级别进行优化。有时您可以积极主动地提前规划性能，而其他时候您可能需要在出现问题后排查配置或代码问题。优化 CPU 和内存使用也可以提高可伸缩性，使数据库能够处理更多负载而不会变慢。

10.1 优化概述

原文：dev.mysql.com/doc/refman/8.0/en/optimize-overview.html

数据库性能取决于数据库级别的几个因素，例如表、查询和配置设置。这些软件构造在硬件级别导致 CPU 和 I/O 操作，您必须尽量减少并尽可能高效地执行。在处理数据库性能时，您首先学习软件方面的高级规则和指南，并使用挂钟时间来衡量性能。随着您成为专家，您将了解更多内部发生的事情，并开始测量诸如 CPU 周期和 I/O 操作之类的内容。

典型用户的目标是在现有软件和硬件配置中获得最佳的数据库性能。高级用户寻求改进 MySQL 软件本身的机会，或者开发自己的存储引擎和硬件设备，以扩展 MySQL 生态系统。

• 在数据库级别进行优化

• 在硬件级别进行优化

• 平衡可移植性和性能

在数据库级别进行优化

使数据库应用程序快速的最重要因素是其基本设计：

• 表是否结构良好？特别是，列是否具有正确的数据类型，每个表是否具有适合工作类型的适当列？例如，频繁更新的应用程序通常有许多列较少的表，而分析大量数据的应用程序通常有少量列较多的表。

• 是否有正确的索引以使查询高效？

• 您是否为每个表使用适当的存储引擎，并充分利用您使用的每个存储引擎的优势和特性？特别是，选择事务性存储引擎如 InnoDB 或非事务性存储引擎如 MyISAM 对性能和可伸缩性非常重要。

  注意

  InnoDB 是新表的默认存储引擎。实际上，高级的 InnoDB 性能特性意味着 InnoDB 表通常比简单的 MyISAM 表性能更好，特别是对于繁忙的数据库。

• 每个表是否使用适当的行格式？此选择还取决于表使用的存储引擎。特别是，压缩表使用更少的磁盘空间，因此需要更少的磁盘 I/O 来读取和写入数据。压缩适用于所有类型的工作负载，包括 InnoDB 表和只读 MyISAM 表。

• 应用程序是否使用适当的锁定策略？例如，尽可能允许共享访问，以便数据库操作可以并发运行，并在适当时请求独占访问，以便关键操作获得最高优先级。再次强调，存储引擎的选择非常重要。InnoDB存储引擎处理大部分锁定问题而无需你的参与，从而实现数据库更好的并发性，并减少对代码的实验和调整量。

• 所有用于缓存的内存区域大小是否正确？也就是说，足够大以容纳频繁访问的数据，但不要太大以超载物理内存并导致分页。要配置的主要内存区域是InnoDB缓冲池和MyISAM关键缓存。

在硬件级别进行优化

任何数据库应用程序最终都会受到硬件限制的影响，随着数据库变得越来越繁忙。数据库管理员必须评估是否可能调整应用程序或重新配置服务器以避免这些瓶颈，或者是否需要更多的硬件资源。系统瓶颈通常来自以下来源：

• 磁盘寻道。磁盘找到一段数据需要时间。现代磁盘，这个平均时间通常低于 10ms，因此理论上我们可以每秒做大约 100 次寻道。这个时间随着新磁盘的推出而缓慢改善，对于单个表来说很难优化。优化寻道时间的方法是将数据分布到多个磁盘上。

• 磁盘读写。当磁盘处于正确位置时，我们需要读取或写入数据。现代磁盘，一个磁盘至少提供 10-20MB/s 的吞吐量。这比寻道更容易优化，因为你可以同时从多个磁盘并行读取。

• CPU 周期。当数据在主内存中时，我们必须处理它以获得结果。与内存量相比，拥有大表是最常见的限制因素。但对于小表，速度通常不是问题。

• 内存带宽。当 CPU 需要的数据超过 CPU 缓存容量时，主内存带宽成为瓶颈。对于大多数系统来说，这是一个不常见的瓶颈，但需要注意。

平衡便携性和性能

要在便携式 MySQL 程序中使用面向性能的 SQL 扩展，可以将 MySQL 特定关键字包装在/*! */注释分隔符内的语句中。其他 SQL 服务器会忽略注释的关键字。有关编写注释的信息，请参阅第 11.7 节“注释”。

10.2 优化 SQL 语句

原文：dev.mysql.com/doc/refman/8.0/en/statement-optimization.html

10.2.1 优化 SELECT 语句

10.2.2 优化子查询、派生表、视图引用和公共表达式

10.2.3 优化 INFORMATION_SCHEMA 查询

10.2.4 优化 Performance Schema 查询

10.2.5 优化数据更改语句

10.2.6 优化数据库权限

10.2.7 其他优化技巧

数据库应用程序的核心逻辑是通过 SQL 语句执行的，无论是直接通过解释器发出还是通过 API 在后台提交。本节中的调优指南有助于加快各种 MySQL 应用程序的速度。这些指南涵盖了读取和写入数据的 SQL 操作，一般 SQL 操作的后台开销，以及在特定场景中使用的操作，如数据库监控。

10.2.1 优化 SELECT 语句

原文：dev.mysql.com/doc/refman/8.0/en/select-optimization.html

10.2.1.1 WHERE 子句优化

10.2.1.2 范围优化

10.2.1.3 索引合并优化

10.2.1.4 哈希连接优化

10.2.1.5 引擎条件下推优化

10.2.1.6 索引条件下推优化

10.2.1.7 嵌套循环连接算法

10.2.1.8 嵌套连接优化

10.2.1.9 外连接优化

10.2.1.10 外连接简化

10.2.1.11 多范围读取优化

10.2.1.12 块嵌套循环和批量键访问连接

10.2.1.13 条件过滤

10.2.1.14 常量折叠优化

10.2.1.15 IS NULL 优化

10.2.1.16 ORDER BY 优化

10.2.1.17 GROUP BY 优化

10.2.1.18 DISTINCT 优化

10.2.1.19 LIMIT 查询优化

10.2.1.20 函数调用优化

10.2.1.21 窗口函数优化

10.2.1.22 行构造表达式优化

10.2.1.23 避免全表扫描

查询以SELECT语句的形式在数据库中执行所有查找操作。调优这些语句是首要任务，无论是为了实现动态网页的亚秒响应时间，还是为了缩短生成大型隔夜报告的时间。

除了SELECT语句外，对查询的调优技术也适用于诸如CREATE TABLE...AS SELECT、INSERT INTO...SELECT以及DELETE语句中的WHERE子句。这些语句有额外的性能考虑，因为它们将写操作与面向读取的查询操作结合在一起。

NDB Cluster 支持连接下推优化，其中符合条件的连接完整地发送到 NDB Cluster 数据节点，可以在这些节点之间分发并并行执行。有关此优化的更多信息，请参阅 Conditions for NDB pushdown joins。

优化查询的主要考虑因素包括：

• 要使慢SELECT ... WHERE查询更快，首先要检查的是是否可以添加索引。在WHERE子句中设置索引，以加快评估、过滤和最终检索结果。为了避免浪费磁盘空间，构建一小组索引，可以加速应用程序中使用的许多相关查询。

  索引对于引用不同表的查询特别重要，使用诸如连接和外键等功能。您可以使用EXPLAIN语句确定哪些索引用于SELECT。请参阅 Section 10.3.1，“MySQL 如何使用索引”和 Section 10.8.1，“使用 EXPLAIN 优化查询”。

• 隔离和调整查询的任何部分，例如需要大量时间的函数调用。根据查询的结构，函数可能会为结果集中的每一行调用一次，甚至为表中的每一行调用一次，从而极大地放大任何低效性。

• 尽量减少查询中的全表扫描次数，特别是对于大表。

• 使用ANALYZE TABLE语句定期更新表统计信息，以便优化器具有构建高效执行计划所需的信息。

• 了解针对每个表的存储引擎特定的调整技术、索引技术和配置参数。InnoDB和MyISAM都有一套指导原则，用于启用和维持查询的高性能。有关详细信息，请参阅 Section 10.5.6，“优化 InnoDB 查询”和 Section 10.6.1，“优化 MyISAM 查询”。

• 您可以使用 Section 10.5.3，“优化 InnoDB 只读事务”中的技术，为InnoDB表优化单个查询事务。

• 避免以使其难以理解的方式转换查询，特别是如果优化器自动执行了一些相同的转换。

• 如果性能问题不容易通过基本准则之一解决，请通过阅读EXPLAIN计划的内部细节并调整您的索引、WHERE子句、连接子句等来调查特定查询。 （当您达到一定的专业水平时，阅读EXPLAIN计划可能是每个查询的第一步。）

• 调整 MySQL 用于缓存的内存区域的大小和属性。通过有效利用InnoDB 缓冲池、MyISAM 关键字缓存和 MySQL 查询缓存，重复查询会更快运行，因为第二次及以后的结果是从内存中检索的。

• 即使使用缓存内存区域运行快速查询，您仍然可以进一步优化，使其需要更少的缓存内存，从而使您的应用程序更具可扩展性。可扩展性意味着您的应用程序可以处理更多同时用户、更大请求等，而不会出现性能大幅下降。

• 处理锁定问题，其中您的查询速度可能会受到其他会话同时访问表的影响。

原文：dev.mysql.com/doc/refman/8.0/en/where-optimization.html

10.2.1.1 WHERE 子句优化

本节讨论了用于处理WHERE子句的优化。示例使用SELECT语句，但相同的优化也适用于DELETE和UPDATE语句中的WHERE子句。

注意

由于 MySQL 优化器的工作正在进行中，MySQL 执行的并非所有优化都在此处记录。

您可能会尝试重写查询以加快算术运算速度，但会牺牲可读性。由于 MySQL 自动执行类似的优化，您通常可以避免这项工作，并将查询保留在更易理解和可维护的形式中。MySQL 执行的一些优化如下：

• 删除不必要的括号：

   ((a AND b) AND c OR (((a AND b) AND (c AND d))))
  -> (a AND b AND c) OR (a AND b AND c AND d)
  

• 常量折叠：

   (a<b AND b=c) AND a=5
  -> b>5 AND b=c AND a=5
  

• 常量条件移除：

   (b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6)
  -> b=5 OR b=6
  

  在 MySQL 8.0.14 及更高版本中，这是在准备阶段而不是在优化阶段进行的，这有助于简化连接。有关更多信息和示例，请参见 Section 10.2.1.9, “Outer Join Optimization”。

• 用于索引的常量表达式仅评估一次。

• 从 MySQL 8.0.16 开始，将检查和折叠或删除数值类型列与常量值的比较，以处理无效或超出范围的值：

  # CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);
    SELECT * FROM t WHERE c ≪ 256;
  -≫ SELECT * FROM t WHERE 1;
  

  更多信息请参见 Section 10.2.1.14, “Constant-Folding Optimization”。

• 在没有WHERE的单个表上的COUNT(*)直接从MyISAM和MEMORY表的表信息中检索。当与仅一个表一起使用时，对于任何NOT NULL表达式也会执行此操作。

• 早期检测无效常量表达式。MySQL 快速检测到一些SELECT语句是不可能的，并且不返回任何行。

• 如果您不使用GROUP BY或聚合函数（COUNT(), MIN()等），HAVING将与WHERE合并。

• 对于连接中的每个表，构建一个更简单的WHERE以获得快速的表WHERE评估，并尽快跳过行。

• 在查询中的任何其他表之前首先读取所有常量表。常量表包括以下内容：

  • 空表或具有一行的表。

  • 在PRIMARY KEY或UNIQUE索引上使用WHERE子句的表，其中所有索引部分与常量表达式进行比较并定义为NOT NULL。

  所有以下表都被用作常量表：

  SELECT * FROM t WHERE *primary_key*=1;
  SELECT * FROM t1,t2
    WHERE t1.*primary_key*=1 AND t2.*primary_key*=t1.id;
  

• 通过尝试所有可能性来找到连接表的最佳连接组合。如果ORDER BY和GROUP BY子句中的所有列来自同一张表，那么在连接时首选该表。

• 如果存在ORDER BY子句和不同的GROUP BY子句，或者如果ORDER BY或GROUP BY包含来自连接队列中第一张表以外的表的列，那么会创建一个临时表。

• 如果使用SQL_SMALL_RESULT修饰符，MySQL 会使用内存临时表。

• 每个表索引都会被查询，除非优化器认为使用表扫描更有效，否则会使用最佳索引。曾经，基于最佳索引是否跨越表的 30%以上而使用扫描，但现在不再根据固定百分比来决定使用索引还是扫描。优化器现在更加复杂，根据诸如表大小、行数和 I/O 块大小等额外因素来估计。

• 在某些情况下，MySQL 可以从索引中读取行，甚至无需查阅数据文件。如果从索引中使用的所有列都是数字型的，则仅使用索引树来解析查询。

• 在输出每一行之前，不符合HAVING子句的行会被跳过。

一些查询的示例非常快速：

SELECT COUNT(*) FROM *tbl_name*;

SELECT MIN(*key_part1*),MAX(*key_part1*) FROM *tbl_name*;

SELECT MAX(*key_part2*) FROM *tbl_name*
  WHERE *key_part1*=*constant*;

SELECT ... FROM *tbl_name*
  ORDER BY *key_part1*,*key_part2*,... LIMIT 10;

SELECT ... FROM *tbl_name*
  ORDER BY *key_part1* DESC, *key_part2* DESC, ... LIMIT 10;

MySQL 仅使用索引树解析以下查询，假设索引列是数字型的：

SELECT *key_part1*,*key_part2* FROM *tbl_name* WHERE *key_part1*=*val*;

SELECT COUNT(*) FROM *tbl_name*
  WHERE *key_part1*=*val1* AND *key_part2*=*val2*;

SELECT MAX(*key_part2*) FROM *tbl_name* GROUP BY *key_part1*;

以下查询使用索引检索按排序顺序排列的行，而无需单独的排序过程：

SELECT ... FROM *tbl_name*
  ORDER BY *key_part1*,*key_part2*,... ;

SELECT ... FROM *tbl_name*
  ORDER BY *key_part1* DESC, *key_part2* DESC, ... ;

原文：dev.mysql.com/doc/refman/8.0/en/range-optimization.html

10.2.1.2 范围优化

range 访问方法使用单个索引检索包含在一个或多个索引值区间内的表行子集。它可用于单部分或多部分索引。以下部分描述了优化器在何种条件下使用范围访问。

• 单部分索引的范围访问方法

• 多部分索引的范围访问方法

• 多值比较的等值范围优化

• 跳过扫描范围访问方法

• 行构造表达式的范围优化

• 限制范围优化的内存使用

单部分索引的范围访问方法

对于单部分索引，索引值区间可以通过WHERE子句中对应的条件方便地表示，表示为范围条件而不是“区间”。

单部分索引的范围条件定义如下：

• 对于BTREE和HASH索引，当使用=、<=>、IN()、IS NULL或IS NOT NULL运算符进行键部分与常量值的比较时，为范围条件。

• 此外，对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=或<>运算符进行键部分与常量值的比较，或者对于LIKE比较，如果LIKE的参数是不以通配符字符开头的常量字符串，则为范围条件。

• 对于所有索引类型，多个范围条件与OR或AND组合形成一个范围条件。

在前述描述中，“常量值”指以下之一：

• 来自查询字符串的一个常量

• 来自相同连接的const或system表的列

• 一个无关子查询的结果

• 由前述类型的子表达式完全组成的任何表达式

以下是一些在WHERE子句中具有范围条件的查询示例：

SELECT * FROM t1
  WHERE *key_col* > 1
  AND *key_col* < 10;

SELECT * FROM t1
  WHERE *key_col* = 1
  OR *key_col* IN (15,18,20);

SELECT * FROM t1
  WHERE *key_col* LIKE 'ab%'
  OR *key_col* BETWEEN 'bar' AND 'foo';

优化器常量传播阶段可能会将一些非常量值转换为常量。

MySQL 尝试从WHERE子句中提取每个可能索引的范围条件。在提取过程中，无法用于构建范围条件的条件被丢弃，产生重叠范围的条件被合并，产生空范围的条件被移除。

考虑以下语句，其中key1是一个索引列，而nonkey不是索引列：

SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z');

对key1的提取过程如下：

1. 从原始WHERE子句开始：

   (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
   (key1 < 'bar' AND nonkey = 4) OR
   (key1 < 'uux' AND key1 > 'z')
   

2. 移除nonkey = 4和key1 LIKE '%b'，因为它们不能用于范围扫描。正确的做法是用TRUE替换它们，这样在进行范围扫描时不会错过任何匹配的行。将它们替换为TRUE得到：

   (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
   (key1 < 'bar' AND TRUE) OR
   (key1 < 'uux' AND key1 > 'z')
   

3. 合并始终为真或假的条件：

   • (key1 LIKE 'abcde%' OR TRUE)始终为真

   • (key1 < 'uux' AND key1 > 'z')始终为假

   将这些条件替换为常量得到：

   (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
   

   移除不必要的TRUE和FALSE常量得到：

   (key1 < 'abc') OR (key1 < 'bar')
   

4. 将重叠的区间合并为一个得到用于范围扫描的最终条件：

   (key1 < 'bar')
   

一般来说（并且如前面的示例所示），用于范围扫描的条件比WHERE子句要宽松。MySQL 执行额外检查以过滤出满足范围条件但不满足完整WHERE子句的行。

范围条件提取算法可以处理任意深度的嵌套AND/OR结构，并且其输出不依赖于条件在WHERE子句中出现的顺序。

MySQL 不支持合并多个范围用于空间索引的range访问方法。为了解决这个限制，可以使用具有相同SELECT语句的UNION，只是将每个空间谓词放在不同的SELECT中。

用于多部分索引的范围访问方法

多部分索引上的范围条件是单部分索引的范围条件的扩展。多部分索引上的范围条件将索引行限制在一个或多个键元组区间内。键元组区间是在索引上使用的键元组集合上定义的，使用索引的排序。

例如，考虑一个定义为key1(*key_part1*, *key_part2*, *key_part3*)的多部分索引，以及按关键顺序列出的以下一组关键元组：

*key_part1*  *key_part2*  *key_part3*
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

条件*key_part1* = 1定义了这个区间：

(1,-inf,-inf) <= (*key_part1*,*key_part2*,*key_part3*) < (1,+inf,+inf)

该区间涵盖了前述数据集中的第 4、5 和 6 个元组，并可被范围访问方法使用。

相比之下，条件*key_part3* = 'abc'并未定义单个区间，因此无法被范围访问方法使用。

以下描述更详细地说明了多部分索引的范围条件如何工作。

• 对于HASH索引，每个包含相同值的区间都可以使用。这意味着区间只能针对以下形式的条件生成：

   *key_part1* *cmp* *const1*
  AND *key_part2* *cmp* *const2*
  AND ...
  AND *key_partN* *cmp* *constN*;
  

  在这里，const1、const2、…是常量，*cmp是=、<=>或IS NULL比较运算符之一，条件涵盖了所有索引部分。（也就是说，有N个条件，每个条件对应一个N*部分索引的部分。）例如，以下是三部分HASH索引的范围条件：

  *key_part1* = 1 AND *key_part2* IS NULL AND *key_part3* = 'foo'
  

  有关什么被视为常量的定义，请参阅单部分索引的范围访问方法。

• 对于BTREE索引，一个区间可能适用于与AND组合的条件，其中每个条件将一个关键部分与常量值使用=、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN或LIKE '*pattern*'进行比较（其中'*pattern*'不以通配符开头）。只要能够确定包含所有符合条件的行的单个关键元组（或者如果使用<>或!=则为两个区间），就可以使用区间。

  优化器尝试使用额外的键部分来确定区间，只要比较运算符是=、<=>或IS NULL。如果运算符是>、<、>=、<=、!=、<>、BETWEEN或LIKE，优化器会使用它，但不考虑更多的键部分。对于以下表达式，优化器从第一个比较中使用=。它还从第二个比较中使用>=，但不考虑更多的键部分，并且不使用第三个比较来构建区间：

  *key_part1* = 'foo' AND *key_part2* >= 10 AND *key_part3* > 10
  

  单个区间是：

  ('foo',10,-inf) < (*key_part1*,*key_part2*,*key_part3*) < ('foo',+inf,+inf)
  

  创建的区间可能包含比初始条件更多的行。例如，前面的区间包括值('foo', 11, 0)，这不符合原始条件。

• 如果涵盖区间内的行集的条件与OR组合，它们形成一个涵盖其区间并集内的行集的条件。如果条件与AND组合，它们形成一个涵盖其区间交集内的行集的条件。例如，对于两部分索引上的此条件：

  (*key_part1* = 1 AND *key_part2* < 2) OR (*key_part1* > 5)
  

  区间是：

  (1,-inf) < (*key_part1*,*key_part2*) < (1,2)
  (5,-inf) < (*key_part1*,*key_part2*)
  

  在此示例中，第一行的区间使用一个键部分作为左边界，两个键部分作为右边界。第二行的区间仅使用一个键部分。EXPLAIN输出中的key_len列指示使用的键前缀的最大长度。

  在某些情况下，key_len可能表明使用了一个键部分，但这可能不是您期望的。假设*key_part1和key_part2*可以是NULL。然后，key_len列显示以下条件的两个键部分长度：

  *key_part1* >= 1 AND *key_part2* < 2
  

  但实际上，条件被转换为这样：

  *key_part1* >= 1 AND *key_part2* IS NOT NULL
  

有关如何对单部分索引上的范围条件执行优化以组合或消除区间的描述，请参见单部分索引的范围访问方法。类似的步骤也适用于多部分索引上的范围条件。

多值比较的等值范围优化

考虑以下表达式，其中*col_name*是一个索引列：

*col_name* IN(*val1*, ..., *valN*)
*col_name* = *val1* OR ... OR *col_name* = *valN*

如果 col_name 等于多个值中的任何一个，则每个表达式为真。这些比较是相等范围比较（其中“范围”是单个值）。优化器估计读取符合相等范围比较条件的行的成本如下：

• 如果 col_name 上有唯一索引，则每个范围的行估计值为 1，因为最多只有一行可以具有给定值。

• 否则，col_name 上的任何索引都是非唯一的，优化器可以通过对索引或索引统计数据的深入来估计每个范围的行数。

使用索引深入，优化器在每个范围的两端进行深入，并将范围内的行数作为估计值。例如，表达式 *col_name* IN (10, 20, 30) 有三个相等范围，优化器每个范围进行两次深入以生成行估计值。每对深入提供给定值的行数的估计值。

索引深入提供准确的行估计值，但随着表达式中比较值的增加，优化器生成行估计值的时间也会增加。使用索引统计数据比索引深入的准确性低，但允许更快地为大型值列表进行行估计。

eq_range_index_dive_limit 系统变量允许您配置优化器在何时从一种行估计策略切换到另一种。要允许使用索引深入进行多达 N 个相等范围的比较，请将 eq_range_index_dive_limit 设置为 N + 1. 要禁用统计数据的使用并始终使用索引深入，无论 N 如何，请将 eq_range_index_dive_limit 设置为 0。

要更新表索引统计数据以获得最佳估计值，请使用 ANALYZE TABLE。

在 MySQL 8.0 之前，除了使用 eq_range_index_dive_limit 系统变量外，没有跳过使用索引深入来估计索引有用性的方法。在 MySQL 8.0 中，对于满足以下所有条件的查询，可以跳过索引深入估计：

• 查询针对单个表，而不是多个表的连接。

• 存在单索引 FORCE INDEX 索引提示。这样做的想法是，如果强制使用索引，则执行对索引的深入所带来的额外开销没有任何好处。

• 索引是非唯一的，不是 FULLTEXT 索引。

• 没有子查询。

• 没有 DISTINCT、GROUP BY 或 ORDER BY 子句。

对于 EXPLAIN FOR CONNECTION，如果跳过索引深入，则输出如下更改：

• 对于传统输出，rows 和 filtered 值为 NULL。

• 对于 JSON 输出，rows_examined_per_scan和rows_produced_per_join不会出现，skip_index_dive_due_to_force为true，成本计算不准确。

没���FOR CONNECTION，当索引潜水被跳过时，EXPLAIN输出不会改变。

执行一个查询，其中索引潜水被跳过后，信息模式OPTIMIZER_TRACE表中的相应行包含一个index_dives_for_range_access值为skipped_due_to_force_index。

跳过扫描范围访问方法

考虑以下情景：

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

要执行这个查询，MySQL 可以选择一个索引扫描来获取所有行（索引包括所有要选择的列），然后应用WHERE子句中的f2 > 40条件来生成最终结果集。

范围扫描比完整索引扫描更有效，但在这种情况下无法使用，因为对第一个索引列f1没有条件。然而，从 MySQL 8.0.13 开始，优化器可以执行多个范围扫描，每个值f1一个，使用一种称为 Skip Scan 的方法，类似于 Loose Index Scan（参见 Section 10.2.1.17, “GROUP BY Optimization”）：

1. 在第一个索引部分f1（索引前缀）的不同值之间跳过。

2. 对剩余索引部分上的f2 > 40条件的每个不同前缀值执行子范围扫描。

对于之前显示的数据集，算法的操作如下：

1. 获取第一个关键部分的第一个不同值（f1 = 1）。

2. 基于第一个和第二个关键部分构建范围（f1 = 1 AND f2 > 40）。

3. 执行一个范围扫描。

4. 获取第一个关键部分的下一个不同值（f1 = 2）。

5. 基于第一个和第二个关键部分构建范围（f1 = 2 AND f2 > 40）。

6. 执行一个范围扫描。

使用这种策略可以减少访问的行数，因为 MySQL 跳过了不符合每个构建范围的行。这种 Skip Scan 访问方法适用于以下条件：

• 表 T 至少有一个复合索引，其关键部分形式为([A_1, ..., A_k,] B_1, ..., B_m, C [, D_1, ..., D_n])。关键部分 A 和 D 可能为空，但 B 和 C 必须非空。

• 查询仅引用一个表。

• 查询不使用GROUP BY或DISTINCT。

• 查询仅引用索引中的列。

• A_1, ..., A_*k*上的谓词必须是等式谓词，且它们必须是常量。这包括IN()运算符。

• 查询必须是一个连接查询；也就是说，是OR条件的AND：(*cond1*(*key_part1*) OR *cond2*(*key_part1*)) AND (*cond1*(*key_part2*) OR ...) AND ...

• 必须对 C 列有一个范围条件。

• 允许对 D 列的条件。D 列上的条件必须与 C 列上的范围条件一起。

在EXPLAIN输出中指示使用跳过扫描如下：

• 在Extra列中使用Using index for skip scan表示使用了宽松索引跳过扫描访问方法。

• 如果索引可以用于跳过扫描，索引应该在possible_keys列中可见。

在优化器跟踪输出中，使用"skip scan"元素指示使用跳过扫描：

"skip_scan_range": {
  "type": "skip_scan",
  "index": *index_used_for_skip_scan*,
  "key_parts_used_for_access": [*key_parts_used_for_access*],
  "range": [*range*]
}

您还可能会看到一个"best_skip_scan_summary"元素。如果跳过扫描被选择为最佳范围访问变体，则会写入"chosen_range_access_summary"。如果跳过扫描被选择为整体最佳访问方法，则会出现"best_access_path"元素。

使用跳过扫描取决于optimizer_switch系统变量的skip_scan标志的值。请参见 Section 10.9.2, “Switchable Optimizations”。默认情况下，此标志为on。要禁用它，请将skip_scan设置为off。

除了使用optimizer_switch系统变量来控制会话范围内优化器使用跳过扫描外，MySQL 还支持优化器提示以影响每个语句的优化器。请参见 Section 10.9.3, “Optimizer Hints”。

行构造器表达式的范围优化

优化器能够将范围扫描访问方法应用于此类查询：

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

以前，为了使用范围扫描，必须将查询编写为：

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );

为了使优化器使用范围扫描，查询必须满足以下条件：

• 仅使用IN()谓词，不使用NOT IN()。

• 在IN()谓词的左侧，行构造器仅包含列引用。

• 在IN()谓词的右侧，行构造器仅包含运行时常量，这些常量可以是文字或在执行期间绑定为常量的本地列引用。

• 在IN()谓词的右侧，存在多个行构造器。

有关优化器和行构造器的更多信息，请参见 Section 10.2.1.22, “Row Constructor Expression Optimization”

限制范围优化的内存使用

要控制范围优化器可用的内存，请使用range_optimizer_max_mem_size系统变量：

• 值为 0 表示“没有限制”。

• 当值大于 0 时，优化器会跟踪考虑范围访问方法时消耗的内存。如果即将超过指定限制，范围访问方法将被放弃，而考虑其他方法，包括全表扫描。这可能不太理想。如果发生这种情况，将出现以下警告（其中*N*是当前range_optimizer_max_mem_size值）：

  Warning    3170    Memory capacity of *N* bytes for
                     'range_optimizer_max_mem_size' exceeded. Range
                     optimization was not done for this query.
  

• 对于UPDATE和DELETE语句，如果优化器回退到全表扫描，并且启用了sql_safe_updates系统变量，则会发生错误而不是警告，因为实际上没有使用键来确定要修改哪些行。有关更多信息，请参见使用安全更新模式（--safe-updates）")。

对于超出可用范围优化内存并且优化器回退到不太理想计划的单个查询，增加range_optimizer_max_mem_size值可能会提高性能。

要估算处理范围表达式所需的内存量，请使用以下准则：

• 对于像下面这样的简单查询，其中有一个候选键用于范围访问方法，每个与OR结合的谓词大约使用 230 字节：

  SELECT COUNT(*) FROM t
  WHERE a=1 OR a=2 OR a=3 OR .. . a=*N*;
  

• 类似地，对于像下面这样的查询，每个与AND结合的谓词大约使用 125 字节：

  SELECT COUNT(*) FROM t
  WHERE a=1 AND b=1 AND c=1 ... *N*;
  

• 对于带有IN()谓词的查询：

  SELECT COUNT(*) FROM t
  WHERE a IN (1,2, ..., *M*) AND b IN (1,2, ..., *N*);
  

  在IN()列表中的每个文字值都算作与OR结合的谓词。如果有两个IN()列表，则与OR结合的谓词数量是每个列表中文字值数量的乘积。因此，在前述情况下，与OR结合的谓词数量为*M* × N。