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