报错
quickBI上报错 数据源执行SQL失败:INTERNAL: java.sql.SQLException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections" Navicat上报错 "Too many connections" 或者Linux上报错 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1040 (08004): Too many connections
处理
#1、 root 用户登录mysql [root@master etc]# mysql -u root -p123456@qwe Server version: 5.7.38 MySQL Community Server (GPL) mysql> # 查看MySQL能建立的最大连接数 mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.00 sec) # 查看响应的连接数 mysql> show status like 'max%connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 152 | +----------------------+-------+ 1 row in set (0.00 sec) # 设置最大连接数为1000 mysql> set global max_connections=1000; Query OK, 0 rows affected (0.00 sec) # 查看MySQL能建立的最大连接数 mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1000 | +-----------------+-------+ 1 row in set (0.00 sec) # 设置的最大连接数只在mysql当前服务进程有效，一旦mysql重启，又会恢复到初始状态。 # 因为mysql启动后的初始化工作是从其配置文件中读取数据的，而这种方式没有对其配置文件做更改。 # 可以通过修改配置文件来修改mysql最大连接数(max_connections) #2、 MySQL 的配置文件是 my.cnf，一般会放在 /etc/my.cnf 或 /etc/mysql/my.cnf 目录下 [root@master mysql]# cat /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # ... max_connections=1000 log-error=/var/log/mysqld.log
Linux下mysql修改连接超时wait_timeout（在这个问题中，不是必要操作）
# 大规模多线程操作事务的时候，有时候打开一个链接，会进行等待， # 这时候如果数据库的超时时间设置的过短，就可能会出现，数据链接自动被释放，可能会遭遇到“mysql has gone away”之类的问题。 # 当然设置过大也不好，慢SQL或其他因素引起的链接过长，MySQL里大量的SLEEP进程无法及时释放，导致整个系统被拖慢，甚至挂掉。 # 所以，需要适当的设置超时时间 mysql> # 查看超时时间设置 mysql> SHOW GLOBAL VARIABLES LIKE '%timeout%'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | have_statement_timeout | YES | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 60 | | wait_timeout | 28800 | +-----------------------------+----------+ 13 rows in set (0.00 sec) # 通过jdbc连接数据库是非交互式连接 # 设置为10000（临时方法，重启MySQL服务器会失效，恢复默认值） mysql> SET GLOBAL wait_timeout=10000; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE '%timeout%'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | have_statement_timeout | YES | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 60 | | wait_timeout | 10000 | +-----------------------------+----------+ 13 rows in set (0.01 sec)
