How to get partial data from large MySQL databases using mysqldump

I know what you’re thinking….

Jmac, like, I need to get a sample dataset from my enormous mysql db but there’s no easy way to just SOME data using mysqldump!

I have wanted to find a way to accomplish this for some time now.  I’m not specifically looking for an ‘identical’ copy of the db, just the schema and some real data for use in my local dev environment, or, say I want to sync some prod data with a staging environment for testing.

And, today, I finally did the research to find the way to do just that.

How to Export/Dump a limited data set

If you’re familiar with mysqldump syntax, you’re more than halfway there.  Now, how do we limit the size of the dump to something manageable?

Example syntax:

mysqldump -u root -p -h hostname --single-transaction --opt --where="1 limit 700" dbname < dump.sql

…remember, you’ll be prompted for the password…so have it handy!

This example will dump a partial data set from every table in ‘dbname’ consisting of a max of 700 rows of data from each.

The –opt flag allows the use of the –where flag and once you’ve got that far, it’s a simple clause to get as many or few rows as you wish.

Also note the use of the the –single-transaction flag.  This is important because if you’re running this dump on a very large db having a variety of engine types per table, or (duh) live activity, the flag will execute the dump as a single transaction, avoiding table locks or otherwise slowing down your production database while you drawn down your data.

Import/Restoring Data

Restoring the partial data set is simple.

Import that exported data from PROD into your local DB using the same ole’ syntax you’ve already been using.

mysql -u root -p dbname < dump.sql

Done!

%d bloggers like this: