Copying all tables from one database to another in MySQL

[05beckga]

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

[liontas76]

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 

[r937]

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

MySQL does not support SELECT INTO

[r937]

@05beckga

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

what is the error message?

[internet_code]

If the tables have different structures you can do:

INSERT INTO TARGET_TABLE (col1,col2) SELECT col1,col2 FROM SOURCE_TABLE;

EDIT: to constrain this…

INSERT INTO TARGET_TABLE (col1_,col2_) SELECT col1,col2 FROM SOURCE_TABLE WHERE foo=1

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

[r937]

@internet_code

INSERT INTO TARGET_TABLE…

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

[Mittineague]

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.

[05beckga]

[Mittineague]

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

[r937]

@Mittineague

if there’s a way to run mysqldump from it I don’t know how.

there isn’t

[SpacePhoenix]

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

Do you have access to phpmyadmin?