I need to perform an upgrade of MySQL on my web server. There could be many users connected to the server at any time, performing queries or updates on the database, or uploading files to the server. Besides putting up an advanced notification that the system will undergo maintenance during a certain period of time, what should I do to avoid the scenario of stopping MySQL service or the server while some user operations are still being carried out, which may lead to a corruption of the file system or the database?
migrated from webmasters.stackexchange.com Jan 16 '13 at 10:30
This question came from our site for pro webmasters.
If you stop the MySQL Server process gracefully, the filesystem and databases won't be corrupted.
Here are a couple of things I typically do:
According to the documentation, this is only needed when doing a major version upgrade, but I maintain that it's still a good idea -- this step increases your shutdown time but leaves your instance in a more stable state for the restart.
Next, you can lock all of the tables to prevent clients from doing anything. This step, also, isn't technically necessary, but I like it because it gives me a sense of knowing what's going on -- and what isn't -- and when -- and it shortens the next part of the shutdown, since there won't be any client activity to wait for.
Note that on a busy server, that statement won't return so quickly. It won't return until the server has successfully closed all open tables and acquired a lock on every table in the entire database -- so, if you have queries executing when you issue this statement, they will be allowed to finish, then the locks acquired, and then is when you'll get your prompt back.
Important: leave this connection open. Just let it sit there and move to a new window, because the connection being open is what holds the global read lock. If you close or otherwise lose this connection, the read lock is released.
If you want to look at any variables, the processlist, etc., you can still use this connection if you want to. Just don't disconnect from the server.
At this point (since you got your prompt back), your tables are locked from further changes and your application tier may start to behave badly because a backlog might be growing. That's to be expected, since any queries against the server will just wait indefinitely for that read lock. The number of client connections may be increasing, but they won't be able to do anything.
In another window, tail your MySQL error log.
In yet another window, shutdown MySQL Server:
You will most likely need to add --username --password and possibly other options to this command. You won't see much from this window, so go back and watch your error log:
The server says, "we're going to do a graceful shutdown." (Not in so many words, but that's what this next log entry means...)
The server shuts down any system threads. In this case, I left the event scheduler and even replication running to illustrate that a graceful shutdown is indeed graceful. (Yes, my server started right back up, including replication, right after I captured this output.) In practice, it might be better to stop replication manually with
InnoDB does its cleanup (including the "slow shutdown")...
The server shuts down...
The mysqld_safe wrapper notices that the server has stopped running. This process is charge of trying to restart the server if it crashes, but if the server has removed its own pid file, no restart is attempted, because the shutdown is assumed to be graceful.
You're now gracefully shut down.
This is all pretty, but a little on the paranoid side. Depending on what kind of server you're running, you can use ionice to make sure the machine is not doing anything heavy, the rest is taken care of by mysql itself. Let's say some of my mysql server's scripts suffer from memory leakage, I'll find out the time of day the server is at lowest usage, then do a graceful restart of mysqld when CPU is idle, so I use crontab and do this: