Ramblings of Narc

By request, here’s the script I worked up to make periodic database dumps into a directory and gzip them up:

#!/bin/zsh

mysql_user=root
mysql_pass=your-root-password-here
bk_path='/where/to/put/the/dumps'

right_now=`date +"%Y%m%d-h%H"`

bk_fname="${bk_path}/full-db-dump.sql"
bk_gzname="${bk_path}/full-db-dump-${right_now}.sql.gz"

mysqldump -u"$mysql_user" -p"$mysql_pass" --all-databases > "${bk_fname}"
gzip -c "${bk_fname}" > "${bk_gzname}"

Running this as a cron job every [x] hours should be pretty good for small sites, especially if the archive directory is periodically rsynced to another remote host (as in my case).

For serious stuff, you may consider adding MySQL replication for continuous backup.

Oh, and since not everybody uses zsh, you can probably change the hash-bang to point to /bin/sh safely. I haven’t tried it myself, though.