Export just rows modified in last month (date defined by user)

Question

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?

Answer 1

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.