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

How to implement logging of all changes mysql database?

user1361
1#
user1361 Published in June 19, 2018, 4:33 pm

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;
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO