How can I transfer data between 2 MySQL databases?

Question

I want to do that using a code and not using a tool like "MySQL Migration Toolkit". The easiest way I know is to open a connection (using MySQL connectors) to DB1 and read its data. Open connection to DB2 and write the data to it. Is there a better/easiest way ?

Answer 1

First I'm going to assume you aren't in a position to just copy the data/ directory, because if you are then using your existing snapshot/backup/restore will probably suffice (and test your backup/restore procedures into the bargain).

In which case, if the two tables have the same structure generally the quickest, and ironically the easiest approach will be to use SELECT...INTO OUTFILE... on one end, and LOAD DATA INFILE... on the other.

See http://dev.mysql.com/doc/refman/5.1/en/load-data.html and .../select.html for definitive details.

For trivial tables the following will work:

SELECT * FROM mytable INTO OUTFILE '/tmp/mytable.csv'    FIELDS TERMINATED BY ','    OPTIONALLY ENCLOSED BY '"'    ESCAPED BY '\\\\'    LINES TERMINATED BY '\\n' ;  LOAD DATA INFILE '/tmp/mytable.csv' INTO TABLE mytable    FIELDS TERMINATED BY ','    OPTIONALLY ENCLOSED BY '"'    ESCAPED BY '\\\\'    LINES TERMINATED BY '\\n' ; 

We have also used FIFO's to great effect to avoid the overhead of actually writing to disk, or if we do need to write to disk for some reason, to pipe it through gzip.

ie.

mkfifo /tmp/myfifo gzip -c /tmp/myfifo > /tmp/mytable.csv.gz & ... SEL  ECT... INTO OUTFILE '/tmp/myfifo' ..... wait  gunzip -c /tmp/mytable.csv.gz > /tmp/myfifo & ... LOAD DATA INFILE /tmp/myfifo ..... wait 

Basically, one you direct the table data to a FIFO you can compress it, munge it, or tunnel it across a network to your hearts content.

Answer 2

The FEDERATED storage engine? Not the fastest one in the bunch, but for one time, incidental, or small amounts of data it'll do. That is assuming you're talking about 2 SERVERS. With 2 databases on one and the same server it'll simply be:

INSERT INTO databasename1.tablename SELECT * FROM databasename2.tablename;