Easiest way to copy a table from one database to another?

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?

Solution 1:

If you have shell access you may use mysqldump to dump the content of database1.table1 and pipe it to mysql to database2. The problem here is that table1 is still table1.

mysqldump --user=user1 --password=password1 database1 table1 \
| mysql --user=user2 --password=password2 database2

Maybe you need to rename table1 to table2 with another query. On the other way you might use sed to change table1 to table2 between the to pipes.

mysqldump --user=user1 --password=password1 database1 table1 \
| sed -e 's/`table1`/`table2`/' \
| mysql --user=user2 --password=password2 database2

If table2 already exists, you might add the parameters to the first mysqldump which dont let create the table-creates.

mysqldump --no-create-info --no-create-db --user=user1 --password=password1 database1 table1 \
| sed -e 's/`table1`/`table2`/' \
| mysql --user=user2 --password=password2 database2

Solution 2:

CREATE TABLE db1.table1 SELECT * FROM db2.table1

where db1 is the destination and db2 is the source

Solution 3:

If you are using PHPMyAdmin, it could be really simple. Suppose you have following databases:

DB1 & DB2

DB1 have a table users which you like to copy to DB2

Under PHPMyAdmin, open DB1, then go to users table.

On this page, click on the "Operations" tab on the top right. Under Operations, look for section Copy table to (database.table):

& you are done!

Solution 4:

MySql Workbench: Strongly Recommended

Database Migration Tool From MySql Workbench

This will easily handle migration problems. You can migrate selected tables of selected databases between MySql and SqlServer. You should give it a try definitely.