Best practices for backup checking

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.

Best practices depends on what do you need to recover. You definitely should backup to different storage, ideally in different geolocation. I have this little script, which detects all databases and backup each database to separated file, so I can recover only one database.

#!/bin/bash
echo "Starting..."
ROOTDIR="/backup/mysql/es2"
YEAR=`date +%Y`
MONTH=`date +%m`
DAY=`date +%d`
HOUR=`date +%H`
SERVER="mysql.local"
BLACKLIST="information_schema performance_schema"
if [ ! -d "$ROOTDIR/$YEAR/$MONTH/$DAY/$HOUR" ]; then
    mkdir -p "$ROOTDIR/$YEAR/$MONTH/$DAY/$HOUR"
fi
echo "running dump"
dblist=`mysql -u backuper -pXXXXXXXXXXX -h $SERVER -e "show databases" | sed -n '2,$ p'`
for db in $dblist; do
    echo "Backuping $db"
    isBl=`echo $BLACKLIST |grep $db`
    if [ $? == 1 ]; then
        mysqldump --single-transaction -u backuper -pXXXXXXXXXX -h $SERVER $db | gzip --best > "$ROOTDIR/$YEAR/$MONTH/$DAY/$HOUR/$db.sql.gz"
        echo "Backup $db ends with return code $?"
    else
        echo "Database $db is on blacklist, skip"
    fi
done

echo "dump completed"

Each backup is keeped in directory structure YEAR/MONTH/DAY/HOUR/DB_NAME.sql.gz. You can specify which DB you don't want to backup in variable BLACKLIST.