How to find MySQL Views or Triggers that have been added or updated in the last hour?

Question

I need to track database changes. Can already get this information for Tables, Functions and Stored Procedures from the relevant INFORMATION_SCHEMA tables. But in the case of Views and Triggers there doesn't seem to be data for CREATED or MODIFIED DateTime. How can this be achieved?

Answer 1

For a trigger's "created" date/time, there is a CREATED field in INFORMATION_SCHEMA.TRIGGERS. But according to the MySQL documentation, this is only correctly populated in MySQL version 5.7.2 or higher:

CREATED: The date and time when the trigger was created. This is a TIMESTAMP(2) value (with a fractional part in hundredths of seconds) for triggers created in MySQL 5.7.2 or later, NULL for triggers created prior to 5.7.2.

But unfortunately there isn't an equivalent "updated" or "deleted" date/time column and the INFORMATION_SCHEMA.VIEWS table does not have any of these. My first thought for a possible workaround was to create triggers on the relevant INFORMATION_SCHEMA tables but unfortunately this doesn't seem to be possible as the INFORMATION_SCHEMA tables are actually more like "views" (but not based on actual tables). Hence the following "next best" solution...

Workaround: "Snapshot" tables

Create a scheduled event using the MySQL event scheduler that runs every hour and runs queries to copy the contents of the INFORMATION_SCHEMA.TRIGGERS and INFORMATION_SCHEMA.VIEWS tables into "snapshot" tables in a local database:

-- Remove existing event of this name if there is one
DROP EVENT IF EXISTS update_snapshots;

DELIMITER $$
CREATE EVENT update_snapshots ON SCHEDULE EVERY 1 HOUR 
    DO BEGIN
        -- Drop the current snapshot table (if there is one)
        DROP TABLE IF EXISTS triggers_snapshot;
        DROP TABLE IF EXISTS views_snapshot;

        -- Recreate snapshot tables
        CREATE TABLE triggers_snapshot AS SELECT * FROM INFORMATION_SCHEMA.TRIGGERS;
        CREATE TABLE views_snapshot AS SELECT * FROM INFORMATION_SCHEMA.VIEWS;
    END $$
DELIMITER ;

-- Turn the MySQL event scheduler on
SET GLOBAL event_scheduler = ON;

-- Show all events (to check it was created successfully and its status)
SHOW EVENTS;

Then at any point the current INFORMATION_SCHEMA tables can be queried and compared with the snapshots using a technique such as this one:

SELECT  'new' AS `status`, s.*
FROM    INFORMATION_SCHEMA.TRIGGERS s
WHERE   ROW(s.TRIGGER_CATALOG, s.TRIGGER_SCHEMA, s.TRIGGER_NAME, s.EVENT_MANIPULATION,
            s.EVENT_OBJECT_CATALOG, s.EVENT_OBJECT_SCHEMA, s.EVENT_OBJECT_TABLE,
            s.ACTION_ORDER, s.ACTION_CONDITION, s.ACTION_STATEMENT, s.ACTION_ORIENTATION,
            s.ACTION_TIMING, s.ACTION_REFERENCE_OLD_TABLE, s.ACTION_REFERENCE_NEW_TABLE,
            s.ACTION_REFERENCE_OLD_ROW, s.ACTION_REFERENCE_NEW_ROW, s.CREATED, s.SQL_MODE,
            s.DEFINER, s.CHARACTER_SET_CLIENT, s.COLLATION_CONNECTION, 
            s.DATABASE_COLLATION) NOT IN (SELECT * FROM triggers_snapshot)
UNION ALL
SELECT  'old' AS `status`, t.*
FROM    triggers_snapshot t
WHERE   ROW(t.TRIGGER_CATALOG, t.TRIGGER_SCHEMA, t.TRIGGER_NAME, t.EVENT_MANIPULATION,
            t.EVENT_OBJECT_CATALOG, t.EVENT_OBJECT_SCHEMA, t.EVENT_OBJECT_TABLE,
            t.ACTION_ORDER, t.ACTION_CONDITION, t.ACTION_STATEMENT, t.ACTION_ORIENTATION,
            t.ACTION_TIMING, t.ACTION_REFERENCE_OLD_TABLE, t.ACTION_REFERENCE_NEW_TABLE,
            t.ACTION_REFERENCE_OLD_ROW, t.ACTION_REFERENCE_NEW_ROW, t.CREATED, t.SQL_MODE,
            t.DEFINER, t.CHARACTER_SET_CLIENT, t.COLLATION_CONNECTION, 
            t.DATABASE_COLLATION) NOT IN (SELECT * FROM INFORMATION_SCHEMA.TRIGGERS)

...and...

SELECT  'new' AS `status`, s.*
FROM    INFORMATION_SCHEMA.VIEWS s
WHERE   ROW(s.TABLE_CATALOG, s.TABLE_SCHEMA, s.TABLE_NAME, s.VIEW_DEFINITION,
            s.CHECK_OPTION, s.IS_UPDATABLE, s.DEFINER, s.SECURITY_TYPE,
            s.CHARACTER_SET_CLIENT, s.COLLATION_CONNECTION)
        NOT IN (SELECT * FROM views_snapshot)
UNION ALL
SELECT  'old' AS `status`, t.*
FROM    views_snapshot t
WHERE   ROW(t.TABLE_CATALOG, t.TABLE_SCHEMA, t.TABLE_NAME, t.VIEW_DEFINITION,
            t.CHECK_OPTION, t.IS_UPDATABLE, t.DEFINER, t.SECURITY_TYPE,
            t.CHARACTER_SET_CLIENT, t.COLLATION_CONNECTION)
        NOT IN (SELECT * FROM INFORMATION_SCHEMA.VIEWS)

Limitations

These won't tell you exactly when the changes were made and doesn't exactly cover the last hour - only the time since the last snapshot, which could be any time in the last hour. If more accuracy than this is required, the snapshot interval could be decreased - but this would add complexity as potentially multiple snapshots would then need to be maintained and the correct one used for the comparison.

Answer 2

Go to your data directory and check the modified date time

Answer 3

If it's purely structural changes and you just need to track them, then another approach is to use mysqldump which will give you all the current structure:

mysqldump --no-data --skip-comments dbname

With the skip-comments option it'll be consistent over time and only change if the structure changes. You can diff it:

mysqldump --no-data --skip-comments dbname > /tmp/schema1.sql
# (wait)
mysqldump --no-data --skip-comments dbname > /tmp/schema2.sql
diff /tmp/schema1.sql /tmp/schema2.sql

Or even put a single file into a version control system, which will let you use hooks & other 'change triggers':

mysqldump --no-data --skip-comments dbname > schema.sql && git commit -m "MySQL schema change" schema.sql

Obviously these methods don't give you any information on precisely when, or who/why changes were made.