#!/bin/bash
# full&&increment backup and recover
# 说明:事先要确保存在/data/bak目录,且要保证在执行增量备份时已做过至少一次全量备份,否则找不到position文件。
port='3306'back_src_dir="/data/mysql/${port}/logs/binlog"back_dir='/data/bak'DATE=`date +%Y%m%d`
user='root'pass='cy2009'bak_db='test1'mysql_bin='/usr/local/mysql-5.1.48/bin'socket="/data/mysql/${port}/mysql.sock"full_bak()
{
cd ${back_dir}
DumpFile=Full_back$DATE.sql
${mysql_bin}/mysqldump --lock-all-tables --flush-logs --master-data=2 -u${user} -p${pass} ${bak_db} >${DumpFile}
${mysql_bin}/mysql -u${user} -p${pass} --socket=${socket} -e "unlock tables"#把当前的binlog和position信息存入position文件cat ${DumpFile} |grep 'MASTER_LOG_FILE'|awk -F"'" '{print $2}' > ${back_dir}/positioncat ${DumpFile} |grep 'MASTER_LOG_FILE'|awk -F"=" '{print $3}' |awk -F";" '{print $1}' >> ${back_dir}/position
}
incre_bak()
{
#锁定表,刷新log
${mysql_bin}/mysql -u${user} -p${pass} --socket=${socket} -e "flush tables with read lock"${mysql_bin}/mysqladmin -u${user} -p${pass} --socket=${socket} flush-logs
#获取上次备份完成时的binlog和position
cd ${back_dir}
start_binlog=`sed -n '1p'position`
start_pos=`sed -n '2p'position`
#获取目前的binlog和position
mysql-u${user} -p${pass} --socket=${socket} -e "show master status\G" | awk '{print $2}'| sed -n '2,3p' >now_position
stop_binlog=`sed -n '1p'now_position`
stop_pos=`sed -n '2p'now_position`
#如果在同一个binlog中if [ "${start_binlog}" == "${stop_binlog}" ]; then${mysql_bin}/mysqlbinlog --start-position=${start_pos} --stop-position=${stop_pos} ${back_src_dir}/${start_binlog} >>Incr_back$DATE.sql
#跨binlog备份elsestartline=`awk "/${start_binlog}/{print NR}" ${back_src_dir}/mysql-bin.index`
stopline=`wc -l ${back_src_dir}/mysql-bin.index |awk '{print $1}'`for i in `seq${startline} ${stopline}`dobinlog=`sed -n "$i"p ${back_src_dir}/mysql-bin.index |sed 's/.*\///g'`case "${binlog}" in
"${start_binlog}")
${mysql_bin}/mysqlbinlog --start-position=${start_pos} ${back_src_dir}/${binlog} >>Incr_back$DATE.sql
;;"${stop_binlog}")
${mysql_bin}/mysqlbinlog --stop-position=${stop_pos} ${back_src_dir}/${binlog} >>Incr_back$DATE.sql
;;*)
${mysql_bin}/mysqlbinlog ${back_src_dir}/${binlog} >>Incr_back$DATE.sql
;;esac
done
fi#解除表锁定,并保存目前的binlog和position信息到position文件。
${mysql_bin}/mysql -u${user} -p${pass} --socket=${socket} -e "unlock tables"
cpnow_position position
}
full_recov()
{
cd ${back_dir}
recov_file1=`ls | grep 'Full_back'`
${mysql_bin}/mysql -u${user} -p${pass} --socket=${socket} -e "use ${bak_db}; source ${back_dir}/${recov_file1};"}
incre_recov()
{
cd ${back_dir}
recov_file2=`ls |grep 'Incr_back'`
${mysql_bin}/mysql -u${user} -p${pass} --socket=${socket} -e "use ${bak_db}; source ${back_dir}/${recov_file2};"}while true
do
echo -e "\t\t**************************************"
echo
echo -e "\t\t\tWelcome to backup program!"
echo
echo -e "\t\t\t(1) Full Backup For MySQL"
echo -e "\t\t\t(2) Increment Backup For MySQL"
echo -e "\t\t\t(3) Recover From The Full Backup File"
echo -e "\t\t\t(4) Recover From The Increment Backup File"
echo -e "\t\t\t(5) Exit The Program!"
echo
echo -e "\t\t**************************************"read-p "Enter your choice:"choicecase $choice in
1)echo "now! let's backup the data by full method......."full_bakecho "succeed!"
sleep 2;;2)echo "now! let's backup the data by increment method......"incre_bakecho "succeed"
sleep 2;;3)echo "now! let's recover from the full back file"full_recovecho "successful"
sleep 2;;4)echo "now! let's recover from the increment backup file"incre_recovecho "successful"
sleep 2;;5)
break
;;*)echo "Wrong Option! try again!"
sleep 2continue
;;esac
done