Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Windows batch script to backup all MySQL databases, with compression and versioning.
rem for a windows server running multiple mysql databases, for example hosting multiple wordpress sites.
rem simple batch file makes a full backup of all mysql databases, then compresses and versions it
rem keeping the last 5 versions. ex. run daily from task scheduler.
rem be sure that another process such a robocopy picks up and stores the backup files remotely, in case of server failure.
rem install 7-zip for compression.
rem update my.ini with mysqldump credentials:
rem [mysqldump]
rem user=<user>
rem password=<password>
rem backup all mysql databases. run from task scheduler.
rem to restore one single db from full backup: mysql -u root -p -o database_name < all_databases.sql
rem time stamping for file name.
set YYYMMDD=%DATE:~-4%%DATE:~4,2%%DATE:~7,2%
rem if today's zip file exists, delete it.
del /q all_databases.sql.%YYYMMDD%.zip
rem dump all databases out. my.ini has the credentials.
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump" --all-databases > all_databases.sql
timeout 2
rem compress the dump file.
"C:\Program Files\7-Zip\7z" a all_databases.sql.%YYYMMDD%.zip all_databases.sql
timeout 2
rem delete original dump file, no need to keep.
del /q all_databases.sql
timeout 2
rem delete older zip files >5 days.
forfiles /m *.zip /D -5 /C "cmd /c del /q @path"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment