使用 Dump 自動化備份 MySQL DB

前言

當 MySQL 上線服務前第一個要思考的問題當然就是災難復原了，預計讓系統每天凌晨自動備份 MySQL 使用 Dump 備份方式，之後使用 Rsync 方式將遠端備份好的 MySQL Dump 檔案同步回來，並透過 amd (automounter daemon) mount 方式將 MySQL Dump 備份資料寫入至備份伺服器中 (遠端路徑使用/host/機器名稱)，當然前提是你已經設好管理 MySQL 帳號及密碼在 /etc/my.cnf 檔案內。請注意：以下 Script 只適合我的環境，若您取用請自行調整，恕不負損壞之責。


備份 MySQL DB 流程說明：

  1. 遠端 MySQL Server 每天定期執行 sqldump.sh 來備份 MySQL 資料庫。
  2. 只保留最新 3 天的備份 MySQL Database 資料夾。
  3. 本地端的機器每天定期將遠端 MySQL Server 所備份 Dump 資料夾透過 Rsync 方式同步回來。


實作環境

  • FreeBSD 6.1-RELEASE-p12
  • rsync-2.6.9 


Script 功能說明

daily.local 達成需求

  • 系統會每天凌晨時自動執行 daily.local 內指定的 Script 內容 (正常情況為凌晨 3 ~ 4 點間)。

sqldump.sh 達成需求

  • 每天定期利用指令 mysqldump 來備份指定的 MySQL 資料庫。
  • 可指定保留幾天份的 MySQL 資料庫。

sqldump_list.sh 達成需求

  • 利用指令 mysqldump 來備份指定的 MySQL 資料庫。

mysql_rsync.sh 達成需求

  • 將遠端備份完成的 MySQL 資料庫 Dump 檔案同步回來至本地端。

/etc/my.cnf 達成需求

填入管理 MySQL 的帳號、密碼資訊內容如下 (這裡的 root 帳號指的是 MySQL 的管理帳號跟系統的 Super User root 沒任何關系)。
 [client]
 user=root         //管理 MySQL 資料庫的帳號
 password=1234     //管理 MySQL 資料庫帳號的密碼


Script 內容說明

daily.local 內容 (每天定期執行)

將 sqldump.sh 路徑寫入 daily.local 中以便系統每天自動執行備份 MySQL 資料庫的 Script。
 #cat /etc/daily.local
   #!/bin/sh
 / usr/local/sbin/sqldump.sh

sqldump.sh 內容 (備份 MySQL 資料庫)

自動執行備份 MySQL 資料庫的 sqldump.sh Script 內容如下：
 1. #!/bin/sh
 2. #$Id: sqldump.sh,v 0.1 2006/12/18 weithenn Exp $
 3. #Daily Dump MySQL DB Script
 4. TODAY=`date +%Y%m%d`
 5. DUMPDESTDIR="/home/backup/mysql/dump"
 6. DUMPCONF="/usr/local/sbin/sqldump_list.sh"
 7. KEEPN="3"
 8.
 9. if [ -d ${DUMPDESTDIR} ]; then
 10.      cd ${DUMPDESTDIR}
 11.      N=`ls -1|grep -e '[0-9]'|wc -l`
 12.      if [ ${N} -gt ${KEEPN} ]; then
 13.            echo "Removing Stale MySQL DB Backup..."
 14.            ls -1 | grep -e '[0-9]'|sort -nr|tail -`expr ${N} - ${KEEPN}` | xargs rm -r
 15.             echo "Finished"
 16.      fi
 17.      mkdir -p ${DUMPDESTDIR}/${TODAY}
 18.      if [ -f ${DUMPCONF} ]; then
 19.             cd ${DUMPDESTDIR}/${TODAY};${DUMPCONF}
 20.      else
 21.             echo "Configuration File ${DUMPCONF} does not exist!!"
 22.             exit 1
 23.      fi
 24. fi


