shell每天自動備份mysql資料庫的方法執行個體

本文主要和大家分享shell每天自動備份mysql資料庫的方法執行個體，希望能協助到大家。

一、mysql提供了一個mysqldump的工具可以方便的匯出匯入資料庫資訊；

二、使用命令列shell測試執行mysqldump，理解必備的參數，查看產生的sql備份檔案是否符合需求；

/usr/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > /mnt/mysqlBackup/db_`date +%F`.sql

註解：
1、執行 /usr/bin/mysqldump；
2、--opt是quick，add-drop-table，add-locks，extended-insert，lock-tables幾個參數的合稱，一般都要使用，具體意思自行搜尋；
3、-u資料庫使用者名稱 -p資料庫使用者密碼 -h資料庫地址 資料庫名 > 匯出的檔案路徑；
4、`date +%F`是shell中產生當前日期，格式如2015-11-05，所以成功匯出時產生的檔案名稱為 db_2015-11-05.sql；
5、下載產生的 sql檔案 ，用文字編輯器開啟檢查，本地匯入測試資料庫，看是否有問題；

三、整理編寫比較靈活的shell指令碼，方便重用；

#!/bin/sh# Database infoDB_USER="batsing"DB_PASS="batsingpw"DB_HOST="localhost"DB_NAME="timepusher"# Others varsBIN_DIR="/usr/bin"            #the mysql bin pathBCK_DIR="/mnt/mysqlBackup"     #the backup file directoryDATE=`date +%F` # TODO# /usr/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher >  /mnt/mysqlBackup/db_`date +%F`.sql$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME >  $BCK_DIR/db_$DATE.sql#還原資料庫#用mysql-front匯入前一天的 *.sql 檔案即可恢複資料

儲存到檔案或上傳到 /usr/local/apache/htdocs/timepusher/sqlBak/sqlAutoBak.sh

四、測試Shell指令碼

1、進入到該指令檔目錄 chmod +x sqlAutoBak.sh 添加執行許可權，否則會報錯 Permission denied
2、./sqlAutoBak.sh ，如果是在windows編寫上傳的檔案可能會報錯

/bin/sh^M: bad interpreter: No such file or directory

這是不同系統編碼格式引起的：在 windows系統中編輯的 .sh檔案可能有不可見字元，所以在 Linux系統下執行會報以上異常資訊。可以在Windows上使用Notepad++轉換成Unix格式（菜單中選擇：編輯>檔案格式轉換>轉換成UNIX）
3、修改後上傳繼續執行 ./sqlAutoBak.sh ，沒有報錯。再查看匯出的sql檔案。

五、壓縮mysql的備份資料

1、查看匯出來的sql檔案，發現其檔案大小非常大，mysqldump也提供了產生gzip壓縮檔的參數設定
2、sqlAutoBak.sh修改為如下

#!/bin/sh# Database infoDB_USER="batsing"DB_PASS="batsingpw"DB_HOST="localhost"DB_NAME="timepusher"# Others varsBIN_DIR="/usr/bin"            #the mysql bin pathBCK_DIR="/mnt/mysqlBackup"    #the backup file directoryDATE=`date +%F`# TODO# /usr/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > /mnt/mysqlBackup/db_`date +%F`.sql# $BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME > $BCK_DIR/db_$DATE.sql$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME ｜ gzip > $BCK_DIR/db_$DATE.sql.gz# 還原資料庫# 把 *.sql.gz 使用gunzip 或 本地的解壓軟體 解壓為 *.sql 檔案# 用mysql-front匯入前一天的 *.sql 檔案即可恢複資料

3、修改後上傳繼續執行 ./sqlAutoBak.sh ，沒有報錯。如果用文字編輯器查看匯出的 *.sql.gz檔案，應該是一堆亂碼。
4、下載到本地使用解壓軟體開啟，解壓就能看到裡面真正的 *.sql 檔案了。

六、設定linux定時任務執行該指令碼；

1、編輯定時工作清單

crontab -e

2、插入下面這一行，因為通常來說5點鐘網站的訪問量最低。

00 05 *   * * /bin/sh /usr/local/apache/htdocs/timepusher/sqlBak/sqlAutoBak.sh#每天早上 5:00am 執行

3、查看任務是否建立成功

crontab -l

七、第二天檢查自動產生的sql檔案是否符合要求

如果產生的檔案和解壓出來查看沒有問題，那麼這個自動定時備份資料庫的指令碼就算是完成了。因為產生的檔案多了會佔用一定的空間，所以建議要定期（比如一個月）清理一下檔案。

$、補充

1、如果該資料庫的使用者沒有分配 鎖表 的許可權，則備份會報錯 when using LOCK TABLES 。那是因為mysqldump命令預設在匯出時是要鎖定表的，所以解決方式有兩個。一個是給該使用者開放 鎖表 的許可權；另一個是在命令中加上 --skip-lock-tables 這個參數。即是：

$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME --skip-lock-tables｜ gzip > $BCK_DIR/db_$DATE.sql.gz