Skip to content

Instantly share code, notes, and snippets.

@csdy
Created April 7, 2017 09:37
Show Gist options
  • Save csdy/b498db9288a291a9bb604b1a5366cd76 to your computer and use it in GitHub Desktop.
Save csdy/b498db9288a291a9bb604b1a5366cd76 to your computer and use it in GitHub Desktop.
Windows MariaDB Backup + ZIP + Prune
@echo off
CLS
cd C:\Windows\Temp
set MYSQLUSER=root
set MYSQLPASS=**********
set BATCHFILE=C:\Windows\Temp\Batch_mysqldump.bat
set DUMPPATH=C:\Windows\Temp
SET backuptime=%DATE:~4,2%-%DATE:~7,2%-%DATE:~10,4%-%TIME:~0,2%-%TIME:~3,2%
SET backuptimelog=%DATE:~4,2%-%DATE:~7,2%-%DATE:~10,4% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2%
echo starting MySqlDump at %backuptime%
echo ------ starting MySqlDump at %backuptimelog% ------ >> "C:\Backup\sqldump.log"
echo Running dump...
set 7zip_path=
mkdir "%backuptime%" >> "C:\Backup\sqldump.log"
cd "C:\Program Files\MariaDB 10.1\bin"
echo @echo off > %BATCHFILE%
echo cd %DUMPPATH% >> %BATCHFILE%
echo copy "C:\Program Files\MariaDB 10.1\bin\mysqldump.exe" "C:\Windows\Temp\%backuptime%" >> %BATCHFILE%
echo cd "%backuptime%" >> %BATCHFILE%
mysql -u%MYSQLUSER% -p%MYSQLPASS% -AN -e"SELECT CONCAT('mysqldump -u%MYSQLUSER% -p%MYSQLPASS% ' ,schema_name,' --result-file=',schema_name,'.sql') FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','performance_schema')" >> %BATCHFILE%
echo exit >> %BATCHFILE%
start /wait %BATCHFILE%
echo Compressing bk_%backuptime%.sql...
SET ziptime=%DATE:~4,2%-%DATE:~7,2%-%DATE:~10,4% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2%
echo starting 7zip compression at %ziptime%
echo starting 7zip compression at %ziptime% >> "C:\Backup\sqldump.log"
"C:\Program Files\7-Zip\7z.exe" a -t7z -m0=PPMd "C:\Backup\bk_%backuptime%.7z" "C:\Windows\Temp\%backuptime%" >> "C:\Backup\sqldump.log"
echo Deleting the SQL file ...
rmdir /s /q "C:\Windows\Temp\%backuptime%" >> "C:\Backup\sqldump.log"
echo deleting files older than 60 days
echo deleting files older than 60 days >> "C:\Backup\sqldump.log"
forfiles -p "C:\Backup\sqldump" -s -m *.* /D -60 /C "cmd /c del @path" >> "C:\Backup\sqldump.log"
SET finishtime=%DATE:~4,2%-%DATE:~7,2%-%DATE:~10,4% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2%
echo ------ Done at %finishtime%! ------ >> "C:\Backup\sqldump.log"
echo Done at %finishtime%!
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment