mysqldump batch script to output individual tables for selective restore

Question

We currently have a DOS batch script that performs a MySQL dump on the entire database in a single file. What I would like to do is breakup the database by tables, so that within each 'date' folder, there would be a 'db' folder containing individual table dumps. This allows us to restore selected tables rather than restoring an entire db.

@echo off
md C:\backups\%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%
cd C:\Program Files\MySQL\MySQL Workbench 5.2 CE\
mysqldump -h -u -p --databases db0 > c:\backups\%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%\db0.sql
mysqldump -h -u -p --databases db1 > c:\backups\%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%\db1.sql

I found a possible solution using shell: https://stackoverflow.com/a/134296/679449

This appears that it would allow me to export into individual tables, however I'm not sure how to/if it's possible to write in batch. Any help with that is appreciated.

`mysqldump` has the `--tables` option. You choose the tables that you want to dump after the flag. — Tristian, Feb 20 '12 at 15:42
I would like to avoid using a brute force method and list every table (100+ tables). — Kermit, Feb 20 '12 at 16:08

score 0 · Accepted Answer · answered Feb 20 '12 at 23:20

My solution:

@echo off
md C:\backups\%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%\db0
md C:\backups\%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%\db1

cd C:\Program Files\MySQL\MySQL Workbench 5.2 CE\

mysql -s -e "SHOW TABLES FROM db0" -u -p --skip-column-names > C:\backups\tables.txt
for /f %%A in (C:\backups\tables.txt) DO (mysqldump -h -u -p db0 %%A > c:\backups\%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%\db0\%%A.sql)

mysql -s -e "SHOW TABLES FROM db1" -u -p --skip-column-names > C:\backups\tables.txt
for /f %%A in (C:\backups\tables.txt) DO (mysqldump -h -u -p db1 %%A > c:\backups\%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%\db1\%%A.sql)

mysqldump batch script to output individual tables for selective restore

1 Answers1