mysqldump with LIMIT and --order-by-primary 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 &gt;&gt; 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.
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 &lt;primary key&gt; LIMIT 40" &lt;database&gt; &lt;table&gt;


Note that &lt;primary key&gt; 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.