#!/bin/bash
MYSQLBIN=/opt/mysql/bin/mysql
MYSQLDUMP=/opt/mysql/bin/mysqldump

MYSQLUSER=dbbackup
MYSQLPASSWORD=ugagCm#l4tf@@@sdf$$xre9xl

MYSQLBACKNM="192.168.1.8_220"

LOCALPATH=/home/backup/mysql_backup

if [ ! -d $LOCALPATH ];then
        mkdir -p $LOCALPATH
fi

LOCALTM=$(date +%Y-%m-%d-%Hh-%Mm-%Ss)

NOBACKUP="information_schema|Database|test|performance_schema"

DATANAME_LIST=$(echo 'show databases'|$MYSQLBIN -u$MYSQLUSER -p$MYSQLPASSWORD 2>/dev/null|grep -v -E $NOBACKUP )

cd $LOCALPATH

for dbname in $DATANAME_LIST
do

        $MYSQLDUMP   --master-data=2 --opt $dbname > ${dbname}_${LOCALTM}.sql
       
done

tar jcf  ${MYSQLBACKNM}_${LOCALTM}.tar.bz2  *${LOCALTM}.sql
rm *${LOCALTM}.sql

# remote server
##/home/dbbackup/mysql_bakckup_220
# 备份放在两台服务器 218 221服务器

serverip="192.168.1.9 192.168.1.6"
username="dbbackup"
port="12381"
backupdir="/home/dbbackup/mysql_bakckup_1-59-37-220"

for i in $serverip
do
	/usr/bin/scp  -P 12381 -q ${MYSQLBACKNM}_${LOCALTM}.tar.bz2  ${username}@${i}:$backupdir
done

if [[ $? -eq 0 ]]; then
	echo "$(date +%Y-%m-%d-%Hh-%Mm-%Ss__Databases-Backup-Ok)" >> ${LOCALPATH}/mysql_backup.log
else
	echo "$(date +%Y-%m-%d-%Hh-%Mm-%Ss__Databases-Backup-Error)" >> ${LOCALPATH}/mysql_backup.log
fi

/usr/bin/find $LOCALPATH -type f -name "*.tar" -mtime +10 -exec rm {} \;