#!/bin/bash

#备份目录
BACKUP_ROOT=/opt/lampp/htdocs/www
BACKUP_FILEDIR=$BACKUP_ROOT/mysql
command_linebin="/opt/lampp/bin/mysql"
username="username"
password="password"
ip="www.sysiw.net"
#当前日期
DATE=$(date +%Y%m%d)

######备份######

#查询所有数据库
#-uroot -p123456表示使用root账号执行命令，且root账号的密码为:123456
DATABASES=$(/opt/lampp/bin/mysql -uusername -ppassword -e "show databases" | grep -Ev "Database|sys|information_schema|mysql|performance_schema|phpmyadmin|test|man|webftp")
#循环数据库进行备份
for db in $DATABASES
do
echo
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz BEGIN----------
#/opt/lampp/bin/mysqldump -uusername -ppassword--default-character-set=utf8 -q --lock-all-tables --flush-logs -E -R --triggers -B ${db}  > $BACKUP_FILEDIR/${db}_$DATE.sql

### 远程连接到数据库 并且创建数据库####
#select_sql="CREATE DATABASE ${db}  DEFAULT CHARACTER SET utf8"
#${command_linebin} -h${ip} -u$username -p$password -s mysql -e "${select_sql}"

###远程连接到数据库 恢复刚备份的数据库###
#${command_linebin} -h${ip} -P3306 -u$username -p$password ${db} < $BACKUP_FILEDIR/${db}_$DATE.sql

select_sql="drop database ${db}  "
${command_linebin} -h${ip} -u$username -p$password -s mysql -e "${select_sql}"

echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------
echo
done

echo "done"