Howto/20120923 Repair corrupt MySQL database that won't start: Difference between revisions

From Interpause
Jump to navigationJump to search
(Created page with "Sources: http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html http://dba.stackexchange.com/questions/23361/innodb-force-recovery-when-innodb-corruption # Always check log f...")
 
No edit summary
Line 1: Line 1:
Sources:  
Sources:  
http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html
* http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html
http://dba.stackexchange.com/questions/23361/innodb-force-recovery-when-innodb-corruption
* http://dba.stackexchange.com/questions/23361/innodb-force-recovery-when-innodb-corruption


# Always check log files first for suggestions, /var/log/mysql/
# Always check log files first for suggestions, /var/log/mysql/

Revision as of 19:02, 23 September 2012

Sources:

  1. Always check log files first for suggestions, /var/log/mysql/
  2. Try to repair the database offline, in a shell run:
sudo myisamchk --force --update-state --key_buffer_size=64M \
--sort_buffer_size=64M --read_buffer_size=1M \
--write_buffer_size=1M /var/lig/mysql/*/*.MYI
  1. Reboot and test that the database is online.
  2. If database still offline, edit /etc/mysql/my.cnf, and add the following line to section [mysqld]
innodb_force_recovery = 6 # Should try 1 to 6 in increasing numbers
  1. Reboot and test that the database is online. Note: Database should be online but the innoDB is still corrupt and must be fixed!
  2. Create a dump of the sql database
sudo mysqldump -u root -p--all-databases > ~/backup.sql
  1. Delete the ibdata and ib_logfile files
sudo rm /var/lib/mysql/ibdata1
sudo rm /var/lib/mysql/ib_logfile*
  1. Reboot and test that the database is online.
  2. Restore the dump
sudo mysql -u root -p < ~/backup.sql