今天我们来聊一聊数据库连接的一些事情。在这里，连接这个词有两个意思。
- 首先连接是一个动词，表示客户端连接到数据库的这个过程。
- 其次连接还是一个名词，表示客户端和服务器建立的一个通道，客户端的命令、SQL、服务器端返回的数据都会经过这个通道来传输。
这一讲，我们一起来分析数据库连接不上的问题，以及连接异常中断的问题。
为什么连接不上数据库？
客户端执行命令或 SQL 前，需要先创建一个到数据库服务端的连接，并完成用户认证。MySQL 服务端使用插件的方式认证客户端的用户身份。不同的插件在验证用户密码时，细节上会有所不同。MySQL 中，认证插件和用户相关，不同用户可以使用不同的插件进行密码验证。创建用户时，如果不显式指定，会使用参数 default_authentication_plugin 指定的插件。从 MySQL 8.0 开始，使用 caching_sha2_password 作为默认的认证插件，而 5.7 使用的默认插件是 mysql_native_password。
mysql8 为 caching_sha2_password
mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+mysql5.7 为 mysql_native_password
当然，你也可以在创建用户的时候指定认证插件，或者通过 alter user 命令修改用户的认证插件。
mysql> create user 'user_01'@'%' identified by 'somepass';
Query OK, 0 rows affected (0.27 sec)
mysql> create user 'user_02'@'%' identified with 'mysql_native_password' by 'somepass';
Query OK, 0 rows affected (0.56 sec)
mysql> create user 'user_03'@'%' identified by 'somepass';
Query OK, 0 rows affected (0.26 sec)
mysql> alter user 'user_03'@'%' identified with 'sha256_password' by 'somepass';
Query OK, 0 rows affected (0.06 sec)从 mysql.user 表可以查看每个用户使用了哪个认证插件。
mysql> select user,host,plugin,substring(authentication_string, 1, 18) as passwd
from mysql.user where user like 'user%';
+---------+------+-----------------------+--------------------+
| user | host | plugin | passwd |
+---------+------+-----------------------+--------------------+
| user_01 | % | caching_sha2_password | $A$005$M,PHGCm7 |
| user_02 | % | mysql_native_password | *13883BDDBE566ECEC |
| user_03 | % | sha256_password | $5$N3kzW9A@+;+P'g |
+---------+------+-----------------------+--------------------+caching_sha2_password 完整登录流程
我们以 MySQL 8.0 默认的插件 caching_sha2_password 为例，分析连接建立的过程。
如上图所示，使用 caching_sha2_password 插件时，登录过程中服务端和客户端需要进行这些交互。
- 客户端首先要和服务端建立一个 TCP 连接。TCP 连接建立成功后，才能进行后续的步骤。
- 服务端发送握手协议包（ServerHandshake），握手协议包里面包含了服务器版本、使用的协议版本、服务端支持的特性（如是否支持加密连接）、服务端使用的认证插件（caching_sha2_password）、一串随机数据。
- 客户端读取和解析服务端的协议包，发送握手协议回包，回包里面有用户名、通过一定规则计算得到的密码哈希值、客户端支持的特性、客户端使用的认证插件、客户端版本等信息。不同版本的客户端可能会使用不同的默认认证插件。如 MySQL 5.7 版本的客户端默认使用 mysql_native_password 插件，MySQL 8.0 默认使用 caching_sha2_password 插件。客户端和服务端使用的默认插件可以不一样。
- 服务端在接收到客户端发送的握手协议回包之前，并不知道客户端使用的用户名，因此也不知道用户使用的认证插件。接收到客户端的回包后，服务端从里面解析出用户名，到用户列表中获取到用户信息，得到用户的认证插件。如果用户使用的认证插件和服务器的默认插件不一样，或者客户端和服务端使用的认证插件不一样，服务端就需要告诉客户端切换认证方式。
- 如果客户端和服务端的认证插件不一致，客户端需要根据服务端的要求，重新计算密码哈希后再发送到服务端。服务端接收到客户端新发送过来的认证包之后，就可以验证用户密码是否匹配了。对于 caching_sha2_password 插件，这里分为两种情况。
- 用户首次登录时，服务端没有缓存用户的密码信息，此时需要进行完整的登录流程，就是上图中的 6、7、8、9 这几个步骤。
- 用户登录成功后，会在服务端缓存哈希后的密码信息。下一次用户登录时，就可以根据缓存的密码哈希来验证，不需要进行完整的登录流程。缓存的密码哈希会在执行 ALTER USER、Flush Privileges 或重启数据库后失效。
- 使用 caching_sha2_password 时，如果服务端还没有缓存用户的密码哈希，会通知客户端发送明文的密码。
- 如果客户端和服务端建立了加密连接，则可以直接发送明文密码。但如果客户端和服务端之间的连接没有加密，直接发送明文密码是不安全的，此时客户端可以向服务端请求 RSA 公钥，用于加密明文的密码。
- 服务端将 RSA 公钥发送给客户端。
- 客户端使用接收到的 RSA 公钥加密明文密码，发送到服务端。
- 服务端得到原始密码后，根据一定的规则计算哈希值，然后再跟存储在用户表中的 authentication_string 进行对比。用户认证成功后，服务端将密码哈希缓存起来。用户再次登录时，就可以基于缓存的密码哈希来验证用户登录信息。
如果服务端、客户端以及用户的认证插件都一样，并且用户信息已经缓存在服务端，那么上述 4～9 之间的步骤都会跳过，只需执行步骤 1、2、3、10。
加密连接
MySQL 支持使用 TLS 协议建立加密连接。使用 MySQL 客户端连接数据库时，默认就会开启连接加密。
使用 MySQL 客户端登录服务器后，执行\s，查看 SSL 这一行的输出，如果显示“Cipher in use …”，则说明当前连接启用了加密。
# mysql -uuser_01 -h172.16.121.234 -P3306 -psomepass
mysql> \s
--------------
mysql Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 121
Current database:
Current user: user_01@172-16-121-234
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
...
--------------
当然，也可以在连接数据库时指定参数，不开启连接加密，此时“SSL”这一行显示“Not in use”。
# mysql --ssl-mode=disabled -uuser_01 -h172.16.121.234 -P3306 -psomepass
mysql> \s
--------------
mysql Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 123
Current database:
Current user: user_01@172-16-121-234
SSL: Not in use
...
--------------使用 JDBC 或其他语言的客户端驱动连接数据库时，也可以通过相应的参数来控制是否开启连接加密。
使用 JDBC 或其他语言的客户端驱动连接数据库时，也可以通过相应的参数来控制是否开启连接加密。
为了保障数据库连接的安全性，我们可以强制要求某些用户必须使用加密连接，这可以在创建用户时指定，或者通过 ALTER USER 命令修改。下面创建的几个用户都需要开启连接加密。其中 user_06 和 user_07 在登录时还必须提供客户端的证书，user_06 对证书 subject 有要求，user_07 对证书 issuer 有要求，如果提供的证书不满足要求，也无法登录数据库。
mysql> create user 'user_05'@'%' identified by 'somepass' require ssl;
mysql> create user 'user_06'@'%' identified by 'somepass' require subject '/CN=MySQL_Server_8.0.32_Auto_Generated_Client_Certificate';
mysql> create user 'user_07'@'%' identified by 'somepass' require issuer '/helloworld';使用这几个账号登录时，必须开启加密连接，否则无法登录数据库。
# mysql -uuser_05 -h127.0.0.1 -psomepass --ssl-mode=disabled
ERROR 1045 (28000): Access denied for user 'user_05'@'localhost' (using password: YES)使用 user_06 和 user_07 登录时，还必须指定正确的证书，否则也无法登录数据库。下面的测试案例中，使用了 MySQL 数据库初始化过程中自动创建的证书，证书都在数据库的 datadir 目录下。
# mysql -uuser_06 -h127.0.0.1 -psomepass --ssl-key=server-key.pem --ssl-cert=server-cert.pem -e 'select 1';
ERROR 1045 (28000): Access denied for user 'user_06'@'localhost' (using password: YES)
# mysql -uuser_06 -h127.0.0.1 -psomepass --ssl-key=client-key.pem --ssl-cert=client-cert.pem -e 'select 1';
+---+
| 1 |
+---+
| 1 |
+---+未开启加密连接或证书不对而无法登录数据库时，服务端返回的报错也是 ERROR 1045，跟密码不对时的报错信息是一样的。在确认密码没有问题后，如果还是报 ERROR 1045，需要检查用户是否有加密连接和证书相关的要求。这一项我们可以到 mysql.user 表查看。
mysql> select user,host,ssl_type, cast(ssl_cipher as char) cipher,
cast(x509_issuer as char) issuer, cast(x509_subject as char) subject
from mysql.user where user in ('user_05', 'user_06', 'user_07');
+---------+------+-----------+--------+-------------+-----------------------------------------------------------+
| user | host | ssl_type | cipher | issuer | subject |
+---------+------+-----------+--------+-------------+-----------------------------------------------------------+
| user_05 | % | ANY | | | |
| user_06 | % | SPECIFIED | | | /CN=MySQL_Server_8.0.32_Auto_Generated_Client_Certificate |
| user_07 | % | SPECIFIED | | /helloworld | |
+---------+------+-----------+--------+-------------+-----------------------------------------------------------+从服务端的错误日志中，也可以看到一些相关的错误信息（需要将参数 log_error_verbosity 设置为 3）。
### alert.log
[Note] [MY-010290] [Server] X.509 issuer mismatch: should be '/helloworld' but is '/CN=MySQL_Server_8.0.32_Auto_Generated_CA_Certificate'
[Note] [MY-010926] [Server] Access denied for user 'user_07'@'localhost' (using password: YES)数据库无法连接问题
通过前面的内容，你应该已经了解了 MySQL 建立连接的大致过程。现在我们来总结下数据库连接不上时，分析问题的一般思路。
1. 检查数据库监听是否正常开启。
可以在数据库服务器上通过 netstat 或 ss 命令查看数据库端口的监听是否正常开启。这里需要注意监听的 IP，如果监听的 IP 是 127.0.0.1，则只能在本地连接到数据库。
# ss -nltp | grep 3306
LISTEN 0 128 [::]:3306 [::]:* users:(("mysqld",pid=13600,fd=33))
# netstat -nltp | grep 3306
tcp6 0 0 :::3306 :::* LISTEN 13600/mysqld2. 检查客户端到服务端之间的网络是否能连通。
可以使用 telnet 等工具检查客户端到服务端口是否能连通。
# telnet 172.16.121.234 3306
Trying 172.16.121.234...
telnet: connect to address 172.16.121.234: No route to host如果端口不通，使用 MySQL 客户端访问数据库的时候，也会有相应的报错信息。
# mysql -h 172.16.121.234 -P 3306
ERROR 2003 (HY000): Can't connect to MySQL server on '172.16.121.234:3306' (113)
# mysql -h 172.16.121.234 -P3307 -uuser_01 -psomepass
ERROR 2003 (HY000): Can't connect to MySQL server on '172.16.121.234:3307' (111)注意到上面报错信息最后括号里的数字，这可能是操作系统返回的错误码，可以使用 MySQL 提供的工具 perror 查看跟错误码关联的错误信息。
# perror 111
OS error code 111: Connection refused
# perror 113
OS error code 113: No route to host这里的错误码跟操作系统有关，比如在 mac 下，错误码就变成了 61，需要在 mac 环境下使用 perror 工具查看。
$ mysql -uuser_01 -h172.16.121.234 -psomepass
ERROR 2003 (HY000): Can't connect to MySQL server on '172.16.121.234' (61)
$ perror 61
OS error code 61: Connection refused如果连端口都不通，那么就无法建立 TCP 连接，因此也无法连接到数据库。有时候，客户端到服务端之间的网络链路可能比较复杂，可能会存在防火墙，或者是受某些网络访问策略的限制，需要从网络层面进行排查。
3. 是否是认证阶段出了问题。
客户端连接到数据库时，需要经过一个复杂的认证过程，认证阶段各个步骤都可能出错。接下来我们分析一些比较常见的报错信息。
- ERROR 2059，Authentication plugin ‘caching_sha2_password’ cannot be loaded
/opt/mysql5.6/bin/mysql -uuser_01 -h127.0.0.1 -psomepass ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
这个报错通常是因为客户端的版本太低了，不支持 caching_sha2_password 认证插件。解决方法是使用新版本的客户端。
- ERROR 2061，Authentication requires secure connection
# mysql --ssl-mode=disabled -uuser_01 -h127.0.0.1 -psomepass ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
使用 caching_sha2_password 认证插件时，用户首次登录时还没有被缓存，服务端需要获取用户的明文密码，如果客户端没有开启连接加密，发送明文密码有安全风险，就会报这个错误。开启连接加密可以解决这个问题，或者也可以在客户端指定 get-server-public-key 选项。
mysql --get-server-public-key --ssl-mode=disabled -uuser_01 -h127.0.0.1 -psomepass当然，不同的客户端指定参数的方式可能会有一些差异，比如使用 JDBC 时需要添加连接属性 allowPublicKeyRetrieval=true。
MySQL 备库连接到主库时，也可能会遇到一样的问题，可以在建立复制时，指定 GET_MASTER_PUBLIC_KEY 或 GET_SOURCE_PUBLIC_KEY 选项。
## 使用change master
change master to master_host='master-host-name',
master_user='repl',
master_password='somepass',
master_auto_position=1,
GET_MASTER_PUBLIC_KEY=1;
## 或者使用change replication source
change replication source to
source_host='master-host-name',
source_user='repl',
source_password='somepass',
source_auto_position=1,
GET_SOURCE_PUBLIC_KEY=1;使用 MySQL 组复制（MGR）如果遇到这个问题，可以通过设置参数 group_replication_recovery_get_public_key 来解决。
set global group_replication_recovery_get_public_key=ON;关于复制和组复制在后续的课程中我会详细介绍。
- ERROR 1045 (28000): Access denied for user ‘username’@‘hostname’
# mysql -uuser_07 -h172.16.121.234 -psomepassx --ssl-key=client-key.pem --ssl-cert=client-cert.pem -e 'select 1' ERROR 1045 (28000): Access denied for user 'user_07'@'172-16-121-234' (using password: YES)
ERROR 1045 可能是我们平时遇到最多的一个报错，通常这是由于客户端输入的密码不正确引起的。但是我们在这一讲前面加密连接演示过，如果强制要求用户使用加密连接，或者对客户端的证书有要求，而客户端没有满足这些条件，那么连接时也会报这个错。可以到 mysql.user 表查看用户是否有 SSL 相关要求。同时也可以到数据库的错误日志中查看是否有相应的报错信息。
4. 数据库连接数限制
MySQL 中有几个地方限制用户的连接数。参数 max_connections 限制了数据库允许创建的总连接数。参数 max_user_connections 限制了同一个用户允许创建的最大连接数。我们还可以指定某个具体的用户允许创建的最大连接数。
mysql> create user 'user_09'@'%' identified by 'somepass' with MAX_USER_CONNECTIONS 2;
Query OK, 0 rows affected (0.04 sec)如果连接数超过了限制，根据上面几种情况，分别会报下面这 3 个错误。
- ERROR 1040 (08004): Too many connections
- ERROR 1203 (42000): User user_01 already has more than ‘max_user_connections’ active connections
- ERROR 1226 (42000): User ‘user_09’ has exceeded the ‘max_user_connections’ resource (current value: 2)
5. 操作系统资源限制
MySQL 创建连接时，需要消耗操作系统资源，如果操作系统资源超出了限制，也会导致客户端连接失败。下面这个例子中，MySQL 服务端无法创建新的线程。
- ERROR 1135 (HY000): Can’t create a new thread (errno 11)
# mysql -uroot -h127.0.0.1 -pabc123
ERROR 1135 (HY000): Can't create a new thread (errno 11);
if you are not out of available memory, you can consult the manual
for a possible OS-dependent bug
# perror 11
OS error code 11: Resource temporarily unavailable如果文件句柄数超出了限制，在数据库的错误日志中还可能会出现这样的报错信息。
[ERROR] [MY-010283] [Server] Error in accept: Too many open files在这个课程中的第 12 讲｜操作系统是否存在瓶颈？Linux 问题诊断入门，我们会介绍操作系统相关问题的排查。
6. 其它错误
ERROR 1129 (HY000): Host '172.16.121.237' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'这也是一个比较致命的问题。客户端从某台机器连接数据库时，连续出错，出错的次数超过了参数 max_connect_errors 的设置后，服务端会禁止这台机器后续的连接。这里限制的是客户端的 IP，也就是从这个 IP 的发起所有连接都会被限制。从 performance_schema.host_cache 表里，我们可以看到客户端的连接错误次数。
mysql> select * from performance_schema.host_cache\G
*************************** 1. row ***************************
IP: 172.16.121.237
HOST: 172-16-121-237
HOST_VALIDATED: YES
SUM_CONNECT_ERRORS: 10
COUNT_HOST_BLOCKED_ERRORS: 5
COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
COUNT_NAMEINFO_PERMANENT_ERRORS: 0
COUNT_FORMAT_ERRORS: 0
COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
COUNT_ADDRINFO_PERMANENT_ERRORS: 0
COUNT_FCRDNS_ERRORS: 0
COUNT_HOST_ACL_ERRORS: 0
COUNT_NO_AUTH_PLUGIN_ERRORS: 0
COUNT_AUTH_PLUGIN_ERRORS: 0
COUNT_HANDSHAKE_ERRORS: 10
COUNT_PROXY_USER_ERRORS: 0
COUNT_PROXY_USER_ACL_ERRORS: 0
COUNT_AUTHENTICATION_ERRORS: 0
COUNT_SSL_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
COUNT_DEFAULT_DATABASE_ERRORS: 0
COUNT_INIT_CONNECT_ERRORS: 0
COUNT_LOCAL_ERRORS: 0
COUNT_UNKNOWN_ERRORS: 0
FIRST_SEEN: 2024-07-04 15:24:54
LAST_SEEN: 2024-07-04 15:37:46
FIRST_ERROR_SEEN: 2024-07-04 15:24:54
LAST_ERROR_SEEN: 2024-07-04 15:37:46其实上面的报错信息中就提供了解决方法：执行 flush hosts 操作。
mysql> flush hosts;
Query OK, 0 rows affected, 1 warning (0.14 sec)并不是所有的连接错误都会引起客户端被禁，比如密码错误并不会导致客户端被禁。host_cache 表的 COUNT_HANDSHAKE_ERRORS 达到 max_connect_errors，才会导致客户端被禁。比如连续 telnet mysql 的端口会引起这个问题，或者使用了无效的 ssl 证书可能会导致这个问题。
# mysql -uuser_06 -h 172.16.121.234 -psomepass --ssl-cert=client-cert.pem --ssl-key=client-key.pem --ssl-ca=ca.pem
ERROR 2026 (HY000): SSL connection error: error:14090086:SSL routines:ssl3_get_server_certificate:certificate verify failed连接中断问题
连接中断也是我们日常使用 MySQL 过程中经常会遇到的问题。不同的客户端在遇到连接中断时，具体的报错信息可能会不一样。比如使用 MySQL 自带的命令行客户端时，可能会遇到下面这几个报错。
- ERROR 2013 (HY000): Lost connection to MySQL server during query
- ERROR 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
而使用 Java 编写的应用程序，在访问 MySQL 数据库时，比较常见的报错有 2 个。
- CommunicationsException: The last packet successfully received from the server was 15,032 milliseconds ago. The last packet sent successfully to the server was 15,035 milliseconds ago. is longer than the server configured value of ‘wait_timeout’.
- Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
上面这几个报错信息实际上都是在说客户端到服务器之间的连接断开了，至于连接为什么会断开，现实中存在很多不同的情况，接下来我们会对一些情况做一些分析。
情况 1：连接被 Kill
如果有人使用 Kill 命令终止了某个会话，那么原先的那个客户端再执行 SQL 时，就会发现连接已经中断了。
mysql> show processlist;
+----+-----------------+----------------------+------+---------+------+
| Id | User | Host | db | Command | Time |
+----+-----------------+----------------------+------+---------+------+
| 30 | user_01 | 192.168.113.13:58850 | db01 | Sleep | 5 |
+----+-----------------+----------------------+------+---------+------+
mysql> kill 30;
Query OK, 0 rows affected (0.00 sec)情况 2：数据库重启了
如果数据库重启发生了重启，那么原先所有的连接都会断开。有一种比较特殊的情况，由于数据库底层数据文件损坏等原因，数据库在不停地重启。表现出来的现象是可以连接到数据库，但是执行 SQL 时，连接已经断开了。我们可以通过查看数据库的 Uptime 状态变量来判断数据库最近是否有重启。Uptime 记录了数据库从启动后至今经过的秒数。
mysql> show global status where variable_name in ('Uptime');
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 64909 |
+---------------+-------+情况 3：连接空闲时间超时
MySQL 中参数 interactive_timeout 和 wait_timeout 用来控制连接的空闲超时，如果一个连接在指定的时间内没有发起任何请求，就会被服务器断开。
mysql> show global variables where variable_name in ('wait_timeout', 'interactive_timeout');
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
| wait_timeout | 28800 |
+---------------------+-------+全局变量 interactive_timeout 用来控制交互式连接的空闲超时时间，wait_timeout 用来控制非交互式连接的空闲超时时间。wait_timeout 还是一个会话级别的参数，每个会话可以分别设置不同的超时时间。默认情况下，服务器在创建一个连接时，根据客户端的连接类型来设置超时时间，对于交互式连接，服务器基于 interactive_timeout 来设置连接的超时时间，对于非交互式连接，服务器根据全局变量 wait_timeout 来设置超时时间。连接还可以自己在会话级别修改超时时间。
要查看一个连接真实的超时时间，最简单的办法是通过这个连接查看会话变量 wait_timeout 的值。
mysql> set wait_timeout=3600;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables where variable_name in ('wait_timeout');
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 3600 |
+---------------+-------+
mysql> select @@wait_timeout;
+----------------+
| @@wait_timeout |
+----------------+
| 3600 |
+----------------+
1 row in set (0.00 sec)情况 4：代理（Proxy）超时
有的时候，数据库连接自身的空闲超时设置得比较大，但是在数据库之前配置了代理，而代理的空闲超时时间比较短。应用程序通过代理访问数据库时，就可能会遇到连接中断的问题。
下面是 nginx 的 4 层代理的一个例子。nginx 4 层代理默认的超时时间是 10 分钟，也就是如果 10 分钟内没有任何请求，就会把连接断开。
## /etc/nginx/nginx.conf
stream {
server {
listen 13306;
##proxy_timeout 10m; # 默认10分钟
proxy_pass 172.16.121.234:3306;
}
}如果应用程序通过 nginx 来访问数据库，空闲时间超过 10 分钟后连接就会被断开。而且还有一个更严重的问题，通过代理无法执行耗时超过 10 分钟的 SQL。
mysql> select sleep(610);
ERROR 2013 (HY000): Lost connection to MySQL server during query那么连接断开的问题应该怎么解决呢？
首先可以根据业务的实际情况，将空闲超时时间设置得长一些。如果使用了代理，需要注意代理的超时设置。对于 Java 应用程序，一般会使用数据库连接池，要正确地设置连接池的参数。有的连接池支持空闲连接回收，有的连接池支持连接探活（Keepalive），也就是每隔一定时间就执行一个 Keepalive 的 SQL，需要注意连接池的 Keepalive 执行间隔要比数据库的 wait_timeout 或代理的空闲超时时间设置得更短。
但是，我们其实很难完全避开数据库连接中断的问题，因为现实中总会存在一些意外，比如运行数据库的服务器异常重启了，或者应用程序到数据库之间的某个网络设备出故障了等等各种情况。因此我们的程序需要能处理这些异常，进行重连数据库、重新执行 SQL。
总结
这一讲中，我们学习了 MySQL 建立连接和认证用户的过程。有很多原因会导致连接数据库失败，遇到问题时首先要拿到数据库具体的报错信息、错误编号。这一讲我也提到了一些你可能会遇到的报错信息。如果你遇到的连接问题在课程中没有提到，可以根据我们提供的步骤，分析连接的哪个阶段出现了问题。
此外我们还分析了数据库连接异常中断的一些情况。通过正确设置数据库参数、代理软件参数和应用程序的连接池参数，可以避免一部分连接中断的问题。应用程序也要有能力处理中断的连接，进行重连、重试。
思考
开发同学反馈访问数据库总是报错，并提供了一些报错日志。你应该怎么来分析和解决这个问题呢？
ERROR druid.sql.Statement -{conn-10094, stmt-26348} execute error. SELECT 1 FROM DUAL
com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 11,394,944 milliseconds ago. The last packet sent successfully to the server was 11,394,950 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
The last packet successfully received from the server was 899,883 milliseconds ago. The last packet sent successfully to the server was 899,890 milliseconds ago.
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
The last packet successfully received from the server was 1,799,883 milliseconds ago. The last packet sent successfully to the server was 1,799,891 milliseconds ago.
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.思考题这种报错确实经常遇到，开发说是数据库问题，DBA说是程序配置问题，一般都是建议排查程序连接池及重启应用
回答：
-
首先确认连接的空闲超时时间，8.0可以这么查：select * from performance_schema.variables_by_thread where variable_name = 'wait_timeout'。老一点的版本可以通过应用程序执行show variables where variable_name in ('wait_timeout')来确认。
-
确认报错时，离这个连接中的上一个请求过了多少时间。jdbc报错信息中就提供了这个时间。有时程序刚启动没多久就报连接断开，那么要查看数据库或者应用到数据库之间的网络链路有没有异常。
-
通过show processlist观察command为Sleep的连接，时间是多少。
-
还可以执行一些耗时比较长的SQL，看看是否能正常执行。比如select sleep(901); 有一个案例中，应用通过代理访问数据库，代理的超时时间是900秒，因此 select sleep(901)无法执行完成。
-
如果使用了连接池，要确认连接池的一些参数是否生效。比如配置了Keepalive，那么要看看是不是在设置的间隔时间里确实发送了keepalive的SQL。
要点总结
- MySQL中，认证插件和用户相关，不同用户可以使用不同的插件进行密码验证，创建用户时需要注意指定认证插件。
- 使用caching_sha2_password插件时，登录过程中服务端和客户端需要进行一系列交互，包括握手协议、密码哈希验证等步骤。
- MySQL支持使用TLS协议建立加密连接，可以通过参数控制是否开启连接加密，也可以强制要求某些用户必须使用加密连接。
- 为了保障数据库连接的安全性，可以在创建用户时指定或通过ALTER USER命令修改用户的连接要求，包括是否需要加密连接以及对证书的要求。
- 未开启加密连接或证书不对而无法登录数据库时，服务端返回的报错也是ERROR 1045，需要检查用户是否有加密连接和证书相关的要求。
- 从服务端的错误日志中，可以查看到一些相关的错误信息，帮助排查连接问题。
- 客户端和服务端使用的默认插件可以不一样，需要注意客户端和服务端的认证插件是否一致。
- 用户首次登录时，服务端没有缓存用户的密码信息，需要进行完整的登录流程，而用户登录成功后，会在服务端缓存哈希后的密码信息.
- 服务端、客户端以及用户的认证插件都一样，并且用户信息已经缓存在服务端时，连接过程中的一些步骤会被跳过，提高连接效率.
- 通过查看mysql.user表和服务端的错误日志，可以获取用户的连接要求和相关错误信息，帮助排查连接问题.
问答
问题1 - 文中的会话级别变量是什么意思，是说一个连接里可以直接设置的变量，还是说一个事务里可以设置的变量？
回答：
会话级别变量是指每个连接可以各自的变量。
问题2 - MySQL连接超时与配置问题分析
我这边有个案例：
server 参数：
wait_timeout ：3600
interactive_timeout ：7200
max_allowed_packet ：1G
客服端经常出现错误：(MySQLdb.OperationalError) (2013, 'Lost connection to MySQL server during query')
MySQL服务器经常看见错误： Aborted connection 87082567 to db: 'test' user: 'test' host: 'x.x.x.x' (Got timeout reading communication packets)
这是哪儿没有配置对吗？还是其他问题？
回答：
看这里的参数设置，对于应用程序的连接，空闲超时应该是3600秒。那么如果连接确实时空闲超过了3600，那么连接断开是符合MySQL的正常行为的。
但是连接到底空闲了多少时间呢？如果是JDBC，可以根据报错信息里的时间来判断，比如“The last packet successfully received from the server was 1,799,883 milliseconds” ago，上一个请求是1800之前的。
其他客户端可能没提供这个时间信息。可以使用show processlist观察会话的Sleep时间。有一个场景下，我发现Sleep时间一到900秒，连接就消失了，远远小于wait_timeout的配置，那么就要考虑其他原因了。
另外一种情况，可能是应用程序设置了不同的空闲超时时间，你可以通过应用程序执行show variables where variable_name in ('wait_timeout')，看看实际的超时时间是多少。
InnoDB 数据文件
InnoDB Buffer Pool 中的数据，最终会持久化到数据文件中。InnoDB 使用 IO 线程来进行 IO 操作。参数 innodb_read_io_threads、innodb_write_io_threads 分别指定了读 IO 和写 IO 的线程数，默认值都为 4，如果服务器上 CPU 核数多，可以适当增加这 2 个参数。
Redo Log Buffer
为了保障数据的持久化，修改 Buffer Pool 中的页面时，需要生成 Redo 日志。如果数据库或服务器异常崩溃，可以使用 Redo 日志来恢复数据。事务执行过程中，Redo 日志会先写入到 Redo Log Buffer 中，Buffer 的大小由参数 innodb_log_buffer_size 控制。一般分配几十兆就可以，比如 8.4 中默认为 64M。如果你的数据库并发写入量高，可适当把这个参数增加到几百兆。
事务提交时，需要将事务产生的 Redo 日志持久化到 Redo 文件中，这样才能保证数据不丢。参数 innodb_flush_log_at_trx_commit 控制事务提交时，Redo 日志的刷盘行为。设置为 1 时，每个事务提交时都会等待 Redo 日志刷盘完成，这是最安全的设置。但由于要等待 Redo 日志刷盘完成，性能上有一定的开销。这个参数设置为 2 时，事务提交时只会将 Redo 日志写到 Redo 文件中，然后每隔 1 秒刷新一次 Redo 文件，如果服务器异常崩溃，可能会导致部分数据丢失。
Redo 日志
早期版本中，通过参数 innodb_log_file_size 和 innodb_log_files_in_group 控制 Redo 日志文件的大小和数量。8.0.30 后，新增了 innodb_redo_log_capacity 参数，就不再需要单独设置 innodb_log_file_size 和 innodb_log_files_in_group 了。Redo 文件循环使用，随着数据库事务不停地执行，新的 Redo 日志最终会覆盖老的 Redo 日志。
数据库崩溃恢复时，需要通过 Redo 日志来恢复数据，那么覆盖老的 Redo 文件会不会导致数据丢失呢？或者 MySQL 如何保证覆盖 Redo 文件不影响数据库恢复？其实只要保证覆盖 Redo 文件时，数据库的 Checkpoint LSN 比 Redo 文件最大的 LSN 号更大就行了。
mysql> select file_id, start_lsn, end_lsn from innodb_redo_log_files;
+---------+------------+------------+
| file_id | start_lsn | end_lsn |
+---------+------------+------------+
| 656 | 2168369664 | 2171644416 |
+---------+------------+------------+
mysql> show global status like '%lsn%';
+-------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------+------------+
| Innodb_redo_log_checkpoint_lsn | 2170307263 |
| Innodb_redo_log_current_lsn | 2170307263 |
| Innodb_redo_log_flushed_to_disk_lsn | 2170307263 |
+-------------------------------------+------------+如果 innodb_redo_log_capacity 设置得太小，数据库写入量又比较大，那么覆盖 Redo 文件时，就可能需要等待数据库 Checkpoint，这会严重影响数据库写入的性能。对于 Buffer Pool 比较大，写入频繁的数据库，需要把 innodb_redo_log_capacity 设置得大一些，设置成几个 G 到几十 G 都是可以的。
下面以这个表格对 InnoDB 参数的设置做一个简单的总结。
其它参数设置
关于 MySQL 主备复制相关的参数，我们到后续的课程中再单独讨论。此外，sql_mode 也是 MySQL 中非常重要的一个参数。在生产环境中，修改这个参数可能会导致原先能运行的 SQL 直接报错，下一讲中我们会详细讨论 sql_mode 参数的设置。
总结
这一讲中，我们一起学习了 MySQL 的参数设置机制。我建议将一个数据库实例的参数都配置在同一个参数文件中。修改参数时，在参数文件中添加注释，简要说明参数这么设置的原因。有可能的话，使用版本控制工具来管理参数文件。
MySQL 只需要配置最基本的参数就能运行起来。但如果对性能和数据一致性有要求，就需要设置一些重要的参数，比如我们在这一讲中讨论到的资源限制参数、InnoDB 的一些参数。我们很难给 MySQL 设置真正意义上最优的参数，而且在不同的业务场景下，最优的配置可能是不一样的。我的建议是将参数设置得差不多好就行了，将更多的时间花在业务优化上。
如果你的业务场景对性能有极致的要求，可以使用真实业务场景，对数据库性能进行测评，测试在不同的参数配置下，数据库的性能表现。
思考
我们写 SQL 语句时，关键字一般不区分大小，不同的人可能有不同的习惯。对于库名、表名、列名，不同的数据库有不同的处理方法。比如 Oracle 中，表名默认不区分大小写。在 MySQL 中，根据操作系统的不同，表名就可能会区分大小写。
mysql> show tables;
+----------------+
| Tables_in_db01 |
+----------------+
| Ta |
| ta |
+----------------+
2 rows in set (0.01 sec)
mysql> select * from ta;
Empty set (0.01 sec)
mysql> SELECT * From tA;
ERROR 1146 (42S02): Table 'db01.tA' doesn't exist参数 lower_case_table_names 可用来控制表名是否区分大小写。
mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+MySQL 8.0 中，这个参数只能在数据库初始化之前设置，之后就不能再修改了，修改后数据库都无法启动。
[ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.你觉得这个参数应该怎样设置？原因是什么？
回答：
- lower_case_table_names参数一般推荐设置成1，这样库名表名不区分大小写。
- 在 linux 系统里面 lower_case_table_names 只支持设置为 0 或者 1，设置为 0 表示区分大小写，按照用户输入存储，设置为 1 表示不区分大小写，用户创建表名为大写时会按照小写存储，设置为 0 可能会出现两种命名方式，驼峰或者下划线，从标准化的角度想，希望表命名规则是统一的，可读性高，设置为 1 时，相当于数据库表名只能有下划线一种规则，所以推荐为 1。这样即使是跨操作系统迁移数据库，也不会出现表名大小写的问题。
- 在 Unix 上lower_case_table_names的默认值为 0。在Windows上默认值为 1。在macOS 上默认值为 2
- 0表示，表在文件系统存储的时候，对应的文件名是按建表时指定的大小写存的，MySQL 内部对表名的比较也是区分大小写的；
- 1表示，表在文件系统存储的时候，对应的文件名都小写的，MySQL 内部对表名的比较是转成小写的，即不区分大小写；
- 2表示，表在文件系统存储的时候，对应的文件名是按建表时指定的大小写存的，但是 MySQL 内部对表名的比较是转成小写的，即不区分大小写。
0适用于区分大小写的系统，1都适用，2适用于不区分大小写的系统。
我倾向于在所有的环境下，都将lower_case_table_names设置为1，保持一致。
要点总结
- MySQL参数设置机制包括全局参数和会话参数，可以通过命令行参数、配置文件、SET命令和SET PERSIST命令来设置参数。
- MySQL的日志文件包括错误日志、慢SQL日志和General Log，建议开启慢SQL日志以进行SQL优化。
- MySQL有一系列与文件路径相关的参数，如basedir和datadir，需要合理设置以指定程序文件的路径和数据文件的存放路径。
- MySQL的参数分为全局参数和会话参数，全局参数对整个实例生效，需要用SET GLOBAL命令设置，会话参数只对某一个会话生效，使用SET命令设置。
- MySQL 8.0开始支持参数修改持久化，通过SET PERSIST命令来设置，可以将参数的设置保存在数据目录下的mysqld-auto.cnf文件中，实现参数修改的持久化.
- Redo Log Buffer和Redo日志对于数据持久化和恢复至关重要，需要合理设置相关参数如innodb_log_buffer_size和innodb_flush_log_at_trx_commit。
- InnoDB参数设置包括innodb_buffer_pool_size和innodb_redo_log_capacity，需要根据实际情况进行合理配置。
- 数据库崩溃恢复时，通过Redo日志来恢复数据，需要保证数据库的Checkpoint LSN比Redo文件最大的LSN号更大。
- 对于Buffer Pool比较大，写入频繁的数据库，需要把innodb_redo_log_capacity设置得大一些，设置成几个G到几十G都是可以的。
- 参数lower_case_table_names可用来控制表名是否区分大小写，需要根据实际情况进行合理设置。
