好好学MySQL：MySQL问题记录

1. 前言

本文记录使用MySQL过程中遇到的一些问题，备忘。

2. ERROR 2002 (HY000)

2.1. 问题描述

mysql -u root -p，然后输入密码，登录mysql，报错如下：

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

2.2. 排查解决

参考文档：MySQL错误ERROR 2002 (HY000)

1、查看mysql.sock文件位置
find / -name mysql.sock，结果为：/data/db/3306/mysql.sock

和报错中的/tmp/mysql.sock不一致。

在/tmp下没有mysql.sock文件，依次执行touch mysql.sock，chmod 666 mysql.sock。

重启mysql，service mysqld restart，再次登录mysql，报错如下：

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)

2、找到mysql配置文件
ps aux | grep mysql，在结果中找到：

--defaults-file=/opt/mysql/my.cnf

3、查看mysql.sock配置
more /opt/mysql/my.cnf | grep sock，结果如下：

socket=/data/db/3306/mysql.sock
socket = /data/db/3306/mysql.sock

4、修改my.cnf
vim /opt/mysql/my.cnf，修改两个socket如下：

socket = /tmp/mysql.sock
socket = /tmp/mysql.sock

重启mysql，再次登录，成功！

3. Lost connection to MySQL server during query

3.1. 问题描述

Python使用tortoise-orm库连接MySQL，偶尔报错：

2013, 'Lost connection to MySQL server during query ([Errno 104] Connection reset by peer)'

3.2. 排查解决

这是报错，是因为mysql自动断开了长时间没有任何动作的连接。

解决办法：使用pool_recycle参数，并且小于wait_timeout和interactive_timeout。。

原理：
mysql根据wait_timeout和interactive_timeout的配置，自动断开长时间没有任何动作的连接。

tortoise-orm的pool_recycle参数可以避免MySQL自动断开长时间没有任何动作的连接，是因为它会定期检查连接的有效性，并在超过指定时间后关闭并重新打开连接。这样，就可以保证连接池中的连接都是新鲜的，不会因为MySQL服务器的超时设置而被断开。

pool_recycle应该小于wait_timeout和interactive_timeout。

MySQL配置timeout的方法：
1、查看wait_timeout和interactive_timeout

show global variables like '%timeout%';
show variables like '%timeout%';

2、编辑/etc/my.cnf，如下修改

wait_timeout=28800
interactive_timeout=28800

3、重启MySQL

4. caching_sha2_password cannot be loaded

4.1. 问题描述

mysql命令行连接mysql8.0，报错：

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

4.2. 排查解决

原因：该错误通常是由于 MySQL 8 使用的默认身份验证插件已更改而引起的。MySQL 8 默认使用 caching_sha2_password 身份验证插件，而之前版本默认使用 mysql_native_password。

解决方法一：服务端改用 mysql_native_password 插件

[mysqld]
default-authentication-plugin=mysql_native_password

解决方法二：升级客户端，安装支持mysql8.0的client，参考文档《使用Docker安装配置MySQL》

5. root无权grant问题

5.1. 问题描述

root用户登录mysql后，执行命令

grant all privileges on *.* to 'root'@'%' identified by 'vkpassword2';

报错：Error 1045 (28000): Access denied for user ‘root‘@’localhost’ (Using password: YES)

5.2. 问题排查

原因：root@localhost 不具备grant权限。

show grants for root@'localhost';

如果具备grant权限，那么会看到下面的输出：

+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

如果没有grant权限，那么第一行不会出现 WITH GRANT OPTION。

解决办法：

update mysql.user set Grant_priv="Y" where user="root" and host="localhost";
flush privileges;
exit;

重新登录后，就可以正常执行授权命令了。

6. mysql身份认证插件问题

6.1. 问题描述

python连接mysql8.0，正常。后来切换了一个新的mysql8.0数据库，连接报错：

RuntimeError: 'cryptography' package is required for sha256_password or caching_sha2_password auth methods

6.2. 解决办法

参考文档：浅谈 MySQL 新的身份验证插件 caching_sha2_password

