Recently we saw how we can catch audit information using invisible column, JSON data type and triggers in MySQL 8.0:
Of course, the creation of these triggers can quickly become a tedious operation. Once again, MySQL has all what we need to make it simple.
As you may know, it’s impossible to create triggers from store procedure, so using a sys schema function would not be something possible. However, MySQL Shell is again the answer ! It’s very easy to use a python plugin to perform the necessary operations.
I’ve written such example, available on GitHub.
Let’s see the plugin in action (in a Terminal and in VScode):
We start with a simple table (demo3):
Let’s enable audit (without specifying any parameters, the current schema is used and an external table in information_audit_log is created. The table name from with changes need to be collected will be prompted:
And we can see it in action:
We have the possibility, to disable all triggers to stop collecting the changes and also to remove the content of the audit table:
The advantage to use an external table, is that the delete operations are also collected.
As from the previous articles, we can also use an invisible column in the table we want to audit (if we want to avoid an extra table):
And now the changes (but not the deletes) are triggered into a JSON invisible column:
And you can see the same result in MySQL Shell from a terminal:
In case you modify the schema of an audited table, you will notice that in case of a new column for example, the changes won’t be collected:
You need to update the triggers using the same command (audit.enable()):
After that, the triggers are updated and all columns are tracked:
This article concludes to Poor man’s audit table series. As usual, enjoy MySQL, MySQL Shell and MySQL Shell for VScode.
