Use OS X launchd to do automatic daily backups of local DB. This will keep files for 30 days, but is customizable. Backup script is a script my boss wrote (credit where credit is due). OS X deprecated cron.

README.md

Auto Backup Local DB

• if nothing in logfile specified grep through the /var/log/system.log file for your launchd name

This example uses following paths that would need to be changed.

/Users/covard/bsh_scripts
/Users/covard/bsh_scripts/logs

Bash Script

Also need to set the bash script to have execute perm for launchd to run it.

$ chmod +x /Users/covard/bsh_scripts/auto_mysql_backup.sh

launchctl

To load or unload the launchd file to be run use the following commands (this has to be done anytime the file is changed)

$ launchctl load ~/Library/LaunchAgents/mysql.backup.plist
$ launchctl load ~/Library/LaunchAgents/mysql.backup.plist

To check the status of the agent:

• first column = PID (if currently running)
• second column = status (0 is good, if anything else look at log file)

$ launchctl list | grep mysql.backup
=> -	0	mysql.backup

auto_mysql_backup.sh

#!/bin/bash
#CRON:
  # example cron for daily db backup @ 9:15 am
  # min  hr mday month wday command
  # 15   9  *    *     *    /Users/covard/bsh_scripts/auto_mysql_backup.sh

#RESTORE FROM BACKUP
  #$ gunzip < [backupfile.sql.gz] | mysql -u root -padmin [dbname]

#==============================================================================
# CUSTOM SETTINGS
#==============================================================================

# directory to put the backup files
BACKUP_DIR=/Users/covard/db_backup

# MYSQL Parameters
MYSQL_UNAME=[username]
MYSQL_PWORD=[password]

# Don't backup databases with these names
# Example: starts with mysql (^mysql) or ends with _schema (_schema$)
IGNORE_DB="(^mysql|_schema$|_test[0-9]?$)"

# include mysql and mysqldump binaries for cron bash user - mysql installed with homebrew (location)
PATH=$PATH:/usr/local/bin/mysql

# Number of days to keep backups
KEEP_BACKUPS_FOR=30 #days

#==============================================================================
# METHODS
#==============================================================================

# YYYY-MM-DD
TIMESTAMP=$(date +%F)
echo $TIMESTAMP

function delete_old_backups()
{
  echo "Deleting $BACKUP_DIR/*.sql.gz older than $KEEP_BACKUPS_FOR days"
  find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +$KEEP_BACKUPS_FOR -exec rm {} \;
}

function mysql_login() {
  local mysql_login="-u $MYSQL_UNAME"
  if [ -n "$MYSQL_PWORD" ]; then
    local mysql_login+=" -p$MYSQL_PWORD"
  fi
  echo $mysql_login
}

function database_list() {
  local show_databases_sql="SHOW DATABASES WHERE \`Database\` NOT REGEXP '$IGNORE_DB'"
  echo $(/usr/local/bin/mysql $(mysql_login) -e "$show_databases_sql"|awk -F " " '{if (NR!=1) print $1}')
}

function echo_status(){
  printf '\r';
  printf ' %0.s' {0..100}
  printf '\r';
  printf "$1"'\r'
}

function backup_database(){
    backup_file="$BACKUP_DIR/$TIMESTAMP.$database.sql.gz"
    output+="$database => $backup_file\n"
    echo_status "...backing up $count of $total databases: $database"
    $(/usr/local/bin/mysqldump $(mysql_login) $database | gzip -9 > $backup_file)
}

function backup_databases(){
  local databases=$(database_list)
  local total=$(echo $databases | wc -w | xargs)
  local output=""
  local count=1
  for database in $databases; do
    backup_database
    local count=$((count+1))
  done
  echo -ne $output | column -t
}

function hr(){
  printf '=%.0s' {1..100}
  printf "\n"
}

#==============================================================================
# RUN SCRIPT
#==============================================================================
delete_old_backups
hr
backup_databases
hr
printf "All backed up!\n\n"
echo $(osascript -e 'display notification "Finished" with title "DB Backup"')

mysql.backup.plist

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
  <dict>
      <key>Label</key>
      <string>mysql.backup</string>
      <key>Program</key>
        <string>/Users/covard/bsh_scripts/auto_mysql_backup.sh</string>
      <key>StartCalendarInterval</key>
      <dict>
          <key>Hour</key>
          <integer>9</integer>
          <key>Minute</key>
          <integer>00</integer>
      </dict>
      <key>StandardOutPath</key>
      <string>/Users/covard/bsh_scripts/log/mysql_backup.log</string>
      <key>StandardErrorPath</key>
      <string>/Users/covard/bsh_scripts/log/mysql_backup.log</string>
  </dict>
</plist>