首頁 > 文章 > 後端開發 > shell每天自動備份mysql資料庫的方法實例

shell每天自動備份mysql資料庫的方法實例

小云云原創: 2018-03-14 10:36:182000瀏覽

本文主要和大家分享shell每天自動備份mysql資料庫的方法實例，希望能幫助大家。

相關mysql影片教學推薦：《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