asked on

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?

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gopher_49

ASKER

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?

Chris Stanyon

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.

gopher_49

ASKER

I used root to export / import. The app uses different creds. We'll test and reply back shortly.

Thanks

gopher_49

ASKER

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.