Copying all tables from one database to another in MySQL

Hi Im trying to copy all my tables from one database to another just using mySQL. I have a code that I’ve been trying to use which dumps the tables into a sql file which i will then import into another database.

mysqldump -u root -p[root-password] [database] > [sqlfile].sql

so my code would be
mysqldump -u root -pMyNewPass brochureSite > brochure.sql;

yet Im getting a syntax error when I trying to perform this

Try this.The code is for one table but repeating this as for all tables (one by one) it should work

SELECT * INTO DestinationDB.dbo.tableName FROM SourceDB.dbo.SourceTable 

LOL “dbo” – that’s MS SQL, not MySQL

MySQL does not support SELECT INTO

what is the error message?

1 Like

If the tables have different structures you can do:


EDIT: to constrain this…


source : How to copy data from one table to another new table in MySQL?

you might have taken the trouble to illustrate how this works from one database to another

With all due respect, copying an entire database into another database is more complex than running INSERT queries.

Can it be done with a series of queries? Yes, I suppose so.

But mysqldump can do it all with a relatively short command.

Working from the CLI may be uncomfortable for some. And knowing what options to specify can take a bit of time to figure out.

Going (cd-ing) to the folder where mysqldump is - eg. mine is at
c:\Program Files\MySQL\MySQL Server 5.7\bin
and entering “mysqldump --help” will be, errrm, a help.

And as r937 posted, error messages will help to pinpoint problems.

I can barely see it, but it looks like you’re in the mysql CLI

It’s good for working with databases, but if there’s a way to run mysqldump from it I don’t know how.

The way I run mysqldump is by using the Windows Command Prompt CLI

there isn’t :slight_smile:

1 Like

Are both databases (source and destination) on the same MySQL server?

Do you have access to phpmyadmin?

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.