Sign in to follow this  
Followers 0
Logman

Backup MySql databases on localhost

2 posts in this topic

#1 ·  Posted (edited)

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



#2 ·  Posted (edited)

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  
Followers 0

  • Similar Content

    • Vivi
      Is it possible to post into the usenet with autoit ?
      By Vivi
      hey guys,
       
      is there a way to post into the "Usenet" with autoit ?
       
      I didn't found any resources about it online.
    • ronmage
      Autoit and remote SQL quarry
      By ronmage
      I am trying to get remote autoit to work with SQL. I know that I can use SQLLite however I have a server that I have SQL setup on already that I have used. I am using a SQL include “see attached”. However I can’t get it to work. Here is a simple snippet of code that I am using. I have looked in both google and here but can’t get anything working.  Was wondering if it is a problem with being on windows 10.

      #include <Array.au3> #include <SQL.au3> $IP = "192.168.1.100" $User = "user" $Pass = "pass" $DB = "Login" $out = _SQLConnect($IP,$DB,1,$User,$Pass) _ArrayDisplay($out) MsgBox(0,"",$out) Here is the error I am getting when it runs.

      --> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop "C:\Program Files (x86)\AutoIt3\Include\SQL.au3" (29) : ==> The requested action with this object has failed.: If $fAuthMode Then $oConn.Open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";") If $fAuthMode Then $oConn^ ERROR ->22:36:28 AutoIt3.exe ended.rc:1 +>22:36:28 AutoIt3Wrapper Finished. >Exit code: 1 Time: 16.16  
      SQL.au3
    • willichan
      SQL Query Help
      By willichan
      Hopefully someone a little more solid in SQL can give me a hand.
      I have a many-to-many-to-many setup.
      a.field1 a.field2 a.field3 x.afield1 x.bfield1 b.field1 b.field2 b.field3 y.bfield1 y.cfield1 c.field1 c.field2 c.field3 In essence, I need all a.field1 where c.field3="stringvalue".
      I could do it with multiple queries, and looping, but I am hoping there is a simpler, single query I can make.
      Thanks in advance for any help.
    • FMS
      autoit and using / connecting SQL DB
      By FMS
      Hello,

      I've made a script where this is a piece of, this because it will be a lit easyer to read and use for others iff solved.
      I am willing to learn the use of SQL / autoit connection but can't find anythin helpfull on the internet
      Or iff I find something it is a little dated.
      The following script is also build whit dated material.
      What mine consernens are is SQL injection and all the other security isseu's.
      I hope somebody can look at mine script and help me whit making this work.
      - Is this a good way to make a connection and check it or is there a better way?
      At this point I've an error :
       
      "D:\map\file.au3" (72) : ==> The requested action with this object has failed.: $adCN.Open ($constrim) $adCN^ ERROR >Exit code: 1 Time: 18.51 #include <ButtonConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <GUIListBox.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> $Form1 = GUICreate("Form1", 480, 100, 190, 320) $button1 = GUICtrlCreateButton("check and run", 128, 24, 113, 33) $Input_ip = GUICtrlCreateInput("127.0.0.1", 8, 16, 113, 21) $Input_usr = GUICtrlCreateInput("harry", 8, 40, 113, 21) $connection_label = GUICtrlCreateLabel("connection = ", 248, 16, 52, 17) $user_label = GUICtrlCreateLabel("username = ", 248, 40, 52, 17) $connection_status_label = GUICtrlCreateLabel("connection not checked", 304, 16, 190, 70) $user_status_label = GUICtrlCreateLabel("user not checked", 304, 40, 190, 17) GUISetState(@SW_SHOW) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $button1 $checked = check_connection_usr(GUICtrlRead($Input_ip),GUICtrlRead($Input_usr)) if $checked Then ;MsgBox($MB_SYSTEMMODAL, "function","true") get_username(GUICtrlRead($Input_ip),GUICtrlRead($Input_usr)) Else MsgBox($MB_SYSTEMMODAL, "function","an error occured") EndIf EndSwitch WEnd Func check_connection_usr($input_ip,$input_usr) GUICtrlSetData($connection_status_label, "checking") GUICtrlSetData($user_status_label, "checking") if $input_usr = "" then GUICtrlSetData($user_status_label, "no username!") Return False Else GUICtrlSetData($user_status_label, $input_usr & " will be checked") if $input_ip = "" then GUICtrlSetData($connection_status_label, "no IP or adres!") Return False else $check_ping = Ping($input_ip, 250) if not $check_ping Then GUICtrlSetData($connection_status_label, "error in ping") Return False Else GUICtrlSetData($connection_status_label, "ping = " & $check_ping & "ms.") return True EndIf EndIf EndIf EndFunc Func get_username($xIP,$usr) Local $ServerAddress = $xIP Local $ServerUserName = "root" Local $ServerPassword = "" Local $DatabaseName = "" $constrim="DRIVER={SQL Server};SERVER=" & $xIP & ";DATABASE=" & $DatabaseName & ";uid=" & $ServerUserName & ";pwd=" & $ServerPassword & ";" $adCN = ObjCreate ("ADODB.Connection") $adCN.Open ($constrim) MsgBox(0,"",$constrim ) if @error Then MsgBox(0, "ERROR", "Failed to connect to the database") Exit Else MsgBox(0, "Success!", "Connection to database successful!") EndIf $sQuery = "select * from users where username=" & $usr $result = $adCN.Execute($sQuery) MsgBox(0, "", $result.username( " = username" ).Value) ;---------------------------is this ok ? GUICtrlSetData($user_status_label, $result.username & " username present");---------------------------is this ok ? $adCN.Close ; ==> Close the database EndFunc  
       
    • mLipok
      ADO.au3 UDF - BETA - Support Topic
      By mLipok
      I want to present BETA Version of my ADO.au3 UDF.
      This is modifed version of _sql.au3 UDF.
       

      For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH
       
      This is first public release , and still is as BETA
       
       
      DOWNLOAD LINK (in download section): 
       

       
      Have fun,
      mLipok