Copy data from 1 DB to another DB

Question

I have to delete some table data in prod. db and for the records that are going to be deleted a backup of records should be copied to another local db. This involves two databases, residing in two different servers/instances.

Is it possible to do via sql (mysql) query to do this?

Answer 1

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:

1. copy data to a local database then replicate that database to another server
2. 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

Answer 2

I would use mysqldump with a where condition to get the records out. Once you have everything saved, you can then delete them from prod. These commands should work from the command line, including the password to avoid the prompt is optional.

mysqldump -u user -pPassword -h hostname1 dbname tablename 
    --where 'field1="delete"' 
    --skip-add-drop-table --no-create-db --no-create-info > deleted.sql
mysql -u user -pPassword -h hostname2 dbname < deleted.sql
mysql -u user -pPassword -h hostname1 dbname -e 'DELETE FROM tablename WHERE field1="delete"'