Setting cron to email MySQL database backups using mutt

mysqldump --skip-lock-tables --host localhost -u Database_username -pDatabase_password Database_name | gzip > /home/user/etc/backup/`date +\%A`.sql.gz && mutt -s "`date +\%d_\%B_\%Y`'s backup" -a /home/user/etc/backup/`date +\%A`.sql.gz user@mail.com < /dev/null

The above command will dump the database to the server directory /home/user/etc/backup/ after compressing it. The name of the backup file will be the day of the week. eg: Sunday.sql.gz, Monday.sql.gz, ..., Saturday.sql.gz. This backup file will be emailed to user@mail.com as an attachment with a subject line, for example like 11_November_2010's backup.

This command should be set in cron to run once every day. Using the week day for naming the backup file ensures that only the latest 7 backup files will be present in the backup folder on server. Sending the backups to a gmail address will place them in the spam folder. Since each email in the spam folder is deleted after 30 days, any backup older than 30 days will be automatically removed.

So at any time, we will have the latest 7 days backup on the server and the latest 30 days backup in the email.

--all-databases can be used in place of Database_name to dump all the databases.

Specifying table(s) after Database_name will dump only the specified table(s), instead of the whole database.