Here I have given command to take backup of all database in one file
If you want to take backup all database in separate file with automatic retention and email alert you can use below shell script.
# mysqldump -u root -p --all-databases > all_dbs.sql
If you want to take backup all database in separate file with automatic retention and email alert you can use below shell script.
#!/bin/bash
#==============================================================================
#TITLE: mysql_backup.sh
#DESCRIPTION: script for automating the daily mysql backups
#USAGE: ./mysql_backup.sh
#CRON:
# example cron for daily db backup @ 9:15 am
# min hr mday month wday command
# 15 9 * * * /Users/[your user name]/scripts/mysql_backup.sh
#RESTORE FROM BACKUP
#$ gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]
#==============================================================================
# CUSTOM SETTINGS
#==============================================================================
# directory to put the backup files
BACKUP_DIR=/backup/mysqlbkp
# MYSQL Parameters
MYSQL_UNAME=backup
MYSQL_PWORD=B@ckUp@1@3
# Email Parameters
MAIL="mail1.example.com", "mail2.example.com"
MAILER="$(which mail)"
STATUSFILE="/tmp/statusfile.txt"
# Don't backup databases with these names
# Example: starts with mysql (^mysql) or ends with _schema (_schema$)
IGNORE_DB="(^mysql|_schema$)"
# include mysql and mysqldump binaries for cron bash user
PATH=$PATH:/usr/local/mysql/bin
# Number of days to keep backups
KEEP_BACKUPS_FOR=2 #days
#==============================================================================
# METHODS
#==============================================================================
# YYYY-MM-DD
TIMESTAMP=$(date +%F)
function delete_old_backups()
{
echo "Deleting $BACKUP_DIR/*.sql.gz older than $KEEP_BACKUPS_FOR days"
find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +$KEEP_BACKUPS_FOR -exec rm {} \;
}
function mysql_login() {
local mysql_login="-u $MYSQL_UNAME"
if [ -n "$MYSQL_PWORD" ]; then
local mysql_login+=" -p$MYSQL_PWORD"
fi
echo $mysql_login
}
function database_list() {
local show_databases_sql="SHOW DATABASES WHERE \`Database\` NOT REGEXP '$IGNORE_DB'"
echo $(mysql $(mysql_login) -e "$show_databases_sql"|awk -F " " '{if (NR!=1) print $1}')
}
function echo_status(){
printf '\r';
printf ' %0.s' {0..100}
printf '\r';
printf "$1"'\r'
}
function backup_database(){
backup_file="$BACKUP_DIR/$TIMESTAMP.$database.sql.gz" > $STATUSFILE
output+="$database => $backup_file\n"
echo_status "...backing up $count of $total databases: $database" >> $STATUSFILE
$(mysqldump $(mysql_login) $database | gzip -9 > $backup_file)
}
function backup_databases(){
local databases=$(database_list)
local total=$(echo $databases | wc -w | xargs)
local output=""
local count=1
for database in $databases; do
backup_database
local count=$((count+1))
done
echo -ne $output | column -t >> $STATUSFILE
}
function hr(){
printf '=%.0s' {1..100}
printf "\n"
}
#==============================================================================
#Run Rsync After Backup
#To sync first configure password less ssh from source to destination server
#==============================================================================
echo_status "Start rsync to destination server" >> $STATUSFILE
rsync /backup/mysqlbkp/* -ave "ssh -c arcfour -p 2255" --recursive --delete-during root@10.0.0.1:/home/mysql_Backup_10SRV >> $STATUSFILE
echo_status "complete rsync to destination server" >> $STATUSFILE
#==============================================================================
# RUN SCRIPT
#==============================================================================
delete_old_backups
hr
backup_databases
hr
printf "All backed up!\n\n" >> $STATUSFILE
$MAILER -s "MySQL Database Backup report for 10.10.10.1 $NOW" -- $MAIL < $STATUSFILE
rm $STATUSFILE
