0

I have to create a xls datafeed for a website and I would like to know which tables are getting affected when I do a manual entry from CMS.

If i have installed fresh database and I'm doing first entry in it using CMS: I would like to know which tables got updated/appended in last 1 min in that DB.

It is somewhat similar to this question https://stackoverflow.com/questions/307438/how-can-i-tell-when-a-mysql-table-was-last-updated

But in my case I dont know which tables to check. I can check each and every table in the db using the solution posted in the question but I have a gut feeling that there is a better solution for this.

1

There are two distinct answers I have to give by Storage Engine

MyISAM

For all MyISAM tables, checking the last time a table was updated is quick and dirty

SET @IntervalMinutes = 2;
SET @IntervalSeconds = @IntervalMinutes * 60;
SET @DBtoCheck = DATABASE();
SELECT table_schema,table_name,update_time
FROM information_schema.tables WHERE engine='MyISAM'
AND table_schema = @DBtoCheck
AND update_date > (NOW() - INTERVAL @IntervalSeconds SECOND)
ORDER BY update_time DESC A;

InnoDB

I have some bad news: you cannot trust the INFORMATION_SCHEMA database because

  • Transaction Behavior of InnoDB
  • Location of the Data and Index Pages
  • Heavy duty locking of the INFORMATION_SCHEMA when reading about an InnoDB table

In order to know the last update by some timestamp, you must have innodb_file_per_table enabled. If not, all the data and index pages will sit inside ibdata1 (system tablespace). It would be anybody's guess when a table experienced an update.

You should first cleanup the InnoDB Infrastructure.

Then, the easiest way to get the timestamp is to ask the OS for it

EXAMPLE

Given this scenario

  • datadir is /var/lib/mysql
  • you want timetamp of mydb.mytable

Simply run this in the OS

TS_TAB=`ls -l --time-style="+%s" /var/lib/mysql/mydb/mytable.ibd | awk '{print $6}'`
TS_NOW=`date +%s`
(( TS_DIFF = TS_NOW - TS_TAB ))

TS_DIFF will be the number of seconds elapsed since the last time a write was posted to that table. That write can be anything (INSERT, UPDATE, DELETE, DDL).

That's as fast as I can get it for InnoDB

Give it a Try !!!

  • Hi, Im using AWS RDS with MySQL 5.7, Innodb_file_per_table is enabled by default, but I can't able to get the update_time, its showing NULL; – Bhuvanesh Aug 29 '17 at 11:14
  • @Bhuvanesh please run SELECT create_time,update_time FROM information_schema.tables WHERE table_schema='...' AND table_name='...'; The table will have a create_date for sure. After its initial load, the update_time should change when you start doing new writesto the table (INSERTs, UPDATEs, DELETEs). This I have seen in Amazon RDS running MySQL 5.7.16. – RolandoMySQLDBA Aug 29 '17 at 11:53
  • Will it work in Aurora as well, Becuase in aurora create time is also null :( – Bhuvanesh Aug 29 '17 at 12:02

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.