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

    • Valnurat
      By Valnurat
      I hope my title is good enough.
      I'm using the ADO UDF and I have question regarding editing SQL records with this UDF.
      The owner of the UDF suggested an idea, but maybe there is another trix.
    • Paranthaman
      By Paranthaman
      Hi,
      I want to click a link by the element ID through IEGetObjById.
      <!DOCTYPE html> <html> <body> <button type="button" id="Random-1-ID" onclick="alert('Hello world!')"></button> </body> </html> I intend to click the button with ID"Random-1-ID". But on every refresh the ID changes to next number like "Random-2-ID" "Random-3-ID"
       
      The code i which i wrote for this function is
      #include <IE.au3> #include <MsgBoxConstants.au3> Local $oIE = _IECreate("I:\Documents\1. Work\Automation\My codes\Collections\11. Clicking button by Value and ID\button.html") Local $oDiv = _IEGetObjById($oIE, "Random-1-ID") _IEAction($oDiv, "click") _IELoadWait($oIE) So can anyone help me to alter this code like it clicks for every ID in format "Random-%-ID"
    • macran
      By macran
      I use mysql.au3 to connect mysql database .  If restart the server ,the client how to reconnect mysql?
      The function prototype is
      _MySQL_Options($MySQL_ptr, $option, $arg = 0, $type = "ptr")
      so,
      _MySQL_Options($MysqlConn,$MYSQL_OPT_RECONNECT, ?, ?)
      pls help me,thanks!
       
       
       
    • Trong
      By Trong
      DB1:
      CREATE TABLE [dbo].[Item]( [ItemID] [nchar](10) NOT NULL, [Money] [bigint] NOT NULL, CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED ( [ItemID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Account]( [Index] [int] IDENTITY(1,1) NOT NULL, [AccountID] [nchar](10) NOT NULL, [AccountName] [int] NOT NULL, [ItemList] [int] NOT NULL, ) ON [PRIMARY] GO CREATE TABLE [dbo].[Money]( [AccountID] [nchar](10) NOT NULL, [Money] [bigint] NOT NULL, CONSTRAINT [PK_Money] PRIMARY KEY CLUSTERED ( [AccountID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO  
      DB2:
      CREATE TABLE [dbo].[Item]( [ItemID] [nchar](10) NOT NULL, [Money] [bigint] NOT NULL, [ItemName] [bigint] NOT NULL, [MoneyType] [bigint] NOT NULL, CONSTRAINT [Item] PRIMARY KEY CLUSTERED ( [ItemID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Account]( [Index] [int] IDENTITY(1,1) NOT NULL, [AccountID] [nchar](10) NOT NULL, [AccountName] [int] NOT NULL, [ItemList] [int] NOT NULL, ) ON [PRIMARY] GO CREATE TABLE [dbo].[Money]( [AccountID] [nchar](10) NOT NULL, [Money] [bigint] NOT NULL, [MoneyType] [bigint] NOT NULL, CONSTRAINT [Money] PRIMARY KEY CLUSTERED ( [AccountID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO  
      Compare and merge database.
      alter table [Item] add [ItemName] bigint not null default(0) alter table [Item] add [MoneyType] bigint not null default(0) alter table [Money] add [MoneyType] bigint not null default(0) Please help automate code AutoIt to generation new code for large sql file.
      Thanks
    • WoodGrain
      By WoodGrain
      Hi guys,
      This post was originally going to be a question on how to fix this issue but as I ended up figuring it out I thought I'd post it here for others that have the same issue.
      So you've downloaded and extracted the latest version of the SQLite dll files etc into the same directory as your SQLite script but it's failing at _SQLite_Startup()?
      What you need to do, that I couldn't see anywhere in the documentation, to fix the issue is rename the dll files from (for example) "sqlite3_301500000.dll" to "sqlite3.dll" and "sqlite3_x64_301500000.dll" to "sqlite3_x64.dll".
      Fixed my issues instantly!
      Hope it can help others too.
      Cheers.