一个linux中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 statusg | 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 $ip ${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 tt**************************************
echo
echo -e tttwelcome to backup program!
echo
echo -e ttt(1) full backup for mysql
echo -e ttt(2) increment backup for mysql
echo -e ttt(3) recover from the full backup file
echo -e ttt(4) recover from the increment backup file
echo -e ttt(5) exit the program!
echo
echo -e tt**************************************
read -p enter your choice: choice
case $choice in
)
echo now! let's backup the data by full method.......
full_bak
echo succeed!
sleep 2
;;
)
echo now! let's backup the data by increment method......
incre_bak
echo succeed
sleep 2
;;
)
echo now! let's recover from the full back file
full_recov
echo successful
sleep 2
;;
)
echo now! let's recover from the increment backup file
incre_recov
echo successful
sleep 2
;;
)
break
;;
*)
echo wrong option! try again!
sleep 2
continue
;;
esac
done