How Do I Restore a MySQL Database from Time Machine Backup?

Question

I develop locally on a Mac, and my computer completely died yesterday. The hard drive is gone, apparently not recoverable.

I have a current backup on Time Machine.

So my question is:

How exactly do I restore that backup? Is there a physical location of the databases that I can restore? Where would that be?

I don't use MySQL from the command line much, but I am familiar with the console and such.

As I understand it, Time Machine full backups are only intended to go onto the machine that died, but I need the MySQL backups before I'll have time get tend to my other computer, so I am looking for a way to get the databases restored on my laptop when the original MySQL files are on an external hard drive plugged into my laptop.

Thanks!

Solution

I just had to handle this.

Finding the backup

In my case, mysql lives in /usr/local/mysql/ (where the last part is actually a symlink to the current installed version of mysql). It actually does correctly back up the files, and the whole structure exists on the backup.

But for some reason, even if you navigate to the correct local path in finder, if you run "Time Machine", it will show that no previous backup exists. Which thankfully is not true, unless you excluded it.

So, to recover your backup, you have to navigate to the respective path on your backup volume, which should be something like /Volumes/<yourbackupvolume>/Backups.backupdb/<host>/<revision>/Macintosh HD/usr/local/mysql/data/. It's easiest in the Terminal. Once you cd'd to the correct path you can use open . to open a finder window in that path and use drag & drop. However Finder will probably ask you to take ownership of the folder first, so you'd have to fix the permissions afterwards.

Finding a stable backup

If you have mysql running constantly, you may still have a problem. As far as I know InnoDB is fairly resilient, but it is possible that the file structure is corrupted. MySQL ships with mysqlcheck which supposedly can repair such tables..

I you don't, it's probably easier to find a backup where the modification date/time of all files in data/ is (reasonably, say a few minutes) lower than the date/time of the backup. In this case you can be reasonably sure to have a consistent backup and mysql should start smoothly from it.

Permissions

If for some reason your permissions do not work out after recovery, currently (OS X 10.10 / Yosemite) the permissions set by

sudo chown -R _mysql:admin /usr/local/mysql/data

worked great for me (MySQL runs as user _mysql). Note that mysqld will fail almost silently and will only try to create an error log file in /usr/local/mysql/data/, which may also fail due to permissions.

Comments

[chocripple]

if you do full backup with time machine, normally you just restore specific folder /usr/local/mysql/data/db_name. if you're sure your db mysql engine machine is MyISAM NOT INNODB, you just copy that folder and restore it back.

[CWSpear]

Why MyISAM and not INNODB? They are both Macs, with MySQL installed from Mac Ports.

[CWSpear]

So... what's the difference and how do I make sure I'm in MyISAM mode or whatever?

[CWSpear]

I tried sort of doing what Rikih suggested in the comments. I copied the whole mysql folder, data and all from the Time Machine backup, changed the permissions and it seems to be running and working... but I'm not sure I did it right. I have no idea what you mean by consistency tool. The said mysql folder was in /usr/local/

[Snellgrove]

DB's sometimes need to be made 'consistent' because Time Machine is not aware that the DB is in use (which it most likely was - is it a busy one, or just for dev/test?) The tool will likely tidy up any half-done transactions or similar. Google it - a good backup product will Quiesce a database while it is being backed up, i.e. the DB will stop writing for a while, or divert it's writes somewhere temporary and then compile them back in - it varies by product. Microsoft for example, use VSS.

[CWSpear]

Just for dev/test, so good chance it wasn't being used the last time it was backed up. It seems to be working on the few sites I've tested so far.