How to version control data stored in mysql

I'm trying to use a simple mysql database but tweak it so that every field is backed up up to an in开发者_运维技巧definite number of versions. The best way I can illustrate this is by replacing each and every field of every table with a stack of all the values this field has ever had (each of these values should be timestamped). I guess it's kind of like having customized version control for all my data..

Any ideas on how to do this?

The usual method for "tracking any changes" to a table is to add insert/update/delete trigger procedures on the table and have those records saved in a history table.

For example, if your main data table is "ItemInfo" then you would also have an ItemInfo_History table that got a copy of the new record every time anything changed (via the triggers).

This keeps the performance of your primary table consistent, yet gives you access to the history of any changes if you need it.

Here are some examples, they are for SQL Server but they demonstrate the logic:

My Repository table My Repository History table My Repository Insert trigger procedure My Repository Update trigger procedure

Hmm, what you're talking about sounds similar to Slowly Changing Dimension.

Be aware that version control on arbitrary database structures is officially a rather Hard Problem. :-)

A simple solution would be to add a version/revision field to the tables, and whenever a record is updated, instead of updating it in place, insert a copy with the changes applied and the version number incremented. Then when selecting, always choose the record with the latest version. That's roughly how most such schemes are implemented (e.g. Wikimedia does it pretty much this exact way).

Maybe a tool can help you to do that for you. Have a look at nextep designer :

https://github.com/christophefondacci/nextep-designer

With this IDE you will be able to take snapshots of your database structure and data and put it under version control. After this you can compute the differences between any 2 versions and generate the appropriate SQL that can insert / update / delete your data.

Maybe this is an alternative way to achieve what you wanted.