I have a mysql database running on a CentOS server. I have noticed that, when left to run for several weeks without restarting the service, then when I need to do a restart (/etc/init.d/mysqld restart), the service will display "fail" for both stop and start. It will then refuse to start again, unless I kill -9 the mysqld_safe and mysqld processes.
Why does this happen and what can I do to prevent it?
Using mysql 5.1.66 on Centos 6.3
The script /etc/init.d/mysqld is dependent on the presence of the file mysql.sock. If that file is missing, run service mysqld restart or service mysqld stop simply does not work.
Over the years, I have seen many incidents where mysql.sock just disappears (going back to MySQL 4.x). See ServerFault Post : What should mysqld.sock contain, why don't I have it?. When this happens, you will have to resort to mysqladmin shutdown.
If you tried this:
mysqladmin -h127.0.0.1 -uroot -p shutdown
this may fail if it authenticates as root@localhost
Instead, shutdown mysql using the TCP/IP protocol
mysqladmin -h127.0.0.1 --protocol=tcp -uroot -p shutdown
This bypasses checking for the socket file mysql.sock.
Give it a Try !!!
I have discussed using mysqladmin shutdown before
Dec 14, 2012 : Percona-server time out on /etc/init.d/mysql start (ServerFault)May 08, 2012 : How to properly stop MySQL server on Mac OS X?Feb 06, 2012 : MySQL Database and Table Loss
tmpwatch linuxcommand.org/man_pages/tmpwatch8.html which may possibly be configured inside /etc/cron.daily on CentOS.
Commented
Feb 28, 2013 at 20:17
SHOW VARIABLES LIKE 'pid_file';?