MySQL backup of multiple schemas to CSV with logging and 7zip compression

backups_agent.bat

@ECHO OFF
SET timeformatstring='%%W_%%M-%%d-%%Y'
FOR /F "usebackq tokens=1-4 delims=: " %%A IN (`time /t`) DO (
  FOR /F "usebackq tokens=1-2 skip=1 delims=| " %%H IN (`mysql -h %dbHost% -u %dbUser% -p%dbPass% -e "SET @@lc_time_names='es_MX'; SELECT UPPER(DATE_FORMAT(NOW(), %timeformatstring%));"`) DO (
    ECHO Creating log in %dbBackDir% for backup job started at: %%H and time: %%A%%B%%C
    :: Getting output from script called through new shell: http://superuser.com/a/661678
    START "" /B /WAIT /HIGH CMD /C %dbBackDir%\backups_worker.bat ^> %dbBackDir%\%%H_%%A%%B%%Clog 2^>^&1
    :: CMD equivalent of Unix touch? http://superuser.com/a/764725
    COPY %dbBackDir%\%%H_%%A%%B%%Clog +,,
  )
)

backups_worker.bat

@ECHO OFF
SET %backup_temp% = %ALLUSERSPROFILE%\temp_backup_files
PUSHD %backup_temp%
:: DANGER: Erase subdirectories without prompting user previously!
FOR /D /R %%D IN (*) DO ( 
  CALL :timestamp_msg erasing %%D
  RD /S /Q %%D 
)
:: We use the output of SHOW DATABASES to build our list
FOR /F "usebackq tokens=1-2 delims=| " %%D in (`mysql -h %dbHost% -u %dbUser% -p%dbPass% -e "show databases;"`) DO ( 
  :: Nested if's to exclude unwanted entries in list
  IF NOT "%%D" == "Database" (
    IF NOT "%%D" == "information_schema" (
      IF NOT "%%D" == "performance_schema" (
        IF NOT "%%D" == "mysql" (
          :: Make a directory per schema
          MKDIR %%D
          :: Enter in it
          PUSHD %%D
          CALL :timestamp_msg Start dumping of data in Schema: %%D
          :: db{Host,User,Pass} are set as environment variables
          mysqldump -h %dbHost% -u %dbUser% -p%dbPass% -t -T . --fields-terminated-by=, --fields-escaped-by=\\ --lines-terminated-by=\r\n --fields-optionally-enclosed-by=0x22 %%D
          CALL :timestamp_msg Finished data dump of schema: %%D
          :: Back to parent directory
          POPD
)))))
:: Get rid of empty *.sql files in every subdirectory from parent (data in .txt)
FOR /R %%S IN (*.sql) DO ERASE %%~pxnS
:: Archive entire dump with 7-zip command line, run in new shell, capture output
START /HIGH /WAIT CMD /C 7z.exe a -t7z -mx9 -bd -o%dbBackDir% -aou %dbBackDir%\backup_archive.7z %backup_temp% 1^> %ALLUSERSPROFILE%\compression.log 2^>^&1
:: Get log captured to return it to agent batch
TYPE %ALLUSERSPROFILE%\compression.log
:: And delete it
ERASE %ALLUSERSPROFILE%\compression.log
CALL :timestamp_msg Backup finished!
PUSHD %backup_temp%
:: Deleting temporary CSV dumps to recover disk space
FOR /D /R %%D IN (*) DO RD /S /Q %%D
POPD
EXIT /B 0

:: subroutine for timestamping of messages for better logs
:timestamp_msg 
FOR /F "tokens=2 skip=1 delims=| " %%T IN ('mysql -h %dbHost% -u %dbUser% -p%dbPass% -e "SELECT NOW();"') DO ECHO %%T %*
GOTO :eof