0

I'm trying to implement an incremental backup procedure that only dumps and backs up tables in a MySQL database which have been modified within the last 24 hours. I'm able to look at the modification times of .MYD and .MYI files under /var/lib/mysql/DBNAME/ to determine if a MyISAM table has been modified, but InnoDB tables (which have not been set up to use their own data files) have no unique files "exposed" to the filesystem for which I can check modification times. Some of these InnoDB tables are huge (100M+ rows), but they get updated/added-to seldomly, for instance two or three times per month. I don't want to waste time/space dumping these tables every day for backups if no data has been changed.

Because of the nature of the databases and tables (some are transient/ad-hoc, and some have static schemas where I cannot predict whether a table has a timestamp column, and if so, what its name is), I cannot query the tables themselves to determine if they've been modified recently.

Is there a built-in "table modified time" kept by MySQL that can be exposed by querying built-in variables/tables?

All of our MySQL servers use 5.1 or later.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user3443522
  • 1
  • 1

1 Answers1

-1

If you have the information_schema database at your disposal, you could get a list of recently updated tables as follows:

SELECT table_schema, table_name, update_time
FROM information_schema.`TABLES`
WHERE update_time > DATE_SUB(NOW(), INTERVAL 1 DAY);
Tim Burch
  • 1,088
  • 7
  • 9
  • 2
    InnoDB doesn't maintain any meaningful value in the `update_time`. It's unclear if "update time" is the time of the `UPDATE`, the time of the `COMMIT`, or the time that the page was flushed to the tablespace. – Bill Karwin Mar 20 '14 at 20:15
  • 1
    Like Bill Karwin commented, update_time does not work with InnoDB. – Dave Jul 03 '14 at 02:47