Register changes in Mysql

Question

Precise record all changes in the database, as UPDATE, INSERT and so on, so that I can restore the last backup and run queries from the last backup, manually deleting the unwanted wishes.

I read about the Binary log of Mysql, but it is possible delete the unwanted queries and open the log file in a user-friendly way for editing?

I need something like this:

-- [...]
-- 2017-04-11 22:07:32
UPDATE foo SET bar = 'fubá';
-- 2017-04-11 22:08:07
DELETE FROM tabela WHERE id = 127;
-- [...]

Use Mysql 5.7.14 in the Windows

Answer 1

Good morning,

I need to record all changes in the database, such as UPDATE, INSERT and etc » I suggest you do it on your own, create a table log or similar to the information desired, such as date and time, description, operation performed and which user was. I have been doing this for several years and the logs have already saved me from typical situations like "lost the X client..."

Now, so that I can restore the last backup and run queries from the last backup it seems much more complicated. I suggest you schedule hourly, or optimum frequency, backup dumps and have these dumps as your Restore option.

but it is possible to delete unwanted queries and open the log file in a user-friendly way for editing? » I may be mistaken, but reading like this seems to me a solution "too magical" to solve a very complex situation. I would try with the backup dumps and, if necessary, have the queries in the log table and run them in a maintenance routine. But again, I would implement this via programming, for example in PHP.

I consider potentially dangerous to manually edit backup Sqls before making a Store, I try to avoid this type of approach.

Good luck!