Friday, May 2, 2008

Backing up MySQL Database

How is it done:

mingle database is hosted on mySQL. To backup this database we use mysqldump utility which is invoked through a VB script. Earlier we were using "My SQL Administrator" utility, but it lacks some capabilities and due to which we saw that some of tables were missing in the backup. After testing we found that mysqldump is good for our purpose.

Here is how the VB script looks like

on error resume next

Dim TodaysFileName, BatchFileTxt

Set WshShell = WScript.CreateObject("WScript.Shell")

TodaysFileName = DatePart("yyyy", Now) & Lpad(DatePart("m", Now), "0", 2) & Lpad(DatePart("d", Now), "0", 2) & _
"_" & Lpad(DatePart("h", Now), "0", 2) & Lpad(DatePart("n", Now), "0", 2) & _
Lpad(DatePart("s", Now), "0", 2)

BatchFileTxt = "mysqldump -uroot -p" & CryptVBS("#^rT4", "mingle") & " mingle > ""E:\Backups\Mysql Daily\Mingle_"& _
TodaysFileName &".sql"""

'*** Add delete old backup files using FORFILES cmd
BatchFileTxt = BatchFileTxt & vbcrlf & "forfiles /P ""E:\Backups\Mysql Daily"" /M *.SQL /D -3 /C ""CMD /c del @file"" "

'*** Add delete "myself" step to batch file
BatchFileTxt = BatchFileTxt & vbcrlf & "del backup.bat"

'*** create a batch file with all the step/commands
OpenTextFileForWrite BatchFileTxt, "backup.bat"

'*** invoke the batch file
WshShell.Run "backup.bat"

'*** Function to LEFT pad the number to create fixed length number string. ***
Function Lpad (MyValue, MyPadChar, MyPaddedLength)
Lpad = string(MyPaddedLength - Len(MyValue),MyPadChar) & MyValue
End Function

'*** Procedure to DECRYPT the string ***
Function CryptVBS(Text, Key)
KeyLen = Len(Key)
For i = 1 To Len(Text)
KeyPtr = (KeyPtr + 1) Mod KeyLen
sTxtChr = Mid(Text, i, 1)
wTxtChr = Asc(stxtchr)
wKeyChr = Asc(Mid(Key, KeyPtr + 1, 1))
CryptKey = Chr(wTxtChr Xor wKeyChr)
hold = hold & CryptKey
CryptVBS = hold
End Function

'*** Procedure to WRITE the output to a given file ***
Sub OpenTextFileForWrite (TxtToWrite, strFileName)

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile( strFileName, ForWriting, True)
f.Write TxtToWrite & VBCRLF

End Sub

This is how the output batch file would look the end it delets itself!

mysqldump -uroot -p****** mingle > "E:\Backups\Mysql Daily\Mingle_20080508_111407.sql"
forfiles /P "E:\Backups\Mysql Daily" /M *.SQL /D -3 /C "CMD /c del @file"
del backup.bat

Currently VB script Mingle_Bkp.vbs is located in WINNT system folder on C:\ drive and that is where it creates the backup.bat and triggers it from.

The backup file retention period is set to 3 days, any files older than 3 days will get deleted. We are doing it using *FORFILES* utility. forfiles.exe is part of windows OS & is available on all windows systems(computers).

The script lines are not completely written by me I picked them up from different places and modified them to suite my requirements, but you are free to use it as you like.

No comments:

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.