NAVICAT 连接mysql数据库，报错：“1040 too many connection”

一般情况下，报too many connections，是linux服务器登录不上mysql，navicat也登录不上mysql，所以我们只能重新启动mysql：

service mysqld restart

启动后：

临时解决too many connections办法是：

登录linux服务器， 进入MySQL数据库：

查询最大连接数：

select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES where VARIABLE_NAME='MAX_CONNECTIONS';

报错：ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'

--从mysql5.7.6开始information_schema.global_status已经开始被舍弃，为了兼容性，此时需要打开 show_compatibility_56
MySQL> select * from information_schema.global_status limit 3;
ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'

--查看show_compatibility_56其值
mysql> show variables like '%show_compatibility_56%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| show_compatibility_56 | OFF   |
+-----------------------+-------+
1 row in set (0.01 sec)


--把show_compatibility_56打开
mysql> set global show_compatibility_56=on;
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like '%show_compatibility_56%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| show_compatibility_56 | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)


mysql> select * from information_schema.global_status limit 3;
+-----------------------+----------------+
| VARIABLE_NAME         | VARIABLE_VALUE |
+-----------------------+----------------+
| ABORTED_CLIENTS       | 0              |
| ABORTED_CONNECTS      | 0              |
| BINLOG_CACHE_DISK_USE | 0              |
+-----------------------+----------------+
3 rows in set, 1 warning (0.00 sec)

完成上述操作后，即可用mysql_front连接到mysql数据库了，可以对数据库进行图形化的界面操作~

修改最大连接数：

执行以下语句修改最大连接数：set global max_connections = 3600;

 

永久性的修改：

找到 my.cnf 修改配置文件

max_connections=2000

然后，重新启动mysql: service mysqld restart

 

修改mysql数据库密码：

1.vim /etc/my.cnf

2.在[mysqld]中添加

skip-grant-tables

例如：

[mysqld]
skip-grant-tables
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

3.重启mysql

service mysql restart

4.用户无密码登录

mysql -uroot -p (直接点击回车，密码为空)

5.选择数据库

use mysql;

6.修改root密码

update mysql.user set authentication_string=password('新密码') where user='用户';

7.执行

 flush privileges;

8.退出 mysql

quit

9.编辑 /etc/my.cnf

删除 skip-grant-tables 保存退出

10.重启mysql

service mysql restart