mysql audit 表_我應該如何審核MySQL表中的更改（使用MySQL 4）？

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支持,可能會做你想要的: