最新消息:

备份本地MySql数据库

autoit 永恒心锁 1258浏览 0评论
#include <Array.au3>
#include <Constants.au3>
 
; ------------------------------------------------------------------------
; BACKUP MYSQL DATABASES ON LOCALHOST
; ------------------------------------------------------------------------
; Definition and meaning:
; $export_defs ..... combine two constants: $cEXPORT_DB + ($cEXPORT_TO_... or $cEXPORT_AS_...)
; e.g. [ $cEXPORT_DB_ALL_DATABASES + $cEXPORT_TO_SINGLE_FILE ] => export all dbs to one file
; $custom_dbs ...... user-created databases. Use comma as separator, e.g. "drupal, joomla"
; $export_path ..... an export destination folder
; $dbUsr ........... user login credentials, usually 'root'
; $dbPwd ........... passwords for MySQL accounts
; $dbSrv ........... MySQL server, 127.0.0.1 for localhost
; $sMySqlPath ...... full path to MySQL bin directory
; $sSytemDbs ....... list of databases created during installation MySql app
 
; use this constants in variable $export_defs:
Const $cEXPORT_DB_SYSTEMS_ONLY = 1 ; export default databases (e.g. XAMPP default databases)
Const $cEXPORT_DB_NON_SYSTEMS = 2 ; export user-created databases (e.g. all non XAMPP default dbs)
Const $cEXPORT_DB_ALL_DATABASES = 4 ; export all databases
Const $cEXPORT_DB_CUSTOM_DATABASES = 8 ; export selected databases (e.g. 'Drupal' database only)
 
Const $cEXPORT_TO_SINGLE_FILE = 128 ; export databases as one .sql file
Const $cEXPORT_AS_SEPARATE_FILES = 256 ; export each stored database as separate .sql file
 
;=== user definition ===================================================>>
Local $export_defs = $cEXPORT_DB_CUSTOM_DATABASES + $cEXPORT_AS_SEPARATE_FILES
;Local $export_defs = $cEXPORT_DB_NON_SYSTEMS + $cEXPORT_TO_SINGLE_FILE
Local $custom_dbs = "drupal" ; as separator use comma, e.g. "drupal, joomla"
Local $export_path = "E:\Backup\FullBackup\Aplikace\MySQL"
Local $dbUsr = "root"
Local $dbPwd = "123456"
Local $dbSrv = "127.0.0.1"
Local $sMySqlPath = "C:\xampp\mysql\bin\"
Local $sSytemDbs = "cdcol, information_schema, mysql, performance_schema , phpmyadmin, test, webauth"
;=== user definition ==== (Do not change anything below this line) ====<<
 
$export_path = StringRegExpReplace($export_path, "[\\/]+\z", "") & "\"
$sMySqlPath = StringRegExpReplace($sMySqlPath, "[\\/]+\z", "") & "\"
Local $sMySqlExe = FileGetShortName($sMySqlPath & "mysql.exe")
Local $sMySqlDmpExe = FileGetShortName($sMySqlPath & "mysqldump.exe")
Local $sFormat = "%s -u %s -p%s -h%s %s -e ""show databases"" -s -N"
Local $sExtCmd = StringFormat($sFormat, $sMySqlExe, $dbUsr, $dbPwd, $dbSrv)
Local $aSytemDbs = StringSplit(StringStripWS($sSytemDbs, 8), ",", 2)
Const $2L = @LF & @LF
 
If FileExists($sMySqlExe) <> 1 Then
 MsgBox(8240, "MySql.exe not found", "The mysql.exe not found!" & $2L & _
 "The path '$export_path' is probably not being set properly.")
 Exit
EndIf
 
; run in cmd
Local $CMD = Run(@ComSpec & " /c " & $sExtCmd, "", @SW_HIDE, $STDERR_CHILD+$STDOUT_CHILD)
ProcessWaitClose($CMD)
Local $sMsg = StdoutRead($CMD)
Local $sErr = StderrRead($CMD)
 
; if an error in mysql.exe (eg. server is not running)
If StringInStr($sErr, "ERROR") <> 0 Then
 MsgBox(8208, "Error", $sErr)
 Exit
