#!/bin/bash

set -eu
User=root
Password=YOUR PASSWORD
Backup_Path=/root/backup/
Date_dir=sql_backup_$(date +%F)
Remote_Addr=YOUR HOST
Remote_Path=/data/filerun/user-files/hanjy-files/backup/sql/

if [ ! -d  ${Backup_Path}${Date_dir} ];then
       	mkdir -p  ${Backup_Path}${Date_dir}
fi

command -v gzip >/dev/null||yum install -y gzip >/dev/null

mysql_login(){
       	mysql -u${User} -p${Password} "$*" 2>/dev/null
}

Sock=`mysql_login "-e \s" |awk -F"[\t]+" '/^UNIX socket:/{print $2}'`

for db in  $(mysql_login   "-e show databases") ;do	
	if [[ "$db" =~ (^sys|^Database$|^information_schema|^performance_schema) ]]; then
	      	continue
        fi
        mysqldump -u$User -p$Password -S $Sock -B $db --single-transaction --source-data=2 2>/dev/null |gzip > ${Backup_Path}${Date_dir}/sql_${db}_$(date +%F:%X ).sql.gz
	if [ $? -eq 0 ];then
		scp -r ${Backup_Path}${Date_dir} ${Remote_Addr}:${Remote_Path} > /dev/null
		if [ $? -eq 0 ];then
			find ${Backup_Path} -type  d  -mtime +7  -name "sql_backup_*"|xargs rm -f 
		fi
	fi
done
