#!/bin/bash
#auto backup mysql script by toey@tc20.net
#first setup
#add this to /usr/dbbackuptool/autobackup_mysql.sh
#chmod 700 /usr/dbbackuptool/autobackup_mysql.sh
#mkdir -p /usr/dbbackuptool/{ptmp,inc}
#addmysql username passwd ip to /usr/dbbackuptool/inc/inc_database
#global var
dname1=$(date +%Y-%m-%d)
backup_path=’/backup/mysqldb’
prg_backup_path=’/usr/dbbackuptool’
prg_backup_tmp_file="$prg_backup_path/ptmp/all_backup_database_name"
prg_backup_inc_file="$prg_backup_path/inc/inc_database"
################################################# all function #####################################################
#func dump database and compress files
mysql_dump()
{
echo “--------------------start backup from host $3 database $4--------------------------”
echo “now backup $4 to $backup_path/$3/$dname1/$4.sql.gz”
/usr/bin/mysqldump -u $1 -p$2 -h $3 -c -f --insert-ignore $4 | gzip > $backup_path/$3/$dname1/$4.sql.gz
echo “backup $4 to $backup_path/$3/$dname1/$4.sql.gz $(du -sh $backup_path/$3/$dname1/$4.sql.gz | awk ‘{print $1}’) complete”
echo “--------------------end backup from host $3 database $4----------------------------”
}
#func get database name
mysql_get_dbname()
{
/bin/rm -f $prg_backup_tmp_file
/bin/echo “show databases” | /usr/bin/mysql -u $1 -p$2 -h $3 | grep -v Database > $prg_backup_tmp_file
}
#func clear old backup db
clear_old_backup_db()
{
if [ -d “$backup_path/$1/” ]; then
/usr/bin/find “$backup_path/$1/” -mindepth 1 -maxdepth 1 -type d -mtime +30 -exec rm -rf {} ;
fi
}
#func check null
check_null()
{
if [ -z $1 ] ; then
echo ‘VALUE is null’
exit
fi
}
################################################ start shell script #################################################
if [ ! -f $prg_backup_inc_file ]; then
exit 0
fi
if [ ! -d “$backup_path/logs/$dname1” ]; then
mkdir -p “$backup_path/logs/$dname1”
fi
#read username password host from file
while read f_username f_password f_dbhostname
do
#check null data
check_null $f_username
check_null $f_password
check_null $f_dbhostname
#check full path backup directory
if [ ! -d “$backup_path/$f_dbhostname/$dname1” ]; then
/bin/mkdir -p “$backup_path/$f_dbhostname/$dname1”
fi
#get database name
mysql_get_dbname $f_username $f_password $f_dbhostname
#backup database
while read loopdbname
do
if [[ $loopdbname == “information_schema” ]]; then
continue
fi
mysql_dump $f_username $f_password $f_dbhostname $loopdbname
done < $prg_backup_tmp_file &> “$backup_path/$f_dbhostname/$dname1/auto_mysql_backup.log”
#run claer old backup db
clear_old_backup_db $f_dbhostname
#send report to email
/usr/bin/mail -s “auto mysql daily backup form host $f_dbhostname date $dname1” "to@mydomain.com" – -f from@mydomain.com < “$backup_path/$f_dbhostname/$dname1/auto_mysql_backup.log”
done < $prg_backup_inc_file