EndIf
If StringLen($sMsg) = 0 Then
 MsgBox(8208, "Error", "Failed to get databases names")
 Exit
EndIf
 
; read all installed databases to $aAllDbs array
Local $aAllDbs = StringSplit($sMsg, Chr(13), 2)
For $i = UBound($aAllDbs) - 1 To 0 Step -1
 $aAllDbs[$i] = StringStripWS($aAllDbs[$i],3)
 If StringLen($aAllDbs[$i]) = 0 Then
 _ArrayDelete($aAllDbs, $i)
 EndIf
Next
 
; move requested names of databases to $aDbs array
Select
 Case BitAND($export_defs, $cEXPORT_DB_SYSTEMS_ONLY) <> 0
 $aDbs = $aSytemDbs
 Local $sResult = fncItemsInArray($aDbs, $aAllDbs)
 If @error Then
 MsgBox(8240, "Error", "Defined system database name '" & $sResult & "' not found!")
 Exit
 EndIf
 Case BitAND($export_defs, $cEXPORT_DB_NON_SYSTEMS) <> 0
 $aDbs = fncArrayExclude($aAllDbs, $aSytemDbs)
 Case BitAND($export_defs, $cEXPORT_DB_ALL_DATABASES) <> 0
 $aDbs = $aAllDbs
 Case BitAND($export_defs, $cEXPORT_DB_CUSTOM_DATABASES) <> 0
 $aDbs = StringSplit(StringStripWS($custom_dbs, 8), ",", 2)
 Local $sResult = fncItemsInArray($aDbs, $aAllDbs)
 If @error Then
 MsgBox(8240, "Error", "Defined custom database name '" & $sResult & "' not found!")
 Exit
 EndIf
EndSelect
 
; export
Local $sOutFile
Local $sFileFirstPart = $export_path & @YEAR & @MON & @MDAY & "." & @HOUR & @MIN & @SEC
$sFormat = "%s --lock-all-tables -u %s -p%s -h%s %s > " & """" & "%s" & """"
Select
 Case BitAND($export_defs, $cEXPORT_TO_SINGLE_FILE) <> 0
 $sOutFile = FileGetShortName($sFileFirstPart & "_" & _ArrayToString($aDbs, ",") & ".sql")
 $sExtCmd = StringFormat($sFormat, $sMySqlDmpExe, $dbUsr, $dbPwd, $dbSrv, "-B " & _
 _ArrayToString($aDbs, " "), $sOutFile)
 $CMD = RunWait(@ComSpec & " /c " & $sExtCmd, "", @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD)
 If FileExists($sOutFile) = 0 Then
 MsgBox(8208, "Error", "An error occurring during the export..." & $2L & "databases: " & _
 _ArrayToString($aDbs, ", ") & @LF & "destination: " & $sOutFile)
 Exit
 EndIf
 
 Case BitAND($export_defs, $cEXPORT_AS_SEPARATE_FILES) <> 0
 For $x = 0 To UBound($aDbs) - 1
 $sOutFile = FileGetShortName($sFileFirstPart & "_" & $aDbs[$x] & ".sql")
 $sExtCmd = StringFormat($sFormat, $sMySqlDmpExe, $dbUsr, $dbPwd, $dbSrv, $aDbs[$x], $sOutFile)
 $CMD = RunWait(@ComSpec & " /c " & $sExtCmd, "", @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD)
 If FileExists($sOutFile) = 0 Then
 MsgBox(8208, "Error", "An error occurring during the export..." & $2L & "database: " & _
 $aDbs[$x] & @LF & "destination: " & $sOutFile)
 Exit
 EndIf
 Next
 EndSelect
 
; final msg
$sFormat = "%s database%s was exported:%s%s%sTo destination:%s%s"
$sMsg = StringFormat($sFormat, UBound($aDbs), _iIf(UBound($aDbs) > 1, "s", ""), $2L, "- " & _
 _ArrayToString($aDbs, @LF & "- "), $2L, $2L, $export_path)
MsgBox(8256, "Done", $sMsg)
 
