I am tasked with getting a copy of a table on a MySQL 4.0 server synced to a completely different database on a MySQL 5.6 server every few minutes for the purposes of having that table available during a migration phase for a really old application. Basically this:
10.10.10.10:oldb.table -> 22.214.171.124:newdb.table
The table is ~65,000 rows and ~18MB.
Obviously I cannot use replication with version 4.0. I have looked at using mysqlhotcopy, but it appears that it only copies entire databases, and I need the table to live in a different database on the destination host. I have also looked at using mysqldump, however I cannot have the table on the new server be dropped for the new data to be inserted resulting in queries that see an empty table, and if I use --insert-ignore --no-create-db --no-create-info it still does not account for deleted rows on the source table. I also looked at selecting all rows from the existing table and doing a REPLACE INTO, or INSERT/UPDATE ON DUPLICATE KEY but this would also not account for rows that were deleted from the source table. Any thoughts on how to accomplish this?