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

Backup MySQL Databases Into Seperate Files

  • submit to reddit
        A cronable script for backing up all your databases as seperate files.  I'd suggest limiting the backup user's access to the IP of the computer backing up, and using some sort of encryption if you're on a capable version of mysql.

# MySQL backups from the Data

MOUNTED=`grep /etc/mtab -e \/mnt\/backup`
if [ "$MOUNTED" = '' ]; then
        echo "/mnt/backup is not mounted, there is no drive to backup to"
        exit 1

mkdir -p /mnt/backup/cluster_sql

for i in $(echo 'SHOW DATABASES;' | mysql -ubackup -pSUPERSECRET -hData|grep -v '^Database$'); do
  mysqldump -ubackup -pSUPERSECRET -hData --opt $i > /mnt/backup/cluster_sql/$i.sql;