Archival copy of table, including overwrote data 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?
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.