Backup all databases nightly w/ mysqldump
This is something very useful and everybody should be doing this. Because you can’t trusted the hosting provider backup. So you should always make your own backup and here is the tools will help you make your own mysql backup with ease.
#!/bin/bash DB_BACKUP="/backups/mysql_backup/`date +%Y-%m-%d`" DB_USER="root" DB_PASSWD="secretttt" HN=`hostname | awk -F. '{print $1}'` # Create the backup directory mkdir -p $DB_BACKUP # Remove backups older than 10 days find /backups/mysql_backup/ -maxdepth 1 -type d -mtime +10 -exec rm -rf {} \; # Option 1: Backup each database on the system using a root username and password for db in $(mysql --user=$DB_USER --password=$DB_PASSWD -e 'show databases' -s --skip-column-names|grep -vi information_schema); do mysqldump --user=$DB_USER --password=$DB_PASSWD --opt $db | gzip > "$DB_BACKUP/mysqldump-$HN-$db-$(date +%Y-%m-%d).gz"; done # Option 2: If you aren't using a root password then comment out option 1 and use this # for db in $(mysql -e 'show databases' -s --skip-column-names|grep -vi information_schema); # do mysqldump --opt $db | gzip > "$DB_BACKUP/mysqldump-$HN-$db-$(date +%Y-%m-%d).gz"; # done
If you use this, throw this text into something like /usr/local/bin/mysql_backup.sh and since it has mysql’s root password in it, make sure that you chmod 700 to it so no one else can read it. Then just call it from cron like:
30 3 * * * /usr/local/bin/mysql_backup.sh
BTW, a simpler way to grab all of them is to use the –all-databases flag in the mysqldump command.. but it doesn’t make nice separate files for you..
Cr: http://www.linux.org/threads/backup-all-databases-nightly-w-mysqldump.692/
Ref: https://github.com/jwhite530/Random/tree/master/Backup_Scripts