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 |
