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

一个linux中mysql完全备份,增量备份及恢复脚本实现程序,有需要的朋友可参考一下,可以把它做成定时备份,代码如下:

1. #!/bin/bash
2. #full&&incrementbackupandrecover
3. #说明:事先要确保存在/data/bak目录,且要保证在执行增量备份时已做过至少一次全量备份,否则找不到position文件.
4. port='3306'
5. back_src_dir="/data/mysql/${port}/logs/binlog"
6. back_dir='/data/bak'
7. DATE=`date+%Y%m%d`
8. user='root'
9. pass='cy2009'
10. bak_db='test1'
11. mysql_bin='/usr/local/mysql-5.1.48/bin'
12. socket="/data/mysql/${port}/mysql.sock"
13. full_bak()
14. {
15. cd${back_dir}
16. DumpFile=Full_back$DATE.sql
17. ${mysql_bin}/mysqldump--lock-all-tables--flush-logs--master-data=2-u${user}-p${pass}${bak_db}>${DumpFile}
18. ${mysql_bin}/mysql-u${user}-p${pass}--socket=${socket}-e"unlocktables"
20. #把当前的binlog和position信息存入position文件
21. cat${DumpFile}|grep'MASTER_LOG_FILE'|awk-F"'"'{print$2}'>${back_dir}/position
22. cat${DumpFile}|grep'MASTER_LOG_FILE'|awk-F"="'{print$3}'|awk-F";"'{print$1}'>>${back_dir}/position
23. }
24. incre_bak()
25. {
26. #锁定表，刷新log
27. ${mysql_bin}/mysql-u${user}-p${pass}--socket=${socket}-e"flushtableswithreadlock"
28. ${mysql_bin}/mysqladmin-u${user}-p${pass}--socket=${socket}flush-logs
29. #获取上次备份完成时的binlog和position
30. cd${back_dir}
31. start_binlog=`sed-n'1p'position`
32. start_pos=`sed-n'2p'position`
34. #获取目前的binlog和position
35. mysql-u${user}-p${pass}--socket=${socket}-e"showmasterstatusG"|awk'{print$2}'|sed-n'2,3p'>now_position
36. stop_binlog=`sed-n'1p'now_position`
37. stop_pos=`sed-n'2p'now_position`
38. #如果在同一个binlog中
39. if["${start_binlog}"=="${stop_binlog}"];then
40. ${mysql_bin}/mysqlbinlog--start-position=${start_pos}--stop-position=${stop_pos}${back_src_dir}/${start_binlog}>>Incr_back$DATE.sql
42. #跨binlog备份
43. else
44. startline=`awk"/${start_binlog}/{printNR}"${back_src_dir}/mysql-bin.index`
45. stopline=`wc-l${back_src_dir}/mysql-bin.index|awk'{print$1}'`
46. foriin`seq${startline}${stopline}`
47. do
48. binlog=`sed-n"$i"p${back_src_dir}/mysql-bin.index|sed's/.*///g'`
49. case"${binlog}"in
50. "${start_binlog}")
51. ${mysql_bin}/mysqlbinlog--start-position=${start_pos}${back_src_dir}/${binlog}>>Incr_back$DATE.sql
52. ;;
53. "${stop_binlog}")
54. ${mysql_bin}/mysqlbinlog--stop-position=${stop_pos}${back_src_dir}/${binlog}>>Incr_back$DATE.sql
55. ;;
56. *)
57. ${mysql_bin}/mysqlbinlog${back_src_dir}/${binlog}>>Incr_back$DATE.sql
58. ;;
59. esac
60. done
61. fi
62. #解除表锁定，并保存目前的binlog和position信息到position文件。
63. ${mysql_bin}/mysql-u${user}-p${pass}--socket=${socket}-e"unlocktables"
64. cpnow_positionposition
65. }
66. full_recov()
67. {
68. cd${back_dir}
69. recov_file1=`ls|grep'Full_back'`
70. ${mysql_bin}/mysql-u${user}-p${pass}--socket=${socket}-e"use${bak_db};source${back_dir}/${recov_file1};"
71. }
73. incre_recov()
74. {
75. cd${back_dir}
76. recov_file2=`ls|grep'Incr_back'`
77. ${mysql_bin}/mysql-u${user}-p${pass}--socket=${socket}-e"use${bak_db};source${back_dir}/${recov_file2};"
78. }
79. whiletrue
80. do
81. echo-e"tt**************************************"
82. echo
83. echo-e"tttWelcometobackupprogram!"
84. echo
85. echo-e"ttt(1)FullBackupForMySQL"
86. echo-e"ttt(2)IncrementBackupForMySQL"
87. echo-e"ttt(3)RecoverFromTheFullBackupFile"
88. echo-e"ttt(4)RecoverFromTheIncrementBackupFile"
89. echo-e"ttt(5)ExitTheProgram!"
90. echo
91. echo-e"tt**************************************"
92. read-p"Enteryourchoice:"choice
93. case$choicein
94. )
95. echo"now!let'sbackupthedatabyfullmethod......."
96. full_bak
97. echo"succeed!"
98. sleep2
99. ;;
100. )
101. echo"now!let'sbackupthedatabyincrementmethod......"
102. incre_bak
103. echo"succeed"
104. sleep2
105. ;;
106. )
107. echo"now!let'srecoverfromthefullbackfile"
108. full_recov
109. echo"successful"
110. sleep2
111. ;;
112. )
113. echo"now!let'srecoverfromtheincrementbackupfile"
114. incre_recov//phpfensi.com
115. echo"successful"
116. sleep2
117. ;;
118. )
119. break
120. ;;
121. *)
122. echo"WrongOption!tryagain!"
123. sleep2
124. continue
125. ;;
126. esac
127. done