#194504 - 2009-06-30 04:56 PM
Dump (Backup) MySQL 5.1 Databases
|
Witto
MM club member
Registered: 2004-09-29
Posts: 1828
Loc: Belgium
|
I just created a script to dump (backup) MySQL 5.1 databases to separate compressed files. All required and optional parameters are provided via an INI-file. Download bzip2 for Windows for optional compression. The DirPlus() UDF is required. Here is more information about Backing Up and Restoring Your MySQL Database. MySQL5.1Dump.ini
; ===========================================================================================
;
; Ini Information
;
; Title: MySQL 5.1 Dump
; Author: Wim Rotty
; Description: Backup (Dump) all databases on a MySQL 5.1 server to separate files
; MySQLDump is used with --add-drop-table
;
; ===========================================================================================
;INI file to be used with backup or dump script for MySQL 5.1 databases
;Only 1 section [MySQL] exists
;UserName :Required
; UserName to logon to the MySQL Database, i.e. "root"
;Password :Required
; Password for UserName to logon to MySQL Database
;BackupPath :Optional
; Path were backup files will be stored
; If not provided, the script directory will be used
;BZip :Optional
; Compression is supported by use of BZip
; Full Path and filename to the BZip executable
[MySQL]
UserName=root
Password=MyVerySecretPassword0123456789!?
BackupPath=C:\MySQLBackup\DB
BZip=C:\MySQLBackup\bzip2-105-x86-win32.exe
MySQL5.1Dump.kix
; ===========================================================================================
;
; Script Information
;
; Title: MySQL 5.1 Dump
; Author: Wim Rotty
; Description: Backup (Dump) all databases on a MySQL 5.1 server to separate files
; MySQLDump is used with --add-drop-table
;
; ===========================================================================================
;Script Options
If Not @LOGONMODE
Break On
Else
Break Off
EndIf
Dim $RC
$RC = SetOption("Explicit", "On")
$RC = SetOption("NoMacrosInStrings", "On")
$RC = SetOption("NoVarsInStrings", "On")
If @SCRIPTEXE = "KIX32.EXE"
$RC = SetOption("WrapAtEOL", "On")
EndIf
;Declare variables
Dim $MySQLData
Dim $MySQLDump
Dim $INI
Dim $UserName, $Password
Dim $BZip
Dim $BackupPath
Dim $MySQLDumpBatchJob
Dim $Dirs, $Dir
Dim $Handle
;Initialize variables
$MySQLData=ReadValue("HKEY_LOCAL_MACHINE\SOFTWARE\MySQL AB\MySQL Server 5.1","DataLocation")+"data\"
If @error
Exit @error
EndIf
$MySQLDump=ReadValue("HKEY_LOCAL_MACHINE\SOFTWARE\MySQL AB\MySQL Server 5.1","Location")+"bin\mysqldump.exe"
If @error
Exit @error
EndIf
$INI = @SCRIPTDIR+'\'+Join(Split(@SCRIPTNAME,'.'),'.',UBound(Split(@SCRIPTNAME,'.')))+'.ini'
If Exist($INI) = 0
Exit 2
EndIf
$UserName=ReadProfileString($INI,'MySQL','UserName')
If @ERROR
Exit @ERROR
EndIf
$Password=ReadProfileString($INI,'MySQL','Password')
If @ERROR
Exit @ERROR
EndIf
$BackupPath=ReadProfileString($INI,'MySQL','BackupPath')
If $BackupPath = ''
$BackupPath=@SCRIPTDIR+'\'
Else
If Not Right($BackupPath,1) = '\'
$BackupPath=$BackupPath+'\'
EndIf
If Not Exist($BackupPath)
$BackupPath=@SCRIPTDIR+'\')
EndIf
$BZip=ReadProfileString($INI,'MySQL','BZip')
If Not Exist($BZip)
$BZip = ''
EndIf
$MySQLDumpBatchJob=$BackupPath+Join(Split(@SCRIPTNAME,'.'),'.',UBound(Split(@SCRIPTNAME,'.')))+'.cmd'
If Exist($MySQLDumpBatchJob)
Exit 5
EndIf
;Code
$Dirs = DIRPlus($MySQLData, '/AD')
$Handle = FreeFileHandle()
$RC = Open($Handle,$MySQLDumpBatchJob,5)
For Each $Dir in $Dirs
If Not $BZip = ''
$RC=WriteLine($Handle,'"'+$MySQLDump+'" --add-drop-table -u"'+$UserName + '" -p"'+$Password+
'" "'+$Dir.Name+'" | "'+ $BZip + '" -c > "'+$BackupPath+$Dir.Name+'.sql.bz2"'+@CRLF)
Else
$RC=WriteLine($Handle,'"'+$MySQLDump+'" --add-drop-table -u"'+$UserName + '" -p"'+$Password+
'" "'+$Dir.Name+'" > "'+$BackupPath+$Dir.Name+'.sql"'+@CRLF)
EndIf
Next
$RC=Close($Handle)
$Dirs=''
$Dir=''
Shell '"%ComSpec%" /C "'+$MySQLDumpBatchJob+'"'
Del $MySQLDumpBatchJob /h /s /c /f
;Personal UDF Section
;UDF Section
;Don't forget to add the DirPlus() UDF
|
Top
|
|
|
|
#194520 - 2009-07-01 12:51 PM
Re: Dump (Backup) MySQL 5.1 Databases
[Re: NTDOC]
|
Witto
MM club member
Registered: 2004-09-29
Posts: 1828
Loc: Belgium
|
Yep Doc, I found it somewhere here. But I wanted a script that most probably does not have to be changed anymore in case extra databases are added. I presume almost anyone can copy this script, modify the ini file, schedule a task in the server's scheduler and the job is finished.
|
Top
|
|
|
|
#194543 - 2009-07-02 09:27 AM
Re: Dump (Backup) MySQL 5.1 Databases
[Re: NTDOC]
|
Witto
MM club member
Registered: 2004-09-29
Posts: 1828
Loc: Belgium
|
Maybe a good idea, but I don't know if all versions of MySQL use these registry keys and keynames to indicate the "Location" and the "DataLocation" directories?
$MySQLData=ReadValue("HKEY_LOCAL_MACHINE\SOFTWARE\MySQL AB\MySQL Server 5.1","DataLocation")+"data\"
$MySQLDump=ReadValue("HKEY_LOCAL_MACHINE\SOFTWARE\MySQL AB\MySQL Server 5.1","Location")+"bin\mysqldump.exe"
If yes, I could query for a key starting with "MySQL Server".
|
Top
|
|
|
|
#194573 - 2009-07-03 04:07 PM
Re: Dump (Backup) MySQL 5.1 Databases
[Re: NTDOC]
|
Witto
MM club member
Registered: 2004-09-29
Posts: 1828
Loc: Belgium
|
Some questions about MySQL and your suggestion to make this script a UDF
- Can --User and --Password be optional? I presume yes.
- Can more than one version of MySQL be installed on a host?
- I presume that if previous answer is "yes", I presume using different ports?
|
Top
|
|
|
|
Moderator: Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Ruud van Velsen, Mart
|
0 registered
and 557 anonymous users online.
|
|
|