使用 HotCopy 自動化備份 MySQL DB

1、前言

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


備份 MySQL DB 流程說明：

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

文章目錄

1、前言
2、實作環境
3、Script 功能說明
          daily.local 達成需求
          sqlcopy.sh 達成需求
          sqlcopy.sh 達成需求
          sqlcopy_list.sh 達成需求
          mysql_rsync.sh 達成需求
          /etc/my.cnf 達成需求
4、Script 內容說明
          daily.local 內容 (每天定期執行)
          sqlcopy.sh 內容 (備份 MySQL 資料庫)
          解說 sqlcopy.sh 內容
          sqlcopy_list.sh 內容 (指定要備份的 MySQL 資料庫)
          mysql_rsync.sh 內容 (同步備份好的 MySQL 資料庫回本地端)
          解說 mysql_rsync.sh 內容
5、參考
6、Me FAQ
          Q1.無法使用 mysqlhotcopy 出現訊息 Access denied; you need the RELOAD privilege？
          Q2.無法執行 mysqlhotcopy 並出現 Invalid db.table name？
          Q3.系統沒有 mysqlhotcopy 指令？


2、實作環境

  • FreeBSD 6.1-RELEASE-p12
  • rsync-2.6.9


3、Script 功能說明

daily.local 達成需求

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

sqlcopy.sh 達成需求

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

sqlcopy_list.sh 達成需求

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

mysql_rsync.sh 達成需求

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

/etc/my.cnf 達成需求

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


4、Script 內容說明

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

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

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

自動執行備份 MySQL 資料庫的 sqlcopy.sh Script 內容如下：
 1. #!/bin/sh
 2. #$Id: sqlcopy.sh,v 0.1 2006/12/18 weithenn Exp $
 3. #Daily HotCopy MySQL DB Script
 4. TODAY=`date +%Y%m%d`
 5. COPYDESTDIR="/home/backup/mysql/copy"
 6. COPYCONF="/usr/local/sbin/sqlcopy_list.sh"
 7. KEEPN="3"
 8.
 9. if [ -d ${COPYDESTDIR} ]; then
 10.      cd ${COPYDESTDIR}
 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 ${COPYDESTDIR}/${TODAY}
 18.      if [ -f ${COPYCONF} ]; then
 19.             cd ${COPYDESTDIR}/${TODAY};${COPYCONF}
 20.      else
 21.             echo "Configuration File ${COPYCONF} does not exist!!"
 22.             exit 1
 23.      fi
 24. fi


解說 sqlcopy.sh 內容

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

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

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

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：開始執行同步。


5、參考



6、Me FAQ

Q1.無法使用 mysqlhotcopy 出現訊息 Access denied; you need the RELOAD privilege？

Error Message：
無法正常使用 mysqlhotcopy 並出現如下錯誤訊息？
DBD::mysql::db do failed: Access denied; you need the RELOAD privilege for this operation at /usr/bin/mysqlhotcopy line 472.
Ans：
原因就是必須要使用對該資料庫有管理權限的帳號才行(通常是root)，而本帳號對資料庫權限僅有資料、結構而已，沒有系統管理權限。


Q2.無法執行 mysqlhotcopy 並出現 Invalid db.table name？

Error Message：
無法執行 mysqlhotcopy 並出現如下錯誤訊息？
#/usr/local/bin/mysqlhotcopy xoops > /home/test
 Invalid db.table name 'xoops.xoops`.`activity' at /usr/local/bin/mysqlhotcopy line 855.
Ans：
找到了 MySQL Bugs: #27303: mysqlhotcopy dies with error Invalid db.table name 'foo.bar`.`baz' 說明將 mysqlhotcopy 檔案修改一下後即可順利執行 mysqlhotcopy 了。
#vi /usr/local/bin/mysqlhotcopy    //在第 835 下新增一行
 835     my @dbh_tables = eval { $dbh->tables() };
 836     map { s/^.*?\.//o } @dbh_tables;     //加入此行


Q3.系統沒有 mysqlhotcopy 指令？

Error Message：
執行 mysqlhotcopy 指令後發現系統並沒有這個指令並出現如下錯誤訊息？
#/usr/local/bin/mysqlhotcopy
 mysqlhotcopy: Command not found.
Ans：
請安裝在您 Ports 目錄下對應你 MySQL Server 版本的 Script 目錄去安裝，此例我安裝的 MySQL Server 版本為 mysql51-server 所以去安裝對應的 mysql51-scripts。
#cd /usr/ports/databases/mysql51-scripts
#make install clean
#pkg_info -L mysql-scripts-5.1.18 | grep mysqlhotcopy
   /usr/local/bin/mysqlhotcopy    //可發現有此指令了
於