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