DZone 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

Snippets has posted 5883 posts at DZone. View Full User Profile

Simple MySQL Backup Script For Cron

06.20.2007
| 41426 views |
  • submit to reddit
        Simple mySQL backup script for cron - backs up all databases, saves the last 4 copies.
#!/bin/bash

# modify the following to suit your environment
export DB_BACKUP="/backup/mysql_backup"
export DB_USER="root"
export DB_PASSWD="********"

# title and version
echo ""
echo "mySQL_backup"
echo "----------------------"
echo "* Rotating backups..."
rm -rf $DB_BACKUP/04
mv $DB_BACKUP/03 $DB_BACKUP/04
mv $DB_BACKUP/02 $DB_BACKUP/03
mv $DB_BACKUP/01 $DB_BACKUP/02
mkdir $DB_BACKUP/01 

echo "* Creating new backup..."
mysqldump --user=$DB_USER --password=$DB_PASSWD --all-databases | bzip2 > $DB_BACKUP/01/mysql-`date +%Y-%m-%d`.bz2
echo "----------------------"
echo "Done"
exit 0
    

Comments

Clayton Cafiero replied on Tue, 2010/01/19 - 9:43pm

For security reasons, it's not such good idea to export username and password! DB_BACKUP="/backup/mysql_backup" DB_USER="root" DB_PASSWD="********" should suffice.

Snippets Manager replied on Mon, 2010/02/01 - 6:34pm

Maybe the best solution to secure the user and password is to create a file called ".my.cnf" in the user's home. The file's contents should look like this : [client] user=username pass=password Then, in the backup script delete the user and pass parameteres, because mysqldump (and all mysql command) looks for a .my.cnf file in the user's home and takes the login information from there. I hope you will find this useful.