Windows batch file to backup MySQL databases into separate files with file compression

You are here:
< All Topics

The built-in “MYSQLDUMP –all-databases” function in MySQL backs up the entire database into a single file, combining the DB structure and data into the one file. This is rather convenient if you need a scatter gun approach for a quick backup of the entire database, but if you need a more granular and manageable tool, then this Windows batch file could go quite some distance.

The file compression is in CAB format as it it built into Windows, so no additional file dependencies exist to have the compression feature included.

@ECHO off


REM Windows Batch script to backup all MySQL databases in a server and
REM then compress the individual files into CAB files.

REM Database information
SET dbhost="localhost"
SET dbuser="[username]"
SET dbpass="[password]"

REM Paths
SET "binaries=C:\Program Files\MySQL\MySQL Server 8.0\bin\"
SET "destination=C:\Backup"

REM Dates
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%"

REM set "datestamp=%YYYY%%MM%%DD%" & SET "timestamp=%HH%%Min%%Sec%"
SET "_date=%YYYY%-%MM%-%DD%_%HH%-%Min%-%Sec%"

REM Create the destination directory
IF NOT EXIST %destination%\%_date% mkdir "%destination%\%_date%"

CD %binaries%
REM MySQL Binary files
CD %binaries%

mysql --host=%dbhost% --user=%dbuser% --password=%dbpass% -s -N -e "SHOW DATABASES" | for /F "usebackq" %%D in (`findstr /V "information_schema performance_schema"`) do (
	CD %binaries%
	mysqldump --no-data --host=%dbhost% --user=%dbuser% --password=%dbpass% %%D > "%destination%\%_date%\%%D_structure.sql"
	CD "%destination%\%_date%\"
	MAKECAB "%%D_structure.sql" ""
	DEL /q /f "%%D_structure.sql"
	CD %binaries%
	mysqldump --no-create-info --host=%dbhost% --user=%dbuser% --password=%dbpass% %%D > "%destination%\%_date%\%%D_data.sql"
	CD "%destination%\%_date%\"
	MAKECAB "%%D_data.sql" ""
	DEL /q /f "%%D_data.sql"

If you are going to save your backup data to a different drive than the C: then just ensure that you change the directory and drive to the appropriate drives before calling the binaries.

That’s it, enjoy.

Table of Contents