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

昝辉Zac Zac的SEO博客,坚持12年,优化成为生活。

一个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"
  19. #把当前的binlog和position信息存入position文件
  20. cat${DumpFile}|grep'MASTER_LOG_FILE'|awk-F"'"'{print$2}'>${back_dir}/position
  21. cat${DumpFile}|grep'MASTER_LOG_FILE'|awk-F"="'{print$3}'|awk-F";"'{print$1}'>>${back_dir}/position
  22. }
  23. incre_bak()
  24. {
  25. #锁定表,刷新log
  26. ${mysql_bin}/mysql-u${user}-p${pass}--socket=${socket}-e"flushtableswithreadlock"
  27. ${mysql_bin}/mysqladmin-u${user}-p${pass}--socket=${socket}flush-logs
  28. #获取上次备份完成时的binlog和position
  29. cd${back_dir}
  30. start_binlog=`sed-n'1p'position`
  31. start_pos=`sed-n'2p'position`
  32. #获取目前的binlog和position
  33. mysql-u${user}-p${pass}--socket=${socket}-e"showmasterstatusG"|awk'{print$2}'|sed-n'2,3p'>now_position
  34. stop_binlog=`sed-n'1p'now_position`
  35. stop_pos=`sed-n'2p'now_position`
  36. #如果在同一个binlog中
  37. if["${start_binlog}"=="${stop_binlog}"];then
  38. ${mysql_bin}/mysqlbinlog--start-position=${start_pos}--stop-position=${stop_pos}${back_src_dir}/${start_binlog}>>Incr_back$DATE.sql
  39. #跨binlog备份
  40. else
  41. startline=`awk"/${start_binlog}/{printNR}"${back_src_dir}/mysql-bin.index`
  42. stopline=`wc-l${back_src_dir}/mysql-bin.index|awk'{print$1}'`
  43. foriin`seq${startline}${stopline}`
  44. do
  45. binlog=`sed-n"$i"p${back_src_dir}/mysql-bin.index|sed's/.*///g'`
  46. case"${binlog}"in
  47. "${start_binlog}")
  48. ${mysql_bin}/mysqlbinlog--start-position=${start_pos}${back_src_dir}/${binlog}>>Incr_back$DATE.sql
  49. ;;
  50. "${stop_binlog}")
  51. ${mysql_bin}/mysqlbinlog--stop-position=${stop_pos}${back_src_dir}/${binlog}>>Incr_back$DATE.sql
  52. ;;
  53. *)
  54. ${mysql_bin}/mysqlbinlog${back_src_dir}/${binlog}>>Incr_back$DATE.sql
  55. ;;
  56. esac
  57. done
  58. fi
  59. #解除表锁定,并保存目前的binlog和position信息到position文件。
  60. ${mysql_bin}/mysql-u${user}-p${pass}--socket=${socket}-e"unlocktables"
  61. cpnow_positionposition
  62. }
  63. full_recov()
  64. {
  65. cd${back_dir}
  66. recov_file1=`ls|grep'Full_back'`
  67. ${mysql_bin}/mysql-u${user}-p${pass}--socket=${socket}-e"use${bak_db};source${back_dir}/${recov_file1};"
  68. }
  69. incre_recov()
  70. {
  71. cd${back_dir}
  72. recov_file2=`ls|grep'Incr_back'`
  73. ${mysql_bin}/mysql-u${user}-p${pass}--socket=${socket}-e"use${bak_db};source${back_dir}/${recov_file2};"
  74. }
  75. whiletrue
  76. do
  77. echo-e"tt**************************************"
  78. echo
  79. echo-e"tttWelcometobackupprogram!"
  80. echo
  81. echo-e"ttt(1)FullBackupForMySQL"
  82. echo-e"ttt(2)IncrementBackupForMySQL"
  83. echo-e"ttt(3)RecoverFromTheFullBackupFile"
  84. echo-e"ttt(4)RecoverFromTheIncrementBackupFile"
  85. echo-e"ttt(5)ExitTheProgram!"
  86. echo
  87. echo-e"tt**************************************"
  88. read-p"Enteryourchoice:"choice
  89. case$choicein
  90. )
  91. echo"now!let'sbackupthedatabyfullmethod......."
  92. full_bak
  93. echo"succeed!"
  94. sleep2
  95. ;;
  96. )
  97. echo"now!let'sbackupthedatabyincrementmethod......"
  98. incre_bak
  99. echo"succeed"
  100. sleep2
  101. ;;
  102. )
  103. echo"now!let'srecoverfromthefullbackfile"
  104. full_recov
  105. echo"successful"
  106. sleep2
  107. ;;
  108. )
  109. echo"now!let'srecoverfromtheincrementbackupfile"
  110. incre_recov//phpfensi.com
  111. echo"successful"
  112. sleep2
  113. ;;
  114. )
  115. break
  116. ;;
  117. *)
  118. echo"WrongOption!tryagain!"
  119. sleep2
  120. continue
  121. ;;
  122. esac
  123. done

相关广告
  • linux中mysql备份,增量备份及恢复程序 linux中mysql备份,增量备份及恢复程序 linux中mysql备份,增量备份及恢复程序
相关阅读

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

2019/10/10 17:34:10 | 谷歌SEO算法 | SEO博客