Thursday, January 12, 2012

DOS Batch File with MySQL Progress Logging


I wrote the following DOS batch file to unpack a tar/g-zipped file containing four database backup files, load the results to a MySQL database, and then clean up afterwards while logging progress to both a file and to the MySQL database.  It limits the size of the required file storage by deleting the oldest files once it is done.  I debated installing Cygwin up front so I could do it in a more powerful shell, but then I came to the conclusion that DOS is better since it’s native to the machine and did nor require the introduction of yet more additional software to what was an already fairly complex server configuration.

This DOS batch file contains some good examples of scripting MYSQL transactions, which is a great way to log scripting results regardless of what shell you are using (assuming you already have a MySQL database server running on the machine of course).  Otherwise, you can use the file logging methodology, which is also included herein.  And it also shows how to implement functions in DOS.

This batch file would presumably be called by a daily process set in Task Scheduler.  Because it is only being used for this one task, and it’s not likely the parameters would need to be changed once the server is up and running, I did not pass any parameters in by command line, but rather set them via variables at the top of the file.  However, the format to access command line parameters is identical to what you see in the subroutines/functions used below.  The first argument would be referenced as %1, second as %2, etc.

Microsoft has another shell called “PowerShell” which ships with Windows server 2008 and presumably other distributions as well.  If you try and run an old-school DOS batch file such as this one from a PowerShell window, it’s not going to work.  The command set is different.  Make sure you are using an actual DOS shell (black screen) vs the PowerShell window (generally a blue screen by default).

You could make better use of subroutines and I've done some shady stuff here and there.  But publishing it was really kind of an afterthought, and I am too lazy to fix it.  Beyond that, IT WORKS in its present form.  If I were to clean it up, then I'd have to go back and test it again because I don't like publishing stuff I am not sure actually works.

A couple notes before I start walking through this script. 
1) DOS comments are prefaced by “::”.  There are other comment operators, but “::” is what I use, therefore:
:: This is a comment

2) DOS will not tolerate spaces in variable assignments: 
“set logging=ON” is okay
“set logging = ON” is not. 

3) DOS supports subroutines.  You call a subroutine with something akin to:
CALL :ULTRASUB ARGUMENT1 ARGUMENT2

: ULTRASUB
echo %1
echo %2
GOTO :EOF

ARGUMENT1 is now available within the :ULTRASUB function via referencing it as %1, ARGUMENT2 gets passed in as %2, etc.  GOTO :EOF is the equivalent of “return;” in most languages.   But it requires “SETLOCAL ENABLEEXTENSIONS” to use.

4) There is a lot of commentary floating around on the Internet about how you need %%variable_name for variables inside a batch file.  This is only true within FOR statements, which you see several examples of here.  But in all other cases, you need %variable_name%, just as you would from the command line.

A heavily commented version of the batch file follows:

:: @ suppresses output on a line by line basis
:: “echo off” stops echoing for subsequent lines
@echo off
:: SETLOCAL means we only want ENABLEEXTENSIONS in effect while this batch file
:: is running.  It will revert back to its previous status when we exit this batch file
:: ENABLEEXTENSIONS allows us to use :EOF as well as some other things
SETLOCAL ENABLEEXTENSIONS
:: set logging=ON to log steps to logFile
set logging=ON
set logFIle=c:\Logs\logLoad.txt
set folder=c:\ftproot\upload
set completeDir=c:\success
set failDir=c:\mylife

:: MySQL Connection params
set mysqlUser=rootrules
set mysqlPass=iheartcookies

:: Keep how many of the most recent database files we have restored into mySQL?
:: will delete all the older ones keeping only this many
set saveSuccessfulRestore=5
set saveFailedRestore=2

:: Number of files found in the target directory
set count=0

:: Save current location and change to directory %folder% defined above
pushd %folder%
:: make sure that the old unpacked files are not still hanging around
:: unpacking process creates a directory called mnt.  We want to remove it
rmdir /S /Q %folder%\mnt
:: get rid of any .tar file that might still be lurking lurkily
del %folder%\*.tar
set MSG="Beginning directory scan"
:: Let’s log our progress.  Should have sent MSG and a bunch of junk as arguments
:: This is just sloppy programming.  My bad. :P
CALL :LOGSTATUS
:: let's count the number of files in this directory
:: The following command works okay,
:: BUT it gives a "File Not Found" error if the directory is empty
:: for /f "tokens=*" %%P in ('dir /a-d /b') do Set /A count+=1
:: The /a-d part causes this error
:: dir /a-d means "attribute NOT d where D = directory so (not a directory)
:: the /b means bare (no pathname, just a file name), which we want to keep. 
:: So we have to do the following to avoid that error
for /f "tokens=*" %%P in ('dir /b') do Set /A count+=1
:: our setup works, but it also counts directories.
:: so better not be any in there
set MSG="Found %count% files in directory: %folder%"
call :LOGSTATUS
:: if there's no file here then bail out
if %count%==0 GOTO TERMINATE
:: If the count > 1, then delete all but the newest file
if %count% GTR 1 CALL :DELETEOLDEST 1 %folder%

:: There is now only one file here, let's do some more checking
:: Save the filename (%%F) to variables so we can use it later
:: bunch of options that can be used here, but we’ll just grab base
:: and extension with the weird ~nF thing (hey I didn’t write the shell)
for %%F in (*.*) do set baseName=%%~nF
for %%F in (*.*) do set fullName=%%F

if exist %baseName%.tgz goto FILEFOUND
:: There’s a file, but apparently it’s not a .tgz file.
:: The next pass (tomorrow) will delete it since it’s going to be older than the
:: new one, so let’s just ignore it and bail out.
set MSG="File found, but it's not a *.tgz file; bailing; fileName = %fullName%; Expected: %baseName%.tgz"
CALL :LOGSTATUS
:: Note the lack of “:” in the following GOTO unlike CALL :FILEFOUND
GOTO TERMINATE

:FILEFOUND
:: Found a file, and it’s a keeper.  So let’s try and unpack it!
set MSG="Attempting to unpack %fullName%; Logging to MySQL"
CALL :LOGSTATUS
:: This batch file assumes you have set the MySQL bin directory as part of the
:: environment variables so we don't need full path name to execute it
mysql.exe -u %mysqlUser% -p%mysqlPass% -e "INSERT INTO DailyStatus (Date, Status, Start, ExtStatus, ExtText, Filename) VALUES (CURDATE(), '1', NOW(), '1', 'checkingTGZ', '%fullName%')" statusDataBase
:: cannot reliably use error level from MySQL here (known issue). 
:: So just have to hope this went to plan
:: Let's try and unpack this thing; unzip it first
c:\"Program Files"\7-Zip\7za.exe x %baseName%.tgz
:: Now let's try and untar it
c:\"Program Files"\7-Zip\7za.exe x %baseName%.tar
:: we should now have four files in /mnt/tmp/%baseName%, so let's look for them
set count=0
:: return to our starting directory and clear stack
popd
:: jump to c:\ftproot\upload\mnt\tmp\(whatever base filename was)
pushd %folder%\mnt\tmp\%baseName%
:: we know for sure we have files in here.  So the /a-d thing should work without error
:: and if it does error, then it will abort, and the mess will get cleaned up tomorrow
for /f "tokens=*" %%P in ('dir /a-d /b') do Set /A count+=1
echo finished checking file count=%count%
if %count%==4 GOTO UNPACKSUCCESS
:: Unpack failed
set MSG="Found %count% database images in /mnt/tmp/%baseName%, but expected 4, so exiting.."
CALL :LOGSTATUS
CALL :CLEANUP %fullName% %folder% %failDir% %saveFailedRestore%
GOTO TERMINATE

