如何定期備份 MySQL 及刪除舊有檔案-適用 Windows

MySQL 是免費的關聯式資料庫，具有輕量級速度快的優點，適合小型網站架設使用。
目前最流行的 WordPress 就是採用 MySQL 做為指定資料庫。

如果還沒安裝 MySQL 的話，可參考我這篇教學文章: Windows Server 安裝 MySQL Community 免費社群版

在上一篇我講解了如何備份與還原 MySQL 資料庫，我所教學的方法是手動備份的方式。

接下來這一篇將會講解如何在 Windows 做到定期備份，並定期刪除舊有備份檔。
而我所使用的方式是命令提示字元(CMD)，呼叫 MySQL 備份程式。
再使用工作排程器來定期執行批次指令。

1 編寫命令列
- 1.1 發生錯誤時檢查方式
2 設定工作排程器
3 重點整理

編寫命令列

在開始寫命令列之前，我們先建立要存放備份的目錄。例如放在 C:\mysql_backup。
然後在建立一個批次檔案，副檔名為 .bat。例如放在 C:\mysql_backup.bat 。
mysql_backup.bat 內容如下：

rem ******MySQL backup start********

@echo off

:: 備份目錄
set BackupFile=C:\mysql_backup

:: 資料庫連線
set userId=root
set UserPwd=123456
set Host=127.0.0.1
set port=3306
set DBName=world

:: 備份保留天數
set RetentionDays=-3

:: 設定時間格式 yyyyMMdd
set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%"

::刪除舊有備份檔
forfiles /p %BackupFile% /m backup_*.sql -d %RetentionDays% /c "cmd /c del /f @path"

:: 執行備份指令
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump" --opt --single-transaction=TRUE --user=%userId% --password=%UserPwd% --host=%Host% --protocol=tcp --port=%port% --default-character-set=utf8 --single-transaction=TRUE --routines --events %DBName% > "%BackupFile%\backup_%Ymd%.sql"

@echo on

rem ******MySQL backup end********
rem pause

rem ******MySQL backup start********

@echo off

:: 備份目錄

set BackupFile=C:\mysql_backup

:: 資料庫連線

set userId=root

set UserPwd=123456

set Host=127.0.0.1

set port=3306

set DBName=world

:: 備份保留天數

set RetentionDays=-3

:: 設定時間格式 yyyyMMdd

set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%"

::刪除舊有備份檔

forfiles /p %BackupFile% /m backup_*.sql -d %RetentionDays% /c "cmd /c del /f @path"

:: 執行備份指令

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump" --opt --single-transaction=TRUE --user=%userId% --password=%UserPwd% --host=%Host% --protocol=tcp --port=%port% --default-character-set=utf8 --single-transaction=TRUE --routines --events %DBName% > "%BackupFile%\backup_%Ymd%.sql"

@echo on

rem ******MySQL backup end********

rem pause

關於需要調整的參數有：
BackupFile=備份目錄。
userId=資料庫登入帳號，建立用管理者帳號。
UserPwd=資料庫登入密碼。
Host=主機 IP。
port=連接埠。
DBName=資料庫名稱。
RetentionDays=-3 表示刪除超過3天的檔案。

關於檔名的時間，我只有保留{年月日}而已。
如果想要使用{年月日時分秒}格式的話，可以使用以下語法。
set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%"

另外要特別注意的是在執行備份指令的 C:\Program Files\MySQL\MySQL Server 8.0\bin\，要指向你安裝的 MySQL 目錄。
mysqldump是 MySQL 提供的備份程式。

當執行過後就會產生以下檔案

發生錯誤時檢查方式

如果當你執行後，檔案沒有正常出現，可能出現了問題。
可以把指令最下面的rem pause 改為 pause，
而 rem 是註解的意思，pause 則是暫停的意思。
會讓執行畫面停住不會消失，這時候你可以看看是那裡出現問題。
較常出現的問題是資料庫連線失敗或是目錄有錯。

設定工作排程器

當完成了批次檔 mysql_backup.bat 後，接下來就是建立「工作排程器」定期備份。

打開「工具排程器」，在右邊執行「建立工作」。

輸入排程名稱

排程名稱例如: “MySQL Backup”。
執行選項可選擇「只有使用者登入時才執行」或是「不論使用者登入與否均執行」。
這個看你的電腦是否要在登出時執行。

設定觸發程序

觸發時間依個人想要的週期來備份。

通常我是每天備份一次。

設定動作

動作的部份就選擇啟動剛剛的批次檔，要注意的是「開始位置」也要輸入批次檔的存放目錄。

完成設定後，就可以在清單看到已建立的排程了。

重點整理

編寫命令列 ( .bat 檔)
設定工作排程器

4 thoughts on “如何定期備份 MySQL 及刪除舊有檔案-適用 Windows”

yen表示:

2023-01-1109:01

可否請問，我按照你的做法做了備份，但還原要怎麼做?我有看到https://blog.hungwin.com.tw/mysql-workbench-backup/這篇做Data Import，但不work

回覆
1. Mars表示:
  
  2023-01-1110:44
  
  定期產生 sql 的還原，你不用使用「Server > Data Import」來還原，因為這是對應「Server > Data Export」在用的
  你只要直接執行這 sql 內的語法就行了，通常要注意的就是 sql 內是否有 create database 語法，如果有的話，就不用預先建立空的資料庫，如果沒有的話，就手動建新的空資料庫後再執行 sql
  
  回覆
Ethan表示:

2022-01-1015:16

之前找到的其他教學都無法運作，這篇的bat檔終於會動了，十分感謝
想請教一下這些參數的作用，因為我還是很好奇之前自己找到的寫法為什麼無法運作

-u root -ppassword –database dbname >D:\
mysqldump: [Warning] Using a password on the command line interface can be insecure.

回覆
1. Mars表示:
  
  2022-01-1019:02
  
  你的 -ppassword 是不是應該為 -p password (中間需要有空格才會正確?)
  你的錯誤訊息是使用密碼可能不安全，這個我看不出原因，我沒有用這種方法登入過
  
  但你可以正常運行 bat 檔還是恭喜你喔
  
  回覆

如何定期備份 MySQL 及刪除舊有檔案-適用 Windows