3

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!

Improve this question
4
  • 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.
    – chocripple
    Apr 3, 2013 at 5:36
  • Why MyISAM and not INNODB? They are both Macs, with MySQL installed from Mac Ports.
    – CWSpear
    Apr 3, 2013 at 5:55
  • You can't backup and restore Mysql with Innodb engine just with standard file system copy like time machine. You must using mysql dump or 3rd party hot backup. if your db not much transaction when time machine backup, maybe you can give it try to recover all /usr/local/mysql/data folder and see how.
    – chocripple
    Apr 3, 2013 at 6:01
  • So... what's the difference and how do I make sure I'm in MyISAM mode or whatever?
    – CWSpear
    Apr 3, 2013 at 6:03

2 Answers 2

Reset to default
4

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.

Improve this answer
0

Time Machine is not a SQL backup, what it will have done is backed up the actual files that make up your SQL database.

I don't know how Macs handle backing up open files (I'm assuming you run MySQL as some kind of service, or daemon)

What you'll probably need to do is use the time machine GUI to bring back the database files (simply browse to the version you want and hit go I believe; Time Machine will make sure it brings back the original + incremental changes)

Then you'll probably need to run some consistency tools to make sure your DB is consistent. MySQL should have some of these built in, but I'm no expert. Does MySQL have transaction logs? if so, use Time Machine to pull these back to the exact same point in time.

Improve this answer
3
  • 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/
    – CWSpear
    Apr 3, 2013 at 15:40
  • 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.
    – Snellgrove
    Apr 3, 2013 at 17:01
  • 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.
    – CWSpear
    Apr 4, 2013 at 0:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .