linux中mysql备份，增量备份及恢复程序

一个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 "$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 "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