MySQL 常见问题及解决方案

一、配置远程访问

---

二、too many connections

1. 通过命令行 mysql -uroot -p进入MySQL monitor。

D:\>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2696
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

2. 使用 show variables like "max_connections" 命令查看最大连接数。

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 200   |
+-----------------+-------+
1 row in set, 1 warning (0.00 sec)

使用 show processlist 命令可以查看具体的连接数信息。

mysql> show processlist;
+------+-----------------+-----------------+------+---------+-------+------------------------+------------------+
| Id   | User            | Host            | db   | Command | Time  | State                  | Info             |
+------+-----------------+-----------------+------+---------+-------+------------------------+------------------+
|    4 | event_scheduler | localhost       | NULL | Daemon  | 19665 | Waiting on empty queue | NULL             |
| 1803 | root            | localhost:55664 | NULL | Query   |     0 | starting               | show processlist |
+------+-----------------+-----------------+------+---------+-------+------------------------+------------------+
2 rows in set (0.00 sec)

3. 使用 set GLOBAL max_connections 命令修改最大连接数。

mysql> set GLOBAL max_connections=1000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+
1 row in set, 1 warning (0.00 sec)

4. 默认 MySQL 在关闭一个非交互连接前要等待的秒数为 28800s，可以通过命令 show global variables like 'wait_timeout' 查看。

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

优化这个配置，控制在5分钟内。

mysql> set global wait_timeout=300;
Query OK, 0 rows affected (0.00 sec)

5. interactive_timeout 为 MySQL 在关闭一个连接之前等待的时间（秒），设置此值可以让 MySQL 自动关闭没用的连接，但是正处于使用中的连接到时间后也会被关闭，使用时要格外注意。