2011-03-13 21:42

Here are some lines I’m using to backup my MySQL databases on my Debian server:

#!/bin/sh
# This will dump all your databases

DATE=$(date +%Y%m%d%H%M)

for DB in $(echo "show databases" | mysql --defaults-file=/etc/mysql/debian.cnf -N)
do
        mysqldump --defaults-file=/etc/mysql/debian.cnf $DB > /backup/mysql/${DB}_${DATE}.sql

        gzip /backup/mysql/${DB}_${DATE}.sql
done

# purge old dumps
find /backup/mysql/ -name "*.sql*" -mtime +8 -exec rm -vf {} \;

You can run it in a cron:

11 1 * * * /usr/local/bin/mysqldump.sh > /tmp/mysqldump.log

This way any error displayed by the script will be sent by mail to the root user (mail address in /etc/aliases).

If you are not under Debian and there is no password file in /etc/mysql, you should create such file.

2011-03-13 21:42 · Tags: , ,