Exporting data from one schema to another in MySQL Workbench

Question

Is there a way to export the tables and data from one schema to another? The manage import/export option asks me to select a server to connect to, which comes up blank. I'm currently connected to a server that my school has rented, specifically for this class, so I don't have any admin rights.

Solution 1

You can create a dump via Data Export in MySQL Workbench and import that right after the export to a new schema. MySQL Workbench allows to override the target schema in a dump.

Solution 2

If you run into troubles importing your data into the new schema, like not getting any data in it, a workaround might be needed. I ran an export of a schema from MySQL workbench to a .sql file to later import it in a different schema and the problem was that the .sql file exported maintained the previous schema.

So if you find this at the beginning of the .sql exported file:

CREATE DATABASE  IF NOT EXISTS `old_schema` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `old_schema`;

Replace it with this:

CREATE DATABASE  IF NOT EXISTS `new_schema` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `new_schema`;

That will do the trick. In some situations, your .sql file might be of a few hundreds MB so you will have to wait a little until it opens up in your editor. This code should be at the beginning of the file though so it is easy to find.

I hope it helps!

Solution 3

in 6.0 and up, it looks like the dump writes out individual tables in a directory that you name the dump. All of the schema and table names are defaulted to your schema that you exported from (as you've noted.) In order to facilitate an import to a new schema, simply run the following in your dump directory:

find . -type f -exec sed -i 's/your_export_schema/your_different_schema_name/g' {} \;

Be careful though, you'll bone your self if you have data in your export that has your old schema name in it.

Comments

[Hokerie]

Where would that option be? I've looked everywhere and in the help files, it says to it's in the data dump tab, which appears to be in the admin screen... which is something I don't have access to. I'm using version 5.2.31

[Mike Lischke]

You are using MySQL Workbench 5.2.31? Heaven, just update finally! We are at 6.0.8 (with 6.0.9 around the corner). With the lastest release, when you open a connection, go to Management -> Data Import/Restore. There's a setting named "Default Schema to be iported to" where you can set a target schema to override the one in the dump (works only with self contained dump files, not those with a with file per table etc.).

[april26]

I'm on 6.0.8 and there is a very clear note that says the override ONLY works when you have an export with no schema name. I'm now trying to figure out how to export without a schema name. I'll post if/when I find such an option. So far, no dice. (not done searching though). My database is quite big so editing the dump file to remove the schema name isn't an option.

[aceslowman]

Worked fine for me just removing the top two lines from the sql dump file. Then you can just select the default schema.