[shell]mysql backup脚本

脚本很烂,是一个数据全备的场景

1,同步rds到线上某台ecs

backup="/data/backup/mysql-data"
rsync="/data/backup/sqldir"
MYSQLDUMP="/usr/local/mysql/bin/mysqldump"
zip="/usr/bin/zip"
rm="/bin/rm"
time=`date +%Y-%m-%d_%H_%M`


$zip -q -r $backup/$time.sql.zip $rsync/* 
$rm -rf $rsync/* 


for i in `cat /root/shell/tables.txt`
#for i in `cat /root/shell/t.txt`
do
    $MYSQLDUMP --set-gtid-purged=off --skip-tz-utc -utest -p123456 -h fangka.mysql.rds.aliyuncs.com elc_db $i > "$rsync/$i.sql" 
done


find $backup -name "*.sql" -mtime +7 -type f |xargs $rm -rf

2,内网--通过rsync取回各库的sql文件--无差异同步

$ cat rsync.sh 
#!/bin/bash
##################tong bu xianshang shuju dao neiwang###############
host=200.2.2.2
src=/home/mysql/ #取回到本地本目录
mysql="/usr/bin/mysql"
des=sqldir


/usr/bin/rsync -vzrtopg --delete --progress --password-file=/etc/rsync.passwd  $user@$host::$des $src
echo "rsync is ok" > /var/log/rsync_and_sql.log

3,内网导入sql

$ cat source_mysql.sh 
#!/bin/bash
########xian wang shuju tongbu dao neiwang##############
data="/home/mysql"
mysql="/usr/bin/mysql"
rsync_log_file="/var/log/rsync_bi_increment.log"


for i in `cat /root/shell/tables.txt`
#for i in `cat /root/shell/t.txt`
do
     printf "%s - [INFO] - run_user[${USER}] - msg_or_rc[%s]\n" "${run_time}" "Table $i begin source." >> ${rsync_log_file}
     $mysql -uroot -p123456 testdb  < $data/$i.sql >> ${rsync_log_file} 2>&1
done


     $mysql -uroot -p123456 testdb < /root/shell/droptable.sql
     run_time="`date '+%Y-%m-%d %H:%M:%S'`"
     printf "%s - [INFO] - run_user[${USER}] - msg_or_rc[%s]\n" "${run_time}" "All import success." >> ${rsync_log_file}

#日志输出
- [INFO] - run_user[root] - msg_or_rc[Table finance begin.]
2017-05-10 14:27:52 - [INFO] - run_user[root] - msg_or_rc[All import success.]
2017-05-10 14:40:02 - [INFO] - run_user[root] - msg_or_rc[Rsync BI increment success.]