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/
* Always check log files first for suggestions, /var/log/mysql/
# Try to repair the database offline, in a shell run:
* 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.
* 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]
* 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!
* 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
* 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
* 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.
* Reboot and test that the database is online.
# Restore the dump
* 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:

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