MYSQL backup and compress with a batch script

Here is how to do a MYSQL backup and compress using 7zip though a batch file or .bat file this makes the process easy to run with windows scheduler to ensure your database is always backed up.

Edit the following with your MYSQL database and connection info. Also make sure that you have the 7za.exe where it is defined. You can also change the backups save directory.

@echo off
color 0E
title MySQL backup.

for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a"
set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%"
set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%" & set "MS=%dt:~15,3%"

set "dirname=%DD%_%MM%_%YY%_%HH%%Min%"
 
set basedir=C:
set workdir=c:\Users\Administrator\Documents\
set zip ="C:\7za\7za.exe"
set mysqldir=c:\xampp\mysql\bin
set mysqlpassword=PASSWORDHERE
set mysqluser=USERHERE
set mysqldb=DATABASEHERE
 
cd %mysqldir%
 
mysqldump -u %mysqluser% -p%mysqlpassword% -p%mysqldb% >%workdir%\backup.sql

7za a -t7z "%dirname%.7z" %workdir%\backup.sql

MOVE %dirname%.7z %workdir%