CODEWITHSUNDEEP
mysql
Yevgeny Simkin
Yevgeny Simkin

Reputation: 28349

Copy rows from one database to another

I have two databases with the same schema (dev/prod) hosted on different machines (and different hosts).

Is there any mechanism or tool whereby I can do a select against specific rows in one db and insert them into the other?

Upvotes: 1

Views: 570

Answers (1)

eggyal
eggyal

Reputation: 125865

You can use MySQL's FEDERATED storage engine:

The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.

So, to create a connection:

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

Having defined such a table, you could then perform INSERT ... SELECT as you see fit:

INSERT INTO federated_table SELECT * FROM local_table WHERE ...

Or

INSERT INTO local_table SELECT * FROM federated_table WHERE ...

If you are federating multiple tables from the same server, you may wish to use CREATE SERVER instead.

Upvotes: 1

Related Questions