MySQL 8 疑难解答详解

在上一章中，我们学习了 MySQL 8 数据库的一个重要方面、基准测试和最佳实践。基准测试有助于将当前数据库性能与预期性能矩阵进行比较。我们了解了什么是基准测试，以及可以用来确定 MySQL 8 服务器的基准性能的工具。在本章后面的部分中，我们了解了 memcached、复制、分区和索引应遵循的最佳实践。最佳实践有助于确保 MySQL 8 数据库的最佳配置。

在本章中，我们将重点了解在使用 MySQL 8 数据库时可能遇到的常见错误。这些错误可能是服务器错误或客户端错误。我们将研究确定问题是否已发生的方法。我们还将学习错误的故障排除和解决技术。在本章后面的部分中，我们将研究这些技术适用的真实场景。以下是要涵盖的主题列表：

当故障排除是一个问题时，要做的第一件事是在我们遇到问题时找出导致故障的程序或设备。

以下是指示硬件或内核问题的症状：

• 键盘不工作。可按Caps Lock键进行检查。如果Caps Lock键上的指示灯不亮，说明键盘有问题。同样，鼠标不移动表示鼠标有问题。
• ping是一个操作系统命令，用于检查一台机器与另一台机器的可访问性。执行 ping 命令的机器称为本地机器，而 ping 的机器称为远程机器。如果远程计算机没有响应本地计算机的 ping，则表明存在硬件或网络相关问题。
• 如果 MySQL 以外的程序工作不正常，则可能表明操作系统内核程序存在问题。
• 如果系统意外重启，则可能表明操作系统或硬件存在问题。在典型情况下，用户级程序永远不能关闭系统。

要对问题进行故障诊断与排除，可以执行以下一项或多项操作：

• 运行诊断工具以检查硬件
• 确保相关的库文件是最新的
• 检查操作系统的更新、修补程序或 service Pack 是否可用
• 检查所有连接

ECC memory是一种纠错码存储器。它可以检测和纠正最常见的内部数据损坏问题。建议使用 ECC 内存，以便在早期阶段检测内存问题。

以下说明可能有助于进一步确定问题：

• 检查系统日志文件可能有助于发现问题的原因。还必须检查 MySQL 日志文件，以防 MySQL 出现问题。
• 特定于操作系统的命令可用于检查内存、文件描述符、磁盘空间或其他关键资源的问题。
• 如果一个有问题的失控进程没有死掉，即使我们执行了一个杀死它的命令，也可以在操作系统内核中发现一个 bug。
• 如果硬件似乎没有问题，应尝试确定可能导致问题的程序。使用特定于操作系统的命令，如 Windows 上的任务管理器、Linux 上的ps和top或类似程序，我们可以识别消耗 CPU 或阻塞系统进程的程序。
• 即使键盘已锁定，也可以恢复对机器的访问。这可以通过从另一台机器登录到系统来完成。登录成功后执行kbd_mode -a命令。

MySQL 用户可以使用 MySQL 提供的多个通道之一报告问题。在检查了所有可能的替代方案之后，如果可以确定是 MySQL 服务器或 MySQL 客户端导致了问题，用户可以为邮件列表创建错误报告，或者与 MySQL 支持团队联系。bug 报告者必须提供关于 bug、系统信息、行为和预期行为的详细信息。报告者必须根据它似乎是 MySQL 错误的原因来描述原因。如果程序失败，了解以下信息非常有用：

• 借助top命令，检查相关程序是否占用了所有 CPU 时间。在这种情况下，我们应该允许程序运行一段时间，因为该程序可能正在执行密集的计算指令。
• 当客户端程序尝试连接到 MySQL 服务器时，观察该服务器的响应。它停止响应了吗？服务器是否提供了任何输出？
• 如果发现 MySQL 服务器在mysqld程序中引起问题，尝试使用mysqladmin程序连接，检查mysqld是否响应。可以使用mysqladmin -u root ping或mysqladmin -u root processlist命令。
• 失败的程序是否存在分段错误？

本节列出了用户经常遇到的最常见的 MySQL 错误。

MySQL 提供了一个特权系统，可以对从主机连接的用户进行身份验证，并将该用户与数据库的访问权限相关联。权限包括SELECT、INSERT、UPDATE、DELETE，能够识别匿名用户，并授予 MySQL 特定功能的权限，如LOAD DATA INFILE和管理操作。

由于多种原因，可能会发生拒绝访问错误。在许多情况下，问题是由 MySQL 帐户引起的，客户机程序使用 MySQL 帐户连接 MySQL 服务器，并获得服务器的权限。

在本节中，我们将重点介绍遇到无法连接到 MySQL 服务器错误的情况。但在我们开始讨论特定于错误的详细信息之前，有必要了解 MySQL 客户机如何连接到 MySQL 服务器。