Exit
; -------------------------------------------------------------------
; Check if all items from $aSrc are included in $aCmp
; -------------------------------------------------------------------
Func fncItemsInArray($aSrc, $aCmp)
 Local $bFound, $i, $j
 For $i = 0 To UBound($aSrc) - 1
 $bFound = False
 For $j = 0 To UBound($aCmp) - 1
 If $aSrc[$i] = $aCmp[$j] Then
 $bFound = True
 ExitLoop
 EndIf
 Next
 If $bFound = False Then
 SetError(1)
 Return $aSrc[$i]
 EndIf
 Next
 Return 1
EndFunc ;==>> fncItemsInArray
 
; -------------------------------------------------------------------
; Exclude items from array based on another array
; $iFirstIdx1: ... first index of $aAll
; $iFirstIdx2: ... first index of $aExclude
; -------------------------------------------------------------------
Func fncArrayExclude($aAll, $aExclude, $iFirstIdx1=0, $iFirstIdx2=0)
 Local $bFound, $i, $j, $aResult[1]
 For $i = $iFirstIdx1 To UBound($aAll) - 1
 $bFound = False
 For $j = $iFirstIdx2 To UBound($aExclude) - 1
 If $aAll[$i] = $aExclude[$j] Then
 $bFound = True
 ExitLoop
 EndIf
 Next
 If $bFound = False Then
 If StringLen($aResult[0]) <> 0 Then
 ReDim $aResult[UBound($aResult) + 1]
 EndIf
 $aResult[UBound($aResult)-1] = $aAll[$i]
 EndIf
 Next
 Return $aResult
EndFunc ; ==>> fncArrayExclude
 
; -------------------------------------------------------------------
; _Iif from MISC
; -------------------------------------------------------------------
Func _Iif($fTest, $vTrueVal, $vFalseVal)
 If $fTest Then
 Return $vTrueVal
 Else
 Return $vFalseVal
 EndIf
EndFunc ;==>_Iif

简单汉化一下

;很多人备份本地主机上MYSQL数据库一般通过phpMyAdmin,一个很简单的AU3脚本通过命令来备份本机数据库。现在我给出源码供大家参考……

可以选择备份所有或者选定的数据库到一个或单独的SQL文件,

例如:输出文件:20130406.022354_drupal test.sql
或者单独输出文件:20130406.022354_drupal.sql ,20130406.022354_test.sql

#include <Array.au3>
#include <Constants.au3>
 
; ------------------------------------------------------------------------
; 本机备份MySQL数据库
; ------------------------------------------------------------------------
; 定义和意义:
; $export_defs ..... 结合两个常量: $cEXPORT_DB + ($cEXPORT_TO_... 和 $cEXPORT_AS_...)
; 例如[ $cEXPORT_DB_ALL_DATABASES + $cEXPORT_TO_SINGLE_FILE ] => 所有DBS导出到一个文件
; $custom_dbs ...... 用户创建的数据库。使用逗号分隔,例如“Drupal的,joomla
; $export_path ..... 导出目的地文件夹
; $dbUsr ........... 用户登录账号,通常是'root'
; $dbPwd ........... MySQL登陆密码
; $dbSrv ........... MySQL 服务器, 127.0.0.1 代表本机
; $sMySqlPath ...... MySQL的bin目录的完整路径
; $sSytemDbs ....... MySql安装过程中创建的默认数据库
 
; 使用这个常数变量$export_defs:
Const $cEXPORT_DB_SYSTEMS_ONLY = 1 ; 默认导出数据库(如XAMPP默认数据库)
Const $cEXPORT_DB_NON_SYSTEMS = 2 ;导出用户创建的数据库(例如,所有非XAMPP默认DBS)
Const $cEXPORT_DB_ALL_DATABASES = 4 ; 导出所有数据库
Const $cEXPORT_DB_CUSTOM_DATABASES = 8 ; 导出选定的数据库 (例如仅导出'Drupal' 数据库)
 
Const $cEXPORT_TO_SINGLE_FILE = 128 ; 导出数据库为sql文件
Const $cEXPORT_AS_SEPARATE_FILES = 256 ; 每个存储的数据库导出sql文件区分开来
 
