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