3

I'm asking if it's possible in MySQL to export just rows modified in a period of time for example in the past month, if it is possible, how?

  • please edit your question, provide additional data such as show create table. – Arash Mousavi Jul 15 '13 at 16:47
  • @ArashMousavi is not for a particular table or tables is just a general question, just asking for info and if it's possible – Reynier Jul 15 '13 at 19:01
3

This would depend on the table having a timestamp column.

mysqldump has a --where option that you can define when dumping a specific table

EXAMPLE

If you have a table called mydb.mytab defined as follows

CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    tm TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

You could specify the date range on that table as follows (for June 2013):

WHERE_CLAUSE="(tm >= '2013-06-01 00:00:00') AND"
WHERE_CLAUSE="${WHERE_CLAUSE} (tm < '2013-07-01 00:00:00')"
mysqldump -uroot -p mydb mytable --where="${WHERE_CLAUSE}"

This would be most helpful since timestamps can be updated automatically.

I wrote a post on Aug 15, 2011 on how to use --where to dump a subset of data not based on a timestamp : Is it possible to mysqldump a subset of a database required to reproduce a query?

If you do not have a timestamp column in the desired tables, you are better of just keeping all binary logs generated during any given month.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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