Mysql Too many connets紧急处理

Part1环境信息

1.1、数据库版本

[mysql ~]$ mysql --version
mysql  Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)

1.2、数据库参数

mysql> show global variables like '%connections';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 500   |
| max_user_connections   | 0     |
| mysqlx_max_connections | 100   |
+------------------------+-------+

Part2问题处理

2.1、gdb紧急处理

查询数据库进程信息

[root ~]# ps -ef| grep mysqld

mysql     5929     1  0 Apr03 ?        00:29:32 /mysql/app/mysql8.0.36/bin/mysqld --defaults-file=/mysql/conf/my3306.cnf

也可以这样查

[root ~]# find / -name  "*.pid"
/mysql/data/mysql3306/data/node1.pid

[root ~]# cat /mysql/data/mysql3306/data/node1.pid
5929

修改的两种方式

[mysql ~]$ gdb -p 5929 -ex "set max_connections=1000" -batch

[mysql ~]$ gdb -p $(cat /mysql/data/mysql3306/data/node1.pid) -ex "set max_connections=1000" -batch

发现了一些报错，但是不影响登录

Dwarf Error: wrong version in compilation unit header (is 0, should be 2, 3, or 4) [in module /mysql/app/mysql8.0.36/bin/mysqld]

Dwarf Error: wrong version in compilation unit header (is 0, should be 2, 3, or 4) [in module /mysql/app/mysql8.0.36/lib/plugin/component_reference_cache.so]

Dwarf Error: wrong version in compilation unit header (is 0, should be 2, 3, or 4) [in module /mysql/app/mysql8.0.36/lib/plugin/group_replication.so]

Dwarf Error: wrong version in compilation unit header (is 0, should be 2, 3, or 4) [in module /mysql/app/mysql8.0.36/lib/plugin/mysql_clone.so]

查询修改后的数据库参数

mysql> show global variables like '%connections';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 1000  |
| max_user_connections   | 0     |
| mysqlx_max_connections | 100   |
+------------------------+-------+
3 rows in set (0.01 sec)

2.2、数据库紧急处理

查看数据库连接参数

mysql> show global variables like 'max_connections';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 3     |
+-----------------+-------+
1 row in set (0.01 sec)

显示连接具体信息

mysql> show full processlist;
+----+-----------------+-----------+------+---------+--------+------------------------+-----------------------+
| Id | User            | Host      | db   | Command | Time   | State                  | Info                  |
+----+-----------------+-----------+------+---------+--------+------------------------+-----------------------+
|  7 | event_scheduler | localhost | NULL | Daemon  | 426103 | Waiting on empty queue | NULL                  |
| 19 | root            | localhost | NULL | Query   |      0 | init                   | show full processlist |
| 26 | test            | localhost | NULL | Sleep   |    211 |                        | NULL                  |
| 29 | test            | localhost | NULL | Sleep   |     67 |                        | NULL                  |
+----+-----------------+-----------+------+---------+--------+------------------------+-----------------------+
4 rows in set, 1 warning (0.00 sec)

显示连接数

mysql> show global status like 'threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 3     |
+-------------------+-------+
1 row in set (0.01 sec)

链接失败，连接数过多

[mysql ~]$ mysql -utest -p'test'  -P 3306 -S /mysql/data/mysql3306/socket/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): Too many connections

赋予SERVICE_CONNECTION_ADMIN权限

mysql> grant service_connection_admin on *.* to test@'%';
Query OK, 0 rows affected (0.04 sec)

连接成功

[mysql ~]$ mysql -utest -p'test'  -P 3306 -S /mysql/data/mysql3306/socket/mysql.sock

mysql> 

查看具体的链接信息

mysql> show full processlist;
+----+-----------------+-----------+------+---------+--------+------------------------+-----------------------+
| Id | User            | Host      | db   | Command | Time   | State                  | Info                  |
+----+-----------------+-----------+------+---------+--------+------------------------+-----------------------+
|  7 | event_scheduler | localhost | NULL | Daemon  | 427989 | Waiting on empty queue | NULL                  |
| 19 | root            | localhost | NULL | Query   |      0 | init                   | show full processlist |
| 26 | test            | localhost | NULL | Sleep   |   2097 |                        | NULL                  |
| 29 | test            | localhost | NULL | Sleep   |   1953 |                        | NULL                  |
| 35 | test            | localhost | NULL | Sleep   |    958 |                        | NULL                  |
+----+-----------------+-----------+------+---------+--------+------------------------+-----------------------+
5 rows in set, 1 warning (0.00 sec)

连接线程数

mysql> show global status like 'threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 4     |
+-------------------+-------+
1 row in set (0.00 sec)

查看test的权限

mysql> show grants for test@'%';
+-----------------------------------------------------+
| Grants for test@%                                   |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`%`                    |
| GRANT SERVICE_CONNECTION_ADMIN ON *.* TO `test`@`%` |
| GRANT ALL PRIVILEGES ON `test`.* TO `test`@`%`      |
+-----------------------------------------------------+
3 rows in set (0.00 sec)

可以看到root也有SERVICE_CONNECTION_ADMIN权限

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION |


| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION |

2.3、查杀会话

确定哪个用户连接最多

mysql> select user,count(*) from performance_schema.processlist group by user;

查看该用户执行最多的语句

mysql>  select * from performance_schema.processlist where USER ='用户' order by INFO;

查看锁语句并把该sql干掉

select * from sys.innodb_lock_waits\G

sql_kill_blocking_connection这行

Part3思路复盘

1、gdb不方便在生产上操作，可能会有后患，但是情急之下没有办法 2、当时生产库，我用root登录也报连接数过多，说明有人或者有业务也使用了root用户，导致我没办法登录，只能重启数据库，导致费时费力。要定期检查谁用了root用户，一定要控制使用的用户。