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?

@Sharpeye: I am sure you can do it via mysql query. But you can also do it via phpmyadmin, just a simple export then import I am afraid? — Michael Mao
– Michael Mao, Commented Aug 3, 2010 at 23:05
Its going to between 2 databases residing in two different servers. — Sharpeye500
– Sharpeye500, Commented Aug 3, 2010 at 23:07
Actually am building an application for this, internally i need to pass mysql statement to handle this. When do you delete some data in prod. db, the same data must be copied to another server's mysql db for backup purpose. — Sharpeye500
– Sharpeye500, Commented Aug 3, 2010 at 23:09
@ OMG Ponies - Thanks for the link, but will that help if you have DB in two different servers. — Sharpeye500
– Sharpeye500, Commented Aug 3, 2010 at 23:10

Brent Baisley · Accepted Answer · 2010-08-30 15:51:09Z

1

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"'

answered Aug 30, 2010 at 15:51

Brent Baisley

12.7k2 gold badges28 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ceteras · Accepted Answer · 2010-08-30 15:23:55Z

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

Collectives™ on Stack Overflow

Copy data from 1 DB to another DB

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related