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
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:
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:
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
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...
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