如何查找在最近一小时内添加或更新的MySQL视图或触发器？

Question

我需要追踪数据库更改。已经可以从相关的INFORMATION_SCHEMA表中获取关于表、函数和存储过程的这些信息。但是在视图和触发器的情况下，似乎没有创建或修改日期时间的数据。如何实现这一点？

- Hardeep Singh

---

不，MySQL并不支持这样的跟踪器。我建议您将这些触发器和视图放在一个表中。在表更改时，您将运行一个触发器来更改特定的触发器/视图代码。在此之前，您必须将以前的触发器/视图转储到“dump”触发器/视图表中。 - Akhter Al Amin

你使用的是哪个版本的MySQL？ - Steve Chambers

Answer 1

对于触发器的“创建”日期/时间，INFORMATION_SCHEMA.TRIGGERS中有一个CREATED字段。但根据MySQL文档，这只在MySQL版本5.7.2或更高版本中才正确填充：

CREATED：触发器创建时的日期和时间。对于在MySQL 5.7.2或更高版本中创建的触发器，这是一个带有小数部分（以秒为百分之几）的TIMESTAMP（2）值；对于在5.7.2之前创建的触发器，则为NULL。

但不幸的是，没有相应的“更新”或“删除”日期/时间列，INFORMATION_SCHEMA.VIEWS表也没有这些内容。我最初的想法是在相关的INFORMATION_SCHEMA表上创建触发器，但不幸的是，这似乎是不可能的，因为INFORMATION_SCHEMA表实际上更像是“视图”（但不是基于实际表）。因此，以下是“下一个最佳”的解决方案... 解决方法： “快照”表 使用MySQL事件调度程序创建一个定期运行的计划事件，每小时运行一次查询，将INFORMATION_SCHEMA.TRIGGERS和INFORMATION_SCHEMA.VIEWS表的内容复制到本地数据库中的“快照”表中：

-- 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;

然后在任何时候，都可以使用像这样的技术来查询当前的INFORMATION_SCHEMA表并将其与快照进行比较：

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)

局限性

这些内容不能精确地告诉您更改是何时进行的，也不能完全覆盖过去一小时 - 只能涵盖自上次快照以来的时间，这可能是在过去一小时内的任何时间。如果需要比这更高的准确度，则可以缩短快照间隔 - 但这会增加复杂性，因为可能需要维护多个快照，并且必须使用正确的快照进行比较。

- Steve Chambers

---

通过上述代码，我们无法获取已更新的视图。实际上，我需要在开发人员之间同步数据库，因此我需要找到已更新或创建的视图、触发器、函数和过程。 - Hardeep Singh

Answer 2

前往您的数据目录并检查修改日期时间。

- Phaneendra Kasalanati

Answer 3

如果仅涉及结构更改且您只需要跟踪它们，则另一种方法是使用mysqldump，它将为您提供所有当前结构：

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

使用 skip-comments 选项可以使其随时间保持一致，只有在结构发生变化时才会改变。您可以进行差异比较：

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

甚至可以将单个文件放入版本控制系统中，这将让您使用钩子和其他“更改触发器”：

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

很明显，这些方法并没有提供关于更改何时发生，或者由谁/为什么进行更改的任何信息。

- Jeremy Jones