如何检查上一次修改表的时间？

Question

我经常通过mysqldump单独备份表。为了避免不必要的备份，我想检查自上次备份以来表是否已被修改。我所说的“修改”是指任何更改(插入、更新、删除、更改、.)。

使用该模式，我们可以获得类似于UPDATE_TIME的

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'db' AND TABLE_NAME = 't1'

但这还不够。

一种实用的方法是通过常见的Linux命令(如stat、ls等)检查D3的最后修改时间。问题是，我应该给运行用户的脚本访问/var/lib/mysql的权限，而出于安全原因，我不愿意这样做。

是否有任何方法检查表或t1.ibd上一次通过MySQL修改时(而不授予其他用户访问/var/lib/mysql的权限)？

Answer 1

没有一种很好的方法可以将InnoDB表的最后修改时间作为元数据查询。

问题是什么是修改的时间？执行插入/更新/删除的时间？或者包括修改在内的事务提交的时间，可能是几秒钟后？还是数据和索引的修改页面最终被同步到表空间的时间，这可能是几分钟后的事？

如果你转储和恢复表呢？这可能发生在最后一次修改任何数据的几天后，那么这算得上是修改吗？

另一种方法是在表中使用时间戳列，并使其为DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。虽然这只是跟踪插入和更新，而不是删除或更改表。

Answer 2

这是一个非常有趣的附带项目。有几种方法可以做到这一点，而罗兰多共享四种常用方法在这个网站上可以追溯到2014年。尽管如此，有东西告诉我必须有更好的方法..。

您是对的，仅仅在UPDATE_TIME中查看information_schema是不够的，因为当基表是InnoDB时，不会更新这个值。据我所知，确定是否修改了InnoDB表的唯一可靠方法是查询文件系统。但是，正如您所说的，授予任何人对MySQL数据目录的访问权限并不是一个好主意。

因此，考虑到这一点，如果一个数据库很小，由大约十几个表组成，那么实现这一目标的最快方法就是使用一个触发器来更新特定的表，我们称之为TableTracker，也就是说，INSERT、UPDATE或DELETE是在给定的时间内提交的。但是，如果一个数据库由数百个表组成，它也不考虑创建的新表，除非人们记住还包括必要的触发器，否则这并不是特别可伸缩的。从逻辑上看，我们必须查询文件系统。这是自动解决许多潜在问题的唯一方法。

这里有一种可能的方法：

1. 在某个地方的TableTracker数据库中创建一个MySQL表
2. 编写一个shell脚本，该脚本将查询文件系统并记录TableTracker的最后修改时间
3. 在cron作业中自动执行

TableTracker表不需要在被跟踪的同一数据库(或多个数据库)中，但它确实需要存在。这里有张非常粗糙的桌子：

DROP TABLE IF EXISTS `TableTracker`;
CREATE TABLE IF NOT EXISTS `TableTracker` (
    `table_name`    varchar(120)    NOT NULL,
    `created_at`    timestamp       NOT NULL    DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    timestamp       NOT NULL    DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

现在我们需要一个shell脚本。这是在Ubuntu 20.04上编写和测试的，不过它应该可以在任何现代Linux平台上工作：

#!/bin/bash
srcpath="/etc/mysql/my_dbname/*.ibd"
username="watcher"
password="superSecretPassword!123"
dbname="my_dbname"

sql=""
sql+="INSERT INTO TableTracker (table_name, updated_at) "
sql+="SELECT tmp.table_name, tmp.updated_at as updated_at "
sql+="FROM ("

for name in $srcpath; do
    ts=$(date -r $name "+%Y-%m-%d %H:%M:%S")
    fname="${name##*/}"

    tbl="SELECT '${fname%.*}' as table_name, '$ts' as updated_at UNION ALL "
    sql=$sql$tbl
done

sql+="SELECT '' as table_name, '2000-01-01 00:00:00' as updated_at) tmp "
sql+="WHERE tmp.updated_at > '2000-01-01 12:00:00' "
sql+="ON DUPLICATE KEY UPDATE updated_at = tmp.updated_at;"

# Record the Updates to MySQL
mysql -u $username -p$password $dbname <<< $sql

echo "All Done!"

记住使用chmod +x将文件设置为可执行文件。

在那里，您可以将该文件放入cron作业中，并让它尽可能频繁地运行。

注释：

• 这个粗糙的工具可以用来监视多个数据库。
• 这个工具可以设置为监视任何数据库文件，而不仅仅是InnoDB文件。
• 如果这在主数据库服务器上运行，那么文件中不需要存在MySQL密码，因为Ubuntu中的root可以(通常)直接访问MySQL上的root。
• 没有错误检查，因此如果在生产环境中使用了shell脚本，请确保清除该脚本。