MySQL backup script for nightly dumps that clean up after a certain number of days and keep a symlink to the latest one so you can easily setup a webserver to provide dumps for development purposes

msql_backup_all.sh

#!/bin/bash
 
DB_BACKUP="/var/backups/databases/"
LATEST_DIR="/var/backups/databases/latest/"

DB_USER="root"
DB_PASSWD="secretttt"
HN=`hostname | awk -F. '{print $1}'`
 
# Remove backups older than 10 days
find /var/backups/databases/ -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/$db-$(date +%Y-%m-%d).gz";
  ln -fs $DB_BACKUP/$db-$(date +%Y-%m-%d).gz $LATEST_DIR/$db-nightly.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
 
# Make it so only root can read the backup files
# chmod -R 600 $DB_BACKUP