Restore local mysql db

In some cases, when your laptop goes to sleep while mysql is running it causes db corruption issues. You’ll notice this if you are running an app and you are getting dropped connections in the mysql log. (TODO log here)

In order to fix the problem you’ll need to rebuild your information schema and database setup. Don’t worry there is a way to do this without losing to much data. Below is the steps to take:

• Copy your existing data files
• Reinstall mysql (in some cases)
• Create a shell db
• Restore your data

Let’s start the fun. This may take a good hour or so depending on your frustration level. First off, let’s discuss a few basics.

Your data is stored in /usr/local/var/mysql/ with a subdirectory for each database you have created. There are ibdata* files that contain the information schema that the engine uses to find the databases and such. Under each directory there are *.ibd files and *.frm files. The *.ibd files are the ones that contain the data for a given table, where as the *.frm files contain the structure of the tables.

##Copy Existing data## Make sure mysql is stopped before proceeding:

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
# or if you aren't using homebrew
mysql.server stop

Next copy all your mysql data:

cp -rp /usr/local/var/mysql /tmp

##Start restoration process## This section is broken up into: * quick recovery - try this first, if it doesn’t work move on to the next steps * advanced recovery - more surgery, but it will help you get your data back

Quick recovery Let’s hope you are able to do this, if not we got ways to fix it.

Edit /usr/local/Cellar/mysql/5.6.15/my.cnf adding the following:

[mysqld]
innodb_force_recovery = 6
innodb_purge_threads = 0

Next, start mysql server

launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
# or if you aren't using load / homebrew
mysql.server start

Advanced recovery If you’ve delete the ibdata files you will probably have to go down this route. The intial steps are: * stop mysql * remove any files in /usr/local/var/mysql/ * reinstall mysql * start mysql server

To install mysql:

unset TMPDIR
mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp

##Create shell database##

If the db exists, ie. you were able to do the quick recovery above, then drop the database via mysql console:

drop database my_database;

Now both paths are converging by creating the new database.

CREATE DATABASE my_database;

If have a rails app just do:

rake db:reset

##Restore existing data##

In order to restore the data, you will have to stop mysql and copy the data back. The following is the commands:

# you may need to kill the process
mysql.server stop

# copy in the original db files
cp /tmp/mysql/my_database/* /usr/local/var/mysql/my_database

# start mysql
mysql.server start

At this point, if your data is showing up and there are no connection issues then ignore the rest of this article. In the cases where you have deleted your ibdata files you’ll probably need to proceed down this path. Or, if you are still having issues try the following to reset the db tables in the information schema:

mysql -u root -p my_database -e
 "SELECT table_name FROM information_schema.tables WHERE table_schema = 'my_database';" | awk
 'NR>1 {print "ALTER TABLE "$1" IMPORT TABLESPACE;"}'  | mysql -u root -p my_database

Alternatively you can run the following SQL to get a list of the tables:

SET @DATABASE_NAME = 'my_database';
SELECT  CONCAT('ALTER TABLE ', table_name, ' IMPORT TABLESPACE') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
ORDER BY table_name DESC;

More Issues?

Still having issues check out the problems below that might match your issues.

To checkout your tables that still may be having a problem, you can do:

CHECK TABLE my_table EXTENDED

If you are unsure which table is still corrupted run the following:

mysqlcheck -u root -p my_database

Both will return any issues with the table you might have. Once you figure out what the message is, the following may help to resolve your issue.

If you are getting back no data for your queries, there may be an issue with your indexes and the information schema references. In order to fix that you can do:

ALTER TABLE my_table ENGINE=INNODB;

This will update the ibdata files with the right references and allow the innodb engine to find your tables.

If you are getting back an issue with your primary key index, fix it by running:

REPAIR TABLE my_database.my_table

In some cases, you’ll have to run through the above process of importing namespace on down depending if you are up and running. If you are not up and running. Do the following and go back to the import step above:

mysql.server stop
cd /usr/local/var/mysql
rm ib*
mysql.server start

Note: The ibdata files are created on mysql restart.

Prevention

To prevent this tedious process in the future, backup your data every so often by doing the following:

mysqldump -u root -p my_database > dumpfilename.sql

Then you can restore it doing:

# remove and create empty db, then
mysql -u root my_database < dumpfilename.sql

External Resources

Here are a couple pointers to resources that might help you even more: * Why can’t I drop mysql database question on Stack Exchange * Mysql table doesn’t exist but it does or it should question on Stack Exchange * Mysql how to restore table stored in frm and an idb file question on Stack Exchange * Mysql how to restore table stored in frm and an idb file question on Stack Exchange * How to recover an innodb table whose files were moved around question on Stack Exchange * How to clean a mysql storage engine question on Stack Exchange * Recovering innodb table corruption question on Stack Exchange * Connecting orphaned ibd files post on mysqlperformance blog * Under certain circumstances an InnoDB table gets apparently corrupted after import bug report on mysql