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?


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:

Verify that innodb_fast_shutdown is disabled. If it isn't, disable it.

mysql> show variables like 'innodb_fast_shutdown';
| Variable_name        | Value |
| innodb_fast_shutdown | 0     |
1 row in set (0.00 sec)

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.

Query OK, 0 rows affected (0.00 sec)

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.

shell> tail -f /usr/local/mysql/data/hostname.err 

In yet another window, shutdown MySQL Server:

shell> mysqladmin shutdown

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

130119 13:29:17 [Note] /usr/local/mysql-5.5.28-solaris10-x86_64/bin/mysqld: Normal shutdown

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 STOP SLAVE; before you flush the tables and acquire the read lock. The Event Scheduler is an optional feature, which you may or may not be using. Here is example output:

130119 13:29:17 [Note] Event Scheduler: Killing the scheduler thread, thread id 1
130119 13:29:17 [Note] Event Scheduler: Waiting for the scheduler thread to reply
130119 13:29:17 [Note] Slave I/O thread killed while reading event
130119 13:29:17 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001539', position 849647640
130119 13:29:17 [Note] Event Scheduler: Stopped
130119 13:29:17 [Note] Event Scheduler: Purging the queue. 3 events
130119 13:29:17 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.001539' at position 849626716

InnoDB does its cleanup (including the "slow shutdown")...

130119 13:29:19  InnoDB: Starting shutdown...
130119 13:29:20  InnoDB: Waiting for 6 pages to be flushed
130119 13:29:26  InnoDB: Shutdown completed; log sequence number 2718873505306

The server shuts down...

130119 13:29:26 [Note] /usr/local/mysql-5.5.28-solaris10-x86_64/bin/mysqld: Shutdown complete

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.

130119 13:29:27 mysqld_safe mysqld from pid file /usr/local/mysql-5.5.28-solaris10-x86_64/data/psi2.pid ended

You're now gracefully shut down.

  • @Michael, By now wouldn't someone have came up with a 1 click solution to this problem?
    – Pacerier
    Jan 12 '15 at 11:49
  • @Pacerier you don't technically have to do most of these steps. The FLUSH operations are something I like to do because it tends to speed things up. My own adminstration philosophy is that I don't want to "1-click" anything. The more I understand what's really going on, the better equipped I feel like I can be to notice something out of the ordinary and handle the unexpected. Jan 12 '15 at 12:17
  • @Michael-sqlbot, Why not one-click to a cmd script that does all those things for you and show you the exact output? In that case, you would still understand what's really going on.
    – Pacerier
    Jan 16 '15 at 6:33

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:

# clear and clean start MySQL and httpd daily
38 5 * * * root ionice -c3 /etc/init.d/mysqld restart > /dev/null 2>&1
39 5 * * * root /usr/sbin/apachectl graceful > /dev/null 2>&1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.