CODEWITHSUNDEEP
mysqltriggersaudit
Arul
Arul

Reputation: 43

Track all the DML/DDL changes of DB in a log table using trigger in Mysql

I would like to track all the DB changes happening on particular DB using one log table.

I have checked many solutions but they all give one audit table for each table in DB. How can we track them in one single table with the help of a trigger?

Table columns may have like :

  id - primary key
  db_name -- DB Name
  version, -- Ignore it(i have a column in my table)
  event_type, -- DDL/DML command name
  object_name, -- Table/Procedure/Trigger/Function name which is changed
  object_type, -- TYpe like table,procedure,trigger
  sql_command, -- query executed by user
  username, -- who executed it
  updated_on -- timestamp

Thanks in advance.

Upvotes: 1

Views: 4258

Answers (1)

Solarflare
Solarflare

Reputation: 11106

A trigger that is called when ddl commands are executed (so you can log them) does not exist in mysql. But you may want to use logfiles, especially the The General Query Log:

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

The log is disabled by default, and enabling it may reduce performance a bit. And it will not include indirect changes (e.g. ddls executed inside a procedure).

If you can install a plugin, a slightly more configurable (and more performant) alternative would be to use an audit plugin, see MySQL Enterprise Audit, or any free implementation, e.g. this one, or you can write your own, but it will basically log the same things as the general log.

Another great source of information might be the information schema and the performance schema. From there you can collect basically every information you need (especially the log of recently executed queries) and generate your log table from that, but it would require some work to gather all the data you want - and it will not be triggered by actions, so you have to periodically check for changes yourself (e.g. compare the data in INFORMATION_SCHEMA.TABLES with a saved copy to keep track of added, deleted and renamed tables). On the other hand, a periodically mysql_dump followed by a diff to the most recent version might be a lot easier.

Upvotes: 3

Related Questions