0

MySQL backups in batch with folders and 7-Zip compression

I needed a way to do a daily backup of tables individually from a database. I accomplished this with a Windows batch script. This script will create a dated folder and a folder for the database inside it. The database folder contains a SQL export of each table from that database. This is accomplished by fetching a list of tables from the database, storing it in a temporary file, then looping through the file in the batch script and individually backing up each table. I needed the flexibility of having individual table exports instead of restoring an entire database. In the code below, MySQL Workbench 5.2 CE contains mysql.exe needed to perform the commands.

1
2
3
4
5
6
7
@echo off
md C:\backups\%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%\database
 
cd C:\Program Files\MySQL\MySQL Workbench 5.2 CE\
 
mysql -s -e "SHOW TABLES FROM database" -uUSER -pPASSWORD --skip-column-names > C:\temp\tables.txt
for /f %%A in (C:\temp\tables.txt) DO (mysqldump -hHOST -uUSER -pPASSWORD database %%A > C:\backups\%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%\database\%%A.sql)

Optionally, using 7-Zip, I compressed and password protected the backup. After compression, the directory will be removed and we are left with more space.

1
2
3
4
5
6
cd C:\Program Files\7-Zip\
 
7z a C:\backups\%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%.zip C:\backups\%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2% -pPASSWORD
 
rmdir C:\backups\%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2% /s /q
del C:\temp\tables.txt /q

Norbert Krupa

Technical Consultant

Leave a Reply

Your email address will not be published. Required fields are marked *