在 Unix 系统上，MySQL 客户端可以通过两种不同的方式连接到mysqld服务器进程。以下是这两种方法的详细信息：

• TCP/IP 连接：mysqld服务器进程监听特定端口上的客户端连接。MySQL 客户端使用指定的 TCP/IP 端口连接到服务器。
• Unix 套接字文件：在这种连接方式下，Unix 套接字文件通过文件系统（/tmp/mysql.sock中的文件进行连接。

套接字文件连接比 TCP/IP 更快，但在连接到同一台计算机上的服务器时可以使用它。要使用 Unix 套接字文件，我们不指定主机名，或者应该指定一个特殊的主机名 localhost。

以下是 MySQL 客户端在 Windows 上连接 MySQL 服务器的方式：

• TCP/IP 连接：如前所述，对于 Unix 系统，TCP/IP 连接在指定的端口号上运行。MySQL 客户端连接到 MySQL 服务器正在侦听的端口。
• 命名管道连接：可以通过--enable-named-pipe选项启动 MySQL 服务器。如果客户端正在运行服务器的主机上运行，则客户端可以使用命名管道进行连接。MySQL是命名管道的默认名称。如果在连接到mysqld服务器进程时没有提供主机名，MySQL 会首先尝试连接到默认的命名管道。如果无法连接到命名管道，它将尝试连接到 TCP/IP 端口。通过使用.作为主机名，可以在 Windows 上强制使用命名管道。

MySQL 错误由预定义的唯一错误代码标识。同一错误可以有不同的错误代码与之关联。无法连接到 MySQL 服务器错误，错误代码为2002表示存在三个问题之一。可能是 MySQL 服务器未在系统上运行，或者提供的 Unix 套接字文件名不正确，或者提供的用于连接到服务器的 TCP/IP 端口号不正确。TCP/IP 端口可能被防火墙或端口阻止服务阻止。

错误代码2003也与无法连接 MySQL 服务器有关。它表示服务器拒绝网络连接。应该检查 MySQL 服务器是否启用了网络连接，MySQL 服务器是否正在运行，以及服务器上是否配置了指定的网络端口。

可以使用以下命令来确保mysqld服务器进程正在运行：

> ps xa | grep mysqld

如果mysqld服务器进程没有运行，我们应该启动服务器。如果服务器已在运行，则应使用以下命令：

> mysqladmin version
> mysqladmin variables
> mysqladmin -h `hostname` version variables
> mysqladmin -h `hostname` --port=3306 version 
> mysqladmin -h host_ip version
> mysqladmin --protocol=SOCKET --socket=/tmp/mysql.sock version

在前面的命令中，hostname是运行 MySQL 服务器的机器的主机名。host_ip是服务器机器的 IP 地址。

由于本节中解释的三种可能原因之一，可能会发生与 MySQL 服务器的连接丢失错误。

错误的潜在原因之一是网络连接很麻烦。如果这是一个常见错误，则应检查网络状况。如果查询期间的消息是错误消息的一部分，则可以确定错误是由于网络连接问题而发生的。

connection_timeout系统变量定义mysqld服务器在连接超时响应之前等待连接数据包的秒数。当客户端尝试与服务器建立初始连接，并且将connection_timeout值设置为几秒钟时，此错误可能会偶尔发生。在这种情况下，可以根据距离和连接速度增加connection_timeout值来解决问题。SHOW GLOBAL STATUS LIKE和Aborted_connects可用于确定我们是否更频繁地经历这种情况。可以肯定的是，如果错误消息包含读取授权包，增加connection_timeout值是解决方案。

由于二进制大对象（BLOB）的值比max_allowed_packet大，因此可能会面临该问题。这可能会导致与客户端的 MySQL 服务器连接中断错误。如果观察到ER_NET_PACKET_TOO_LARGE错误，则确认max_allowed_packet值应增加。

当使用不带密码值的--password或-p选项调用客户端程序时，MySQL 客户端要求输入密码。命令如下：

> mysql -u user_name -p
Enter password:

在一些系统上，当在选项文件或命令行中指定密码时，可能会发生密码工作正常的情况。但在Enter password:提示下，在命令提示下以交互方式输入时不起作用。发生这种情况是因为系统提供的用于读取密码的库将密码值限制为少量字符（通常为八个）。这是系统库的问题，而不是 MySQL 的问题。作为解决方法，将 MySQL 密码更改为八个或更少字符的值，或者将密码存储在选项文件中。

如果 AutoT0A.Server 从主机中接收到中间中断的连接请求太多，则会发生以下错误：

Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

max_connect_errors系统变量确定允许的连续中断连接请求的数量。一旦有max_connect_errors失败的请求而没有成功的连接，mysqld假设有什么问题，并阻止主机进行进一步的连接，直到发出FLUSH HOSTS语句或mysqladmin flush-hosts命令。

mysqld默认在 100 次连接错误后阻塞主机。可通过在服务器启动时设置max_connect_errors值进行调整，如下所示：

> mysqld_safe --max_connect_errors=10000

也可以在运行时设置此值，如下所示：

mysql> SET GLOBAL max_connect_errors=10000;

如果接收到特定主机的host_name被阻止错误，则应首先检查来自主机的 TCP/IP 连接是否没有问题。如果网络出现问题，增加max_connect_errors变量的值没有帮助。

此错误表示所有可用连接正在用于其他客户端连接。max_connections是控制到服务器的连接数的系统变量。最大连接数的默认值为 151。我们可以为max_connections系统变量设置大于 151 的值，以支持多于 151 的连接。

mysqld服务器进程实际上允许一个以上的max_connections（max_connections + 1值客户端进行连接。另外一个连接保留给具有CONNECTION_ADMIN或SUPER特权的帐户。该权限可授予具有PROCESS权限的管理员。通过此访问权限，管理员可以使用保留连接连接到服务器。他们可以执行SHOW PROCESSLIST命令来诊断问题，即使最大数量的客户端连接已用尽。

如果mysql没有足够的内存来存储 MySQL 客户端程序发出的整个查询请求，服务器会抛出以下错误：

mysql: Out of memory at line 42, 'malloc.c'
mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)
ERROR 2008: MySQL client ran out of memory

为了解决这个问题，我们必须首先检查查询是否正确。我们是否希望查询返回这么多行？如果没有，我们应该更正查询并再次执行它。如果查询正确且无需修改，我们可以将mysql与--quick选项连接。使用--quick选项将生成mysql_use_result()C API 函数以获取结果集。该函数增加了服务器上的负载，减少了客户端上的负载。

通信分组是以下之一：

• MySQL 客户端发送到 MySQL 服务器的单个 SQL 语句
• 从 MySQL 服务器发送到 MySQL 客户端的单行
• 从复制主服务器发送到复制从服务器的二进制日志事件

1GB 数据包大小是可以传输到 MySQL 8 服务器或客户端或从 MySQL 8 服务器或客户端传输的最大数据包大小。如果 MySQL 服务器或客户端接收到大于max_allowed_packet字节的数据包，则会发出ER_NET_PACKET_TOO_LARGE错误并关闭连接。

MySQL 客户端程序的默认max_allowed_packet大小为 16MB。以下命令可用于设置更大的值：

> mysql --max_allowed_packet=32M

MySQL 服务器的默认值为 64 MB。应该注意的是，为这个系统变量设置一个更大的值并没有坏处，因为额外的内存是根据需要分配的。

表满错误发生在以下情况之一：

• 磁盘已满
• 表已达到最大大小

MySQL 数据库中的实际最大表大小可以由操作系统对文件大小施加的约束来确定。

这表示如果执行查询时出现以下错误，MySQL 无法在临时目录中为结果集创建临时文件：

Can't create/write to file '\\sqla3fe_0.ism'.

该错误的可能解决方法是使用--tmpdir选项启动mysqld服务器。命令如下：

> mysqld --tmpdir C:/temp

也可以在 MySQL 配置文件的[mysqld]部分指定，如下所示：

[mysqld]
tmpdir=C:/temp

如果以错误的顺序调用客户端函数，则会收到命令不同步错误。这意味着该命令不能在客户端代码中执行。例如，如果我们执行mysql_use_result()并在执行mysql_free_result()之前尝试执行另一个查询，则可能会发生此错误。如果我们执行两个返回结果集的查询，而不调用中间的mysql_use_result()或mysql_store_result()函数，也可能发生这种情况。

当mysqld服务器启动时或当服务器重新加载授权表时，发现用户表中的帐户密码无效时，会收到以下错误：

Found wrong password for user 'some_user'@'some_host'; ignoring user

因此，MySQL 权限系统将忽略该帐户。要解决此问题，我们应该为该帐户分配一个新的有效密码。

以下错误表示默认数据库中不存在指定的表：

Table 'tbl_name' doesn't exist
Can't find file: 'tbl_name' (errno: 2)

在某些情况下，用户可能错误地引用了该表。这是可能的，因为 MySQL 服务器使用目录和文件来存储数据库表。根据操作系统文件管理的不同，数据库和表名可能区分大小写。

对于不区分大小写的文件系统，如 Windows，对查询中使用的指定表的引用必须使用相同的字母大小写。

本节重点介绍 MySQL 8 服务器错误。本节介绍与 MySQL 服务器管理、表定义和 MySQL 8 服务器中的已知问题相关的错误。

如果服务器启动时UMASK或UMASK_DIR环境变量设置不正确，我们可能会遇到文件权限问题。MySQL 服务器在创建表时可能会发出以下错误消息：

ERROR: Can't find file: 'path/with/file_name' (Errcode: 13)

UMASK和UMASK_DIR系统变量的默认值分别为 0640 和 0750。如果这些环境变量的值以零开始，则向 MySQL 服务器指示这些值是八进制的。例如，八进制的默认值 0640 和 0750 分别相当于十进制的 415 和 488。

为了更改默认的UMASK值，我们应该启动mysqld_safe，如下所示：

> UMASK=384 # = 600 in octal 
> export UMASK 
> mysqld_safe

MySQL 服务器创建默认访问权限值为0750的数据库目录。我们可以设置UMASK_DIR变量来修改此行为。如果设置了此值，则将创建具有访问权限值的新目录，这些值是UMASK和UMASK_DIR值的组合。

以下是提供对所有新目录的组访问的示例：

> UMASK_DIR=504 # = 770 in octal 
> export UMASK_DIR 
> mysqld_safe &

如果 MySQL 中从未设置根用户密码，则 MySQL 服务器不需要作为根用户进行连接。如果先前分配的密码已被遗忘，则可以重置密码。

以下是在 Windows 系统上重置root @ localhost帐户密码的说明：

1. 使用系统管理员凭据登录到系统。
2. 如果 MySQL 服务器已经在运行，请停止服务器。如果 MySQL 服务器作为 Windows 服务运行，请按照开始菜单|控制面板|管理工具|服务转到服务。在服务中，找到 MySQL 服务并停止它。如果 MySQL 服务器未作为 Windows 服务运行，请使用 Windows 任务管理器终止 MySQL 服务器进程。
3. MySQL 服务器停止后，创建一个包含一行密码分配语句的文本文件，如下所示：

 ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

4. 保存文件。例如，文件保存为C:\mysql-root-reset.txt。
5. 按照开始菜单| Run | cmd 打开 Windows 命令提示符。
6. 在命令提示下，使用--init-file选项启动 MySQL 服务器，如下所示：

 C:\> cd "C:\Program Files\MySQL\MySQL Server 8.0\bin" 
        C:\> mysqld --init-file=C:\\mysql-root-reset.txt

7. MySQL 服务器重启后，删除C:\mysql-root-reset.txt文件。

以下是在类 Unix 系统上重置根用户密码的说明：

1. 使用运行 MySQL 服务器的同一用户登录系统。通常是mysql用户。
2. 如果 MySQL 服务器已经在运行，请停止服务器。为此，找到包含 MySQL 服务器进程 ID 的.pid文件。根据 Unix 发行版的不同，文件的实际位置和名称可能有所不同。通常的位置是/var/lib/mysql/、/var/run/mysqld/和/usr/local/mysql/data/。通常，文件名以mysqld或系统主机名开头，扩展名为.pid。MySQL 服务器可以通过向mysqld服务器进程发送正常的 kill 命令来停止。以下命令可用于.pid文件的实际路径名：

 > kill 'cat /mysql-data-directory/host_name.pid'

3. MySQL 服务器停止后，创建一个包含一行密码分配语句的文本文件，如下所示：

 ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

4. 保存文件。假设文件存储在/home/me/mysql-reset-root处。由于文件包含根用户的密码，因此应确保其他用户无法读取该密码。如果我们没有使用适当的用户登录，我们应该确保该用户具有读取该文件的权限。
5. 使用--init-file选项启动 MySQL 服务器，如下所示：

 > mysqld --init-file=/home/me/mysql-reset-root &

6. 服务器启动后，删除/home/me/mysql-reset-root处的文件。

以下是重置根用户密码的一般说明：

1. 如果 MySQL 服务器正在运行，请停止服务器。一旦停止，使用--skip-grant-tables权限重启 MySQL 服务器。与--skip-grant-tables一起，--skip-networking选项自动启用，以防止远程连接。
2. 使用mysql客户端程序连接 MySQL 服务器。由于服务器是以--skip-grant-tables启动的，因此不需要密码：

 > mysql

3. 在 MySQL 客户机本身中，要求服务器重新加载授权表。这将启用帐户管理报表：

 mysql> FLUSH PRIVILEGES;

4. 使用以下命令更改root @ localhost账户密码：

 mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 
          'NewPassword';

5. 重新启动服务器并使用 root 用户和新设置的密码登录。

作为标准的发布实践，每个 MySQL 版本在发布之前都会在不同的平台上进行验证。假设 MySQL 可能有一些难以发现的 bug。当我们遇到 MySQL 的问题时，如果我们试图找出系统崩溃的原因，这会很有帮助。首先要确定的是mysqld服务器进程是否崩溃，或者问题是 MySQL 客户端程序。可以通过执行mysqladmin version命令来检查 MySQL 服务器的运行时间。以下是一个示例输出：

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqladmin version -u root -p
Enter password: *****
mysqladmin Ver 8.0.3-rc for Win64 on x86_64 (MySQL Community Server (GPL))
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version 8.0.3-rc-log
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 9 days 4 hours 4 min 52 sec

