Backup MySQL Databases With SS and Launchd

[rwhite35]

Hello All,

Here's a brief backup process we developed to backup our localhost MySQL databases. It uses a shell script and launchd daemon to automate the backup. The process is straight forward and assumes you have basic knowledge of command line and server operation. Feel free to use it, modify or improve on it.

THE SCRIPT (path/to/your/shellScripts/dbase_backup.sh)

#! /bin/sh
# Author Ron White, 2011-08-09
# Run mysqldump command on MySQL databases
# Backup will be a single .SQL dump file
#
# Set variables for mysqldump command arguments
# dbase_pwd.txt is a one line text file with your database login pwd
PSOURCE='/Users/you/credentials/dbase_pwd.txt’
STORE='/volumes/sharepoint/locally/stored/backups/'
#set the date, use single back quote for date expression
DT=`date +%m_%d_%Y` 
#
# Set an array of the databases to backup
DBASE[1]='nameofdbase1';
DBASE[2]='nameofdbase2';
DBASE[3]='nameofdbase3';
#
# Run the mysqldump command
# This will backup the entire database, 
# using the default options (same as --opt)
# Note back quotes for the cat(concatinate)expression
mysqldump --user='MySQLadminName' --password=`cat $PSOURCE` --databases ${DBASE[@]} > ${STORE}_dbaseBK_$DT.sql
#
# Catch the mysqldump exit status and email a message to admin
OUTPUT=$?
MES1=”Databases were backed up using mysqldump, exit status was: ”
MES2=”Bummer, the database backup failed with exit status: ”
if [ $OUTPUT -eq 0 ]
  then
   echo $MES1 $OUTPUT | mail –s “OTM DBase Success” admin@example.com
  else
   echo $MES2 $OUTPUT | mail –s “OTM DBASE Failed” admin@example.com
   exit 1
fi
#

EXECUTING SCRIPTS AUTOMATICALLY
MacOSX has deprecated cron jobs and prefers to use launchd and launchctl as the best method for running automated jobs.

launchctl is the command-line or interactive utility for running jobs. To set a job, you create a .plist file or preferences list. The .plist is an XML file. Wikipedia has a fairly concise description of the properties list launchctl understands.

Initially I used XCode to generate the .plist and then terminal VIM(vi) to further edit the properties as required. Any text editor will work.

The following is a list/descriptions of the properties I used in the .plist setup.
<Key>Label: contains a <string> file name, minus the .plist extension

<key>ProgramArguments: these are arguments to pass to the launchd. In this case, the argument is an <array> that includes a <string> path to the shell script.

<key>StartInterval: set the time (in seconds) between job execution. In this case I’ve set the <integer> interval for 1 day or 86,400 seconds. The launchctl runs the script at about 12:19a each day.

PLIST (Library/LaunchAgents/dbase_backup.plist)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
        <key>Label</key>
        <string>dbase_backup</string>
        <key>ProgramArguments</key>
  <array>   <string>/Users/you/shell_scripts/dbase_backup.sh</string>
        </array>
        <key>StartInterval</key>
        <integer>86400</integer>
</dict>
</plist>

Notes: The XML MUST BE well formed or the .plist will not load in to the launch daemon memory.

INSTALLATION & SETUP
Once the .plist file is setup, run the following commands as the root user
1.) move the .plist to Library/LaunchAgents directory if it wasn’t already there

mv name_of_file.plist /Library/LaunchAgents/

2.) change owner on the .plist file

chown root:admin name_of_file.plist

* Note, when I changed the read/write permissions on the .plist, and tried to load the .plist in to memory, I got an error saying the process was dropped cuz the file was an executable. The file permissions should be –rw-r—r—(or 644)
3.) change the owner of the script

chown root:admin /path/to/file/scriptname.sh

chmod 777 /path/to/file/scriptname.sh

**Note, this script should be executable
4.) load the .plist in to the launchd memory

launchctl load –w name_of_file.plist

5.) You can see the .plist managed by launchctl by running

launchctl list

If everything loaded properly, the new .plist will display in the list

Enjoy

 

[polly1]

thanx for post!!