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
John Doe, red, boat
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?