Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?
share|improve this question
    
What table type(s) are you using? Some commands and operations only work for certain types. –  John Gardeniers Mar 3 '11 at 10:25
    
@John Gardeniers - it's in the question. I am using InnoDB. –  aviv Mar 3 '11 at 21:51
    
Sorry, I don't know how I overlooked that. –  John Gardeniers Mar 3 '11 at 23:12
add comment

2 Answers

  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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.