解說 sqldump.sh 內容：

  • 4： 執行日期指令(到時建的資料夾命名即為當天日期)。
  • 5： 指定執行 MySQL Dump 指令後存放的目錄夾路徑。
  • 6： 指定要備份的 MySQL 列表(當有超過一個以上 DB 要備份時比較方便)。
  • 7： 指定資料夾只留三個(加上新備份的共會有四個資料夾)。
  • 9： 判斷存放 MySQL Dump 目錄是否存在。
  • 10： 目錄存在則切換至存放 MySQL Dump 目錄路徑。
  • 11 ~ 16： 只留日期最新的三個資料夾，其餘舊的資料夾都刪除。
  • 17： 建立以今天日期為名的資料夾。
  • 18： 判斷備份 MySQL 列表的 script 是否存在。
  • 19： 備份 MySQL 列表的 script 存在的話，就開始執行 sqldump_list.sh 的內容。
  • 20 ~ 23： 若備份 MySQL 列表的 script 不存在則顯示字串訊息並離開此 script。

sqldump_list.sh 內容 (指定要備份的 MySQL 資料庫)

指定要備份的 MySQL 資料庫 sqldump_list.sh Script 內容如下，但如果您想一次備份所有的資料庫您可以使用參數 --all-databases 就會備份所有資料庫而不用一一條列，條件的目的是假如你可能有 10 個 MySQL 資料庫而你只想備份其中的 5 個資料庫時就可利用條列的方式。
 #!/bin/sh
 echo Backup Xoops MySQL DB
 /usr/local/bin/mysqldump xoops > xoops.sql     //備份資料庫名稱為 xoops
 echo Backup Joomla MySQL DB
 /usr/local/bin/mysqldump joomla > joomla.sql   //備份資料庫名稱為 joomla
 echo Backup PHPBB MySQL DB
 /usr/local/bin/mysqldump phpbb > phpbb.sql     //備份資料庫名稱為 phpbb
 chmod -R 600 /home/backup/mysql/dump

mysql_rsync.sh 內容 (同步備份好的 MySQL 資料庫回本地端)

同步備份好的 MySQL 資料庫回本地端 mysql_rsync.sh Script 內容如下：
 1. #!/bin/sh
 2. #$Id: mysql_rsync.sh,v 0.1 2006/12/18 weithenn Exp $
 3. #Rsync MySQL DB Script
 4. export PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
 5. SRCDIR=/host/mysql/home/db/mysql
 6. DESTDIR=/home/database/mysql
 7. RSYNC=""/usr/local/bin/rsync -avP""
 8. DBREPL="${RSYNC} ${SRCDIR} ${DESTDIR}"
 9.
 10. if [ ! -d ${SRCDIR} ]; then
 11.     echo "Source Directory does not exist!!"
 12.        exit 0;
 13. fi
 14.
 15. echo "Syncing Co-Location MySQL db..."
 16. ${DBREPL}


解說 mysql_rsync.sh 內容：

  • 5：指定遠端存放備份 MySQL DB 路徑(來源)。
  • 6：指定同步回來存放 MySQL DB 路徑(目的地)。
  • 7：Rsync同步指令。
  • 8：結合 5,6,7 行的結果。
  • 10 ~ 13：在執行同步以前先檢查遠端存放備份 MySQL DB 目錄是否存在，若不存在則離開程式。
  • 15：顯示開始同步 MySQL DB 字串。
  • 16：開始執行同步。


參考



Me FAQ

Q1.執行 mysqldump 時出現找不到某一個 tables 並且中斷執行？

Error Message：
執行 mysqldump 時出現找不到某一個 tables 並且中斷執行？
#/usr/local/bin/mysqldump xoops > xoops.sql    //執行 mysqldump 並出現如下錯誤訊息
 mysqldump: Got error: 1146: Table 'xoops.vote_results' doesn't exist when using LOCK TABLES
找了 google 說加上 --skip-lock-tables 試試，也是不行。
#/usr/local/bin/mysqldump --skip-lock-tables xoops > xoops.sql
 Error: Couldn't read status information for table vote_results ()
 mysqldump: Couldn't execute 'show create table `vote_results`': Table 'xoops.vote_results' doesn't exist (1146)
進入 phpmyadmin 管理介面後關於此 vote_results table 的徵狀。
  1. 並沒有看到 vote_results table。
  2. 但使用指令 show tables; 卻有看到 vote_results table。
  3. 使用 drop table vote_results 又說該 table 不存在無法 drop。
  4. 至 mysql 目錄下有看到 vote_results.frm 檔案。
Ans：
反正這也只是投票的結果而以，就把 vote_results.frm 檔案拿掉，並重新啟動 mysqld 後，就可順利執行 mysqldump 了。
於