问题原因：mysql8.0之后，默认身份认证插件从 mysql_native_password 换成了 sha256_password，使用老的数据库没有问题，说明老的数据库使用的是 mysql_native_password，因此新的数据库也要换成 mysql_native_password。

具体操作：
1、修改mysql配置

vim /etc/mysql/conf.d/my.cnf

添加配置：

default_authentication_plugin = mysql_native_password

2、重启mysql

systemctl restart mysqld

3、验证修改是否成功

SHOW VARIABLES LIKE 'default_authentication_plugin';

4、配置用户使用默认认证插件
添加新用户：

CREATE USER 'vkuser'@'%' IDENTIFIED WITH mysql_native_password BY 'vk123456';
GRANT all privileges on vkdb.* TO 'vkuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

或者修改已有用户的认证插件：

ALTER USER 'vkuser'@'%' IDENTIFIED WITH mysql_native_password BY 'vk123456';
FLUSH PRIVILEGES;

7. 无法通过主机IP连接MySQL问题

7.1. 问题描述

参考《好好学MySQL：使用Docker安装配置MySQL》安装mysql8.0.28。
mysql容器运行后，mysql容器内测试登录，发现登录时host指定宿主机eth0或者docker0 IP时会报错。
具体表现如下：

mysql -uroot -p
# 可以正常登录

mysql -h127.0.0.1 -uroot -p
# 可以正常登录

mysql -h192.168.56.101 -uroot -p
# host指定宿主机eth0 IP，报错：ERROR 1045 (28000): Access denied for user 'root'@'192.168.56.101' (using password: YES)

mysql -h172.17.0.1 -uroot -p
# host指定宿主机docker0 IP，报错：ERROR 1045 (28000): Access denied for user 'root'@'192.168.56.101' (using password: YES)

7.2. 问题排查

7.2.1. 教程问题？

怀疑是《好好学MySQL：使用Docker安装配置MySQL》文档有误。

1、是否是mysql8.0.28镜像近期有了更新，原本的安装方法不适用了？
检查docker hub上的mysql8.0.28镜像更新时间，两年前更新，不是镜像问题。

2、是否是启动时需要加上-e MYSQL_ROOT_HOST=%参数？
加上该参数后，问题依旧。

3、是否是时区问题？
启动命令加上 -v /etc/localtime:/etc/localtime:ro ，问题依旧。

7.2.2. 远程登录配置问题？

根据以前的测试，默认是允许远程登录的才对。
假设默认没有允许远程登录，那么手动配置允许远程登录。

mysql -uroot -p

> CREATE USER 'root'@'172.20.0.145' IDENTIFIED BY 'mysql123';
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.56.101';
> FLUSH PRIVILEGES;

配置后，问题依旧。

7.2.3. 端口占用问题？

其他程序占用了3306端口？

netstat -nlp | grep 3306

确实是mysql服务占用了3306端口，没有问题。

7.2.4. 主机问题？

换一台主机，同样的方法安装mysql8.0.28，host指定宿主机eth0或者docker0 IP时，可以正常登录。证明了是主机本身存在什么问题。

两台主机，docker版本相同（19.03.15），/etc/docker/daemon.json 配置相同，docker network ls网络配置相同。

最终有两个怀疑，但是没有验证，可供参考：
1、网络问题。有问题的主机，同时也是k8s master节点，也许网络受到ipvs的影响。
2、内核问题。有问题的主机内核版本为 4.19.188-10.el7.ucloud.x86_64 ，正常主机的内核版本为 3.10.0-1160.36.2.el7.x86_64。

临时解决办法：启动mysql8.0.28容器时，使用 --network=host 。

7.2.5. 端口转发问题

继续排查发现，主机上之前配置过一个端口映射，3306端口转发到了另一个主机的mysql服务。具体配置方法可以参考《iptables实现端口映射》。

至此，问题原因就清楚了：主机上配置了3306端口的转发，登录时host指定宿主机eth0或者docker0 IP时，实际上尝试登录的，是另外一个mysql。

解决办法：启动mysql8.0.28容器时，把3306换成另外一个端口。