moving tables from mysql schema to new scheme

Somehow my developer had the bright idea to create tables to be used with a web application into the system/default 'mysql' DB?!  I misunderstood his question and he ended up creating tables in the 'mysql' db/schema.  Anyway...  At this point should I just export the entire mysql schema as a .sql file and then import into the new schema?  Then delete the tables I don't need?  This will resort in system tables coming with it that aren't needed.  Will the below work?

use mysql;
mysqldump -u root mysql > dump.sql
mysql -u root newdb < dump.sql

Then I'll use MySQL WorkBench to delete unneeded system/mysql tables from newdb?
gopher_49Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
Several ways to do it. Which will be easiest will depend on how many tables you have.

If you only have a few, you could do it all through mysql workbench. Copy schema a data for each tables with something like:

CREATE TABLE newdb.tableName LIKE olddb.tableName;
INSERT newdb.tableName SELECT * FROM olddb.tableName;

I tend to use a program called SQLyog - it's as simple as right clicking on a table and choose 'Copy to another database' :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gopher_49Author Commented:
I exported all and imported all.  Then deleted the unneeded ones. It seemed to work.  Is my method and / syntax posted an okay option too?
0
Chris StanyonWebDevCommented:
It's been a while since I've used the command prompt, so not sure. Looks OK but run some test and see. And make sure you new table has the correct permissions setup - as I said before, you don't really want your application accessing your server with root credentials.
0
gopher_49Author Commented:
I used root to export / import.  The app uses different creds.  We'll test and reply back shortly.

Thanks
0
gopher_49Author Commented:
This solution works, however, it was easier for me to backup/export the entire mysql DB..  Restored as a new DB and delete wasn't needed.

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.