MySQL – Watch ‘Alter Table’ Statement Changes with Triggers

alter-tableMySQLtrigger

I have tried to find any way to track changes in tables where the ALTER TABLE statement was used.

It is known that the trigger event can be INSERT, UPDATE or DELETE according to ==> Mysql triggers

Anyway I tried to use this query to enable trigger for ALTER TABLE ==> CREATE TRIGGER my_trigger ALTER TABLE ON my table ... but it did not work.

Is there any way to watch changes in a mysql table if it was changed by using ALTER TABLE statement?

Best Answer

Just some ideas:

Set a trigger on INFORMATION_SCHEMA

Not possible, according to docs:

The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

Monitor the DDL log

I wouldn't go down that road (docs):

The DDL log, or metadata log, records metadata operations generated by data definition statements such as DROP TABLE and ALTER TABLE. [...] This is a binary file; it is not intended to be human-readable...

Use events and track changes yourself

You could keep a copy of the relevant INFORMATION_SCHEMA tables, schedule a recurring event and check for any difference between your copy and the current state.

You would need a very strong use case to justify the effort to develop this, I think.

Track the binary log or general log

If you just want to get a quick heads up, when the schema has changed, you might try to set up a cron job and enable either (a) binary logging to use mysqlbinlog to check for ALTER TABLE statements or (b) to enable the general log and do the same. You could then send yourself an email, when that happens.

Might be more or less reliable than trusting the developers to notify you - depending on your implementation and on the developers ;)

Related Question