Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

Backup MySql databases on localhost

mysql backup sql

  • Please log in to reply
1 reply to this topic

#1 Logman

Logman

    Seeker

  • Active Members
  • 38 posts

Posted 06 April 2013 - 12:36 AM

I was too lazy to frequent backups my database on localhost via phpMyAdmin so I wrote a very simple script to backup databases via command com. Maybe it will be useful to someone...

It backs up all or selected databases into one or separate sql files, e.g:

single file output:
20130406.022354_drupal,test.sql

separate files output:
20130406.022354_drupal.sql
20130406.022354_test.sql


Recommended php utility to import .sql files into MySql:
BigDump: Staggered MySQL Dump Importer

AutoIt         
#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


Attached File  Export_MySql_Databases_v1.au3   7.59KB   235 downloads

Edited by Logman, 06 April 2013 - 01:22 AM.








#2 Emiel Wieldraaijer

Emiel Wieldraaijer

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 711 posts

Posted 28 April 2013 - 07:54 PM

HI Logman,

Forgot to thank you for sharing

i myself use a modified script of Matt Moeller "Auto MySQL Backup For Windows Servers By Matt Moeller v.1.5"

Yours is more configurable with your $export_defs

Edited by Emiel Wieldraaijer, 28 April 2013 - 07:54 PM.

Best regards,Emiel WieldraaijerPosted Image





Also tagged with one or more of these keywords: mysql, backup, sql

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users