mysql使用的常见问题_MySQL 常见问题及解决方案

一、配置远程访问

二、too many connections

通过命令行 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>

使用 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)

使用 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)

默认 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)

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