How to do MySQL Maintenance especially after disk is full

Question

I have a quite large mysql database (InnoDB) running on ubuntu server virtual machine. Recently due to my fault the hard drive became full (on the physical machine) and the server got stuck and users could not work. I had to delete some files from the physical machine and hard restart the ubuntu server with the MySQL database.

1. Is there anything special I need to execute on the MySQL after such a crash?
2. Is there anything special I need to execute on the ubuntu server? (e.g. chkdsk or defragment)
3. Are there any "best-practice" commands needs to run on the MySQL database periodically?

Answer 1

1. No, but check logs for warnings and errors. You may need to issue a REPAIR in certain circumstances.
2. No, if you need to a file system check, it will prompt you to do one, or run one automatically. the Linux equivalent is an fsck (filesystem check). How this works completely depends on the filesystem you're using.
3. OPTIMIZE table can clean up unused space (i.e. if a lot of data was changed or deleted). Depending on your schema and data usage it may or may not be useful. Again, be careful when you run this, MySQL locks the table during it running.

Althuogh these are fine questions you're asking, the real root cause of this was a filesystem filling up. That is the problem you need to solve:

1. If the primary function of this machine is a database server, the MySQL database should be on it's own partition. If it's just a secondary function, at the very least make sure /var is off on it's own partition (or the base directory of whereever your MySQL data directory is). You should not be able to crash MySQL by adding files to /home or /

2. You should be alerted to filesystems that get far too close to being full. Nagios or hyperic can do this for you and a whole host of other sanity checks and warnings that can alert you to potential dangers.

Answer 2

For the file system, it should be handled automatically if you are using a journaling file system.

For Mysql, you can use optimize and repair table mysql commands, but you have to be careful as these commands may take a long time and they require table locking. You can look at mysql documentation for more info.