| #!/bin/bash
# full && increment backup and recover
# Description: Make sure that the/data/bak directory exists beforehand, and that you cannot find the position file if you have done at least one full backup while performing an incremental backup.
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 $} ' > ${back_ Dir}/position
Cat ${dumpfile} |grep ' master_log_file ' |awk-f ' = ' ' {print $} ' |awk-f '; ' {print} ' >> ${back_dir}/position
}
Incre_bak ()
{
#锁定表, refresh log
${mysql_bin}/mysql-u${user}- P${pass}--socket=${socket}-E "Flush tables with read lock"
${mysql_bin}/mysqladmin-u${user}-p${pass}--socket=${s Ocket} 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 $} ' | 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 $} '
For i in ' seq ${startline} ${stopline} '
Todo
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
#解除表锁定 and save the current Binlog and position information to the position file.
${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
Todo
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 of the data by ...
Full_bak
echo "succeed!"
Sleep 2
;;
)
echo "now! Let ' s backup of the data by increment ...
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 |
