Recording MySQL DELETE statements

Question

We have a MySQL->Oracle ETL using Informatica that works great for all statements except DELETE. Unfortunately, the DELETE makes the record go away such that Informatica never sees it again to remove/expire it in Oracle.

How have people gone about recording MySQL DELETE statements?

The tables are InnoDB (ACID-compliant) with unique primary keys on all records (auto_increment). We're using the open-source MySQL on Windows.

We'd prefer not to use a general query log for performance reasons. We'd also prefer to keep the stock MySQL binary and not recompile our own special DELETE statement.

Answer 1

A possible solution is to never delete anything from your database. I avoid deleting from the database because then the information is lost forever. I prefer to mark information as invalid or obsolete by adding an appropriate column to the table.

Another similar solution is to use a trigger to insert the record you want to delete into an audit table and then delete the information. Use the insert with Informatica to do the same thing on the Oracle side.