MySQL Logging

[Guest Nascent]

Is there an easy way to keep track of the changes in a database using MySQL? I could always back it up every day and go through it to see if anyone made any changes, but I want only specific changes to be recorded (example: INSERT queries). I want a log file to be constantly updated with what was changed in the database and who changed it.Thanks,NascentPS I use phpMyAdmin to manage my database...

[aspnetguy]

Are the scripts that make the INSERT statements ones you have written or are they third party?Either way, but if they are your scripts this will be easier, you can create a log function and use it everytime an INSERT statement is called in your code. The function can update a seperate table with info about the insert.

[aquatsr]

Well since you're using php...What I'd do is find out what table is being modified i.e. TABLE booksandwhat information in the table CAN be modified, i.e. title, author, isbn, publisher,and store those in respective variables i.e. $table, $title, $author, $isbn, $publisherand also find out who is executing scripts i.e. $user = johnsmith.Then in a table called log in your SQL database make some sort of a table like so:

CREATE TABLE log (log_id INT(11) NOT NULL AUTO_INCREMENT,table_mod varchar(150),title_mod varchar(150),author_mod varchar(150),isbn_mod varchar(150),publisher_mod varchar(150),modded_by varchar(150),PRIMARY KEY (log_id));

Then whenever the primary INSERT query is executed, you can execute a query of your own that stores what the query modified in your database.Of course this depends on how your tables are set up in your database and looking back I actually wouldn't use my code up there ^^. But hopefully this helps you out.