Is there a safer way to stop mysql(d)?

Solution 1:

503 the website to stop activity (.htaccess maintenance mode) and start top to monitor mysqld cpu usage

mysqladmin -p flush-tables

watch mysql activity (may take several minutes, depending on your installation) and when it tapers off

mysqladmin -p flush-tables

to just be sure and then

mysqladmin -p shutdown

This sequence ensures that your website ceases to create more database requests, tells mysql to flush data in memory and transaction log files to the database tables and then initiates a proper database daemon shutdown.

Solution 2:

Shutting down mysqld can be a time-consuming task. One of the biggest concerns is that all data is properly flushed and all tables closed.

There are two way to ensure proper closure of data. It falls into two basic categories:

CATEGORY #1: All the data is MyISAM

If absolutely all data is MyISAM, you should make sure InnoDB is totally disabled with this

[mysqld]
skip-innodb

Using this option on startup bypass any InnoDB protocols for crash recovery cycles that are normally executed on startup. Combine this with the fact that MyISAM data is never cached. Only the index pages are stored in the MyISAM Key Cache (sized by key_buffer_size). Thus, you have fast startup and fast shutdown.

CATEGORY #2: All or the majority of the data is InnoDB

If most or all of the data is InnoDB, you have to watch for the amount of space you have configured for innodb_buffer_pool_size. The bigger the innodb_buffer_pool_size, the longer it will take to flush out dirty pages.

Sidenote: Performing mysqldumps of InnoDB tables will automatically trigger the flushing of any lingering dirty pages in the Buffer Pool that belong to the table being dumped.

Whether you are mysqldumping all the InnoDB tables or just shutting down mysql, there is only one to hasten the process of flushing dirty pages.

You must set the innodb_max_dirty_pages_pct. By default, it is 90 for MySQL 5.0/5.1. For MySQL 5.5, the default is 75. Surprising, just set innodb_max_dirty_pages_pct to 0. That will keep the number of dirty pages in the Buffer Pool to a bare minimum. This may slight increase disk I/O but still within tolerable levels.

You must do two things:

Please place it in /etc/my.cnf

[mysqld]
innodb_max_dirty_pages_pct=0

Second, you will not have to restart mysql. Just execute this as the root user:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

Server load may spike a little and stay that way until the buffer pool is less than 1% dirty pages.

Give it a Try !!!

UPDATE 2013-03-04 21:31 EST

You should also run this MySQL command first

SET GLOBAL innodb_fast_shutdown = 0;

This will cause a complete flush of everything in the InnoDB Transaction Logs (ib_logfile0, ib_logfile1) followed by

service mysql stop