Jump to content
Sign in to follow this  
Logman

Backup MySql databases on localhost

Recommended Posts

Logman

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

#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

Export_MySql_Databases_v1.au3

Edited by Logman

Share this post


Link to post
Share on other sites
Emiel Wieldraaijer

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

Best regards,Emiel Wieldraaijer

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Similar Content

    • dangr82
      By dangr82
       
      I have created this function for a database, but I can not make it work. I always have two error messages: "not an error" ... and the file created, in the script directory, does not contain anything.
      Global $sDBName = "Hen.db" Func DatabaseTable() Local $sConnDB _SQLite_Startup() If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit -1 EndIf $sConnDB = _SQLite_Open($sDBName) If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't Load Database!") Exit -1 EndIf If Not _SQLite_Exec($sDBName, 'CREATE TABLE Animal ("Name", "Age");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec($sDBName, 'INSERT INTO Animale VALUES ("Charlie","5");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) _SQLite_Close($sConnDB) _SQLite_Shutdown() EndFunc  
    • Case85
      By Case85
      Hi for all!
       
      After a long time I wasted to find the best way to connect to any external MySQL server, I'm ready to hire "C" or "C++" developer to use MySQL connector dll file for create a stable way to connect to a MySQL server.
      I already tried to use the UDF "miniSQL - A simple Standalone PDO-syntax-like MySQL-library with AutoitObject" which is working perfect (this way what I looking for), but unfortunately after 1-2 hours usage failed and exit the script.
      I plan to use the MySQL connector heavily and continuously on long time.
      The developed connector must be have a small memory footprint.
      My budget is moderated, I need this connector quick as possible, please contact with me in PM for more details.
      Thank you for all.
    • argumentum
      By argumentum
      ..let's say I store in a mysql column "[1,2,11,12,123]".
      How can I make s query to get all those rows that is 1, or is 12, etc ?
      I'm using  mysqlnd 5.0.11-dev - 20120503 
      Any advise on what technique I should use is welcomed
      Thanks
      PS: ..I'm adding a comma to each end, as in  [,1,2,11,12,123,] and will search with like %,12,% .
      Not pretty but I'm in a hurry and can't come up with a better solution =/
    • Marc
      By Marc
      Profiler - yet another backup script for Firefox and Thunderbird profiles
      It automatically detects the last recently used profile directories of Firefox and Thunderbird (installed versions, no portables).
      Clicking the Backup-Button creates a complete Backup of the found profiles with a timestamp.
      Restore deletes the according profile dir and extracts the content of the selected archive into the profile directory.
      Uses Winrar as compression tool, but feel free to change it to your needs
      Best regards
      Marc

      Profiler.au3
    • sc4ry
      By sc4ry
      Dear all,
      I already have a formula which will select doublicates, but sometimes there might be entries with additional information starting after "-", which I now want to trim away.
      Current formula is:
      Select DISTINCT a.title, a.oid, a.size, a.disk from DB a, DB b where (a.title like b.title and a.oid <> b.oid)" & $ActiveDisks_Filter & " AND (a.comment <> 'Delete' AND b.comment <> 'Delete') Order by a.title ASC, a.size DESC; testwise I want to add a simple solution like left, but afterwards I get an error but I do not know why.
      Select DISTINCT a.title, a.oid, a.size, a.disk from DB a, DB b where (left(a.title,15) like b.title and a.oid <> b.oid) Order by a.title ASC, a.size DESC; Error: near "(": syntax error: Finally I would like to insert intstr to search for "-" but currently I am not able to extend my current code =/
      Thanks a lot for your help.
      EDIT: hey, I managed it, left was not working also it was somehow shown that the tool knowed the comment. I did it now with substr+instr
      (substr(a.title, 1, instr(a.title, ' - '))  
×