How to properly kill MySQL?

I have CentOS 64bit with CPanel installed and I use:

service mysql stop

It just keeps ticking periods and never seems like it stops. In the logs it just posts a lot of:


130303 17:42:38 [Warning] /usr/sbin/mysqld: Forcing close of thread

In the err.log file, I see lots of these:


[Warning] /usr/sbin/mysqld: Forcing close of thread

It used to be instant. Any idea why it does that and how to fix?

Right now I have to do killall -9 mysql but is there a better way?

The server is also very very active.

Is this a config issue? Do I have memroy settings too high?

[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
skip-networking
max_connections = 500
max_user_connections = 20
key_buffer = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 64M
read_buffer_size = 12M
sort_buffer_size = 12M
read_rnd_buffer_size = 12M
table_cache = 2048
thread_cache_size = 16K
wait_timeout = 30
connect_timeout = 15
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
low_priority_updates=1
concurrent_insert=ALWAYS
log-error=/var/log/mysql/error.log
tmpdir=/home/mysqltmp
myisam_repair_threads=4
[mysqld_safe]
open_files_limit = 8192
log-error=/var/log/mysql/error.log

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

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 root@127.0.0.1 to root@localhost if TCP/IP is not explicitly enabled. By default, mysqld will choose the least path of resistance and connect root@127.0.0.1 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 -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:

• Problem With Shutdown

• Original Post : http://forums.freebsd.org/showthread.php?t=28924

• Solution using mysqladmin shutdown : http://forums.freebsd.org/showpost.php?s=8d095ca69da3daf2ad4b157c8ad95f1f&p=161653&postcount=10

• http://forums.cpanel.net/f354/cant-connect-local-mysql-server-through-socket-var-lib-mysql-mysql-sock-111-a-78444.html

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 fa

StackExchange Database Administrators Q#36102, answer score: 41