25

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 ?

Morano88
  • 2,047
  • 4
  • 25
  • 44

5 Answers5

24

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.

Recurse
  • 3,557
  • 1
  • 23
  • 36
  • Thanks, though it is in the dev.mysql manual but it is not working proberly. It is giving me this error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\n'' at line 1) It is not accepting '\n' .. why ? – Morano88 Jul 16 '10 at 22:06
  • Stack Overflow corrupted the sample query. In the source, the slash in the ESCAPED BY clause was escaped, but the escape was lost when converted to html. Sorry about that, I hadn't noticed the corruption - I've fixed it above. – Recurse Jul 19 '10 at 23:57
  • This is exactly what I need to use to speed up backing up quite a large database, but I can't get your example to work. I can create the FIFO, and if I dump out to the pipe using mysqldump it works just fine. However, if I try to dump out to the pipe using SELECT ... INTO OUTFILE I get an error that "myfifo already exists". – Mark B Oct 06 '11 at 09:09
  • Note: the path is on the server, not the client. – andig Apr 24 '13 at 15:35
14

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;
Wrikken
  • 69,272
  • 8
  • 97
  • 136
  • Thanks a lot, I'm not sure yet If the 2 databases are on the same server or not. – Morano88 Jul 14 '10 at 00:34
  • This was fantastic for my situation. Cheers :) – Nick May 10 '13 at 07:24
  • 1
    Worked great with Amazon RDS where you don't have access to the underlying machine running the MySQL instance, ruling out solutions involving INFILE/OUTFILE. – Andz Sep 27 '15 at 07:35
  • This should be the chosen answer! I've submitted an edit that reorders the statements in this answer to make it faster and clearer to understand. You don't need the FEDERATED storage engine for transfers on the same server. – Andz Sep 27 '15 at 07:59
  • @Andz: the `FEDERATED` is indeed only valid if they're totally different servers. However, I cannot see your edit so I think it's been voted out. Could you share it in a comment so I can incorporate it in the answer if it's worth sharing? – Wrikken Sep 27 '15 at 16:05
  • Hi @Wrikken For databases on the same server it'll simply be: ... For databases across different servers, you can use the FEDERATED storage engine. Not the fastest one in the bunch, but for one time, incidental, or small amounts of data it'll do. – Andz Sep 27 '15 at 18:41
1

from http://dev.mysql.com/doc/refman/5.0/en/rename-table.html:

As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
ErichBSchulz
  • 15,047
  • 5
  • 57
  • 61
1

You can use mysqldump and mysql (the command line client). These are command line tools and in the question you write you don't want to use them, but still using them (even by running them from your code) is the easiest way; mysqldump solves a lot of problems.

You can make selects from one database and insert to the other, which is pretty easy. But if you need also to transfer the database schema (create tables etc.), it gets little bit more complicated, which is the reason I recommend mysqldump. But lot of PHP-MySQL-admin tools also does this, so you can use them or look at their code.

Or maybe you can use MySQL replication.

Messa
  • 24,321
  • 6
  • 68
  • 92
  • Will this (selects, inserts) work if the two databases are on 2 different servers ? No I just want to migrate data, not table creations .. etc. – Morano88 Jul 14 '10 at 00:34
  • Yes, you will have tvo connections - for each server one - in one connection you run SELECT, then in the other INSERT with the data that returned from that SELECT. – Messa Jul 14 '10 at 09:11
0

If you enabled binary logging on your current server (and have all the bin logs) you can setup replication for the second server

desertwebdesigns
  • 1,035
  • 8
  • 14