mysql 常见问题处理

- Mysql链接报Too many connections的解决办法

• 问题描述
  当我们在链接mysql的时候会报Too many connections，如下图所示：
  

• 问题原因
  If you get “Too many connections” error message while connecting to MySQL database it means all available connections have been used by the various clients and your MySQL Server cannot open any new connections until any of the existing connections is closed.
  By default, MySQL 5.5+ can handle up to 151 connections. This number is stored in server variable called max_connections. You can update max_connections variable to increase maximum supported connections in MySQL, provided your server has enough RAM to support the increased connections.

• 解决办法

• 停止一些不用的链接

• 不重启临时增加链接数上限的方式

# 临时解决方案，不需要重启mysql, 但是mysql重启后会失效
# 在已链接的mysql终端执行如下命令：
# 展示 最大链接数
show variables like "max_connections";
# 设置最大链接数为300
set global max_connections = 300;

• 重启mysql 永久修改连接数的上限

 sudo vi /etc/my.cnf
 max_connections = 200
 sudo service mysql restart

参考链接：
https://ubiq.co/database-blog/how-to-increase-max-connections-in-mysql/
https://www.jianshu.com/p/aa76057fe84d

- pymysql.err.OperationalError: (1130, “xxx’ is not allowed to connect to this MySQL server”)

• 问题描述
  
• 解决办法
  登录mysql，查看数据的权限信息 若用户的权限是localhost，则按如下方式修改

 > mysql -u root -p   # 输入密码登录本地mysql
 > use mysql;
 > select host , user from user;
 +-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
 > update user set host = '%' where user ='root'; # 修改权限为 所有ip都可访问
 > flush privileges;   # 刷新权限
 > # 重新查询
 +-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+

• 参考链接
  https://blog.csdn.net/weixin_46077728/article/details/119424190

- Unable to load authentication plugin ‘caching_sha2_password’

• 问题描述
  

• 问题原因
  MySQL 8.0.4开始, 默认的认证插件从mysql_native_password 变为caching_sha2_password.

• 5.X版本是：default_authentication_plugin=mysql_native_password

• 8.x版本是：default_authentication_plugin=caching_sha2_password

• 解决办法

• 方式1：更换驱动的jar包

• 方式2：修改认证插件的默认版本

> ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
> FLUSH PRIVILEGES;

• 参考链接
  https://www.cnblogs.com/wanguncle/p/11432868.html
  https://blog.csdn.net/weixin_38004638/article/details/105321570

- Unknown system variable ‘query_cache_size’

• 问题描述
  

• 问题原因
  版本对对应，还有其他类似问题
  java.sql.SQLException: Unknown system variable ‘query_cache_size’
  java.sql.SQLException: Unknown system variable ‘tx_read_only’
  java.sql.SQLException: Unknown system variable ‘language’

• 解决版本
  下载对应的驱动版本 可以到这里查看对应的驱动版本号

1. 查看数据库版本

   select version();
   

2. 下载对应驱动
   2.1 https://mvnrepository.com/artifact/mysql/mysql-connector-java，找到与mysql匹配的驱动版本，点击驱动版本号
   
   2.2 点击file直接下载，或者点击View All在详情页面获取下载的url
   
   2.3 双击待下载的jar包，在快捷栏中选择”复制链接地址“ ：https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.23/mysql-connector-java-8.0.23.jar
   
   2.4 下载驱动 wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.23/mysql-connector-java-8.0.23.jar