:UNPACKSUCCESS
mysql.exe -u %mysqlUser% -p%mysqlPass% -e "UPDATE DailyStatus SET ExtStatus='2', ExtText='UnPacked' WHERE DATE = CURDATE()" statusDataBase
set MSG="Unpacked %fullName% successfully"
CALL :LOGSTATUS
:: Let's try and load these things up
mysql.exe -u %mysqlUser% -p%mysqlPass% -e "DROP DATABASE destDatabase"
mysql.exe -u %mysqlUser% -p%mysqlPass% -e "CREATE DATABASE destDatabase"
:: call :LOADMYSQL one time for every file in this directory
for /f "tokens=*" %%P in ('dir /b') do (CALL :LOADMYSQL %%P)

:: We expect that if we reach this point, we finished the process, so let's log to MySQL
mysql.exe -u %mysqlUser% -p%mysqlPass% -e "UPDATE DailyStatus SET Status='2',End=NOW(),ExtStatus='3',ExtText='completed' WHERE DATE = CURDATE()"statusDataBase
set MSG="Completed processing of %fullName%, cleaning up.."
CALL :LOGSTATUS
CALL :CLEANUP %fullName% %folder% %completeDir% %saveSuccessfulRestore%
GOTO TERMINATE

:: ****** SUBROUTINES ******
:: The arguments are available within the subroutines as
:: %1, %2, etc in order they were passed in

:CLEANUP
::INPUT %1 filename
::INPUT %2 base FTP dir
::INPUT %3 targetDir for Move
::INPUT %4 number of files to keep in targetDir
popd
pushd %2
CALL :MOVEFILE %2\%1 %3
rmdir /S /Q %2\mnt
del %2\*.tar
CALL :DELETEOLDEST %4 %3%
GOTO :EOF

:DELETEOLDEST
:: INPUT %1 = number of files to keep
:: INPUT %2 = target directory
:: loops through files (/f) in chronological order skipping first %1 and deletes them
for /f "skip=%1 tokens=*" %%i in ('dir /a:-d-s /b /o:-d %2') do del %2\%%i
GOTO :EOF

:MOVEFILE
:: INPUT %1 = File name to move
:: INPUT %2 = Directory to move it to
MOVE /Y %1 %2
GOTO :EOF

:LOADMYSQL
:: INPUT %1 file to load into MySQL destDatabase database
set MSG="Loading in MySQL database dump %1"
call :LOGSTATUS
mysql.exe -u %mysqlUser% -p%mysqlPass% destDatabase < %1
GOTO :EOF

:LOGSTATUS
IF NOT %logging%==ON GOTO:EOF
IF NOT EXIST %logFile% ECHO *** MySQL Log File *** > %logFile%
echo %date%  %time% %MSG% >> %logFile%
GOTO :EOF

: TERMINATE
popd
GOTO :EOF

 *******************************************************
And finally here it is without comments.  Feel free to use and abuse it as you see fit as long as you don’t tell anybody where you got it.

I have no recollection of writing that. 
blinks repeatedly…

********************************************************
@echo off
SETLOCAL ENABLEEXTENSIONS

set logging=ON
set logFIle=c:\Logs\logLoad.txt
set folder=c:\ftproot\upload
set completeDir=c:\success
set failDir=c:\mylife

:: MySQL Connection params
set mysqlUser=root
set mysqlPass=whatever

set saveSuccessfulRestore=5
set saveFailedRestore=2
set count=0

pushd %folder%
rmdir /S /Q %folder%\mnt
del %folder%\*.tar
set MSG="Beginning directory scan"
call :LOGSTATUS

for /f "tokens=*" %%P in ('dir /b') do Set /A count+=1
set MSG="Found %count% files in directory: %folder%"
call :LOGSTATUS

if %count%==0 GOTO TERMINATE
if %count% GTR 1 CALL :DELETEOLDEST 1 %folder%

for %%F in (*.*) do set baseName=%%~nF
for %%F in (*.*) do set fullName=%%F

if exist %baseName%.tgz goto FILEFOUND

:: Failed to find the file
set MSG="File found, but it's not a *.tgz file; bailing; fileName = %fullName%; Expected: %baseName%.tgz"
CALL :LOGSTATUS
GOTO TERMINATE
:FILEFOUND

