Mysql – Log all table updates for all tables in MySQL

MySQLtrigger

Is there anyway I can get the last updated time of a set of tables? By last updated time, I mean not only when the table's schema changed but when any of the rows changed or even when rows were created/deleted in the given set of tables.

I tried both SELECT * FROM information_schema.tables and SHOW TABLE STATUS FROM myDb and I only see the create_time which is only when table was created.

I can alternatively create triggers like:

CREATE TRIGGER tbl1_delete AFTER DELETE ON tbl1 
FOR EACH ROW
BEGIN
  UPDATE table_updates SET update_time = now() WHERE table_name = tbl1
END

But, now I have to repeat this over and over for deletes, updates, creations and schema changes. And, I want to automate this for all my tables!

Best Answer

I got some interesting news for you. You cannot use information_schema.tables because it does not track changes to InnoDB tables.

The best way to find out when a table changed is to go to the OS and get the most recent timestamp of each table. Rather than plagiarize my own posts, here are my posts where I show you how to do this

Sep 25, 2014 : want to find out which databases are used in last 30 days or not
Jun 03, 2013 : Is there a way to find the least recently used tables in a schema?
Apr 04, 2013 : How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute?
Dec 21, 2011 : Fastest way to check if InnoDB table has changed

Give Them a Try !!!

Related Solutions

MySQL Stored Procedure – Loop Through Table and Delete Rows

There are two possible solutions:

set @z to null in the very beginning of your loop (prior to SET @sql_text2 = concat('....)

or instead of

SELECT id INTO @z FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1;

use this:

SET @z = (SELECT id FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1);

Explanation:

When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like SELECT ... INTO that won't reset the value if nothing is found.
see: https://dba.stackexchange.com/a/35207/12923

MySQL gives me:“Can’t open and lock privilege tables: Table ‘host’ is read only”

custom mysql datadir? removed apparmor and still having mysql process failing to start issues? try this. may help some of you depending on your config. worked for me:

when using a custom mysql (5.5) data dir as described above- ie., if the abs. path to datadir (my.cnf) is /opt/some/other/mysql

even with apparmor removed/purged, you must make certain that permissions on ALL of the folders in the mysql datadir path are perm'd correctly. this is a mysql convention- not related to apparmor.

look at /var/lib/mysql- the default datadir (my.cnf) on most installations: perms are 755 for /var and /var/lib.

in my custom datadir example above, when I set any/all of the dirs called 'opt' or 'some' or 'other' without an execute permission for allusers (ie., 750), mysql will not start. once i add an execute perm to allusers (ie. 755, 771, etc.), BINGO. mysql starts.

Best Answer

Related Solutions

MySQL Stored Procedure – Loop Through Table and Delete Rows

MySQL gives me:“Can’t open and lock privilege tables: Table ‘host’ is read only”

Related Question