Site hosting news, tutorials, tips, How Tos and more

Making a MySQL database backup using MySQL Workbench

banner-howto

Note: The manual methods in this tutorial are great, but if you’re looking for a “set-it-and-forget-it” automated backup solution, we offer a site backup service that can also back up your MS SQL and MySQL databases. Read about it on our site, or activate it in Control Panel. It’s easy, it’s inexpensive and it’s cool. What more could you ask for?

Making a backup in MySQL workbench is a pretty easy task once you know what to do, but it can be a little confusing the first time around. Allow us to save you some time with these simple instructions.

Version 6.3.7 is shown here, and of course future versions may differ. Download MySQL Workbench here (you’ll need a free Oracle account if you don’t already have one – just click the “Register” link in the upper right corner of that download page).

First thing you’ll need to do in Workbench is connect to your database.

workbench1

If everything is correct you’ll see the successful connection box.

workbench2

Go ahead and close that, and click the connection that you just set up.

workbench3

Click “Data Export.”

workbench4

There are a lot of options on the next screen. For the purposes of this how-to we’re just making a simple backup of the entire existing database, so we’re not going to use most of those options. But as you can see, you can do a lot more than just a simple database dump here.

workbench5

If everything goes according to plan you’ll see the “Export competed” dialog, and you’ll be all set. Your database is backed up for development use or simply for safe keeping.

workbench6

That’s all there is to making a backup.

But check out the “Data Import/Restore” link right under the “Data Export” link. As you might have guessed, you use that link to restore a locally stored backup up to the MySQL server here at Winhost. We’ll talk about that in a future article.


8 Responses
  • GregGreg Knierim Reply

    Unfortunately this doesn’t work. Tried multiple versions of the workbench and worked with support and the only solution was to install phpMyAdmin in order to do backups of MySQL databases.

    • Michael Reply

      Which versions did you try? I just tested again with 6.3.7 and I was able to connect and export.

      • Greg Knierim Reply

        I’ve tried 6.2.5, 6.3.5 and 6.37. None of them worked. I kept getting the following error:

        12:53:39 Dumping mysql_dbnamehere (all tables)
        Running: mysqldump.exe –defaults-file=”c:\users\myusername\appdata\local\temp\tmpc5jopj.cnf” –user=dbuserhere –host=myserverhere.winhost.com –protocol=tcp –port=3306 –default-character-set=utf8 –single-transaction=TRUE –routines –events “mysql_dbnamehere”
        mysqldump: Got error: 1045: Access denied for user ‘dbuserhere’@’ip.address.here’ (using password: NO) when trying to connect

        Operation failed with exitcode 2

        I have removed the database name, username and ip address above so I can post the message on here.

        I can connect to the database to look at tables just fine though. My export options are the following:
        – Dump stored procedures and functions
        – Drop events
        – Dump Triggers
        – Export to self contained file
        – Create Dump in a Single Transaction

        • Michael Reply

          Thanks for the detailed error. I’m looking at your helpdesk ticket now. I’ll see if we can do a little more research into this. When we tested for the article here we did an export without any options, so it would seem that the failure lies with one of those export options.

  • daethian Reply

    I got a MYSQL Version mismatch error with the option to Continue Anyway.

  • daethian Reply

    Operation failed with exitcode 2. I didn’t do anything different than what is stated here in the article.

    • Moises Reply

      See my comment above for the answer.

Leave a Reply to Michael Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.