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

MySQL-使用 Dump 備份 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 達成如下需求

sqldump.sh 達成如下需求

sqldump_list.sh 達成如下需求

mysql_rsync.sh 達成如下需求

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

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

參考

[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.執行 mysqldump 時出現找不到某一個 tables 並且中斷執行?

Error Meaage:

執行 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 了。

Go To Oddmuse OrgGo To FreeBSD OrgCreative Commons 2.5 Taiwansitestates.com