How to Back Up MySQL Databases: A Guide

There are a variety of techniques that can be used to back up a MySQL Database. One of the more popular options is to perform a data export using Workbench. In this article, I will explain how this is done.

Begin the process by opening MySQL Studio, and connecting to the instance you want to back up. When doing so, it is important to make sure you are logged in with an account that has permission to export the data.

Connecting to the instance

Upon connecting to the instance, you will see two tabs in the lower left corner of the interface—Management and Schema. Click on the Management tab. If you happen to have MySQL Enterprise Backup installed, it will be accessible through the Management tab by clicking the Online Backup option. For the purposes of this article, however, we will focus on a database export.

With the Management tab selected, click on the Data Export option, found in the Management section. Next, choose the tables you wish to export. The tables are listed on the Object Selection tab.

At this point, you will need to specify exactly what it is you want to export. In most cases, the drop-down list found in the Tables to Export section should be set to Dump Structure and Data.

Choose the database objects to include

Just beneath the Tables to Export section, you will find the Objects to Export section. As its name implies, this section allows you to choose the types of database objects that you wish to include in the export process. In most cases, you should choose the Dump Stored Procedures and Functions checkbox, as well as the Dump Events and the Dump Triggers check boxes.

The next decision you will have to make is whether you wish to export the data to a Dump Project Folder, or to a Self-Contained File. If you go with the Self-Contained File option, the export process will create a single .SQL file containing all the data you have opted to export. Conversely, the Export to Dump Project Folder option exports each table to its own separate file. The advantage to using this method is it allows you to easily restore individual tables. The disadvantages to exporting to a Dump Project Folder, however, are that the exported data is not quite as easy to manage as a single file backup, and the export process takes longer to complete.

Start export

After configuring the various export options, click on the Start Export button to launch the export process. The length of time required to complete the export process varies depending on the export format that you have chosen and the amount of data that you are exporting.

If you opt to create a self-contained SQL file, you can open the SQL file in Notepad after the export process completes. The SQL file contains human readable text, and you can use Notepad to review the data that was exported.

 

Brien Posey is a 13-time Microsoft MVP with over two decades of IT experience. Prior to going freelance, Posey was a CIO for a national chain of hospitals and healthcare facilities and has served as a network engineer for the United States Department of Defense at Fort Knox. Posey has also worked as a network administrator for some of the largest insurance companies in America.

You can follow Brien on Twitter at @BrienPosey

© 2018 SolarWinds MSP UK Ltd. All rights reserved.

The SolarWinds and SolarWinds MSP trademarks, service marks, and logos are the exclusive property of SolarWinds MSP UK Ltd. or its affiliates. All other trademarks are the property of their respective owners.