I would like to restart mysql gracefully just like httpd where threads are served before restart. I would dislike queries breaking.
Any "requested" shutdown sequence in MySQL (short of
kill -9) is somewhat graceful, since transactions in progress (on transactional tables) are rolled back, but here are a couple of ways to make a restart as clean as possible.
Note: if you are shutting down the server for an upgrade, then don't use this process; instead, follow the process detailed in this answer.
Otherwise, if you're just restarting an otherwise-healthy server so that you can change a read-only global variable or something similar, here is a graceful path:
innodb_fast_shutdown if it isn't already. This isn't directly related to the gracefulness of the shutdown, but it should bring your server back faster.
mysql> SHOW VARIABLES LIKE 'innodb_fast_shutdown'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | innodb_fast_shutdown | 0 | +----------------------+-------+ 1 row in set (0.00 sec) mysql> SET GLOBAL innodb_fast_shutdown = 1; Query OK, 0 rows affected (0.01 sec)
Next, instruct the server to close all open tables as soon as no currently-running queries are referencing them. This step also has nothing to do with the graceful shutdown, but it will make the subsequent step go faster:
mysql> FLUSH LOCAL TABLES; Query OK, 0 rows affected (41.12 sec)
FLUSH TABLES statement (with the optional
LOCAL keyword, which avoids an unnecessary but otherwise harmless flush of any slaves) will block and your prompt won't return until all of the tables can be closed. Once each table has been "flushed" (closed), if a query subsequently references the table, it will be automatically reopened, but that's okay. What we're accomplishing with this step is making less work for the final step:
mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (13.74 sec) mysql>
This statement flushes all tables (hence the advantage of getting some of that out of the way less disruptively with the prior step) and acquires a global (server-wide) read-only lock on them.
You can't have a global read lock until every currently running "write" query (i.e., pretty much everything but
SELECT) is done. Issuing the lock request will allow existing queries to finish but won't allow new ones to start.
Your prompt doesn't return until you hold this global lock, so every query that is in progress when you request the lock is able to finish, and you know they're finished, because you get the prompt back. Any subsequent queries that try to write anything to any table will just stall, changing no data, waiting indefinitely for the lock, until...
- you change your mind about the restart and release the lock manually (
- you restart the server, or
- you accidentally or intentionally disconnect the command line client from this thread (so don't do that). Keep this window connected and sitting at the mysql prompt:
Resist the temptation to close this.
This idle console prompt is what's holding the global lock for you. Lose this, lose the lock.
From another console window, restart MySQL the way you normally would, either with initscripts (e.g., your local variant of
service mysql.server restart) or with
mysqladmin shutdown followed by a manual restart.
innodb_fast_shutdown = 1really ensure that MySQL starts up faster? Looking at the docs, it seems that this improves shutdown speed (at the cost of startup speed?).– ChrisDec 17, 2018 at 15:43
@Chris the idea is that it helps ensure that the entire shutdown + startup is as quick as practical, but it's somewhat anecdotal since the amount of work being done is theoretically the same -- just moved to the other side of the sequence -- yet for some reason, it has always seemed generally faster overall, like perhaps there's some inefficiency in the way it happens at shutdown that isn't there at startup. Hard to say. Dec 18, 2018 at 0:08
In short, some of the best practices that should be considered before shutting down MySQL are:
- Confirm the instance you are going to shutdown to avoid stopping another instance by mistake.
- Stop the replication if you are going to shutdown a slave
mysql> STOP SLAVE;.
- Flush the dirty pages in advance to reduce the shutdown time
mysql> SET GLOBAL innodb_max_dirty_pages_pct = 0;.
- Check the long running queries
mysql> SHOW PROCESSLIST;, kill them
mysql> kill thread_id;or wait until they finish.
- Dump the buffer pool at shutdown
mysql> SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;and reload it at startup
# vi /etc/my.cnf innodb_buffer_pool_load_at_startup = ONto warmup the buffer pool.
Then after confirming the previous points, you can safely restart MySQL
shell$ service mysql restart
For more details, see my post Check these before shutting down MySQL!
Why was this downvoted? I don't know enough to recognize what command(s) are bad, but I want to know so I can avoid them.– JonFeb 27, 2017 at 23:44
I don't know any of the above is a bad command. I'm following the above steps and so many people do. Only step 4 should be with caution, you should not kill any query until you are sure of what you are doing, otherwise, wait until the long running queries finish. Give it a try and if it helped you, upvote it, if otherwise, downvote it!– MollFeb 28, 2017 at 21:17