Ivan Villareal

I was updating my db dev instance with a set of changes, and when I was restoring the dump for a table it contained a drop db clause it was a simple table and it took a about 15 seconds to finish the operation, when I looked at the db schema all the tables were gone!

This db has a very large table about 16Gb of binary data, so getting a new dump would require a lot of time, so as workaround I’ve dumped only sets of data that I need to my tests.

First I did a dump of the db schema with no data:

mysqldump -h host-u ${USER} --password=${PASS} --no-data --add-drop-database --databases largeDb> schema_largeDb.sql

then I created a dump for the tables that I need, for this I have to include a list of tables that I don’t want, I’ve created the list of unwanted tables manually.

mysqldump -h host-u ${USER} --password=${PASS} --no-create-info --ignore-table=largeDb.unwantedTable1 --ignore-table=largeDb.unwantedTable2 --ignore-table=largeDb.unwantedTable3 > smallData.sql

And because I need data from the big table, I’ve used the –where clause to limit the rows returned in

mysqldump -h host-u ${USER} --password=${PASS} "--where=brand_id='2496'" largeDb bigTable > binaryData.sql

With this one I was able to get only the binaryData that I need to make my tests, instead of waiting to get for the entire dbDump,