Skip to content

Instantly share code, notes, and snippets.

@almirb
Last active January 2, 2023 22:01
Show Gist options
  • Save almirb/371ab84eb50f38975a08628889f95152 to your computer and use it in GitHub Desktop.
Save almirb/371ab84eb50f38975a08628889f95152 to your computer and use it in GitHub Desktop.
MySQL Backup Batch Script (Windows) for 7 day persistence with hour prefix.
@echo off
:: make sure to change the settings from line 4-9
set dbUser=backup
set dbPassword="MyBackupUserPass"
set backupDir="C:\laragon\bkp\mysql"
set mysqldump="C:\laragon\bin\mysql\mysql-5.7.24-winx64\bin\mysqldump.exe"
set mysqlDataDir="C:\laragon\data\mysql"
set zip="C:\Program Files\7-Zip\7z.exe"
:: get date
for /f "skip=2 tokens=2 delims=," %%A in ('WMIC Path Win32_LocalTime Get DayOfWeek /Format:csv') do set "dow=0%%A"
for /F "tokens=1 delims=:" %%A in ('time/t') do set hh=%%A
if %dow%==00 set dirName="Dom"
if %dow%==01 set dirName="Seg"
if %dow%==02 set dirName="Ter"
if %dow%==03 set dirName="Qua"
if %dow%==04 set dirName="Qui"
if %dow%==05 set dirName="Sex"
if %dow%==06 set dirName="Sab"
set prefix=%hh%
:: switch to the "data" folder
pushd "%mysqlDataDir%"
:: create backup folder if it doesn't exist
if not exist %backupDir%\%dirName%\ mkdir %backupDir%\%dirName%
:: remove old backup files
del %backupDir%\%dirName%\%prefix%_*.gz
:: iterate over the folder structure in the "data" folder to get the databases
for /d %%f in (*) do (
:: remove echo here if you like
echo processing folder "%%f"
%mysqldump% --host="localhost" --user=%dbUser% --password=%dbPassword% --single-transaction --add-drop-table --databases %%f > %backupDir%\%dirName%\%%f.sql
%zip% a -tgzip %backupDir%\%dirName%\%prefix%_%%f.sql.gz %backupDir%\%dirName%\%%f.sql
del %backupDir%\%dirName%\%%f.sql
)
popd
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment