mysql 自動定時備份

標籤：壓縮檔   mysql   資料庫   計劃   

說明：以下是資料庫自動備份指令碼，可以儲存成.bat的方式，然後用計劃任務去定時執行。

::echo 資料庫開始備份！

@echo off

::定義磁碟

set disk=D

::定義單位名稱 

set unit=jd_scjdj

::定義壓縮檔路徑

set rar=C:\Program Files\WinRAR

::定義存放路徑

set filepath_ntdat=%disk%:\webserver\data_bak\ntdat

set filepath_ntcms=%disk%:\webserver\data_bak\ntcms_jd_scjdj

set filepath_ntlas=%disk%:\webserver\data_bak\ntlas_jd_sc

set filepath_ntlfs=%disk%:\webserver\data_bak\ntlfs_jd_sc

set filepath_ntpms=%disk%:\webserver\data_bak\ntpms_jd_sc

set filepath_ntrem=%disk%:\webserver\data_bak\ntrem_jd_sc

set filepath_pras=%disk%:\webserver\data_bak\pras

::定義檔案類型

set filetype=MySQL

::定義時間格式

set datetime="%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%"

::定義檔案名稱

set filename_ntdat=ntdat_%unit%-%filetype%-%datetime%

set filename_ntcms_jd_scjdj=ntcms_jd_scjdj_%unit%-%filetype%-%datetime%

set filename_ntlas_jd_sc=ntlas_jd_sc_%unit%-%filetype%-%datetime%

set filename_ntlfs_jd_sc=ntlfs_jd_sc_%unit%-%filetype%-%datetime%

set filename_ntpms_jd_sc=ntpms_jd_sc_%unit%-%filetype%-%datetime%

set filename_ntrem_jd_sc=ntrem_jd_sc_%unit%-%filetype%-%datetime%

set filename_pras=pras_%unit%-%filetype%-%datetime%

::判斷檔案夾是否存在

If Not Exist %filepath_ntdat% MD %filepath_ntdat%

If Not Exist %filepath_ntcms% MD %filepath_ntcms%

If Not Exist %filepath_ntlas% MD %filepath_ntlas%

If Not Exist %filepath_ntlfs% MD %filepath_ntlfs%

If Not Exist %filepath_ntpms% MD %filepath_ntpms%

If Not Exist %filepath_ntrem% MD %filepath_ntrem%

If Not Exist %filepath_pras%  MD %filepath_pras%

::備份資料庫

cd /d "%disk%:\webserver\tools\mysql_5.6.26\bin"

mysqldump.exe --opt -u "root" --password="root" "ntdat" > "%filepath_ntdat%\ntdat.sql"

mysqldump.exe --opt -u "root" --password="root" "ntcms" > "%filepath_ntcms%\ntcms_jd_scjdj.sql"

mysqldump.exe --opt -u "root" --password="root" "ntlas" > "%filepath_ntlas%\ntlas_jd_sc.sql"

mysqldump.exe --opt -u "root" --password="root" "ntlfs" > "%filepath_ntlfs%\ntlfs_jd_sc.sql"

mysqldump.exe --opt -u "root" --password="root" "ntpms" > "%filepath_ntpms%\ntpms_jd_sc.sql"

mysqldump.exe --opt -u "root" --password="root" "ntrem" > "%filepath_ntrem%\ntrem_jd_sc.sql"

mysqldump.exe --opt -u "root" --password="root" "pras" > "%filepath_pras%\pras.sql"

::壓縮備份檔案

cd /d "%filepath_ntdat%"

"%rar%\WinRAR.exe" A "%filename_ntdat%.rar" "ntdat.sql"

del /Q "ntdat.sql"

cd /d "%filepath_ntcms%"

"%rar%\WinRAR.exe" A "%filename_ntcms_jd_scjdj%.rar" "ntcms_jd_scjdj.sql"

del /Q "ntcms_jd_scjdj.sql"

cd /d "%filepath_ntlas%"

"%rar%\WinRAR.exe" A "%filename_ntlas_jd_sc%.rar" "ntlas_jd_sc.sql"

del /Q "ntlas_jd_sc.sql"

cd /d "%filepath_ntlfs%"

"%rar%\WinRAR.exe" A "%filename_ntlfs_jd_sc%.rar" "ntlfs_jd_sc.sql"

del /Q "ntlfs_jd_sc.sql"

cd /d "%filepath_ntpms%"

"%rar%\WinRAR.exe" A "%filename_ntpms_jd_sc%.rar" "ntpms_jd_sc.sql"

del /Q "ntpms_jd_sc.sql"

cd /d "%filepath_ntrem%"

"%rar%\WinRAR.exe" A "%filename_ntrem_jd_sc%.rar" "ntrem_jd_sc.sql"

del /Q "ntrem_jd_sc.sql"

cd /d "%filepath_pras%"

"%rar%\WinRAR.exe" A "%filename_pras%.rar" "pras.sql"

del /Q "pras.sql"

::echo 資料庫結束備份！

本文出自 “楊海龍的部落格” 部落格，請務必保留此出處http://7218743.blog.51cto.com/7208743/1844513

mysql 自動定時備份