mysqldump with LIMIT and --order-by-primary

Question

I want to use mysqldump to retrieve X first rows from a database (where "first" are ordered by primary key).

I'm trying to use mysqldump the following way:

mysqldump [connection-info] -where"1=1 LIMIT 40" --order-by-primary database table >> file.sql

(this is inside a script who runs different configurations for different tables)

Now, the problem, that command creates the following query:

SELECT /*!40001 SQL_NO_CACHE */ * FROMtableWHERE 1=1 LIMIT 40 ORDER BY primary_key

Is an invalid query, I need the LIMIT 40 to be placed after the ORDER BY statement.

If you know the right value of 40th ID or if it goes 1-40 without gaps, you could do --where "<idcol> <= 40" or similar. — jkavalik, May 14 '16 at 10:22

score 9 · Accepted Answer · answered May 13 '16 at 17:52

My guess is that you cant use limit in combination with --order-by-primary

One way around this is to define the order by within the -where argument. If you know the primary key for the table:

mysqldump -u lelle -p -w"1 = 1 order by <primary key> LIMIT 40" <database> <table>

Note that <primary key> is a place holder for the columns in the primary key.

if you don't know the primary key in advance you can use the INFORMATION_SCHEMA to determine that:

select GROUP_CONCAT(column_name SEPARATOR ',') 
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
where CONSTRAINT_NAME = 'PRIMARY' 
  and CONSTRAINT_SCHEMA = ... and table_name = ...;

and then use the information in mysqldump.

mysqldump with LIMIT and --order-by-primary

1 Answers1