Page 1 of 1 1
Topic Options
#194504 - 2009-06-30 04:56 PM Dump (Backup) MySQL 5.1 Databases
Witto Offline
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
 Code:
; ===========================================================================================
;
; 	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
 Code:
; ===========================================================================================
;
; 	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
#194519 - 2009-07-01 12:34 PM Re: Dump (Backup) MySQL 5.1 Databases [Re: Witto]
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11567
Loc: CA
I actually use a KiXtart script to backup the MySQL DB for Korg daily for quite a while now that I wrote. Not as fancy as your script probably. Thanks for sharing.
Top
#194520 - 2009-07-01 12:51 PM Re: Dump (Backup) MySQL 5.1 Databases [Re: NTDOC]
Witto Offline
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
#194531 - 2009-07-01 04:53 PM Re: Dump (Backup) MySQL 5.1 Databases [Re: Witto]
Witto Offline
MM club member
*****

Registered: 2004-09-29
Posts: 1828
Loc: Belgium
If you would use bzip2 in your script, you would see that your backup files shrink more than 1/9.
Top
#194537 - 2009-07-02 02:52 AM Re: Dump (Backup) MySQL 5.1 Databases [Re: Witto]
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11567
Loc: CA
Well if you want to expand on this and maybe make it a UDF then would probably need to use another lookup method for the registry for different versions of MySQL
Top
#194538 - 2009-07-02 02:55 AM Re: Dump (Backup) MySQL 5.1 Databases [Re: NTDOC]
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11567
Loc: CA
WinRAR does not have this limitation that BZIP2 does.

 Quote:
Because it supports (limited) recovery from media errors. If you are trying to restore compressed data from a backup tape or disk, and that data contains some errors, bzip2 may still be able to decompress those parts of the file which are undamaged.


There is also 7zip so for those that do want a free archive util there are a few out there. I just prefer RAR myself.

Top
#194541 - 2009-07-02 09:04 AM Re: Dump (Backup) MySQL 5.1 Databases [Re: NTDOC]
Witto Offline
MM club member
*****

Registered: 2004-09-29
Posts: 1828
Loc: Belgium
Do you want to say that I would better use WinRAR or 7-zip?
I would think it is an advantage that bzip2 can partially recover damaged files.
BZip2 can also create the compressed file via stdout. So it compresses "on the fly" while creating the SQL dumps.

Top
#194543 - 2009-07-02 09:27 AM Re: Dump (Backup) MySQL 5.1 Databases [Re: NTDOC]
Witto Offline
MM club member
*****

Registered: 2004-09-29
Posts: 1828
Loc: Belgium
 Originally Posted By: NTDOC
make it a UDF

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?
 Code:
$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
#194545 - 2009-07-02 11:24 AM Re: Dump (Backup) MySQL 5.1 Databases [Re: Witto]
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11567
Loc: CA
Just an idea if you wanted to investigate it further. RAR supports adding a recovery record so that even if the archive is damaged you can still recover it. I've used that feature on more than one file.

Yeah, many of the installation don't use the Registry and some Admins manually install it so that could be a bit more problematic detecting and determining where it is. If you do want to go further with it though I can probably help do some of the research on it.

Top
#194573 - 2009-07-03 04:07 PM Re: Dump (Backup) MySQL 5.1 Databases [Re: NTDOC]
Witto Offline
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
#194577 - 2009-07-04 12:40 PM Re: Dump (Backup) MySQL 5.1 Databases [Re: Witto]
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11567
Loc: CA
The user name can not be optional, password can be blank but not very safe so I'd not leave it as optional for either one myself.

I'm pretty sure you can run another version of MySQL on the box IF it was all done manually but not 100% sure. I'll try to test it some next week if I can.

Thanks.

Top
#195419 - 2009-08-14 04:57 PM Re: Dump (Backup) MySQL 5.1 Databases [Re: NTDOC]
Björn Offline
Korg Regular
*****

Registered: 2005-12-07
Posts: 950
Loc: Stockholm, Sweden.
Well, njae. Not optional perhaps if you don't manage to use LDAP for auth (ie ad :P - but never heard of that kind either ) - but, nah?. Yes, several can be installed, but for what purpose? Never heard or seen that kind of setup.
And no, not needing to use different ports - just different ip's. but yes - if on same ip - different ports. kinda basic.


Edited by Björn (2009-08-14 04:57 PM)
_________________________
as long as it works - why fix it?
If it doesn't work - kix-it!

Top
Page 1 of 1 1


Moderator:  Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Mart 
Hop to:
Shout Box

Who's Online
1 registered (Allen) and 191 anonymous users online.
Newest Members
thequeen, ameliaryan, shalomsalami, dkdssdk, Vroon
17639 Registered Users

Generated in 0.036 seconds in which 0.011 seconds were spent on a total of 14 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org