Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder mic2100's Avatar
    Join Date
    Feb 2006
    Location
    Scunthorpe
    Posts
    562
    Thanks
    15
    Thanked 28 Times in 27 Posts

    Question mysql changes log

    Hi,

    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)
    timestamp
    ------------------------------------

    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?

    Thanks

    Mic

  • #2
    120
    120 is offline
    Regular Coder
    Join Date
    Nov 2009
    Location
    UK
    Posts
    105
    Thanks
    6
    Thanked 15 Times in 15 Posts
    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.

  • #3
    Regular Coder mic2100's Avatar
    Join Date
    Feb 2006
    Location
    Scunthorpe
    Posts
    562
    Thanks
    15
    Thanked 28 Times in 27 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •