Knowledgebase

Portal Home > Knowledgebase > cPanel > MySQL backup script [on MSSQL2008)

MySQL backup script [on MSSQL2008)

@echo off
:: USE THIS SCRIPT AT YOUR OWN RISK!
:: Set some variables
set backupdir="D:\MYSQL\backup"
set mysqldir="C:\Program Files\MySQL\MySQL Server 5.5"
set mysqldatadir="D:\MYSQL\data"
set logdir="D:\MYSQL\logs"
set dbhost=MSSQL2008
set dbuser=root
set dbpass=tmahwk343
set zip="D:\utilities\gzip-1.3.12-1-bin\bin"
set mailer="D:\utilities\blat300\full"
set to=serverinfo@tomahawk.ca
set from=serverinfo@tomahawk.ca
set server=MERAKMAIL
set endtime=0

rem Delete any files older than 30 days
forfiles /P "D:\MYSQL\backup" /S /D -30 /C "cmd /c del /Q @path"

:DODIR

:: Do DIR of backupdir to establish network
DIR %backupdir%

:GETTIME

:: get the date and then parse it into variables
for /F "tokens=2-4 delims=/ " %%i in ('date /t') do (
set mm=%%i
set dd=%%j
set yy=%%k
)

:: get the time and then parse it into variables
for /F "tokens=5-8 delims=:. " %%i in ('echo.^| time ^| find "current" ') do (
set hh=%%i
set ii=%%j
set ss=%%k
)

:: If this is the second time through then go to the end of the file
if "%endtime%"=="1" goto END

:: Create the filename suffix
set fn=_%dd%-%mm%-%yy%_%hh%-%mm%-%ss%

:: Switch to the data directory to enumerate the folders
pushd %mysqldatadir%

:: Write to the log file
echo Beginning mysqldump Process > %logdir%\MySQL_LOG%fn%.txt
echo (Running on MSSQL2008) > %logdir%\MySQL_LOG%fn%.txt
echo Start Time = %yy%-%mm%-%dd% %hh%:%ii%:%ss% >> %logdir%\MySQL_LOG%fn%.txt
echo Folder %mysqldatadir% on %dbhost% >> %logdir%\MySQL_LOG%fn%.txt
echo --------------------------- >> %logdir%\MySQL_LOG%fn%.txt
echo. >> %logdir%\MySQL_LOG%fn%.txt

:: Loop through the data structure in the data dir to get the database names
for /d %%f in (*) do (

:: Create the backup sub-directory is it does not exist
if not exist %backupdir%\%%f\ (
echo Making Directory %%f
echo Making Directory %%f >> %logdir%\MySQL_LOG%fn%.txt
mkdir %backupdir%\%%f
) else (
echo Directory %%f Exists
echo Directory %%f Exists >> %logdir%\MySQL_LOG%fn%.txt
)

:: Run mysqldump on each database and compress the data by piping through gZip
echo Backing up database %%f%fn%.sql.gz
echo Backing up database %%f%fn%.sql.gz >> %logdir%\MySQL_LOG%fn%.txt
%mysqldir%\bin\mysqldump.exe --host=%dbhost% --user=%dbuser% --password=%dbpass% --databases %%f --opt --quote-names --allow-keywords --complete-insert | %zip%\gzip.exe -9 > %backupdir%\%%f\%%f%fn%.sql.gz
echo Done...
echo Done... >> %logdir%\MySQL_LOG%fn%.txt
)

REM -------------------- WHMCS ----------------------------------------------------------------------------

if not exist %backupdir%\tomahawk_WHMCS\ (
echo Making Directory tomahawk_WHMCS
echo Making Directory tomahawk_WHMCS >> %logdir%\MySQL_LOG%fn%.txt
mkdir %backupdir%\tomahawk_WHMCS
) else (
echo Directory tomahawk_WHMCS Exists
echo Directory tomahawk_WHMCS Exists >> %logdir%\MySQL_LOG%fn%.txt
)

echo Backing up database tomahawk_WHMCS%fn%.sql.gz
echo Backing up database tomahawk_WHMCS%fn%.sql.gz >> %logdir%\MySQL_LOG%fn%.txt
%mysqldir%\bin\mysqldump.exe --host=CPANEL --user=whmcsBACKUP --password=backup --databases tomahawk_whmcs --opt --quote-names --allow-keywords --complete-insert | %zip%\gzip.exe -9 > %backupdir%\tomahawk_WHMCS\tomahawk_WHMCS%fn%.sql.gz
echo Done...
echo Done... >> %logdir%\MySQL_LOG%fn%.txt

REM -------------------------------------------------------------------------------------------------------------

:: Write to the log file
echo. >> %logdir%\MySQL_LOG%fn%.txt
echo --------------------------- >> %logdir%\MySQL_LOG%fn%.txt
echo Analyzing, optimising and repairing databases
echo Analyzing, optimising and repairing databases >> %logdir%\MySQL_LOG%fn%.txt
%mysqldir%\bin\mysqlcheck.exe --all-databases --medium-check --auto-repair --force --use-frm --optimize --analyze --check-only-changed --user=%dbuser% --password=%dbpass%
echo Done...
echo Done... >> %logdir%\MySQL_LOG%fn%.txt

:: Go back and get the end time for the script
set endtime=1
goto :GETTIME

:END
:: Write to the log file
echo. >> %logdir%\MySQL_LOG%fn%.txt
echo --------------------------- >> %logdir%\MySQL_LOG%fn%.txt
echo MySQLDump Process Finished >> %logdir%\MySQL_LOG%fn%.txt
echo End Time = %yy%-%mm%-%dd% %hh%:%ii%:%ss% >> %logdir%\MySQL_LOG%fn%.txt
echo. >> %logdir%\MySQL_LOG%fn%.txt

:: Return to the scripts dir
popd

:: Send the log file in an e-mail
%mailer%\blat.exe %logdir%\MySQL_LOG%fn%.txt -to %to% -f %from% -server %server%
EXIT

Add to Favourites    Print this Article