MySQL ignoring some params from my.cnf

Question

I have a virtual box with 2 installed instances of MySQL.

First instance:

- /etc/mysql/my.cnf
- /var/lib/mysql
- port = 3306

Second instance:

- /etc/mysql2/my.cnf
- /var/lib/mysql2
- port = 3307

I can connect to both. The first instance is fine. The second is not.

MySQL doesn't ignoring some params from my.cnf file (socket, port, pid-file, data-dir...). But if I'm trying to change something else: cache, buffers, log files it completely ignores changes after restarting.

I need to make MySQL read all params from my.cnf.

I tried: - to move my.cnf from /etc/mysql to /var/lib/mysql; - commenting out !includeir /etc/mysql/conf.d.

Could someone guide me?

Answer 1

I had a similar problem with a box I was diagnosing for performance issues, it turned out that there was an incorrect / misplaced heading entry within the my.cnf for that server, so MySQL was ignoring all the settings within that section...

For example, if you had the following;

[mysqld]
######################################################
# First, the generic server configuration items
port                    = 3306
socket                  = /var/lib/mysql/mysql.sock
datadir                 = /mysql/data

[client]
#password               = your_password
port                    = 3306
socket                  = /var/lib/mysql/mysql.sock

key_buffer_size         = 256M
table_open_cache        = 1024
query_cache_size        = 32M

Then the buffer tuning section is ignored as it is within the client section and not the mysqld section... Re-arrange the sections, and all works as intended.

Hopefully, it is as simple as this for you!

Answer 2

I had the same symptoms. I would start the service and it would read the data directory from /etc/my.cnf and pretty much ignore the rest.

I finally decided selinux was the culprit. I looked in the selinux log files in /var/log/audit/audit.log and saw some warnings about reading attributes of my.cnf.

I then compared what selinux thought of /etc/my.cnf on the server that had problems and compared it to another box that was fine. On the bad box, the command:

ls -Z /etc/my.cnf

showed that "admin_home_t" was part of the permission yet on the good box it was "etc_t". I assume at some point I had copied a my.cnf over the top of the file and changed selinux permissions.

To fix, I issued this command on /etc/my.cnf:

chcon -R -t etc_t /etc/my.cnf

I restarted mysqld: service mysqld restart and the settings in my.cnf showed up in the variables: mysql> show variables;

Answer 3

Thanks to all.

Issue is resolved. The problem was in permissions:

- `mysql` doesn't have a permission to read my.cnf;
- I started up instance with sudo user;
- while connected without it.