16

how to take mysqldump of latest 1000 records from a database

bvishal4u
  • 316
  • 1
  • 3
  • 7

1 Answers1

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'
jj33
  • 11,178
  • 1
  • 37
  • 50
  • 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 https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_where – Matt Passell Oct 20 '15 at 18:03
  • 2
    how to get a mysqldump of last 100 rows for EVERY TABLE in an entire database? – Rakib Nov 17 '15 at 12:05