1、前言
當 MySQL 上線服務前第一個要思考的問題當然就是災難復原了，預計讓系統每天凌晨自動備份 MySQL 使用 HotCopy 備份方式，之後使用 Rsync 方式將遠端備份好的 MySQL HotCopy 檔案同步回來，並透過 amd (automounter daemon) mount 方式將 MySQL HotCopy 備份資料寫入至備份伺服器中 (遠端路徑使用 /host/機器名稱)，當然前提是你已經設好管理 MySQL 帳號及密碼在 /etc/my.cnf 檔案內。請注意：以下 Script 只適合我的環境，若您取用請自行調整，恕不負損壞之責。備份 MySQL DB 流程說明：
- 遠端 MySQL Server 每天定期執行 sqlcopy.sh 來備份 MySQL 資料庫。
- 只保留最新 3 天的備份 MySQL Database 資料夾。
- 本地端的機器每天定期將遠端 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.shsqlcopy.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 . //備份資料庫資料夾名稱為 phpbbmysql_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、參考
- Duncan Lo 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'
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 //可發現有此指令了