;=== 用户定义 ===================================================>>
Local $export_defs = $cEXPORT_DB_CUSTOM_DATABASES + $cEXPORT_AS_SEPARATE_FILES
;Local $export_defs = $cEXPORT_DB_NON_SYSTEMS + $cEXPORT_TO_SINGLE_FILE
Local $custom_dbs = "drupal" ; 多个数据库使用逗号分隔,例如 "drupal, joomla"
Local $export_path = "E:\Backup\FullBackup\Aplikace\MySQL"
Local $dbUsr = "root"
Local $dbPwd = "123456"
Local $dbSrv = "127.0.0.1"
Local $sMySqlPath = "C:\xampp\mysql\bin\"
Local $sSytemDbs = "cdcol, information_schema, mysql, performance_schema , phpmyadmin, test, webauth"
;=== 用户定义 ==== (这条线之下,什么都不要改) ====<<
 
$export_path = StringRegExpReplace($export_path, "[\\/]+\z", "") & "\"
$sMySqlPath = StringRegExpReplace($sMySqlPath, "[\\/]+\z", "") & "\"
Local $sMySqlExe = FileGetShortName($sMySqlPath & "mysql.exe")
Local $sMySqlDmpExe = FileGetShortName($sMySqlPath & "mysqldump.exe")
Local $sFormat = "%s -u %s -p%s -h%s %s -e ""show databases"" -s -N"
Local $sExtCmd = StringFormat($sFormat, $sMySqlExe, $dbUsr, $dbPwd, $dbSrv)
Local $aSytemDbs = StringSplit(StringStripWS($sSytemDbs, 8), ",", 2)
Const $2L = @LF & @LF
 
If FileExists($sMySqlExe) <> 1 Then
 MsgBox(8240, "MySql.exe不存在", "mysql.exe不存在!" & $2L & _
 "检查路径变量 '$export_path' 设置不正确.")
 Exit
EndIf
 
; 运行CMD 
Local $CMD = Run(@ComSpec & " /c " & $sExtCmd, "", @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD)
ProcessWaitClose($CMD)
Local $sMsg = StdoutRead($CMD)
Local $sErr = StderrRead($CMD)
 
; 如果是 mysql.exe发生错误 (例如 服务没有启动)
If StringInStr($sErr, "ERROR") <> 0 Then
 MsgBox(8208, "错误", $sErr)
 Exit
EndIf
If StringLen($sMsg) = 0 Then
 MsgBox(8208, "Error", "无法获得数据库名称")
 Exit
EndIf
 
; 读取全部已安装的数据库到数组$aAllDbs
Local $aAllDbs = StringSplit($sMsg, Chr(13), 2)
For $i = UBound($aAllDbs) - 1 To 0 Step -1
 $aAllDbs[$i] = StringStripWS($aAllDbs[$i], 3)
 If StringLen($aAllDbs[$i]) = 0 Then
 _ArrayDelete($aAllDbs, $i)
 EndIf
Next
 
; 移动选定数据库名到$aDbs数组
Select
 Case BitAND($export_defs, $cEXPORT_DB_SYSTEMS_ONLY) <> 0
 $aDbs = $aSytemDbs
 Local $sResult = fncItemsInArray($aDbs, $aAllDbs)
 If @error Then
 MsgBox(8240, "错误", "定义的系统数据库名称 '" & $sResult & "' 不存在!")
 Exit
 EndIf
 Case BitAND($export_defs, $cEXPORT_DB_NON_SYSTEMS) <> 0
 $aDbs = fncArrayExclude($aAllDbs, $aSytemDbs)
 Case BitAND($export_defs, $cEXPORT_DB_ALL_DATABASES) <> 0
 $aDbs = $aAllDbs
 Case BitAND($export_defs, $cEXPORT_DB_CUSTOM_DATABASES) <> 0
 $aDbs = StringSplit(StringStripWS($custom_dbs, 8), ",", 2)
 Local $sResult = fncItemsInArray($aDbs, $aAllDbs)
 If @error Then
 MsgBox(8240, "错误", "定义的自定义数据库名称 '" & $sResult & "' 不存在!")
 Exit
 EndIf
EndSelect
 
