how to insert table data from one user to another in different machines in mysql

Question

I want to insert the data into one of the tables of a different user and a different database.

e.g.,

First DB

ip:ip1

user:user1

b:db1

Second DB

ip:ip2

user:user2

db:db2

So, I want to insert one of table's data of first DB into second DB.

Answer 1

You should try with

SELECT INTO OUTFILE

http://dev.mysql.com/doc/refman/5.0/en/select-into.html

and after that you can use

LOAD DATA INFILE

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Answer 2

You can try SQLyog's 'Copy Database to different host/Database' to copy from one MySQL server to another. Select the Database you want to copy and then Select Database -> Copy Database to different Host/DB to copy a database (with all or selected items of its table structure as well as the data) to another database (which may be located in another host).

Answer 3

You have to do 2 mysql_connect, which return the connection.

Within your mysql_query where you insert the data you have to put your reference as second parameter

Like:

$db2 = mysql_connect('ip1', 'mysql_user', 'mysql_password');
mysql_query ( "INSERT INTO *.....", $db2 )

$db1 = mysql_connect('ip2', 'mysql_user', 'mysql_password');
mysql_query ( "INSERT INTO *.....", $db1 )

If your using Objects (which you should use) you just have to save the reference and pass it all queries executed. Then you can just make 2 DB objects with different IP's.

Answer 4

You may try to attach remote tables using FEDERATED storage engine, if your current version of MySQL is built with --with-federated-storage-engine

Use SHOW ENGINES; to see if the engine is supported in your installation.

Unfortunatelly, I have not yet tried that myself, and can not share any experience.