set MSG="Attempting to unpack %fullName%; Logging to MySQL"
CALL :LOGSTATUS

mysql.exe -u %mysqlUser% -p%mysqlPass% -e "INSERT INTO DailyStatus (Date, Status, Start, ExtStatus, ExtText, Filename) VALUES (CURDATE(), '1', NOW(), '1', 'checkingTGZ', '%fullName%')" statusDataBase

c:\"Program Files"\7-Zip\7za.exe x %baseName%.tgz
c:\"Program Files"\7-Zip\7za.exe x %baseName%.tar
set count=0

popd
pushd %folder%\mnt\tmp\%baseName%
for /f "tokens=*" %%P in ('dir /a-d /b') do Set /A count+=1

if %count%==4 GOTO UNPACKSUCCESS
set MSG="Found %count% database images in /mnt/tmp/%baseName%, but expected 4, so exiting.."
CALL :LOGSTATUS
CALL :CLEANUP %fullName% %folder% %failDir% %saveFailedRestore%
GOTO TERMINATE

:UNPACKSUCCESS
mysql.exe -u %mysqlUser% -p%mysqlPass% -e "UPDATE DailyStatus SET ExtStatus='2', ExtText='UnPacked' WHERE DATE = CURDATE()" statusDataBase
set MSG="Unpacked %fullName% successfully"
CALL :LOGSTATUS
:: Let's try and load these things up
mysql.exe -u %mysqlUser% -p%mysqlPass% -e "DROP DATABASE destDatabase"
mysql.exe -u %mysqlUser% -p%mysqlPass% -e "CREATE DATABASE destDatabase"
for /f "tokens=*" %%P in ('dir /b') do (CALL :LOADMYSQL %%P)

:: We expect that if we reach this point, we finished the process, so let's log to MySQL
mysql.exe -u %mysqlUser% -p%mysqlPass% -e "UPDATE DailyStatus SET Status='2',End=NOW(),ExtStatus='3',ExtText='completed' WHERE DATE = CURDATE()"statusDataBase
set MSG="Completed processing of %fullName%, cleaning up.."
CALL :LOGSTATUS
CALL :CLEANUP %fullName% %folder% %completeDir% %saveSuccessfulRestore%
GOTO TERMINATE

:: ****** SUBROUTINES ******
:: DOS subroutines can take arguments.  The arguments are available within the subroutines as
:: %1, %2, etc in order they were passed in

:CLEANUP
::INPUT %1 filename
::INPUT %2 base FTP dir
::INPUT %3 targetDir for Move
::INPUT %4 number of files to keep in targetDir
popd
pushd %2
CALL :MOVEFILE %2\%1 %3
rmdir /S /Q %2\mnt
del %2\*.tar
CALL :DELETEOLDEST %4 %3%
GOTO :EOF

:DELETEOLDEST
:: INPUT %1 = number of files to keep
:: INPUT %2 = target directory
:: loops through files (/f) in chronological oder skipping first %1 and deletes them
for /f "skip=%1 tokens=*" %%i in ('dir /a:-d-s /b /o:-d %2') do del %2\%%i
GOTO :EOF

:MOVEFILE
:: INPUT %1 = File name to move
:: INPUT %2 = Directory to move it to
MOVE /Y %1 %2
GOTO :EOF

:LOADMYSQL
:: INPUT %1 file to load into MySQL destDatabase database
:: could also pass in the database name as an argument if you wanted to
set MSG="Loading in MySQL database dump %1"
call :LOGSTATUS
mysql.exe -u %mysqlUser% -p%mysqlPass% destDatabase < %1
GOTO :EOF

:LOGSTATUS
IF NOT %logging%==ON GOTO:EOF
IF NOT EXIST %logFile% ECHO *** MySQL Log File *** > %logFile%
echo %date%  %time% %MSG% >> %logFile%
GOTO :EOF

: TERMINATE
popd
GOTO :EOF



No comments:

Post a Comment

Post a Comment