B4J Code Snippet JRDC and Periodic backup of MySql database

I want to share this piece of code found on internet.
Really it has nothing to deal with B4J programming, but anybody that uses JRDC will find useful to make a regular backup of the MySql database.

So, it's a batch file:

B4X:
@echo off

:: Add here the name of the database to exclude from backup
set DBTOEXCLUDE=test Database information_schema licence mysql performance_schema test

:: Name of temporary file created by the script
set TEMP=db_bak.tmp tempdb_bak.tmp

set txtInit="Backing up MySql..."
set txtEnd="Done!"

echo %txtInit%

:: MySql folder
set mysqlDir="C:\wamp\bin\mysql\mysql5.5.16\bin"

:: MySql Backup destination folder
set backupDir="%HOMEPATH%\Desktop\MySql_Backup"

:: Delete temporary files if exist
for %%t in (%TEMP%) do (
    if exist %backupDir%\%%t del %backupDir%\%%t
)

:: Variables that will define the name of the backup folder
:: Hours
set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%

:: Minutes
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%

:: Seconds
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%

:: Year
set year=%date:~-4%

:: Month
set month=%date:~3,2%
if "%month:~0,1%" == " " set month=0%month:~1,1%

:: Day
set day=%date:~0,2%
if "%day:~0,1%" == " " set day=0%day:~1,1%

:: Set current datetime
set datetimef=%year%%month%%day%_%hour%%min%%secs%

:: Set name of the new backup folder beginning with datetime
set newDir=%backupDir%/%datetimef%_dump

:: Create new backup folder
md %newDir%

:: Move to MySql folder
cd %mysqlDir%

:: Create a file with names of all databases found (xxx=database password)
mysql -u root -pxxx -e "show databases" > db_bak.tmp

:: Exclude databases not to be backupped
call :exclude DBTOEXCLUDE

:: Execute the backup script for each database (xxx=database password)
for /f %%f in (db_bak.tmp) do (
    echo "Starting backup of %%f"
    mysqldump -u root -pxxx %%f > %newDir%/%%f.sql
    echo "Backup of %%f, done!"
)
:: Delete backup older than specified number of days
echo "Purging old backup ..."
set days=-2
for /f "usebackq delims=" %%G in (
`forfiles /p "%backupDir%" /c "cmd /c if /i @isdir == true echo @path" /d %days% 2^>nul`
) do echo rd /s /q "%%~G"
echo "done"
;
timeout 5

:: Sub that exclude database from backup
:exclude
:: Delete each databasa name found in variabile DBTOEXCLUDE
for %%d in (%DBTOEXCLUDE%) do (
    type db_bak.tmp | findstr /v %%d >tempdb_bak.tmp
    if exist db_bak.tmp del db_bak.tmp
    ren tempdb_bak.tmp db_bak.tmp
)
GOTO :eof

You can use the Windows Task scheduler to execute this batch every day for example.
It works fine except the part that purges the older backup (forfiles ...). The batch probably needs some tuning, I can't find where is the error. Volounteers are welcome !
 

OliverA

Expert
Licensed User
Regarding the script: Nice. I just learned some new features of "for" (wonder how long it's been that powerful, I'm getting rusty) and did not realize "forfiles" existed :-(.

As to your purging:

B4X:
do echo rd /s /q "%%~G"

This part just "echo's" the command
B4X:
rd /s /q "%%~G"
instead of executing it. Change it to
B4X:
do rd /s /q "%%~G"
and everything should work as intended.
 

marcick

Well-Known Member
Licensed User
Longtime User
oh, yes, I din't observe it !
Now it works as intented, thanks :)
 
Top