一个计算机技术爱好者与学习者

0%

MySQL问题记录

1. 前言

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

2. ERROR 2002 (HY000)

2.1. 问题描述

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

1
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.sockchmod 666 mysql.sock

重启mysql,service mysqld restart,再次登录mysql,报错如下:

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

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

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

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

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

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

1
2
socket = /tmp/mysql.sock
socket = /tmp/mysql.sock

重启mysql,再次登录,成功!

3. Lost connection to MySQL server during query

3.1. 问题描述

Python使用tortoise-orm库连接MySQL,偶尔报错:

1
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

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

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

1
2
wait_timeout=28800
interactive_timeout=28800

3、重启MySQL

4. caching_sha2_password cannot be loaded

4.1. 问题描述

mysql命令行连接mysql8.0,报错:

1
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 插件

1
2
[mysqld]
default-authentication-plugin=mysql_native_password

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

5. root无权grant问题

5.1. 问题描述

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

1
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权限。

1
show grants for root@'localhost';

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

1
2
3
4
5
6
+---------------------------------------------------------------------+
| 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

解决办法:

1
2
3
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数据库,连接报错:

1
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配置

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

添加配置:

1
default_authentication_plugin = mysql_native_password

2、重启mysql

1
systemctl restart mysqld

3、验证修改是否成功

1
SHOW VARIABLES LIKE 'default_authentication_plugin';

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

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

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

1
2
ALTER USER 'vkuser'@'%' IDENTIFIED WITH mysql_native_password BY 'vk123456';
FLUSH PRIVILEGES;
  • 本文作者: 好好学习的郝
  • 原文链接: https://www.voidking.com/dev-mysql-problem/
  • 版权声明: 本文采用 BY-NC-SA 许可协议,转载请注明出处!源站会即时更新知识点并修正错误,欢迎访问~
  • 微信公众号同步更新,欢迎关注~