I have been asked to audit any/all changes in a MySQL table. Does anyone know of any tools out there to help me do this or do I need to write my own solution?
我被要求审核MySQL表中的任何/所有更改。有没有人知道有什么工具可以帮助我这样做,还是我需要编写自己的解决方案?
If I write my own audting, my initial thought it to make a separate table and build a string of changes in the PHP code. Something like "fieldname1 -> oldvalue | fieldname2 -> oldvalue, ...". If you see a major problem with this method, please let me know.
如果我编写自己的审计,我最初认为它是一个单独的表,并在PHP代码中构建一系列更改。像“fieldname1 - > oldvalue | fieldname2 - > oldvalue,...”之类的东西。如果您发现此方法存在重大问题,请告知我们。
4 个解决方案
#1
3
The only sure-fire way to capture all changes to a DB table is to use triggers on the Server. The risk of modifing your own code to audit the changes is that changes from another application/user etc will not be captured.
捕获数据库表的所有更改的唯一可靠方法是在服务器上使用触发器。修改自己的代码以审核更改的风险是不会捕获来自其他应用程序/用户等的更改。
Having said that, I'm not sure that MySQL 4 had trigger support.
话虽如此,我不确定MySQL 4是否有触发支持。
#2
3
Use a trigger to detect changes and to write before/after values to a log table.
使用触发器检测更改并在值之前/之后写入日志表。
#3
2
If you wind up hand-rolling a solution due lack of trigger support, I strongly recommend that you don't just dump the changes in a string blob. One day you will be asked to query that data and you will wind up having to do a bunch of string parsing to get your data back out. (I speak from experience here.)
如果由于缺乏触发器支持而最终手动滚动解决方案,我强烈建议您不要只是将更改转储到字符串blob中。有一天,您将被要求查询该数据,您最终将不得不进行一系列字符串解析以恢复数据。 (我从这里的经验说起。)
The simplest approach is just to create a shadow audit table that has all of the same columns as the original table, plus a change date column and a sequential id. Then you have the entire history at hand to reconstruct in whatever format you need, and you can query it at will.
最简单的方法是创建一个影子审核表,该表具有与原始表相同的所有列,以及更改日期列和顺序ID。然后,您可以手头拥有完整的历史记录,以您需要的任何格式进行重建,您可以随意查询。
#4
0
SoftTree DB Audit has MySQL support, might do what you're after:
SoftTree数据库审计有MySQL支持,可能会做你想要的:
#1
3
The only sure-fire way to capture all changes to a DB table is to use triggers on the Server. The risk of modifing your own code to audit the changes is that changes from another application/user etc will not be captured.
捕获数据库表的所有更改的唯一可靠方法是在服务器上使用触发器。修改自己的代码以审核更改的风险是不会捕获来自其他应用程序/用户等的更改。
Having said that, I'm not sure that MySQL 4 had trigger support.
话虽如此,我不确定MySQL 4是否有触发支持。
#2
3
Use a trigger to detect changes and to write before/after values to a log table.
使用触发器检测更改并在值之前/之后写入日志表。
#3
2
If you wind up hand-rolling a solution due lack of trigger support, I strongly recommend that you don't just dump the changes in a string blob. One day you will be asked to query that data and you will wind up having to do a bunch of string parsing to get your data back out. (I speak from experience here.)
如果由于缺乏触发器支持而最终手动滚动解决方案,我强烈建议您不要只是将更改转储到字符串blob中。有一天,您将被要求查询该数据,您最终将不得不进行一系列字符串解析以恢复数据。 (我从这里的经验说起。)
The simplest approach is just to create a shadow audit table that has all of the same columns as the original table, plus a change date column and a sequential id. Then you have the entire history at hand to reconstruct in whatever format you need, and you can query it at will.
最简单的方法是创建一个影子审核表,该表具有与原始表相同的所有列,以及更改日期列和顺序ID。然后,您可以手头拥有完整的历史记录,以您需要的任何格式进行重建,您可以随意查询。
#4
0
SoftTree DB Audit has MySQL support, might do what you're after:
SoftTree数据库审计有MySQL支持,可能会做你想要的: