Mysql is not using /etc/my.cnf

Hi
I have mysql 5.1.73 installed on centos6.4

when I stop/start mysqld :
/etc/init.d/mysqd stop/start
 , as a root user, the system variables within /etc/my.cnf are applied
when I stop/start mysqld :
/etc/init.d/mysqd stop/start
 , as a mysql user I notice the following variables changes logged into error.log
151023 19:58:15 [Warning] Changed limits: max_open_files: 1024  max_connections: 886  table_cache: 64

any idea why this is happening?

Thanks,
CCVOIPAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
Have you looked to see if the 'user' has a separate 'my.cnf' file?
Tomas Helgi JohannssonCommented:
Hi!

As root issue this command
mysql --help | grep "Default options" -A 1

this will give you the locations/paths that you should look into for the my.cnf file.

Also you can issue this command to see if there is a my.cnf file in the default file option
ps -ef | grep mysql

If no my.cnf file is found the database uses all the defaults for all variables.

Max open files limit is a limit set and controlled by the operating system.
Issue this command (as both root and the user that is running mysql)

ulimit -Hn -Sn

This will tell you how many open files your user is allowed to open.

To set a new value you edit the /etc/security/limits.conf file and set
username soft nofile <some soft value>
username hard nofile <maximum allowed open files for user>

where username is the user running the mysql deamon (mysql user :) ) and restart the mysql server.

To change max open files for the entire system you edit /etc/sysctl.conf and put this line
fs.file-max = <some max value>
and issue sysctl -p as root to update the system.

Regards,
     Tomas Helgi

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CCVOIPAuthor Commented:
Hello Tomas,

 
[root@ ~]#mysql --help | grep "Default options" -A 1
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
[root@ ~]#cat /etc/mysql/my.cnf
cat: /etc/mysql/my.cnf: No such file or directory
[root@ ~]# cat ~/.my.cnf
cat: /root/.my.cnf: No such file or directory
[mysql@ ~]# cat ~/.my.cnf
cat: /home/mysql/.my.cnf: No such file or directory

Open in new window


rebooted the machine, but still the same thing :( . any other suggestions??

I followed your suggestion to fix open files and it worked perfectly. Thanks

Regards,
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Tomas Helgi JohannssonCommented:
Hi!

The command
locate my.cnf

Open in new window

(issued as root) should give you all my.cnf files on your host.
Note that if you don't find any my.cnf on the default paths then you can always create one (in the /etc/ path ) and configure the system parameters you want to differ from default. :)

Regards,
      Tomas Helgi
CCVOIPAuthor Commented:
Hi,
I do have a my.cnf  file under /etc/ where I specified all the configuration . it's just I don't understand why root user uses the /etc/my.cnf and mysql user uses something else which I don't seem to find.!!

Thanks
Tomas Helgi JohannssonCommented:
Hi!

The default location for the my.cnf is usually in the /etc folder where the mysql user (user running the deamon) accesses it.


Regards,
     Tomas Helgi
Tomas Helgi JohannssonCommented:
Hi!

Another thing. Did you issue this command
ps -ef | grep mysql | grep "defaults-file"
Did that command give you any output ?
You could also issue
mysql --print-defaults
This will show you which default values the mysql server is using and what config file if any.

Regards,
    Tomas Helgi
CCVOIPAuthor Commented:
Hi,
both commands return nothing
[root@~]# ps -ef | grep mysql | grep "defaults-file"
[root@~]#

Open in new window

[root@ ~]# mysql --print-defaults
mysql would have been started with the following arguments:

[root@ ~]#

Open in new window

CCVOIPAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for CCVOIP's comment #a41135975
Assisted answer: 250 points for TomasHelgi's comment #a41104064
Assisted answer: 250 points for TomasHelgi's comment #a41134047

for the following reason:

thank you all for your responses. but still having the same issue!!
CCVOIPAuthor Commented:
Thanks all for your response. but I still have the same issue!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.