Mysql when was table last modified

This question comes up from time to time and the short answer is that this information cannot be reliably obtained by any one method for the simple reason that it is not necessarily recorded.

I don't know about any of the others but for InnoDB at least there is no query that will return the information you want. In this case you might consider using triggers to record the timestamp in a table whenever the data is changed but realistically the performance loss will most likely be greater that just going ahead with the dump. Sign up to join this community. The best answers are voted up and rise to the top. Stack Overflow for Teams — Collaborate and share knowledge with a private group.

Create a free Team What is Teams? Learn more. Ask Question. Asked 10 years, 5 months ago. Active 4 months ago. Viewed 24k times. Improve this question. John Gardeniers 27k 12 12 gold badges 52 52 silver badges bronze badges. Add a comment. Active Oldest Votes. Improve this answer. And would I backup those logs or just use them for information? Philip Reynolds Philip Reynolds 9, 1 1 gold badge 31 31 silver badges 33 33 bronze badges.

How to get the last modified date of an mysql table Ask Question. Asked 4 years, 8 months ago. Active 4 years, 8 months ago. Viewed 14k times. Improve this question. Tom Tom 4, 6 6 gold badges 31 31 silver badges 75 75 bronze badges. It's easy if you have a timestamp column that gets updated when the record is created or updated. Deletion of a record and truncation of the records in the table cannot be recorded this way — Lelio Faieta.

Add a comment. Active Oldest Votes. Improve this answer. Which version of mysql are you using? I read that there was a bug for innoDb tables [ bugs. It gives you the last modified one. Reed Alnitak Alnitak k 69 69 gold badges silver badges bronze badges. For details including InnoDB limitations see dev. Although this is listed as a bug , it is mentioned in the MySQL 5.

For MySQL 5. This does not seem to persist on restart for me on version 5. Show 6 more comments. Meloman 2, 3 3 gold badges 37 37 silver badges 41 41 bronze badges. Bill Karwin Bill Karwin k 81 81 gold badges silver badges bronze badges.

The question really wants to know when relevant data is updated, and not when a table which is irrelevant to the user may or may not have been changed for whatever reason. Ammamon, true, if you also need to account for deletions, this solution doesn't reflect that.

A trigger to update a summary table may be the only comprehensive solution, but that would create a bottleneck. Pavan, there is no difference, you're just relying on the default behavior, and my example spells out the option explicitly. But the option is identical to the default behavior. Show 14 more comments.

Radu Maris Radu Maris 5, 4 4 gold badges 38 38 silver badges 54 54 bronze badges. By default, the data is stored in ibdata1 so the ibd file timestamp will not update when data is changed in the table. StackUnderflow, What you've said is not accurate. The ibd files store data and indexes for a tablespace, which contains one or more tables. If the data is stored in ibdata1, there will be no ibd file. Storing data in one file per table has been the default since MySQL 5.

Pablo Bianchi 1, 1 1 gold badge 19 19 silver badges 26 26 bronze badges. Francois Bourgeois Francois Bourgeois 3, 5 5 gold badges 29 29 silver badges 39 39 bronze badges. Any idea? Xaraxia but some of them are not null, do you know why? Mikhail Mikhail 1, 5 5 gold badges 22 22 silver badges 33 33 bronze badges. Soul Reaver Soul Reaver 1, 3 3 gold badges 34 34 silver badges 45 45 bronze badges.

Simas Joneliunas 2, 13 13 gold badges 22 22 silver badges 30 30 bronze badges. Just grab the file date modified from file system. Matthias Robbers Steve Wood Steve Wood 21 2 2 bronze badges. Filesystem checks aren't useful if your database is running on a separate server — Sam Dufel. Welcome to StackOverflow!