Recovering MySQL after improper shutdown

As an amateur DBA I recently made a classic amateur DBA mistake: I restarted my computer without first shutting down MySQL. (The restart in this case was the result of updating macOS to 10.12.4.) After the reboot I couldn’t work out why XAMPP wasn’t launching MySQL again – apache started up fine – so it was time to dig into the log files.

A quick check of var/mysql/hostnameredacted.local.err in the XAMPP installation directory revealed a string of errors and a stacktrace. Reading it carefully I realized the key error message was this:

InnoDB: Operating system error number 2 in a file operation

The other clue was a few lines earlier in the logfile:

InnoDB: Database was not shutdown normally!

As ever, Google to the rescue which led me to a handy blog post which suggested adding the following line to the [mysqld] section of etc/my.cnf:

innodb_force_recovery = 1

I saved the file, and held my breath as I attempted to restart MySQL. Success!

I went back and removed that line again (actually just commented it out – there’s an outside chance I’ll repeat this error at some point in the future!) and re-restarted MySQL. Firing up SequelPro and I was relieved to be able to see all my tables as expected, and ran a few test queries to check it out. Everything looks OK. Phew!

Lesson learned: remember to shutdown MySQL properly before a reboot.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s