Threads: 2 Questions: 4 Slow queries: 0 Opens: 93 Flush tables: 2 Open tables: 69 Queries per second avg: 0.000

resolve_stack_dump是一个将数字堆栈转储解析为符号的工具。为了分析mysqld服务器进程死亡的根本原因，我们在堆栈跟踪错误日志中找到。可通过resolve_stack_dump程序解决。必须注意，错误日志中的变量值可能不准确。

损坏的数据或索引文件可能导致 MySQL 服务器崩溃。这些文件在磁盘上使用write()系统进行更新，该系统在执行每个 SQL 语句时调用，并且在将结果通知客户机之前调用。这意味着即使在mysqld崩溃的情况下，数据文件中的内容也是安全的。在磁盘上写入未刷新的数据由操作系统负责。--flush选项可与mysqld一起使用，强制 MySQL 在每次 SQL 语句执行后将所有内容刷新到磁盘。

MySQL 表损坏的原因可能是以下之一：

• 如果数据文件或索引文件崩溃，则它包含损坏的数据。
• MySQL 服务器进程中的一个错误导致服务器在更新过程中死亡。
• 一个外部程序在mysqld的同时操作数据和索引文件，没有表锁定。
• 在更新的中间，MySQL 服务器进程被杀死。
• 许多mysqld服务器正在系统上运行。服务器使用相同的数据目录。系统没有良好的文件系统锁定或外部锁定已禁用。
• 可能在数据存储代码中发现了错误。我们可以尝试在修复后的表副本上使用ALTER TABLE来更改存储引擎。

本节重点介绍 MySQL 对磁盘已满错误和配额超出错误的响应。它与MyISAM表中的写入更相关。它可以应用于二进制日志文件和索引文件中的写入。它排除了对应视为事件的行和记录的引用。

当磁盘已满时，MySQL 执行以下操作：

• MySQL 确保有足够的空间来写入当前行。
• MySQL 服务器每 10 分钟在日志文件中写入一个条目。它警告磁盘已满的情况。

应采取以下措施来解决问题：

• 应释放磁盘空间，以确保有足够的空间插入所有记录。
• 我们可以执行mysqladmin kill命令中止线程。下次检查磁盘时，线程将中止。
• 可能有几个线程正在等待导致磁盘已满的表。在几个锁定的线程中，在磁盘已满的情况下终止等待的线程将使其他线程能够继续。
• REPAIR TABLE或OPTIMIZE TABLE语句是上述条件的例外。其他例外情况包括在LOAD DATA INFILE或ALTER TABLE语句之后批量创建的索引。这些 SQL 语句可以创建大容量的临时文件。这可能会给系统的其他部分带来大问题。

Unix 上的 MySQL 使用TMPDIR环境变量的值作为目录的路径名来存储临时文件。MySQL 使用系统默认值，如/tmp、/var/tmp或/usr/tmp，如果TMPDIR未设置。

TMPDIR、TEMP和TMP环境变量的值由 Windows 上的 MySQL 检查。如果 MySQL 找到一个集合，它将使用该值，而不检查剩余值。如果这三个变量都没有设置，MySQL 使用系统默认值，即C:\windows\temp\。

如果文件系统中的临时文件目录太小，我们可以使用mysqld --tmpdir选项在文件系统上指定一个有足够空间的目录。对于复制，在从机上，我们可以使用--slave-load-tmpdir并指定在复制LOAD DATA INFILE语句时保存临时文件的目录。可以使用--tmpdir选项设置以循环方式使用的多条路径的列表。在 Unix 系统上，路径可以用冒号字符（：）分隔，而在 Windows 上，分号字符（；）可以用来分隔路径。

To effectively distribute the load, multiple temporary directory paths should belong to different physical disks and not the different partitions of the same disk.

对于用作复制从属服务器的 MySQL 服务器，我们必须注意设置--slave-load-tmpdir选项，以避免指向基于内存的文件系统中的目录，或指向在服务器或服务器主机重新启动时清除的目录。要复制临时表或LOAD DATA INFILE操作，复制从机需要在机器重新启动时使用其临时文件。如果临时文件目录中的文件丢失，复制将失败。

当mysqld服务器进程终止时，MySQL 负责删除临时文件。在类 Unix 平台上，可以通过在打开文件后取消文件链接来完成。这样做的一个主要缺点是名称不会出现在目录列表中。我们也可能看不到占用文件系统的大文件。

ibtmp1是InnoDB存储引擎用来存储临时表的表空间文件的名称。该文件位于 MySQL 的数据目录中。如果我们想指定不同的文件名和位置，innodb_temp_data_file_path选项可用于服务器启动。

