View Full Version : mysql changes log

03-18-2010, 06:25 PM

Here is my problem, I have a project management style system and I want to be able to record what changes have been made and who changed them. This isn't just for manager to point the blame but more for the ability to undo changes that were done by clumsey users. I want to store UPDATE's and DELETES but I am a little worried about how much space this might take up in a DB table.

Data to store
User ID
Type - Update or Delete
Table changed (name of the table)
Field changed (name of the field)
Value changed (from & to)

i was thinking about writing a script to remove changes after a week or month or sumthing but on a heavly used system the table size for this will grow very quickly.

Can anyone advise me on how they think the best way is to handle this?



03-18-2010, 06:44 PM
I've seen some commercial systems 'snapshop' the database (basically a mysqldump) at regular intervals on a crontab - but this probably won't give you the audit info you want. It will, however, give a restore point.

You could write a simple text log file instead - based on the query, this you can download via FTP for 'off server' storage if you wish. You would need to maintain or rotate them and you may be able to get the server to do the donkey work for you with the 'log rotate' command if you have suitable access.

03-18-2010, 06:58 PM
hi 120,
Thanks for the quick response

I had thought of doing this but im not sure that this would work as i want it to. I could potentially have 100/1000's of users using this system all from the DB and not all looking at the same info. The info any user sees is account specific so if i was to do a rollback it would effect more that just the user who as deleted or updated the wrong information. So that method of doing a DB snapshot wudn't really work howeva one of these would be done anyway as a way to backup all the data.

The flat file system i think could work quite well although i would be worried about the disk space some of these files might start to take up.