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

    • Ahile07
      SQL lack of knowledge
      By Ahile07
      Hello guys,
      maybe some of you can help me. I manage to connect with sql database and use a variable to search for different things when i do the select from database. My problem is that i'm reading the database with an MsgBox to see the values. But sometimes for the thing i'm filtering the dabase for it shows me different values and i only one to select one of it and move forward with my script. Is there any chance to read the database...view it...and in front of each value to have a thick box to select with value i want for the line.
      After i select the the line value i want to stock the values from each column in different variables and then i want to send them as values to write in another database.
      Is this possible?
      Hope any of you can help me in this matter
      Apreciate guys.
    • 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
       
       
      EDIT: 2016-06-03
      Below some interesting topics about databases:
       
       
      EDIT 2016/07/04:
      For more info about ADO look here:
      https://www.autoitscript.com/wiki/ADO
       
       
    • Chimp
      How to view only rows with duplicate values in a table
      By Chimp
      Referring to a question posted here, this is a possible SQL query that should do:
      supposed you have an sqlite table named 'multimedia' with a column named 'sha1_hash', and you want to see only rows with the 'sha1_hash' value that appears more than one time within the table, you could use an SQL query like this:
      SELECT sha1_hash, COUNT(sha1_hash) AS sha1_sum FROM multimedia GROUP BY sha1_hash HAVING sha1_sum>1 ORDER BY sha1_sum DESC; this query should show only records with the value of sha1_hash that appears more times also in other records, showing it only one time, with a column indicating how many occurrences are present, ordered from the most recurring to the least recurring one.
      I don't know if this query can be simplified a bit...?,
      .... if there is a simpler way I would like some hint,
      Thanks
    • Chimp
      Best way to store Dates in SQLite?
      By Chimp
      Hi
      I ask for generic advice on what field format is best to use to store dates in a database (SQLite).
      if is a better choice to stored separately year, month and day into 3 separate fields or use one field for a complete date-time group?
      I have to store information of this kind:
      a person is assigned to a job for a certain day (one day only)
      or also
      a person is assigned to a job from a day to another day (a range of days)
      then i have to query the database for a certain day so to know who is working to a certain job in a specific day.
      Well, in the case of ranges of days (for example Mr. Bean works to JOB1 from May/15 to May/20) I have to store one record for each day of the range (6 record in this case) or use only one record with both dates in 2 fields of the same record?
      the SQL query should 'ask' who is working on a specific day (even for days in the middle of the ranges).
      My doubt is, what's the best way to store ranges of dates (as in the above example), so to be facilitated in the retrieval of those informations.
      I'm afraid I'm not been clear, even if I did my best to be, anyway .... any suggestion is welcome..
      Thank You
    • mLipok
      PostgreSQL Video tutorials
      By mLipok
      Currently I'm working on MS SQL >> PostgreSQL migration.
      Here are some of interesting Video tutorials about PostgreSQL