Mysql：my.cnf中的修改不生效

I've updated the my.cnf file of my database with the following line: max_connections=200. I stopped and started the mysql service after that so that the changes would take effect.

我用以下行更新了我的数据库的my.cnf文件:max_connections = 200。之后我停止并启动了mysql服务,以便更改生效。

But for some reason this change doesn't affect the database because if I run:

但由于某种原因,此更改不会影响数据库,因为如果我运行:

mysql> select @@max_connections

it shows that the max number of connections is 100.

它显示最大连接数为100。

Obviously there is some place else that manages this value. Where can I find it or what did I do wrong?

显然,还有一些地方可以管理这个价值。我在哪里可以找到它或者我做错了什么?

Thank you for your reply.

谢谢您的回复。

5 个解决方案

#1

Make sure the max_connections in under the [mysqld] section:

确保[mysqld]部分下的max_connections:

Ex:

[mysqld]
socket=/path/to/mysql.sock
datadir=/var/lib/mysql
max_connections=200

[client]
#mysql-client settings here..

#2

Try running mysqld --verbose --help to see which configuration file is actually read by mysqld and which parameters and values are used. The output will look like this:

尝试运行mysqld --verbose --help以查看mysqld实际读取的配置文件以及使用的参数和值。输出将如下所示:

mysqld  Ver 5.0.51a-24-log for debian-linux-gnu on x86_64 ((Debian))
Copyright (C) 2000 MySQL AB, by Monty and others
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf
The following groups are read: mysql_cluster mysqld server mysqld-5.0

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- -----------------------------
...

To see what values a running MySQL server is using, type
'mysqladmin variables' instead of 'mysqld --verbose --help'.

#3

It may have to do with 'how' the mysql server is being shutdown and restarted. On my system if I use the mysqld daemon service to shutdown mysql (e.g. service mysqld stop), I get a shutdown notice, but a ps shows mysql is still running. Using a similar 'service mysqld restart', some of the changes to the my.cnf file get accepted, but many don't.

它可能与mysql服务器关闭和重启的“方式”有关。在我的系统上如果我使用mysqld守护进程服务来关闭mysql(例如服务mysqld stop),我得到一个关闭通知,但ps显示mysql仍在运行。使用类似的'service mysqld restart',my.cnf文件的一些更改会被接受,但很多都没有。

The other method of shutting down mysql is to use mysqladmin -u user -pPass shutdown. I noticed when I used this method, mysql was shutdown completely (no left overs in ps), and when I restarted the mysql server, all the changes to the my.cnf file were accepted.

关闭mysql的另一种方法是使用mysqladmin -u user -pPass shutdown。我注意到当我使用这个方法时,mysql完全关闭(ps中没有留下),当我重新启动mysql服务器时,my.cnf文件的所有更改都被接受了。

#4

Changes to mysqld are not necessarily reflected in the mysql client! I changed a global variable assignment in my.cnf, restarted the service, and queried it in the mysql client. It returned the old value. When queried from a script, however, the value was in fact changed!

对mysqld的更改不一定反映在mysql客户端中!我在my.cnf中更改了一个全局变量赋值,重新启动了该服务,并在mysql客户端中查询了它。它返回了旧值。但是,从脚本中查询时,该值实际上已更改!

#5

If mysql starts as a Window service, check the 'Path to executable' setting on the windows service. (Services -> MYSQL56 -> Properties).

如果mysql作为Window服务启动,请检查Windows服务上的“可执行路径”设置。 (服务 - > MYSQL56 - >属性)。

If the --defaults-file option is passed in, it could point to a completely different .ini file in a location that is NOT showing with 'mysqld --verbose --help'.

如果传入了--defaults-file选项,则它可能指向一个完全不同的.ini文件,该文件位于未显示“mysqld --verbose --help”的位置。

If you remove the --defaults-file option from the service startup parameters, it will go through the list of ini files as listed with mysqld --verbose --help.

如果从服务启动参数中删除--defaults-file选项,它将通过mysqld --verbose --help列出的ini文件列表。

#1