I have a web application, which constantly receives the database, the application is all linked to the database.
If a change is made it can compromise the whole system, I wanted to be able to go back a point when needed.
GIT is for file versioning.
Would you have any tools or technology to assist in controlling database data?
Starting with version 9i of the Oracle database, a concept called undo was introduced, which allows you to recover deleted or changed data over a period of time. Example select * from clients as of timestamp systimestamp - interval '10' minute. I do not know if it exists for mysql, I would have to search.
In this link is well explained this concept.
link
There is no automatic solution for this, you have to create the versioning engine manually.
You can for example insert a field called VERSAO in the table and do not allow update in the registers, to update a record you create a new one with the number of the increased version. Of course this will inflate your table, so you should be careful when defining which tables in your system you want to keep this revision history.
Another option is to create another table only to keep the old versions of the registry, for example you can have a CLIENTS table and a CLIENTS_HISTORY table, which has the same fields, plus a version number, revision date, user that made the change, etc. This process can be done via triggers to facilitate. When "editing" a record you must enter the current record in the revision table. When deleting a record you can make a physical deletion in cascade (delete in both tables) or make a logical deletion (mark the record as disabled).
All of this is laborious, but if your system needs to track log records for audit reasons, these are a few options. I recommend the second one.
This topic has more discussions about subject.
