How to limit the number of records from mysqldump

mysqldump does not have a flag for limiting the number of rows included in a dump, but you can take advantage of the --where flag to achieve this.

The --where flag must contain a where condition after which we can concatenate other statements. By using a WHERE condition that is alway true and concatenating a LIMIT statement, we can limit the number of rows included in the dump.

The flag --where="1 limit 1000" is translated in the following query:

SELECT * FROM table_name WHERE 1 LIMIT 1000

Export the first 1000 lines of a single database

In order to export the first 1000 rows of every table of a database named my_app, use the following command:

mysqldump my_app --where="1 limit 1000" > my_backup.sql 

Export the first 1000 lines of multiple databases

In order to export the first 1000 rows of every table of two databases named my_app1 and my_app2, use the following command:

mysqldump --databases my_app1 my_app2 --where="1 limit 1000" > my_backup.sql 

Export the first 1000 lines of all databases

In order to export the first 1000 rows of every table of every database, use the following command:

mysqldump --all-databases --where="1 limit 1000" > my_backup.sql