0

I have database server, with a database and table. The database and table can not be changed by me to ensure 3rd party support but the server configuration and MySQL configuration can be. On that table data is overwrote like so,

John Doe, red, car
Jane Doe, blue, boat

Incoming data John Doe, red, boat

results in

John Doe, red, boat
Jane Doe, blue, boat

A second database server, with a database and table that I can and need to change. Needs to contain a modifiable copy of the table but with the historical data. With the above example, it needs to contain,

John Doe, red, car
Jane Doe, blue, boat
John Doe, red, boat

Right now I have this working, but if you are wondering why I am posting this, it's because I don't like the current process. It's error prone. Right now I am using mysqlbinlog to read a binlog file from the 1st server in mixed mode and greping and awking and sed'ing the info I need and then changing the statements to UPDATE instead of REPLACE. Then I pipe that into MySQL. All on one line. This of course is far from ideal. No error checking, no daemon, no reconnection. If the source or destination server dies, all goes up in smoke.

Any ideas on a better method?

closed as unclear what you're asking by mustaccio, Colin 't Hart, Mr.Brownstone, hot2use, Max Vernon Nov 20 '18 at 15:10

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

There are multiple ways you can solve this problem, but this would be the quickest and dirtiest.

You would create a trigger that executes during every change to a row inside the table you want to essentially "version". The trigger copies the change in information as a completely new row inside another table (I call them an audit table, I.E: users_audit). You would be creating the trigger on your replica (not your master). The replica would contain the new AuditLog table and in return would contain all the rows that were changed for the original table on your master. Of course you would need to version the rows, for which, I recommend using a TIMESTAMP column. This post below gives a good explanation of this technique:

https://stackoverflow.com/questions/12563706/is-there-a-mysql-option-feature-to-track-history-of-changes-to-records

Hope this helps.

Not the answer you're looking for? Browse other questions tagged or ask your own question.