How to restore a MySQL database from your Time Machine backup

I’m writing this as much to remind myself as for anyone else really :) I generally run databases running on my MacBook which I use for sites in active development, I then transfer these databases over to a staging server which are then use by other people in the team and sometimes clients to input site content. Last week I imported content from the staging server and overwrote my local database thinking that the staging server was the most up to date, but as it turned out I was incorrect!

Fortunately I’m running Mac OS X’s Time Machine backup on my MacBook so I didn’t see a problem as I could just restore it from that. However, in reality it wasn’t quite as straight forward to do, so here’s what I had to do in the end to restore this database from my Time Machine backup.

Step 1: Find your local databases

Navigate to the location of your MySQL databases. For my MySQL install this is found at /usr/local/mysql but depending on how you’ve installed MySQL it might be elsewhere.

As you’ll probably see that’s not a location you can just regularly view in the Finder on Mac so you’ll need to use the ‘Go to Folder…’ option within the ‘Go’ menu in the Finder.

Once you’ve got to that location you’ll see the MySQL data folder which shows up with a red no-access symbol on it so you can’t actually navigate straight into it via the finder. Seeing as we can’t access the folder to restore a specific database we’ll just restore the whole folder from the Time Machine backup instead.

Step 2: Shutdown your MySQL server

It’s important that you shut down your MySQL server process before you continue. How you do this depends on how you’ve got MySQL installed. If you installed MySQL from the official installer for OS X downloaded from the MySQL website then you may have the a System Preference which you can use to stop it.

If you’ve installed MySQL another way such as self-compiled or via Mac Ports or you just want to use the Terminal then you can stop it from the Terminal instead. The following command can be used:

sudo mysqladmin shutdown

If you don’t have MySQL configured in your path in Terminal you may need to use the full path to the mysqladmin command:

sudo /usr/local/mysql/bin/mysqladmin shutdown

The path to those files may vary depending on how you’ve installed MySQL so if it doesn’t work then you’ll need to check the correct location. Note that I’ve also used ‘sudo’ in that command in case you need root privileges to stop the MySQL process. Check the MySQL documentation for your version of MySQL for further help.

Step 3: Rename your MySQL data folder via the Terminal

The next step is to rename your MySQL data folder temporarily to avoid any clashes when restoring the data folder from the Time Machine backup. First you need to get into your MySQL directory:

cd /usr/local/mysql

You can then list all the files to check you’re in the right place:

ls -al

You should now see all the files including the ‘data’ directory where the databases are located. We can now use the following command to rename the data directory:

sudo mv data data-bak

Once that’s done you should see your directory happily renamed in both the Terminal and the Finder window.

Step 4: Enter Time Machine to restore your MySQL data directory

The next step is to enter Time Machine mode and restore the data directory from the backup. I’m sure you probably know how to enter Time Machine mode but make sure you enter whilst focused on the MySQL Finder window.

Go back in time a sufficient amount so that you’ll definitely be getting the version of the data that you want, highlight the data folder and click Restore. Because we renamed the data directory Time Machine will simply restore the data directory without any prompts. You should now see both the restored data directory and the renamed data-bak directory.

Step 5: Copy the database from the restored data directory

You can now copy the database from the restored data directory, this is done via the following Terminal command:

sudo cp -R data-bak/my_database_name data/my_database_name

The database is actually a directory containing other files so you need to use the ‘-R’ flag to recursively copy all of these across with the directory itself.

Step 6: Delete the restored data directory and rename the data-bak directory

Now that the database is restored and copied you can safely delete the restored data directory as it’s no longer needed at this point. Note: Be very careful using the ‘rm’ command! Bad things can happen if used incorrectly!

sudo rm -R data

You can then rename the data-bak directory back to it’s original name ‘data’, note that the ‘mv’ command doesn’t require the ‘-R’ flag:

sudo mv data-bak data

Step 7: Check file permissions / ownership

You may need to check the file permissions and ownership of the database that you have just restored to make sure that it will be accessible by MySQL when it is running. These may vary depending on your MySQL install method but they should be something like this:

drwx—— 60 _mysql wheel 2040 8 May 12:14 my_database_name

Basically the directory is owned by the user ‘_mysql’ and in the group ‘wheel’. If you need to change the owner then use the following command:

sudo chown -R _mysql data/my_database_name

Step 8: Start up MySQL

Once this has all been done, you can then start MySQL again using either the System Preference if you have it or via the Terminal command:

sudo mysqld_safe &

or with the full path:

sudo /usr/local/mysql/bin/mysqld_safe &

Step 9: There is no Step 9!

Hopefully you should now be able to access your newly restored database. As I said at the beginning, this is the method I used to restore a database myself recently, there may be other ways to do it but hopefully these steps might be of use to anyone else in the same situation.