mysql增量备份恢复脚本_mysql完全备份/增量备份及恢复脚本

#!/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}/position

cat ${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备份

else

startline=`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}`

do

binlog=`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"

cp now_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:" choice

case $choice in

1)

echo "now! let's backup the data by full method......."

full_bak

echo "succeed!"

sleep 2

;;

2)

echo "now! let's backup the data by increment method......"

incre_bak

echo "succeed"

sleep 2

;;

3)

echo "now! let's recover from the full back file"

full_recov

echo "successful"

sleep 2

;;

4)

echo "now! let's recover from the increment backup file"

incre_recov

echo "successful"

sleep 2

;;

5)

break

;;

*)

echo "Wrong Option! try again!"

sleep 2

continue

;;

esac

done