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 -> 20.20.20.20: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?
Best Answer
This looks interesting and might (just might) be a solution to your issue. It shows how a 4.1 server can simulate Stored Procedures (SPs - not available till version 5 of the MySQL server) using the Federated storage engine. Basically, it shows that a 5.x server can access data on a 4.x server and even update that data. It also shows that you can use 5.x to read the data also. This is probably a good fit for your requirements, however the federated engine does not support transactions. It's also covered here (Morgan Tocker is a MySQL community manager).