Mysql: modification in my.cnf doesn't take effect

Question

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.

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.

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.

Hippocrene

What operating system are you using to host your mysql server? – Jacquelynnjacquenetta

I'm using linux for my server. – Hippocrene

Possibly, memory constraints could limit it to 100. I tested my own at 200, but could only achieve 151 from @@max_connections. I set it down to 110 to confirm that the value was read from my.cnf, and I did get back the correct 110 from @@max_connections. – Causey

Though 100 is a suspiciously round number... – Causey

Answer 1

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

Ex:

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

[client]
#mysql-client settings here..

Adkins

Answer 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  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'.

Cassiopeia

Didn't fix it, but it helped me anyway. Thank you! – Hippocrene

Answer 3

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!

Weakfish

Answer 4

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.

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.

Rossiter

Answer 5

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

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'.

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.

Garrett

Thank you! On my PC it was looking for C:\ProgramData\MySQL\MySQL Server 8.0 – Levant

Answer 6

Putting my.cnf in /etc/my.cnf and restarting mysql has resolved the issue for me. I'm using mac os. Mysql version is 5.6.41

Conceptualize