What is the best method to copy the data from a table in one database to a table in another database when the databases are under different users?
I know that I can use
INSERT INTO database2.table2 SELECT * from database1.table1
But here the problem is that both database1
and database2
are under different MySQL users. So user1
can access database1
only and user2
can access database2
only. Any idea?
Best Answer
If you have shell access you may use
mysqldump
to dump the content ofdatabase1.table1
and pipe it tomysql
todatabase2
. The problem here is thattable1
is stilltable1
.Maybe you need to rename
table1
totable2
with another query. On the other way you might use sed to change table1 to table2 between the to pipes.If table2 already exists, you might add the parameters to the first mysqldump which dont let create the table-creates.