The slickest way to shutdown mysql when it does that is simply to run
mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp shutdown
Here is why:
The mysql service file (/etc/init.d/mysql) relies on the presence of the socket file. Historically speaking, going way back to MySQL 4.0, the socket file sometimes disappears inexplicably. This hampers a standard service mysql stop from working.
It is not enough to say
mysqladmin -uroot -p -h127.0.0.1 shutdown
because mysqld will route a user coming in as [email protected] to root@localhost if TCP/IP is not explicitly enabled. By default, mysqld will choose the least path of resistance and connect [email protected] to root@localhost via the socket file. Yet, if there is no socket file, root@localhost will never connect.
Even the MySQL Documentation on mysqladmin says this:
If you execute mysqladmin shutdown when connecting to a local server using a Unix socket file, mysqladmin waits until the server's process ID file has been removed, to ensure that the server has stopped properly.
That is why it is imperative to enable TCP/IP:
mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp shutdown
Back on Sept 30, 2011, I scripted my own version of mysqld_multi called mysqlservice (See my post : Running multiple instances on the same host). It serves as a virtual engine for connecting to mysqld from different ports. You just have to bring your own my.cnf with customized parameters. In that script, I issue shutdowns like this:
stop() {
${ECHO} -n $"Stopping ${PROGNAME}"
${MYSQLD_STOP}
ATTEMPTS=0
STOPPING_MYSQLD=1
MINUTES_TO_TRY=10
(( TICKS_TO_TRY = MINUTES_TO_TRY*240 ))
while [ ${STOPPING_MYSQLD} -eq 1 ]
do
${ECHO} -n "."
${SLEEP} 0.25
MYSQLD_HAS_BEEN_SHUTDOWN=`${TAIL} ${MYSQL_ERROR_LOG} | ${GREP} -c "Shutdown complete$"`
(( ATTEMPTS++ ))
if [ ${ATTEMPTS} -eq ${TICKS_TO_TRY} ] ; then STOPPING_MYSQLD=0 ; fi
if [ ${MYSQLD_HAS_BEEN_SHUTDOWN} -eq 1 ] ; then STOPPING_MYSQLD=2 ; fi
done
${ECHO}
if [ ${STOPPING_MYSQLD} -eq 2 ]
then
${ECHO} "Stopped ${PROGNAME}"
else
${TAIL} -30 ${MYSQL_ERROR_LOG}
fi
}
But what is ${MYSQLD_STOP} ?
MYSQL_CONN="-uroot -p<rootpassword> -P${MYSQLD_PORT} -h127.0.0.1 --protocol=tcp"
MYSQLD_STOP="${MYSQLADMIN} ${MYSQL_CONN} shutdown"
Please notice I use 127.0.0.1 and an explicit port. That way, I am not relying on a socket file.
I have always used mysqladmin --protocol=tcp shtudown as the proper alternative to shutdowns of mysql if service mysql stop hangs. Doing kill -9 on mysqld and mysqld_safe should the last of the last of the last resorts. (Yes, I said last three times).
Many times, mysqld has deleted mysql.sock without warning. Other people have had this issue as well over the years:
EPILOGUE
The secret is just as I stated: Connect to mysql using mysqladmin via TCP/IP (--protocol=tcp) and issue shutdown. This has to work because the shutdown privilege is in mysql.user for the exclusive purpose of authenticated shutdowns. This has saved my workday a few times when I was able to issue a remote shutdown from my Windows machine when shutting down mysqld on a Linux server.
UPDATE 2013-03-06 22:48 EST
If you are worried about what's going on during the shutdown, there is a way to manipulate the shutdown time and the way the data is flushed to disk, especially if you have lots of InnoDB data in the Buffer Pool
SUGGESTION #1
If you have a lot of dirty pages, you can lower the innodb_max_dirty_pages_pct to 0:
SET GLOBAL innodb_max_dirty_pages_pct = 0;
Set this about 15-30 minutes before shutdown. This will give mysqld the least possible amount of dirty pages to write to disk.
SUGGESTION #2
By default, innodb_fast_shutdown is 1. There are three values for this option
- 0 : InnoDB does a slow shutdown, a full purge and an insert buffer merge before shutting down.
- 1 : InnoDB skips these operations at shutdown, a process known as a fast shutdown.
- 2 : InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.
Documentation further says this :
The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.
Use innodb_fast_shutdown=2 in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption.
Defaults for innodb_max_dirty_pages_pct and innodb_fast_shutdown should be just fine in most cases.
