16

how to take mysqldump of latest 1000 records from a database

Improve this question

1 Answer 1

Reset to default
21

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'
Improve this answer
4
  • I did what you suggest and I upvoted your answer, but, what about integrity constraint violation?
    – licorna
    Sep 21, 2011 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, 2011 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 dev.mysql.com/doc/refman/5.6/en/…
    – Matt Passell
    Oct 20, 2015 at 18:03
  • 2
    how to get a mysqldump of last 100 rows for EVERY TABLE in an entire database?
    – Rakib
    Nov 17, 2015 at 12:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .