Backing up a MySQL database using MySQL Workbench

Michael PhillipsBacking up a MySQL database is usually a pretty straightforward and simple task. You can back up a MySQL database using an uploaded application like phpMyAdmin, but for the purposes of this tutorial we’re going to use the MySQL Workbench desktop program. You’ll have to open an Oracle account to download MySQL Workbench, but opening an account is free and only mildly painful.

This article was written using MySQL Workbench 6.1 CE. You may be able to achieve similar results with previous versions, but we have not tested any earlier versions, so your results may vary.

The first time you open MySQL Workbench it will be empty. The first thing you want to do is create a connection to your database. Start by clicking the + next to “MySQL Connections.”

Everleap-MySQL-workbench-1
Next you’ll populate the connection configuration fields. Some of the information you’ll need here is located in the MySQL section of Control Panel. Log in and open up the MySQL Database Overview for the database you want to back up.

Everleap-MySQL-workbench-1a
Now in the MySQL Workbench connection configuration you need to add at least the following:

Connection Name: You can put anything you’d like here.
Hostname: That’s the “Database Server” in Control Panel.
Username: That’s the “Database User” in Control Panel.
Password: The database password. Which, by default, is the same as your Control Panel password.

Once you’ve completed those fields click Test Connection to make sure you’ve got everything entered correctly. If the test is successful, click OK to save the connection.

Everleap-MySQL-workbench-2
Now you should see the connection listed, and to start the backup (or to do anything in MySQL Workbench) just click the connection box.

Everleap-MySQL-workbench-3
MySQL Workbench will connect to your database and pop up the following screen. To perform a backup, click Data Export.

Everleap-MySQL-workbench-4
Now click the checkbox next to the database you want to back up, and for the easiest backup/restoration, select the Export to Self-Contained File option (that creates a single backup file containing your schema and all of your tables and data). Set the destination path for the backup file, and click Start Export.

Everleap-MySQL-workbench-5
That’s it. Backup time depends on the size of your database.

While we’re here you may as well take a look at how database restoration works. That’s also pretty simple – just click the Data Import/Restore link, select the Import from Self-Contained File option, enter the path to the backup file and click Start Import.

Everleap-MySQL-workbench-6
This is a very basic backup tutorial, but it’s sufficient for making your own backups, which you should get in the habit of doing any time you update or change an application, add new database tables, etc.

We do daily database backups for disaster recovery purposes, and we can provide our backup to you if you really need it, but our latest backup might not always be in synch with what you need in the event that you make a database change you want to revert.

So any time you make a change, backup first!


One Response

Leave a Reply