Never been to TextSnippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world (or not, you can keep them private!)

Daily MySQL backups on Textdrive, rotated weekly (See related posts)

This cron job will create a compressed backup of all the mysql databases under your account. The backup will be stored as "\daily-backup\Mon.gz" - and so forth, one for each day of the week. In this way you will have rotating backups going back seven days.

First, create the "daily-backup" folder under your home directory.

Go into the System - Cron Jobs section in webmin and paste this in as a new cron job (all one line)

/usr/local/bin/mysqldump --skip-opt -uUSERNAME -pPASSWORD --quote-names --complete-insert --extended-insert --quick --compact --lock-tables=false --skip-add-locks --all-databases | gzip > /home/USERNAME/daily-backup/sql-alldb-`date "+%a"`.gz


Make sure to replace the USERNAME and PASSWORD with your own info.

You can set it up to run on any kind of daily schedule; I have it set to run daily at an early-morning time that I picked randomly.
to mysql cron textdrive backups by joeldueck on Thu Apr 20 21:42:49 +0000 2006

Comments on this post

baumanj posts on May 22, 2006 at 05:09
Here's my little variation on your idea. I decided to keep backups specific to the rails app that the DB is for rather than all databases. Then, I store this backup file in subversion, essentially making it a differential backup so I can roll back to any point in time (with granularity of however often I run the cron job). To this end, I removed the --extended-insert option so that each row would be on its own line (easier for svn to do nice diffs).

/usr/local/bin/mysqldump --skip-opt -uUSER -pPASSWORD --quote-names --complete-insert --quick --lock-tables=false --skip-add-locks baumanj_www_production -r ~/RAILSAPP/db/backup.sql; /usr/local/bin/svn ci  ~/RAILSAPP/db/backup.sql -m ""

You need to create an account or log in to post comments to this site.


Related Posts