Is there a way to know what are all the tables changed

Question

I am debugging an web application that uses MySQL. I am seeing lot of tables getting modified between different flows ( enrollment , edit etc .. ) . And also , it is very difficult to go through the code base to understand what are all the tables got modified between different invocations .

Is there a better wat to know what are all the tables got modiifed between two times ( two invocations ).

Do you mean that the tables' structure changed or that data was added? You should be able to add triggers to the tables that can log when data is added. — No'am Newman, Mar 28 '12 at 11:44

score 4 · Answer 1 · edited May 23 '17 at 12:27

Have general query log enabled to know what all queries are getting executed.

I came across this- How can I tell when a MySQL table was last updated?

In later versions of MySQL you can use the information_schema database to tell you when another table was updated:

SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'dbname' AND TABLE_NAME = 'tabname'

This does of course mean opening a connection to the database.

safarov · Accepted Answer · 2012-04-05T07:42:48.000

2

See show-table-status

SHOW TABLE STATUS FROM db_name WHERE Update_time BETWEEN (<date1> AND <date2>)

edited Apr 05 '12 at 07:42

answered Mar 28 '12 at 11:48

safarov

7,793
2
36
52

score 2 · Answer 3 · answered Mar 28 '12 at 11:50

2

The following will return you different timestamps about your database. See if this helps you.

SELECT *
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'dbname'

You may also consider using a mysql profiler like http://www.jetprofiler.com/

answered Mar 28 '12 at 11:50

Rahul

1,495
1
15
25

score 1 · Answer 4 · answered Mar 28 '12 at 11:50

Enable binlog on your server, which is better for your purpose than the querylog since it only contains changes (updates, inserts, deletes).

Hoevery, you have to use mysqlbinlog commandline tool to transform it into a plain text file:

mysqlbinlog server-bin.00001 > binlog.txt

Is there a way to know what are all the tables changed

4 Answers4