Howto/20120923 Repair corrupt MySQL database that won't start: Difference between revisions
From Interpause
Jump to navigationJump to search
No edit summary |
No edit summary |
||
Line 4: | Line 4: | ||
'''Steps''' | '''Steps''' | ||
* Always check log files first for suggestions, /var/log/mysql/ | |||
* Try to repair the database offline, in a shell run: | |||
<pre> | <pre> | ||
sudo myisamchk --force --update-state --key_buffer_size=64M \ | sudo myisamchk --force --update-state --key_buffer_size=64M \ | ||
Line 11: | Line 11: | ||
--write_buffer_size=1M /var/lig/mysql/*/*.MYI | --write_buffer_size=1M /var/lig/mysql/*/*.MYI | ||
</pre> | </pre> | ||
* Reboot and test that the database is online. | |||
* If database still offline, edit /etc/mysql/my.cnf, and add the following line to section [mysqld] | |||
<pre> | <pre> | ||
innodb_force_recovery = 6 # Should try 1 to 6 in increasing numbers | innodb_force_recovery = 6 # Should try 1 to 6 in increasing numbers | ||
</pre> | </pre> | ||
* Reboot and test that the database is online. Note: Database should be online but the innoDB is still corrupt and must be fixed! | |||
* Create a dump of the sql database | |||
<pre> | <pre> | ||
sudo mysqldump -u root -p--all-databases > ~/backup.sql | sudo mysqldump -u root -p--all-databases > ~/backup.sql | ||
</pre> | </pre> | ||
* Delete the ibdata and ib_logfile files | |||
<pre> | <pre> | ||
sudo rm /var/lib/mysql/ibdata1 | sudo rm /var/lib/mysql/ibdata1 | ||
sudo rm /var/lib/mysql/ib_logfile* | sudo rm /var/lib/mysql/ib_logfile* | ||
</pre> | </pre> | ||
* Reboot and test that the database is online. | |||
* Restore the dump | |||
<pre> | <pre> | ||
sudo mysql -u root -p < ~/backup.sql | sudo mysql -u root -p < ~/backup.sql | ||
</pre> | </pre> |
Revision as of 19:04, 23 September 2012
Sources:
- http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html
- http://dba.stackexchange.com/questions/23361/innodb-force-recovery-when-innodb-corruption
Steps
- Always check log files first for suggestions, /var/log/mysql/
- 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
- Reboot and test that the database is online.
- 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
- Reboot and test that the database is online. Note: Database should be online but the innoDB is still corrupt and must be fixed!
- Create a dump of the sql database
sudo mysqldump -u root -p--all-databases > ~/backup.sql
- Delete the ibdata and ib_logfile files
sudo rm /var/lib/mysql/ibdata1 sudo rm /var/lib/mysql/ib_logfile*
- Reboot and test that the database is online.
- Restore the dump
sudo mysql -u root -p < ~/backup.sql