CODEWITHSUNDEEP
mysqldatabase
Rakesh Sankar
Rakesh Sankar

Reputation: 9415

MySQL: How to copy data (tables) from database to another database with different user

I am looking for a query to copy data from one database/table to another database/table with different user credentials?

Upvotes: 0

Views: 3811

Answers (3)

Burhan Khalid
Burhan Khalid

Reputation: 174622

Not a query, unless your user has rights on both schemas.

Try this mysqldump -u user dbname.tablename | mysql -u user2 dbname

Upvotes: 1

Rakesh Sankar
Rakesh Sankar

Reputation: 9415

Looks like there is no straight way to copy any database to another database with different users.

Only way I could successfully complete this task is to write a script to take the dump and run it over another database.

Upvotes: 0

Micku
Micku

Reputation: 550

To copy data from one table to another :

 INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1

To copy data from one database to another :

 USE `old_database`;

 INSERT INTO `new_database`.`new_table`(`column1`,`column2`,`column3`)

 SELECT `old_table`.`column2`, `old_table`.`column3`, `old_table`.`column5`

 FROM `old_table`

Upvotes: 2

Related Questions