如果InnoDB表上的ALTER TABLE操作使用了ALGORITHM=COPY技术，则存储引擎将在同一目录中创建原始表的临时副本。临时表文件名以#sql-前缀开头。它们仅在执行ALTER TABLE操作时短暂出现。

如果InnoDB表是由ALTER TABLESQL 语句使用ALGORITHM=INPLACE方法重建的，InnoDB存储引擎会在与原始表相同的目录中创建原始表的中间副本。中间表文件名以#sql-ib前缀开头。它们仅在执行ALTER TABLE操作时短暂出现。

innodb_tmpdir选项不能应用于中间表文件。这些中间文件始终创建并存储在与原始表相同的目录中。

使用ALGORITHM=INPLACE方法重建InnoDB表的ALTER TABLESQL 语句在默认的 MySQL 临时目录中创建临时排序文件。默认的临时目录在 Unix 上由$TMPDIR表示，在 Windows 上由%TEMP%表示，或者由--tmpdir选项提到的目录表示。如果临时目录不足以存储此类文件，则可能需要重新配置tmpdir。或者，我们可以使用innodb_tmpdir选项为在线InnoDB``ALTER TABLE语句定义另一个临时目录。可以在运行时使用SET GLOBAL或SET SESSION语句配置innodb_tmpdir选项。

如果所有服务器都具有相同的操作系统环境，则应在复制环境中考虑复制innodb_tmpdir配置。在其他情况下，innodb_tmpdir设置复制可能导致在执行在线ALTER TABLE操作时复制失败。如果操作环境不同，建议分别为每台服务器配置innodb_tmpdir。

MySQL 服务器使用/tmp/mysql.sock作为 Unix 套接字文件与本地客户端通信的默认位置。根据分发格式可能有所不同，例如 RPM 的/var/lib/mysql。

在几个 Unix 版本上，可以删除存储在/tmp目录中的文件以及用于存储临时文件的类似其他目录。如果套接字文件存储在文件系统上的此类目录中，则可能会导致问题。

可以保护/tmp目录，以确保文件只能由所有者或根超级用户删除。这在几乎所有版本的 Unix 上都是可能的。这可以通过在以 root 用户身份登录时在/tmp目录上设置粘性位来实现。以下是执行相同操作的命令：

chmod +t /tmp

使用ls -ld /tmp命令，也可以检查是否设置了粘滞位。如果最后一个权限字符为t，则设置该位。粘性位用于定义 Unix 系统中的文件权限。

也可以使用另一种方法，即更改 Unix 套接字文件的位置。如果更改 Unix 套接字文件的位置，则必须确保客户端程序也知道该文件的新位置。以下是执行此操作的方法：

• 可以在全局或本地选项文件中设置路径，如下所示：

 [mysqld]
 socket=/path/to/socket

 [client]
 socket=/path/to/socket

使用以下命令，可以确保新插座位置正常工作：

mysqladmin --socket=/path/to/socket version

如果SELECT NOW()返回 UTC 而不是用户当前时区的值时出现问题，则必须告知 MySQL 服务器用户的当前时区。如果UNIX_TIMESTAMP()返回错误的值，也适用。应在运行服务器的环境中执行此操作；例如，mysqld_safe或mysql.server。

我们也可以通过使用带有mysqld_safe的--timezone=timezone_name选项来设置服务器时区。也可以通过在启动mysqld之前将值分配给TZ环境变量来设置。

--timezone或TZ的允许值列表取决于系统。

本节重点介绍 MySQL 8 客户端上发生的错误。MySQL 客户端的工作是连接到 MySQL 服务器，以便执行 SQL 查询并从 MySQL 8 数据库获取结果。本节列出了与查询执行相关的错误。

字符串搜索使用非二进制字符串的比较操作数逻辑序列，如CHAR、VARCHAR和TEXT。二进制字符串的比较，例如BINARY、VARBINARY和BLOB使用操作数中字节的数值。它本质上意味着比较将区分字母字符的大小写。

非二进制字符串与二进制字符串的比较将被视为二进制字符串之间的比较。

>=、>、=、<、<=、sorting和grouping等比较操作取决于每个字符的排序值。具有相似排序值的字符被视为相同的字符。考虑一个 E 和 E 的例子。这些字符在提供的逻辑序列中具有相同的排序值。这些被认为是平等的。

utf8mb4和utf8mb4_0900_ai_ci分别是默认字符集和排序规则。默认情况下，非二进制字符串比较不区分大小写。这意味着，如果使用col_name LIKE 'a%'搜索，我们将获得以 A 或 A 开头的所有列值。为了使其区分大小写，我们必须确保其中一个操作数具有二进制或区分大小写的排序规则。例如，如果将一列与一个字符串进行比较，并且两者都具有utf8mb4字符集，COLLATE运算符可用于使任一操作数具有utf8mb4_0900_as_cs或utf8mb4_bin排序规则。以下是一个例子：

