Return to Answer

Added instructions to force restart mysql and turn off recovery mode once mysql has been fixed

edited Jun 15, 2017 at 18:12

If it times out, you can force restart mysql processes with:

# process id is first column with number, just ignore lines with grep because they list the process running 'grep mysql' ps aux | grep mysql sudo kill -9 <process-id> sudo /etc/init.d/mysql restart

If it works, the log will show something like:

I left all of the other files, and mysql was able to start anyway.

UPDATE: be sure to disable innodb_force_recovery = 1 once mysql is working again, otherwise you will get errors when you attempt to modify databases and tables.

If it works, the log will show something like:

I left all of the other files, and mysql was able to start anyway.

If it times out, you can force restart mysql processes with:

# process id is first column with number, just ignore lines with grep because they list the process running 'grep mysql' ps aux | grep mysql sudo kill -9 <process-id> sudo /etc/init.d/mysql restart

If it works, the log will show something like:

I left all of the other files, and mysql was able to start anyway.

UPDATE: be sure to disable innodb_force_recovery = 1 once mysql is working again, otherwise you will get errors when you attempt to modify databases and tables.

Source Link

answered Jun 15, 2017 at 2:04

Zack Morris

This happened to me in Laravel Homestead (Vagrant after a kernel panic running Mac OS Sierra 10.12.4 (16E195):

$ lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 14.04.3 LTS Release: 14.04 Codename: trusty $ mysql -V mysql Ver 14.14 Distrib 5.7.9, for Linux (x86_64) using EditLine wrapper

Here are some resources you can try, although none of the repair options worked for me:

https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html

https://forums.mysql.com/read.php?22,603093,604631#msg-604631

https://support.plesk.com/hc/en-us/articles/213939865-How-to-fix-InnoDB-corruption-cases-for-the-MySQL-database

I tried adding force recovery to mysql config (start at 1 and go progressively higher since supposedly higher numbers can cause permanent corruption):

sudo nano /etc/mysql/my.cnf

[mysqld] innodb_force_recovery = 1 #innodb-read-only=1 #innodb_purge_threads=0 #key_buffer_size=16M #event-scheduler=disabled

In another window, run:

tail -f /var/log/mysql/error.log

Then try restarting mysqld with the various options enabled:

sudo /etc/init.d/mysql restart

If it works, the log will show something like:

Version: '5.7.9' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)

If it fails, the log will show something like:

InnoDB: Assertion failure in thread 140049488692992 in file log0recv.cc line 1420

When worse came to worse, I tried removing databases likely to be corrupt:

sudo ls -alt /var/lib/mysql

It turned out that the database I had been working on was the most recently modified one at the top of the list. Luckily I had a SQL dump for it from that day so was able to remove it:

sudo rm -rf /var/lib/mysql/<database_name>

I left all of the other files, and mysql was able to start anyway.

Then I recreated the database with Sequel Pro, reimported my data and was able to move on without having to throw away all of the databases from my other projects.

Going forward, I must assume that any mysql database can get corrupted and try to keep daily backups and have the database recreation script documented or coded into my continuous integration tools.