Mysql – What should be done before upgrading MySQL

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.

mysql> FLUSH TABLES WITH READ LOCK;
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.

mysql>

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.