col_name COLLATE utf8mb4_0900_as_cs LIKE 'a%' 
col_name LIKE 'a%' COLLATE utf8mb4_0900_as_cs 
col_name COLLATE utf8mb4_bin LIKE 'a%' 
col_name LIKE 'a%' COLLATE utf8mb4_bin

为了将非二进制区分大小写的字符串比较改为不区分大小写，我们应该使用COLLATE来命名不区分大小写的排序规则。以下是COLLATE如何将比较更改为区分大小写的示例：

mysql> SET NAMES 'utf8mb4'; 
mysql> SET @s1 = 'MySQL' COLLATE utf8mb4_bin, @s2 = 'mysql' COLLATE utf8mb4_bin; mysql> SELECT @s1 = @s2;
+-----------+ 
| @s1 = @s2 | 
+-----------+ 
|         0 | 
+-----------+ 
mysql> SELECT @s1 COLLATE utf8mb4_0900_ai_ci = @s2; 
+--------------------------------------+ 
| @s1 COLLATE utf8mb4_0900_ai_ci = @s2 | 
+--------------------------------------+ 
|                                    1 | 
+--------------------------------------+

在 MySQL 中，DATE值的默认格式为YYYY-MM-DD。标准 SQL 不允许任何其他格式。这是UPDATE表达式和SELECT语句中WHERE子句中应使用的格式。以下是日期格式的示例：

SELECT * FROM table_name WHERE date_col >= '2011-06-02';

当使用<、<=、=、>=、>或BETWEEN运算符将常量字符串与DATE、TIME、DATETIME或TIMESTAMP进行比较时，MySQL 会将该字符串转换为内部长整数值。MySQL 这样做是为了实现更快的比较。但是，以下例外情况适用于此转换：

• 比较两列
• 将DATE、TIME、DATETIME或TIMESTAMP列与表达式进行比较
• 使用除刚刚列出的方法以外的比较方法，如IN或STRCMP()

比较是通过将对象转换为字符串值并在出现这些异常时执行字符串比较来完成的。

NULL值通常是新程序员的困惑点。对于字符串，NULL值被错误地解释为空字符串''。这是不对的。下面是一个完全不同的语句示例：

mysql> INSERT INTO my_table (phone) VALUES (NULL); 
mysql> INSERT INTO my_table (phone) VALUES ('');

在前面的示例中，两条语句都在同一列（phone 列）中插入值。第一条语句插入一个NULL值，而第二条语句插入一个空字符串。第一个值可视为电话号码未知，而第二个值表示已知此人没有电话，因此没有电话号码。

当一个NULL值与任何其他值进行比较时，它的计算结果总是为 false。包含NULL值的表达式总是导致NULL值。以下示例返回一个NULL值：

mysql> SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);

如果 SQL 语句的目的是搜索NULL列值，则不能使用expression = NULL。下面是一个不返回任何行的示例，因为expression = NULL始终为 false：

mysql> SELECT * FROM my_table WHERE phone = NULL;

要进行NULL值比较，应使用IS NULL。以下示例演示了IS NULL的用法：

mysql> SELECT * FROM my_table WHERE phone IS NULL; 
mysql> SELECT * FROM my_table WHERE phone = '';

在本章的这一节中，我们将重点介绍 MySQL 8 故障排除方法。为什么我们需要对 MySQL 8 进行故障排除？故障排除的原因如下：

• 更快地执行 SQL 查询
• 性能增强
• 有效利用资源

主要资源集包括 CPU、磁盘 IO、内存和网络。有两种方法可以测量 MySQL 性能：

• 在以查询为中心的方法中，度量查询执行的速度非常重要
• 在以资源为中心的方法中，查询使用更少的资源是很重要的。

让我们更深入地了解解决 MySQL 问题的方法。

EXPLAIN是 SQL 语句，提供 MySQL 执行 SQL 语句的方式信息。EXPLAIN语句与INSERT、UPDATE、REPLACE、DELETE和SELECT语句一起使用。EXPLAIN语句的输出是SELECT语句中提到或使用的每个表的一行信息。输出按照 MySQL 在执行语句时读取这些表的顺序列出这些表。使用嵌套循环联接方法解析所有联接。在嵌套循环联接方法中，MySQL 从列表的第一个表中读取一行，然后在列表的第二个表中找到匹配行，然后在第三个表中找到匹配行，依此类推。处理完列表中的所有表后，MySQL 将处理所选列的结果，并在表列表中回溯这些结果，直到找到一个包含更多匹配行的表。它读取此表中的下一行。这样，过程将继续。

