I'm trying to do exactly the same thing, copy data from a table to another server, then delete it from the original.
So far I see two options:
- copy data to a local database then replicate that database to another server
- use Federated storage engine
Both require some serious reconfiguration of our servers as neither Federated or binary logging (required for replication) are not enabled. This would take time and it would be best if other solutions could be found.
The process needs to be executed on a daily basis, so it needs to be fully automated.
Perhaps a third option is to automate things with a cron job:
- copy the data to a separate database on the same server
- backup that database with mysqldump in a folder which is linked on the other server too
- on the second server, restore the database from the sql dump