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.

Answer 1

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!

Answer 2

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.

Answer 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.