; 导出
Local $sOutFile
Local $sFileFirstPart = $export_path & @YEAR & @MON & @MDAY & "." & @HOUR & @MIN & @SEC
$sFormat = "%s --lock-all-tables -u %s -p%s -h%s %s > " & """" & "%s" & """"
Select
 Case BitAND($export_defs, $cEXPORT_TO_SINGLE_FILE) <> 0
 $sOutFile = FileGetShortName($sFileFirstPart & "_" & _ArrayToString($aDbs, ",") & ".sql")
 $sExtCmd = StringFormat($sFormat, $sMySqlDmpExe, $dbUsr, $dbPwd, $dbSrv, "-B " & _
 _ArrayToString($aDbs, " "), $sOutFile)
 $CMD = RunWait(@ComSpec & " /c " & $sExtCmd, "", @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD)
 If FileExists($sOutFile) = 0 Then
 MsgBox(8208, "错误", "导出数据库错误..." & $2L & "数据库: " & _
 _ArrayToString($aDbs, ", ") & @LF & "目标地址: " & $sOutFile)
 Exit
 EndIf
 
 Case BitAND($export_defs, $cEXPORT_AS_SEPARATE_FILES) <> 0
 For $x = 0 To UBound($aDbs) - 1
 $sOutFile = FileGetShortName($sFileFirstPart & "_" & $aDbs[$x] & ".sql")
 $sExtCmd = StringFormat($sFormat, $sMySqlDmpExe, $dbUsr, $dbPwd, $dbSrv, $aDbs[$x], $sOutFile)
 $CMD = RunWait(@ComSpec & " /c " & $sExtCmd, "", @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD)
 If FileExists($sOutFile) = 0 Then
 MsgBox(8208, "错误", "导出数据库错误..." & $2L & "数据库: " & _
 $aDbs[$x] & @LF & "目标地址: " & $sOutFile)
 Exit
 EndIf
 Next
EndSelect
 
; final msg
$sFormat = "%s database%s was exported:%s%s%sTo destination:%s%s"
$sMsg = StringFormat($sFormat, UBound($aDbs), _Iif(UBound($aDbs) > 1, "s", ""), $2L, "- " & _
 _ArrayToString($aDbs, @LF & "- "), $2L, $2L, $export_path)
MsgBox(8256, "完成", $sMsg)
 
Exit
; -------------------------------------------------------------------
; 从$aSrc数组检查所有项目是否包含在$aCmp数组
; -------------------------------------------------------------------
Func fncItemsInArray($aSrc, $aCmp)
 Local $bFound, $i, $j
 For $i = 0 To UBound($aSrc) - 1
 $bFound = False
 For $j = 0 To UBound($aCmp) - 1
 If $aSrc[$i] = $aCmp[$j] Then
 $bFound = True
 ExitLoop
 EndIf
 Next
 If $bFound = False Then
 SetError(1)
 Return $aSrc[$i]
 EndIf
 Next
 Return 1
EndFunc ;==>fncItemsInArray
 
; -------------------------------------------------------------------
;清理数组
; $iFirstIdx1: ... 第一个 $aAll索引
; $iFirstIdx2: ... 第一个 $aExclude索引
; -------------------------------------------------------------------
Func fncArrayExclude($aAll, $aExclude, $iFirstIdx1 = 0, $iFirstIdx2 = 0)
 Local $bFound, $i, $j, $aResult[1]
 For $i = $iFirstIdx1 To UBound($aAll) - 1
 $bFound = False
 For $j = $iFirstIdx2 To UBound($aExclude) - 1
 If $aAll[$i] = $aExclude[$j] Then
 $bFound = True
 ExitLoop
 EndIf
 Next
 If $bFound = False Then
 If StringLen($aResult[0]) <> 0 Then
 ReDim $aResult[UBound($aResult) + 1]
 EndIf
 $aResult[UBound($aResult) - 1] = $aAll[$i]
 EndIf
 Next
 Return $aResult
EndFunc ;==>fncArrayExclude
 
; -------------------------------------------------------------------
;三元判断
; -------------------------------------------------------------------
Func _Iif($fTest, $vTrueVal, $vFalseVal)
 If $fTest Then
 Return $vTrueVal
 Else
 Return $vFalseVal
 EndIf
EndFunc ;==>_Iif
永恒心锁,版权所有丨如未注明,均为转载丨本网站采用BY-NC-SA协议进行授权

转载请注明:永恒心锁-分享互联网 » 备份本地MySql数据库

您必须 登录 才能发表评论!