#!/bin/bash
date=`date +%F_%H%M%S`
mysqlUser=bkpewrweT
mysqlPWD='Rwer$&'
encryptPWD="Alwerwer8"
dir_base=/opt/backup/mysql
echo "---Start to fully backup--"
innobackupex --defaults-file=/etc/my.cnf --include="common.*|beta.*|mysql.*" --user=$mysqlUser --password=$mysqlPWD 2>>$dir_base/backup_full.log --stream=tar ./ | gzip - | openssl des3 -salt -k "$encryptPWD" > $dir_base/$date.tar.gz.des3
echo "---Complete to fully backup---"
echo "---Start upload to OSS--"
java -jar /opt/backup/mysql/oss_upload-1.0.0.jar $dir_base/$date.tar.gz.des3
echo "---Complete to upload to OSS--"
备份增量数据:backup_incr.sh
#!/bin/bash
cur_date=`date +%F_%H`
echo "---$cur_date, start to incremental backup---"
last_date=`date -d '-1 day' +%F`
mysqlUser=bksdfpSsdfecureT
mysqlPWD='Rdsfsdf&'
dir_bak_base=/opt/backup/mysql
dir_incr_base=$dir_bak_base/incr_backup
if [ ! -d "$dir_incr_base" ]; then
mkdir -p $dir_incr_base
fi
dir_full_bak=$dir_incr_base/full_backup
dir_incr_curdate=$dir_incr_base/$cur_date
if [ ! -d "$dir_full_bak" ]; then
innobackupex --defaults-file=/etc/my.cnf --include="common.*|beta.*|mysql.*" --user=$mysqlUser --password=$mysqlPWD --no-timestamp $dir_full_bak 2>>/opt/backup/mysql/backup_incr.log
echo "---Complete full backup OK---"
fi
#前一天有备份,则说明非第一次增量备份
if [ -d "$dir_incr_base/$last_date" ];then
innobackupex --incremental $dir_incr_curdate --no-timestamp --incremental-basedir=$dir_incr_base/$last_date --user=$mysqlUser --password=$mysqlPWD --defaults-file=/etc/my.cnf 2>>/opt/backup/mysql/backup_incr.log
else
#第一次增量备份,base是指向全量备份的目录
innobackupex --incremental $dir_incr_curdate --no-timestamp --incremental-basedir=$dir_full_bak --user=$mysqlUser --password=$mysqlPWD --defaults-file=/etc/my.cnf 2>>/opt/backup/mysql/backup_incr.log
fi
echo "---Complete incremental backup---"
恢复:restore_full.sh
#!/bin/bash
echo "---start to decrypt and unpack tar---"
dir_full_bak=/opt/backup/mysql/full_backup
decrypt_unpak(){
openssl des3 -d -in "$1" -out tmp.tar.gz
tar -izxf tmp.tar.gz -C $dir_full_bak
rm -f tmp.tar.gz
}
if [ -z $1 ]; then
echo "请提供要解密的tar.gz.des3文件"
exit;
fi
decrypt_unpak $1
echo "---Start to restore fully---"
innobackupex --defaults-file=/etc/my.cnf --apply-log $dir_full_bak
echo "---Prepare backup done--"
innobackupex --copy-back --defaults-file=/etc/my.cnf $dir_full_bak
增量恢复:restore_incr.sh
#!/bin/bash
mysqlUser=bkpSecureT
mysqlPWD='Rest061$&'
dir_base=/opt/backup/mysql/incr_backup/
dir_full_bak=$dir_base/full_backup
echo "---Start to restore incrementally---"
#对每个备份目录进行--apply-log --read-only操作
#对目录按时间升序排序,先应用全备份,再应用每个增量备份
apply_log_folder(){
#进入备份目录
cd $dir_base
for folder in `ls -rt`
do
if [ x"$folder" != x"." -a x"$file2" != x".." ];then
if [ -d "$folder" ] && [ "full_backup" == "$folder" ];then
echo "$folder, full backup folder"
innobackupex --apply-log --redo-only $dir_full_bak --use-memory=1G --user=$mysqlUser --password=$mysqlPWD 2>>$dir_base/restore_incr.log
elif [ -d "$folder" ]; then
echo "$folder, incremental backup folder"
innobackupex --apply-log --redo-only $dir_full_bak --incremental-dir=$folder --use-memory=1G --user=$mysqlUser --password=$mysqlPWD \
2>>$dir_base/restore_incr.log
fi
fi
done
}
apply_log_folder
echo "apply log to backup folder done, and start to copy back files"
innobackupex --copy-back --defaults-file=/etc/my.cnf --user=$mysqlUser $dir_full_bak 2>>$dir_base/restore_incr.log
echo "---Completed OK---"
数据库恢复步骤:
1、创建备份文件解压后存储目录,如:/opt/backup/mysql/full_backup
2、修改restore_full.sh 文件中的dir_full_bak=第1步创建的目录
3、用root用户修改/etc/my.cnf 中的datadir为恢复数据库后存储的数据文件,如:datadir=/opt/backup/backupData
4、执行restore_full.sh ,如: ./restore_full.sh btDatabase_2016-01-21_040001.tar.gz.des3
5、执行第4步后需输入btDatabase_2016-01-21_040001.tar.gz.des3的密码,输入完密码后,自动将btDatabase_2016-01-21_040001.tar.gz.des3解压到/opt/backup/mysql/full_backup,并将数据文件恢复到/opt/backup/backupData目录
6、启动mysql数据库
7、因btDatabase生产数据库配置了IP访问限制,如需通过别的机进行连接,需用btDatabase生产数据库的root用户密码登录后修改mysql schema的user表的host信息,如修改root用户的访问机为所有机,可采用
mysql>update user set host = '%' where user ='root';
mysql>flush privileges;
8、可通过别的客户端通过root用户密码进行连接刚恢复的btDatabase数据库。
9、查看数据库表及其内容进行验证恢复是否正常。