Copying a database in MySQL Workbench

It's hard to find any information about copying or duplicating a database in MySQL Workbench. It's not MS SQL Server! The best I could figure is that you cannot directly import or export databases within the same server. The work around I followed goes like this. Create a new database. Export the data from your existing table to a self-contained file. Open that in your favorite text editor with a command find and replace, and change all instances of the old database name to the new one. It only occurred in 3 places in my sql export file (.sql). Then come back to Import it into the server.

The detailed explanation: Create your database by Creating a New Schema and naming it. Open up your database server in MySQL Workbench, right click in the Object Browser column (where you databases are listed) and select Create Schema. Give it a name.

Next go to back to the Home tab in MySQL Workbench and double click Manage Import / Export under Server Administration. If you do not have a Server Instance registered, do so on New Server Instance. Connect to your existing database server. When you accomplish this you will see your Server Instance in the Server Administration box.

Back to Manage Import / Export. Double click either your Server Instance or click Manage Import / Export and select your database server. Go to the Data Dump tab (Import / Export Data). Select the Export to Disk tab below, check the box for the database which you wish to export, select all tables, and the radio button for Export to Self-Contained File. Then punch the Start Export button.

Pull up your favorite text editor with a command find and replace. Open up the dumped .SQL file and find all instances of your previous database name and replace with your new database name. You really don't need command and replace because in my case the name occurred only 3 times. Save.

In the Workbench Data Dump tab, select the Import from Disk tab. Check the radio button for Import from Self-Contained File and ensure that the newly created export dump file (.SQL) is in the File Path box. Press the Start Import button.

Check you database and see if it worked. I'm sure you'll find that it did!

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
THANK YOU, THANK YOU, THANK YOU!!!!!!! I have been forever trying to figure this out, and was having no luck with the command prompt or Workbench. This was so easy to follow and did exactly what I needed. I can now breathe a big sigh of relief!
# Posted By Fawn | 5/16/11 9:03 PM
Cool! I'm so glad it helped!
# Posted By ColdFusion Joe | 6/10/11 5:54 PM
Thanks Joe!

This was a HUGE help!!!
# Posted By Craig | 7/24/11 10:23 PM
This worked 1st time!!
Thank you for taking the time out to publish up this article.
# Posted By Patrick Pac | 1/17/12 1:14 AM
Caveat: Don't use find and replace for your database name if it shares a name with a table.
# Posted By Anonymous | 4/6/12 1:27 PM
Sweet! just what i needed. Thanks!
# Posted By Magic | 7/17/12 10:49 AM
Bless you!
# Posted By Steve | 8/25/12 4:57 PM
Signed up just to say thanks! This is exactly what I needed.
# Posted By iReply2Spam | 6/3/13 7:47 PM
Thank you Joe! That worked!
# Posted By Dee | 7/1/13 11:11 PM
Thank you so much! I really needed this :)
# Posted By Reefersleep | 4/8/14 5:04 PM
Old post but still realy usefull. Thank you ;)
# Posted By Alexandre | 10/20/14 9:52 AM
Thank you! This is exactly what I needed and worked perfectly first time.
# Posted By Glen | 10/30/14 10:50 AM
In newer Versions of Workbench you can turn off scheme in the Database Export dialog. There is a checkbox labeled "Include Scheme". If you unset it before exporting, then you don't have to find and replace scheme name in the SQL file.
# Posted By Bojan | 5/9/16 8:36 AM
This worked and was very helpful for me. I'm not sure why MySQL Workbench doesn't have a command or utility to do this automatically. Thanks.
# Posted By Ron | 9/28/16 5:17 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.9.7. Contact Blog Owner