mysql数据变更监控 mysql 监控表数据变化

转载

码农小哥 2023-08-02 23:20:59

文章标签 mysql数据变更监控 mysql状态监听 mysql 数据 sql 文章分类 MySQL 数据库

应用场景：

长时间运行程序，需要几乎整表查询Mysql，还得在可容忍范围内响应数据变化。

方案一：通过Mysql自带的表更新时间

查询方案：SELECT TABLE_NAME,UPDATE_TIME FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA='Palas_V4';

存在问题：innodb 不支持，需要更换数据库引擎；只支持表级判断(可以接受)。

优点：查询速度快。

变种解决方案：1、创建触发器，在表执行update/insert/delete 时更新UPDATE_TIME为当前时间戳；2、在数据库访问接口处增加程序逻辑来更新该时间戳。

方案二：在所有行增加更新时间戳字段

查询方案：判断是否修改只需查询该表最大的updatetime，增量更新仅需要查询大于上次查询时间的数据。

存在问题：表每行会增加4bytes的空间消耗，更新时间戳问题可通过mysql自身解决；不支持delete操作，如要删除需要增加是否有效字段来改变状态。

优点：可以做到行级的更新。

方案三：读取Mysql文件的系统修改时间

查询方案：获取E:/SqlData/data/Palas_V4/ Media.frm 的系统修改时间

存在问题：需要在Mysql所在服务器操作，或者需要远程访问它的权限，可能导致安全问题；只支持表级判断。

优点：速度快，不需要额外表空间或增加mysql负载。

变种解决方案：1、在Mysql所在服务器创建监控服务提供数据库状态信息。

方案选型：

优缺点分析：

方案一与二均需对数据库做出相应修改，一所做修改对业务逻辑不会有影响，但对Mysql的操作会增加一倍(实际修改需要8(可能是4)个字节每次的数据量)无网络传输；程序实现逻辑简单；在数据修改量不大的情况话对性能几乎没有影响，但修改后查询开销大。方案二对表空间的增长会变大，对业务逻辑会有较小的影响(不再支持删除)，部分表还需要增加字段判断状态，修改后查询开销较小。方案三性能与一相差不大，但需要额外编程提供状态查询，时间和硬盘IO会增大(不显著)。

选型：

根据实际情况，排除暂时不会遇到的问题，优先级排列如下：

方案三 -> 方案二 –> 方案一 (方案一排最后是由于太多触发器会导致表维护困难，更新引擎将使数据库性能降低)

Mysql 开启独立表空间：

1. 停掉mysql： /etc/init.d/mysqld stop

2. 改my.cnf的配置文件： innodb-file-per-table=1

3. 备份使用innodb引擎的数据库： mysqldump -u tg -p tg >/home/6fan/tg.sql;

4. 删除使用innodb的数据库，以及日志文件。如果不删除使用innodb的数据库文件夹，启动不了innodb引擎。

5. 启动mysql ： /etc/init.d/mysqld start

6. 导入数据库： mysql -u root -p < /home/6fan/tg.sql

有图形化管理工具的在修改配置文件后直接备份需要监听的数据库，然后删除后恢复也是可以的。

构建文件监听与数据提供服务：