使用ASP文件生成備份腳本
對應設置IIS用戶，需要有讀取MySQL文件夾的權限
vIEw plaincopy to clipboardprint?
<%
'----------MySQLdump用戶名
root = "root"
'----------密碼
pwd = "123456"
'----------mysql MySQLdump 文件所在位置
mysql_bin_path = "D:\MySQL\bin"
'----------MySQL目錄(本地路徑)
mysql_path = "D:\MySQL"
'----------MySQL備份臨時存放目錄(本地路徑)
mysq_back_temp_path = "D:\MySQLbackuptemp"
'----------MySQL備份存放目錄(本地路徑)
mysq_back_path = "D:\MySQLbackup"
'----------winrar所在目錄
winrar_path = "D:\WinRAR"
'----------備份腳本
back_content = ""
back_content = back_content & left(MySQL_bin_path,2) & vbCrLf
back_content = back_content & "cd " & MySQL_bin_path & vbCrLf
'----------壓縮腳本
rar_content = ""
rar_content = rar_content & left(winrar_path,2) & vbCrLf
rar_content = rar_content & "cd " & winrar_path & vbCrLf
'遍歷MySQL數據庫
Set fso = server.CreateObject("scripting.filesystemobject")
Set w1 = fso.getfolder(MySQL_path)
For Each bak In w1.SubFolders
If FolderPermission(bak.Path) then
'MySQLdump
back_content = back_content & "MySQLdump -u" & root & " -p" & pwd & " " & bak.name & ">" & mysq_back_temp_path & "\" & bak.name & ".sql" & vbCrLf
'rar
rar_content = rar_content & "rar a -agYYYY-MM-DD -ep1 -df -ibck " & mysq_back_path & "\" & bak.name & ".rar " & mysq_back_temp_path & "\" & bak.name & ".sql" & vbCrLf
End If
Next
Set fso=Nothing
FileContent = back_content & vbCrLf & rar_content
'創建備份文件
response.write("創建MySQL備份文件")
Call CreateFile("/backup_MySQL.bat",FileContent)
Function FolderPermission(pathName)
'需要過濾的目錄(不列在SiteMap裡面)
PathExclusion = Array("guolv")
Folderpermission =True
for each PathExcluded in PathExclusion
if instr(ucase(pathName),ucase(PathExcluded))>0 then
Folderpermission = False
exit for
end if
next
End Function
'創建文件
Function CreateFile(FileName, ContentStr)
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtFile = fso.CreateTextFile(Server.MapPath(FileName),True)
txtFile.Write(ContentStr)
txtFile.Close
Set fso = Nothing
End Function
%>
<%
'----------MySQLdump用戶名
root = "root"
'----------密碼
pwd = "123456"
'----------mysql MySQLdump 文件所在位置
mysql_bin_path = "D:\MySQL\bin"
'----------MySQL目錄(本地路徑)
mysql_path = "D:\MySQL"
'----------MySQL備份臨時存放目錄(本地路徑)
mysq_back_temp_path = "D:\MySQLbackuptemp"
'----------MySQL備份存放目錄(本地路徑)
mysq_back_path = "D:\MySQLbackup"
'----------winrar所在目錄
winrar_path = "D:\WinRAR"
'----------備份腳本
back_content = ""
back_content = back_content & left(MySQL_bin_path,2) & vbCrLf
back_content = back_content & "cd " & MySQL_bin_path & vbCrLf
'----------壓縮腳本
rar_content = ""
rar_content = rar_content & left(winrar_path,2) & vbCrLf
rar_content = rar_content & "cd " & winrar_path & vbCrLf
'遍歷MySQL數據庫
Set fso = server.CreateObject("scripting.filesystemobject")
Set w1 = fso.getfolder(MySQL_path)
For Each bak In w1.SubFolders
If FolderPermission(bak.Path) then
'MySQLdump
back_content = back_content & "MySQLdump -u" & root & " -p" & pwd & " " & bak.name & ">" & mysq_back_temp_path & "\" & bak.name & ".sql" & vbCrLf
'rar
rar_content = rar_content & "rar a -agYYYY-MM-DD -ep1 -df -ibck " & mysq_back_path & "\" & bak.name & ".rar " & mysq_back_temp_path & "\" & bak.name & ".sql" & vbCrLf
End If
Next
Set fso=Nothing
FileContent = back_content & vbCrLf & rar_content
'創建備份文件
response.write("創建MySQL備份文件")
Call CreateFile("/backup_MySQL.bat",FileContent)
Function FolderPermission(pathName)
'需要過濾的目錄(不列在SiteMap裡面)
PathExclusion = Array("guolv")
Folderpermission =True
for each PathExcluded in PathExclusion
if instr(ucase(pathName),ucase(PathExcluded))>0 then
Folderpermission = False
exit for
end if
next
End Function
'創建文件
Function CreateFile(FileName, ContentStr)
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtFile = fso.CreateTextFile(Server.MapPath(FileName),True)
txtFile.Write(ContentStr)
txtFile.Close
Set fso = Nothing
End Function
%>
生成腳本 backup_MySQL.bat 可以設置Windows的任務計劃來定時自動執行備份。
備注：
mysql中MySQLdump用戶權限
mysql中MySQLdump是用來導出數據用的
如果需要特定的用戶來完成導出工作
那至少需要 Select和LOCK TABLES 兩個權限
MySQL備份的最直接的方法，直接拷貝數據庫文件
直接拷貝數據文件最為直接、快速、方便，但缺點是基本上不能實現增量備份。為了保證數據的一致性，需要在備份文件前，執行以下 SQL 語句：
FLUSH TABLES WITH READ LOCK;
也就是把內存中的數據都刷新到磁盤中，同時鎖定數據表，以保證拷貝過程中不會有新的數據寫入。這種方法備份出來的數據恢復也很簡單，直接拷貝回原來的數據庫目錄下即可。
