Home How to implement logging of all changes mysql database?
Reply: 1

How to implement logging of all changes mysql database?

LostDok
1#
LostDok Published in 2018-02-14 18:04:42Z

I need to track all changes in database: update, insert, delete. I want know WHO and WHAT change.

Need log table with columns:

  1. Date.
  2. Table name.
  3. Column.
  4. Old value.
  5. New value.
  6. IP.

What is the best way to implement this?

Are there ready-made solutions?

I tried use triggers on update / insert / delete. This is a good solution? Or maybe I do not know something about the correct logging in mysql?

My trigger:

CREATE TRIGGER `user_update_trigger`
AFTER UPDATE ON `users`
FOR EACH ROW
BEGIN

DECLARE done int default false;
DECLARE col_name CHAR(255);

DECLARE counter INTEGER(11);

DECLARE column_cursor cursor for SELECT `column_name`
                    FROM `INFORMATION_SCHEMA`.`COLUMNS` 
                    WHERE `TABLE_SCHEMA`='test' 
                    AND `TABLE_NAME`='users';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

open column_cursor;

myloop: loop

fetch column_cursor into col_name;

if done then
    leave myloop;
end if;

/*SET @old_val = OLD.{{col_name}}; <------ HOW GET VALUE? */
/*SET @new_val = NEW.{{col_name}};<------ HOW GET VALUE? */

if @old_val <> @new_val then
    /*INSERT INTO `log` ....*/
end if;


end loop;

close column_cursor;

END;
4givN
2#
4givN Reply to 2018-02-14 22:54:33Z

enable mysql audit log filtering. You can do something like

    {
  "filter": {
    "class": [
      {
        "name": "connection",
        "event": [
          { "name": "connect" },
          { "name": "disconnect" }
        ]
      },
      { "name": "general" },
      {
        "name": "table_access",
        "event": [
          { "name": "insert" },
          { "name": "delete" },
          { "name": "update" }
        ]
      }
    ]
  }
}

or

    "event": [
  { "name": "select", "log": false },
  { "name": "insert", "log": true },
  { "name": "delete", "log": true },
  { "name": "update", "log": true }
]
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.293889 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO