Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from webmasters.stackexchange.com Jan 16 '13 at 10:30

This question came from our site for pro webmasters.

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
This answer is fantastic. Thanks! –  Question Overflow Jan 20 '13 at 3:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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