Entropy.ch Discussion Forums

[alpha2zee]

I use launchd to create gzipped daily backups of all my locally hosted MySQL databases. I would like to share this with others with this how-to.

1. Create a shell script with the following content. You can use TextEdit to create a plain text file (not rich text) and paste it. Save the file in any location as mysql_daily_backup.sh.

#!/bin/bash
cd "/Personal folders/Laboratory/Computer/mysql_backups/"
/usr/local/mysql/bin/mysqldump --user=abcd --password=efgh --opt --all-databases|gzip > $(date +%d).sql.gz

Set cd " " to the path you want for the backup files. Abcd and efgh are the MySQL account username and password that is to be used for accessing the databases. I have a MySQL account just for backup purposes. It has limited privileges (select, execute, lock table and show databases) but access to all the databases.

2. Then, in Terminal, go to that directory (type 'cd ', note the space, and drag the directory icon on to the Terminal window) and type 'chmod a+x mysql_daily_backup.sh' to make the file executable.

3. To set launchd to periodically run the backup script above, create a plain text file named com.mysql.dailybackup.plist inside ~/Library/LaunchAgents. Make folder LaunchAgents if it does not exist. Type in the following and save. You can change the '/Personal folders/Laboratory/Computer/mysql_backups' part as per your needs - this is the directory where 'mysql_daily_backup.sh' - the file for the shell script, above, is located. Once you have made the text-file and saved it, reopen it with Property list editor, check and save again.

<?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>com.mysql.backup</string>
<key>LowPriorityio</key>
<true/>
<key>Program</key>
<string>/Personal folders/Laboratory/Computer/mysql_backups/mysql_daily_backup.sh</string>
<key>ProgramArguments</key>
<string>/Personal folders/Laboratory/Computer/mysql_backups/mysql_daily_backup.sh</string>
<key>Rootdirectory</key>
<string>/Personal folders/Laboratory/Computer/mysql_backups/</string>
<key>RunAtLoad</key>
<true/>
<key>RunOnDemand</key>
<true/>
<key>ServiceDescription</key>
<string>Daily backup of all of local MySQL data</string>
<key>StartInterval</key>
<integer>86400</integer>
<key>WorkingDirectory</key>
<string>/Personal folders/Laboratory/Computer/mysql_backups/</string>
</dict>
</plist>

Then, either logout and log back in, or, in Terminal application, type -

launchctl load ~/Library/LaunchAgents/com.mysql.dailybackup.plist

A backup will be immediately performed, and from then on, every 86,400 seconds (every day), a MySQL backup will be created and compressed and saved as the 'day-date'.gz in the /Personal folders/Laboratory/Computer/mysql_backups folder. And the next month those files will be replaced. So after a month, at any time, you should have 31 backups.

* To unload the scheduled task (e.g., when trying to debug), in Terminal type -

launchctl unload ~/Library/Launchagents/com.mysql.dailybackup.plist

** The gzipping shaves 65-75% off the filesize. The .sql file is generated by double-clicking the 'day-date'.sql.gz file.

*** The mysqldump command options in the shell script can be changed as per your needs. See elsewhere for more. The option '--all' used here is the same as '--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset'.

[neptho]

Why use launchd over cron - other than it's entirely non-portable and Tiger specific?
_________________
Before you post, please use the Search function.

[alpha2zee]

Well, launchd may be new now, but it is going to stay in Mac OS, is open source and will find its way into other systems. Of course, it is much more than cron.

(launchd vs cron)