HowTo get a small sample dataset from a mysql database using mysqldump

Here is a quick tip that will show how you can get a small sample dataset from a mysql database using mysqldump. We frequently need to get a small snapshot from a very big production database to import it into a development or staging database that will not need all the original data; let’s say we need 1,000,000 records from all the tables in the database; we will just use the option –where=“true LIMIT X”, with X the number of records we want mysqldump to stop after.

Simply we will run something like (add whatever other options you need to mysqldump):

mysqldump --opt --where="true LIMIT 1000000" mydb > mydb1M.sql

and this will get 1M records from each of the tables in the database. If you want this for a single table you would use something like this:

mysqldump --opt --where="true LIMIT 1000000" mydb mytable > mydb_mytable_1M.sql

To restore this, you would use the same as on a regular dump:

mysql -p mydb_stage < mydb1M.sql

This will give you a small number of records that you can use for development, testing, etc. whatever you would need.