MySQL備份 MySQL 定期自動備份到遠端機器(SFTP)

之前已經有一篇文章，教大家怎麼自動化備份MySQL到遠端機器上了

MySQL備份 MySQL 定期自動備份到遠端機器

之前那篇說的是，將mysql dump出來之後直接以SSH的方式將檔案傳輸到遠端機器上儲存

我自己的用法是把伺服器上的MySQL資料dump回家裡的NAS儲存，這樣就必須把NAS的SSH打開，並且對外port也必須要打開

有鑒於前陣子QNAP中了很多勒索病毒，考量安全性之下我把對外port全數關閉只能VPN回到NAS上之後才能夠存取資料

這樣一來便造成外部伺服器無法將備份檔案傳回NAS上

今天要講的方法是透過NAS上的Docker建立SFTP伺服器，將對外連線開給docker上的SFTP，伺服器端一樣透過連接到SFTP伺服器將檔案傳輸回NAS上

這樣子做好處是不需要將對外port直接開給NAS，就算真的中了病毒範圍也只限縮在docker這台虛擬機中，相對還是安全許多

接著我們就來進行安裝吧

本次進行的環境將在Synology DSM中進行

STEP1 : 安裝sftp server on docker

這次使用的image是 atmoz/sftp

直接在倉庫伺服器中就可以找到他

點選下載，完成後就會在映像檔的頁面上看到，選擇image後點選”部署”

開始部署先選擇進階設定

1.儲存空間的頁籤需要設定一個NAS上的資料夾給docker做檔案儲存，這樣才能將docker中的檔案存回NAS中、

掛載路徑指的是docker中的資料夾路徑，根據作者的文件需要掛到

/home/使用者名稱/自定一個資料夾名稱，這樣子

網路的部分可以不用特別設定，勾選與docker host使用相同網路即可

最後一個環境設定，這裡根據作者文件，必須要帶入command

因此在命令的地方輸入使用者:密碼:uid(任意指定即可)

按下套用後，等待設定完成即可

這時候sftp已經架設好在nas_ip的22port

接著，必須要到分享器來設定對外port的開放才能夠讓外部連線進來，在port的地方任意輸入埠號，對應到192.168.1.100的22 port

192.168.1.100是我的NAS IP

這樣設定後，外部就可以使用IP:XXXX來連線到docker上的sftp了

STEP2 : 伺服器端安裝lfpt

lftp是一個可以連線到sftp伺服器的client，當然也可以不安裝，直接用sftp指令來傳檔案，只是我覺得這樣子比較方便使用而已

安裝方式很簡單

sudo apt-get update -y
sudo apt-get install -y lftp

1 2	sudo apt-get update -y sudo apt-get install -y lftp

安裝完成後，就可以來設定最後一步了

STEP 3 : 設定自動備份檔案

只要用改上次用來自動備份MySQL的sh批次執行檔修改幾行就可以了

#!/bin/sh
now="$(date +'%Y_%m_%d_%H_%M_%S')"
FILENAME="XXXXX_$now.sql.gz"
backup_path="/PATH_TO_DIR_FOR_DUMP_MYSQL"
cd $backup_path
mysqldump -u root -pMYSQLPASS --routines MYSQL_DB_NAMEE | gzip -c > $FILENAME
lftp -e "set sftp:auto-confirm yes" -u SFTP_USER,SFTP_PASS -p PORT sftp://SFTP_IP_ADDRESS << EOF
cd DIR
put $backup_path/$FILENAME
EOF
rm -f $backup_path/$FILENAME

#!/bin/sh

now="$(date +'%Y_%m_%d_%H_%M_%S')"

FILENAME="XXXXX_$now.sql.gz"

backup_path="/PATH_TO_DIR_FOR_DUMP_MYSQL"

cd $backup_path

mysqldump -u root -pMYSQLPASS --routines MYSQL_DB_NAMEE | gzip -c > $FILENAME

lftp -e "set sftp:auto-confirm yes" -u SFTP_USER,SFTP_PASS -p PORT sftp://SFTP_IP_ADDRESS << EOF

cd DIR

put $backup_path/$FILENAME

EOF

rm -f $backup_path/$FILENAME

逐行說明:

FILENAME 定義了dump出來的備份檔檔案名稱，我這邊使用XXXX_時間來作為檔案的命名

backup_path這邊是dump出來的備份檔所載路徑

接著切換到dump檔需要存在的路徑後執行mysqldump，並且壓縮成zip檔，儲存檔名就是FILENAME定義的

下一個步驟是透過lftp連線到sftp

第一次連線你可以會收到這樣的錯誤訊息

bash Fatal error: Host key verification failed

1	bash Fatal error: Host key verification failed

可以參考這篇，手動連線一次之後應該就可解決

FTP上的操作就是切換到要儲存的資料夾路徑，並且講檔案放入

結束之後記得將檔案rm，移除掉伺服器上的備份檔，才不會早成容量越來越大

如果執行

bash XXX.sh

1	bash XXX.sh

可以順利完成那就代表沒有問題了，應該可以在sftp伺服器上看到備份的檔案，最後一個步驟

設定讓他可以自動執行，我們一樣透過crontab -e

0 16 * * * bash /PATH_TO_THE_XXX.sh

1	0 16 * * * bash /PATH_TO_THE_XXX.sh

我這邊這樣設定的話就是讓系統自動在每天的00:16 執行XXX.sh

這樣的話就可以達成每天自動備份了

MySQL備份 MySQL 定期自動備份到遠端機器(SFTP)

STEP1 : 安裝sftp server on docker

STEP2 : 伺服器端安裝lfpt

STEP 3 : 設定自動備份檔案

延伸閱讀: