Eschew Obfuscation

MySQL Recovery

Posted by Max Dunn Mon, 05 Mar 2007 16:57:00 GMT

We had a bad crash yesterday on our MySQL 5.0.22 database. Our guess is that we ran out of hard disk space during a large transaction and the database somehow got corrupted. Worse, restarting MySQL didn’t clear the error, but continued to have the problem. Luckily, we could still read from the database so we were able to do a current backup which allowed us to recreate the database and re-import the data. Here are the steps we used:

1. In my.conf use:
   innodb_force_recovery = 6
   (or lower) to bring the database up.
   (See http://www.mysql.org/doc/refman/5.1/en/forcing-recovery.html)
2. Dump the tables. For example. to dump the ‘maxwiki’ table use:
   mysqldump - mysqldump --database maxwiki -u (username) -p (password) > maxwiki.sql
   Repeat for all other tables
3. Shut down the database
4. Move all files out of /usr/bin/mysql and put elsewhere
5. Re-init with:
   

     mysqld_safe --user=mysql
     mysql_install_db --user=mysql
     mysqladmin -u (username) password '(password)'
    

6. Reload all the tables from the sql files
7. Reset the users