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.

Image of MySQL's Data folder in the Finder

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

Image of file listing in OS X's Terminal applicationThe 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. 

Image of restored files in Finder windowGo 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.


Digital Ocean

Meta

Tags: mysql,databases,time machine,osx,apple,backup

Originally published on 2009-05-08 17:38:24 by Rick Curran

Last edited on 2009-05-08 17:38:24 by Rick Curran

Permalink: http://suburbia.org.uk/blog/2009/05/08/173824.html

Visitor Comments:

Left by JimmyH on #

Excellent information! But, unfortunately, it did not work for me. From my experience (echoing also a previous post), OSX Time Machine (OSX10.10) does not properly track changes in mySQL databases. It is then a more prudent choice to generate explicit database backups.

Left by Rick Curran on #

Thanks @JimmyH, yep, this is quite an old article now which worked for me at the time, I've no idea if these steps will work with more recent versions of OSX / MacOS.

Left by EJ on 2009-05-09 08:25:38 #

FYI for MAMP users - just restore the /Applications/MAMP/db/mysql/ folder!

Left by VM on 2009-07-24 08:43:22 #

In step 5, don't you have the source/destination backwards? You are copying from data-bak (corrupted data) to data (restored data), whereas, I thought the point of all this was to copy from your restored data to your data-bak, after which point you change data-bak back to data.

Left by furball on 2009-08-24 16:43:27 #

All I had to do was restore the files and rerun the installer. More info here

Left by furball on 2009-08-24 16:46:48 #

Ok the link worked in the preview but it is not there now. Here it is:
http://dmmcintyre2.com/computers/web/restoring-mysql-from-time-machine-backups/

Left by tehkemo on 2009-12-07 15:10:56 #

thanks for the guide, but it's not actual already, seems MYSQL changed it's data folder structure - All I needed to restore my TM backup database was I firstly shut down mysql server and I select whole mysql folder - with all settings and replate with the current one..worked for me..

Left by Al on 2010-12-10 09:29:49 #

I was alarmed this morning when I dropped a database table by accident. I was thrilled to find this guide but gutted to find the latest backup Time Machine had of the MySQL data was SIX MONTH out of date?! WTF?!

I work on this database daily. Really peeved Time Machine has let me down first time I need to use it. Has anyone else experienced this?

BTW I'm a MAMP user and I am 100% sure the MySQL folder location is correct.

Left by Rick Curran on 2010-12-10 11:34:47 #

@Al: That's strange, I have seen weird behaviour with Time Machine and MySQL when it has been installed via the OSX installers from MySQL, but MAMP should get backed up without any problem as it stored as part of the MAMP Application.

Left by Brent Wilson on 2011-01-09 23:04:11 #

Just a warning: This isn't a guaranteed way to backup MySql. If your database is large and sees frequent writes, it's quite likely that changes will be made to the database while it is being backed up, which can cause some serious issues for MySql. I tried using this process once, but any backup I tried to restore to was always corrupted and caused MySql to crash immediately because of the problem above.

Left by Watts Martin on 2011-09-08 18:39:08 #

Another warning, albeit with a fix... the procedure described above only works if your database is entirely MyISAM tables, I think. InnoDB tables are not actually stored in the directory named after your database. I followed this procedure to revert a rather stupid change I made that deleted imported data, and lo and behold after restarting with the old database... I still had the new database.

The solution is simply to skip steps 5-7, and just restart the server after you've restored the "data" folder from Time Machine. After you verify that it's working, then you can delete the data-bak/ folder that you've made.

Left by Victor on 2011-12-29 14:50:06 #

Just to note that sometimes the those in the directories are just table structure, but the rows of the table is stored in 'data/ibdata1'.

Left by Charles Huff on 2012-06-28 05:53:46 #

I tried and failed. backup was on Snow Leopard. Restore on Lion server. also mysql changed 5.5.14 to 5.5.25... any help GREATLY appreciated...

Left by Rick Curran on 2012-07-04 14:34:46 #

@Charles: Sorry, I don't know what else to suggest I'm afraid. I haven't had any experience with Lion Server so I can't say if that has any impact on how to restore it.

Did you get access to the data itself though?

Left by Naveen on 2012-07-18 03:41:47 #

I reinstalled Mac OS and forgot to take a backup of mysql. I luckily had backed up quite often. I had to get the data back since I haven't taken backup of database itself.

Here's what I did. I installed mysql (latest). Removed all the contents and replaced with the contents of mysql folder from TM Backup.

Viola.. I got what I need...

Left by Some guy on the net on 2013-04-09 22:42:24 #

Thanks for the great tutorial! You do a great job covering all situations (like different installs of mysql) and giving extended terminal commands.

I really appreciated your work... clear, complete, easy to follow when I needed it the most: in a panic over lost data!

Kudos and extra bits for you.

Left by Rick Curran on 2013-04-10 13:03:51 #

@Some guy on the net: Thanks, glad it was helpful!

Left by Donald on 2014-04-03 19:20:19 #

echo the thanks!!

Left by Sabine on 2014-10-22 21:42:47 #

Thanks for the very helpful article, it has helped me after every OSX update in the last few years. Unfortunately it doesn't work anymore for the update to Yosemite (OSX 10.10). The databases get imported in the new MySQL installation but they are all empty, not sure why.

Leave a comment...

Comment Preview:




Digital Ocean