backup MySql databases and email them somewhere at a certain time

Question

We're running a CentOS server with a lot of MySql databases atm, what I need is a really easy way for us to back those up. Since many of them are under a couple of meg. Dumping, zipping them up then sending them to a secure Google Apps account sounds like a pretty good idea.

So what I need is: a script that will dump and zip the database, then email it somewhere, if it fails email somewhere else.

Answer 1

I use the following script to send a small dump to a dedicated mail account. This of course assumes you can send mails from your machine using the mail command.

#!/bin/bash
gzdate=`/bin/date +%Y-%m-%d_%H%M`;
gzfile=dump_${gzdate}.sql.gz
[email protected]
dumpuser=username
dbname=mydb
mysqldump --single-transaction --opt -u ${dumpuser} ${dbname} | gzip > ${gzfile}
if [ $? == 0 ]; then    
    ( echo "Database Backup from ${gzdate}:"; uuencode ${gzfile} ${gzfile} ) | mail -s "Database Backup ${gzdate}" ${mailrecpt};
else
    ( echo "Database Backup from ${gzdate} failed." ) | mail -s "FAILED: Database Backup ${gzdate}" ${mailrecpt};
fi

You just need to adapt the variables at the top.