CODEWITHSUNDEEP
mysql
Phil
Phil

Reputation:

Limiting the number of records from mysqldump?

I am trying to load a small sample of records from a large database into a test database.

How do you tell mysqldump to only give you n records out of 8 million?

Thanks

Upvotes: 161

Views: 80507

Answers (4)

Andreas Bergström
Andreas Bergström

Reputation: 14610

As the default order is ASC which is rarely what you want in this situation, you need to have a proper database design to make DESC work out of the box. If all your tables have ONE primary key column with the same name (natural or surrogate) you can easily dump the n latest records using:

mysqldump --opt --where="1 ORDER BY id DESC limit 1000000" --all-databases > dump.sql

This is a perfect reason to why you should always name your PK's id and avoid composite PK's, even in association tables (use surrogate keys instead).

Upvotes: 15

Casper André Casse
Casper André Casse

Reputation: 593

If you want to get n records from a specific table you can do something like this:

mysqldump --opt --where="1 limit 1000000" database table > dump.sql

This will dump the first 1000000 rows from the table named table into the file dump.sql.

Upvotes: 66

Adam Bellaire
Adam Bellaire

Reputation: 110489

As skaffman says, use the --where option:

mysqldump --opt --where="1 limit 1000000" database

Of course, that would give you the first million rows from every table.

Upvotes: 246

skaffman
skaffman

Reputation: 403481

mysqldump can be given a SQL query to execute, from which it will take the data for the dump. You can then use the "limit X" clause in your query to restrict the number of rows.

Upvotes: 7

Related Questions