Resources     Blog  

  1. Home
  2. General Tutorials
  3. How to Make a MySQL Database Backup With MySQL Workbench

How to Make a MySQL Database Backup With MySQL Workbench

It’s likely that your website host is making periodic backups of your site and database files. But if those backups aren’t available to you, you can make your own backups using MySQL workbench and save them to your computer, either for development purposes or just peace of mind.

Making local backups of MySQL databases is quick and easy once you have the proper tool configured. This tutorial uses MySQL Workbench 8.0 CE. Details in future versions may differ, but the basic workflow should be the same.

You can download MySQL Workbench here. You’ll have to create an Oracle account to download, but the account is free.

Configuring MySQL Workbench to Connect to Your Database

The first step in making a backup with MySQL Workbench is to connect to the database you want to back up.

Most commercial hosts will block outside database connections by default, so you may have to add your home IP address to a remote access list. Check with your host to see what their requirements are. If your hosting account uses cPanel, you can set up a remote connection in Databases > Remote MySQL.

Click the + icon to start a new database connection.

MySQL Workbench database backup step 1

A “Manage Server Connections” window will open. Complete the five connection and authorization fields underlined below.

  • Give the connection a name.
  • Choose “Standard (TCP/IP) as the “Connection Method” (SSH connection configuration is also available if your host requires it).
  • Enter the MySQL server hostname or IP address.
  • Enter your MySQL database username.
  • Click the “Store in Vault…” button to enter your database password (if you don’t want to store the password, skip this field).

Click the “Test Connection” button.

MySQL Workbench database backup step 2

If you get a “Cannot Connect to Database Server” error, check your entries in the connection fields.

If everything is correct, you’ll see the successful connection box. Click the “OK” button.

MySQL Workbench database backup step 3

Click the “OK” button to close the connection test window.

MySQL Workbench database backup step 4

Configuring MySQL Workbench to Back up (Export) Your Database

Click the box for the database connection that you just set up.

MySQL Workbench database backup step 5

Click the “Data Export” link.

MySQL Workbench database backup step 6

To back up the entire database, click the “Export” box in the “Tables to Export” window.

MySQL Workbench database backup step 7

To back up specific tables, click the database name, then select the table you wish to back up.

MySQL Workbench database backup step 8

For this tutorial, we’re making a safety backup of the entire database, so we’re choosing “Export to Self-Contained File.”

Using the “Self-Contained File” method makes it easier to restore an entire database in a disaster recovery situation. The other option, “Export to Dump Project Folder” will make separate backups of each table. The “Export to Dump Project Folder” method makes it easier to restore single tables should the need arise. Which method you should choose depends on the purpose of the backup. If you’re simply archiving the database, use “Export to Self-Contained File.”

Click the “Start Export” button.

MySQL Workbench database backup step 9

When the backup is complete, you’ll see an “Export Completed” dialog. The database backup will be located in the location specified in the “Export to Self-Contained File” path.

MySQL Workbench database backup step 10

If the time ever comes to use one of your database backs for restoration, we have another tutorial that shows you how to take care of that, “How to Restore a MySQL Database From a Backup With MySQL Workbench.”

Keeping a Backup Matters

No one wants to see their website taken down by an errant code or someone accidentally deleting the wrong files. Unfortunately, it happens quite often. And a lot of times, it’s not even your fault.

Using MySQL Workbench to backup the database helps you keep those files safe and ready in case there is a restore process in your future.

It’s always better to be safe than sorry. A few moments today can save you a lot of hassle tomorrow.

Do you make regular backups of your website and database files? Which programs or services do you use to make your backups?

Author: Michael Phillips

Michael Phillips is a web hosting industry veteran, helping people make the most of their web presence since 1995.

Was this article helpful?

Related Articles

Comments

    1. Not with Workbench. There are third-party services that will back up databases daily and keep a couple weeks of versions (or as many as you want to pay to store). I like DropMySite, but there are a lot to choose from.

Leave a Comment

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