Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

how to take mysqldump of latest 1000 records from a database

share|improve this question

mysqldump has a --where option. Assuming you have some sort of toehold to figure out what the last 1000 inserted records are (for instance, an auto-increment field called id), you should be able to tack that onto the mysqldump command, like so:

mysqldump --where "1=1 ORDER BY id DESC LIMIT 1000" DB_NAME TBL_NAME

The 1=1 is necessary because the "WHERE" keyword is inserted into the query automatically, so you do have to give it some SQL to evaluate.

EDIT: There was an anonymous edit made to this response removing the space between --where and "1=1, saying that the command errored without a space. I just tested and it works with the space and errors without the space, unsure what the editor was seeing.

The error when leaving the space out:

mysqldump: unknown variable 'where1=1 ORDER BY UserID DESC LIMIT 1000'
share|improve this answer
I did what you suggest and I upvoted your answer, but, what about integrity constraint violation? – licorna Sep 21 '11 at 21:59
That's a whole other problem. My solution is a hack at best. If you want integrity for putting the rows back in somewhere else, that's going to be a tougher problem and very dependent on your design – jj33 Sep 22 '11 at 14:01
This has probably changed since the answer was originally posted but the mysqldump docs now say that the option should either be specified as --where="..." or -w "..." See… – Matt Passell Oct 20 '15 at 18:03
how to get a mysqldump of last 100 rows for EVERY TABLE in an entire database? – syedrakib Nov 17 '15 at 12:05

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.