以下是来自EXPLAIN输出的列：

• id表示查询中SELECT的序号。它也被称为SELECT标识符。当该行属于其他行的并集结果时，该值可能为NULL。输出在表格列中显示<unionM, N>。表示该行表示 ID 值M和N的并集。 select_type：此输出列表示SELECT语句的类型。可能的值列表包括SIMPLE、PRIMARY、UNION、DEPENDENT UNION、UNION RESULT、SUBQUERY、DEPENDENT SUBQUERY、DERIVED、MATERIALIZED、UNCACHEABLE SUBQUERY和UNCACHEABLE UNION。 table：此列表示输出中引用的表的名称。可以有<unionM, N>、<derivedN>和<subqueryN>等值。 partitions：标识查询与记录匹配的分区。对于非分区表，值为NULL。 type：表示JOIN的类型。 possible_keys：此输出列表示 MySQL 可能选择的索引来获取表中的行。如果没有匹配的索引，则返回值为NULL。 key：此输出列表示 MySQL 实际用于从表中获取行的键索引。 ref：ref输出列表示用于与键输出列中提到的索引进行比较以选择表行的列或常量。 rows：rows output 列表示为了成功执行查询而需要检查的行数。

**以下是EXPLAIN中的连接类型：

• system：表示该表只有一行。这是const连接类型的特例
• const：表示该表至少有一个匹配行。在查询开始时读取该行。由于只找到一个匹配行，优化器的其余部分将此行中列的值视为常量。由于常量表只读取一次，因此速度非常快。当将PRIMARY KEY或UNIQUE索引的所有部分与常量进行比较时，使用常量。以下为tbl_name用作常数表的示例：

 mysql> SELECT * FROM tbl_name WHERE primary_key=1; 
 mysql> SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

• ref：对于前面表格中的每一行组合，所有具有匹配索引值的行都从ref表格中读取。如果联接仅使用键的最左侧前缀，则使用ref。

MySQL 查询优化是指提高查询执行时间。例如，当查询执行得不好时，意味着查询的执行时间比预期的要长。查询执行的时间很重要，但也有其他矩阵用于衡量性能。本节解释了应测量的内容以及应如何尽可能精确地进行测量。

出现了以下问题：为什么要优化查询？如果只需要百分之一秒，它真的需要优化吗？是的，它确实需要优化，除非很少执行查询。我们应该优化最昂贵的查询。

让我们讨论一个实时示例。在其中一个应用程序中，我们有一个基于复杂查询生成的报告，该报告花费了太多时间。执行时间以分钟为单位。为了优化如此复杂的查询，我们考虑了以下方法：

1. 使用EXPLAIN分析查询计划：MySQL 提供了两种分析查询性能的方法。一种是EXPLAIN方法，我们已经在本章的前一节中学习过。另一个工具是SHOW STATUS。通常，我们应该使用EXPLAIN来理解SELECT查询的查询计划。在报表查询的情况下，我们将一些非SELECT查询转换为SELECT查询。这也有助于我们理解非SELECT查询的查询执行计划。例如，可以使用UPDATE查询中的WHERE子句将UPDATE查询重写为SELECT查询，并将其传递给SELECT查询。我们还可以在表上找到一些缺少的索引。
2. SHOW STATUS：SHOW STATUS语句输出 MySQL 的内部计数器。每次执行查询时，MySQL 都会增加计数器。通过这些计数器的帮助，我们可以了解服务器执行的操作类型。它还有助于指示每个查询所做的工作。

以下是对 MySQL 服务器变量执行的测量：

• Select_：每当执行SELECT查询时，此计数器递增。此计数器还可用于标识是否执行了表扫描。
• Key_read：此变量提供有关键索引用法的附加信息。
• Last_query_cost：此值表示上次执行的查询有多昂贵。

以下是执行查询优化的步骤：

1. 多次查询执行以确保返回相同的结果。
2. 执行SHOW STATUS。应该保存输出。
3. 执行查询。
4. 执行SHOW STATUS观察与前一次执行的差异。
5. 如果需要，执行EXPLAIN。

应分析以下参数以优化查询性能：

• 表索引
• 分类
• 整体表现
• 行级操作
• 磁盘 I/O 操作

在本书的最后一章中，我们了解了任何数据库的一个重要方面：对使用 MySQL 服务器或客户端可能遇到的错误进行故障排除。我们通过了解故障排除是什么开始了讨论。我们讨论了错误初始诊断的不同方法。我们了解常见的 MySQL 错误以及错误消息的含义。我们还了解了修复这些错误的方法。我们还了解了 MySQL 服务器和客户端错误以及这些错误的修复。在本章后面的部分中，我们了解了 MySQL 故障排除方法，并查看了一个真实场景。最后一章的重要内容，嗯？这本书到此为止。**