站長新書 站長開講 首頁 最新文章 站長著作及審校 FreeBSD 筆記 Linux 筆記 Windows 筆記 虛擬化筆記 網管人雜誌 遊山玩水 關於本站
站長新書 VMware vSphere ICM 團購開跑了!!

MySQL-使用 HotCopy 備份 DB

前言

當 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 方式同步回來

實作環境

FreeBSD 6.1-RELEASE-p12

rsync-2.6.9

Script 功能說明

daily.local 達成如下需求

sqlcopy.sh 達成如下需求

sqlcopy_list.sh 達成如下需求

mysql_rsync.sh 達成如下需求

/etc/my.cnf 達成如下需求

 [client]
 user=root           //管理 MySQL 資料庫的帳號
 password=1234      //管理 MySQL 資料庫帳號的密碼

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

參考

[Duncan Lo MySQL簡易備份方法]

[Tsung's Blog: 用mysqlhotcopy 備份MySQL]

[如何才能实现MySQL的自动备份 - 技术应用子站 - 赛迪网]

[MySQL AB :: MySQL 5.0 Reference Manual :: 8.14 mysqlhotcopy — A Database Backup Program]

[MySQL Bugs: #27303: mysqlhotcopy dies with error Invalid db.table name 'foo.bar`.`baz']

Me FAQ

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

Error Meaage:

無法正常使用 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 Meaage:

無法執行 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 Meaage:

執行 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    //安裝 Script 套件 for mysql51-server
 #pkg_info -L mysql-scripts-5.1.18 | grep mysqlhotcopy
 /usr/local/bin/mysqlhotcopy                                      //可發現有此指令了
Go To Oddmuse OrgGo To FreeBSD OrgCreative Commons 2.5 Taiwansitestates.com