Backup and restore databases using MySQL workbench 6 or 8

Product Management -

Databases are an integral part of the internet. They contain large swathes of information that enterprises use to deliver digital experiences over the internet. This information could include names, phone numbers, email addresses, and physical home addresses. Databases can also store metadata and system data if needed. In short, databases house all of the information on the internet.

Different databases types are used for different things, but the most common type is the relational database management system (RDBMS). This type of database creates links – or relations – between each data object. One RDBMS database is MySQL, which has been a dominant force ever since the early 1990s. While Microsoft SQL Server has recently overtaken it, MySQL is the second-most-popular database software package in the world.

In this guide, we are going to explore how to perform backup and restore operations in MySQL Workbench 6 and 8. The two versions have a similar method of accessing these backup and restore tools, so this guide will work for both.

Prerequisites for this guide

Before you can follow this guide, you will need to ensure you have the following prerequisites:

  • A Fasthosts Control Panel account, which you are automatically given when using one of our cloud server hosting packages
  • A MySQL database hosted on a Fasthosts server, with the necessary ports open to the internet
  • Your database name and the server IP address, which you can access again via the Fasthosts Control Panel
  • A database user account with the DBAdmin role set, for full admin control of the MySQL server
  • An installed copy of MySQL Workbench 6 or 8. You can download the MySQL Workbench from this link

Backing up your database

After ensuring all of the prerequisites are set, you can now follow this section to back up your MySQL database.

ADVICE – We recommend performing regular backup activities for your database, especially with business-critical workloads. The more frequently you back up your database, the better your recovery point objective (RPO) will be. This is the period between the latest data backup being generated, and full recovery using a backup after a data loss event. A lower RPO thus minimises data loss during a disaster.

We are assuming you have already established a connection to your database. If you have not, please follow this Fasthosts guide before continuing.

When creating a backup, MySQL Workbench will export a file backup to your local storage. This file can then be used at a later date for restoring data, or transmitted via FTP to a new server if you are lifting and shifting your database to a new environment.

To get started, open MySQL Workbench. Ensure you are connected to the correct database server, and then click the Server tab at the top of the application. In this drop-down menu, you should see and click on Data Export.

Now a new tab will open called Administration – Data Export. Below, you have Object Selection, which will list a checkbox to Export and the corresponding Schema.

In our case, the Schema was called sys. Tick the checkbox next to the Schema you want to backup.

You will see all of the Schema Objects to the right get ticked. If you want more granular control over your backup, you can select and deselect the Schema Objects you want to include.

Just underneath this, you will see a selection box with Dump Structure and Data specified. You can decide to dump (backup) the data only, the structure only, or both depending on the option you choose here.

Next to the previous selection box, you have Select Views and Select Tables. These are shortcut buttons that select all the views in your Schema Objects list or the tables. You can also Unselect All to deselect all Schema Objects.

In the Objects to Export section, you have three options. Dump Stored Procedures and Functions will dump prepared SQL code (stored procedures) and common formulas or business rules (stored functions). Dump Events will dump MySQL Events, which are scheduled tasks used to execute SQL statements at a point in time. Finally, Dump Triggers will dump any saved triggers, which are automated script responses to changes in your database.

Next, we have Export Options. You can Export to Dump Project Folder, which will create a single backup at your chosen location, selectable with the […] box to the right. Export to Self-Contained File will separate each table into its own backup file, allowing for selective restoring, albeit with a performance penalty during the backup process.

The Include Create Schema option allows you to save a create_database statement, ready for use when you next restore this file. If the linked database for an object does not exist, this statement will trigger the creation of a new database to accommodate the object, avoiding relational problems.

Click Start Export after choosing your parameters, and the backup will start. In the log, the start is denoted by 12:34:56 Dumping sys (sys_config), and the finish by 12:35:56 Export of C:\Users\Fasthosts\Desktop\SQL Backup has finished. If you navigate to this folder, you should see the backup file.

In our case, we had two files named sys_sys_config.sql, and sys_routines.sql.

Restore in SQL Workbench

To restore from a .sql file, we will follow a similar process to before.

Go to Server in the top toolbar and select Data Import.

With Import from Dump Project Folder, you can navigate to the place where you just saved your backup. In our case, this was C:\Users\Fasthosts\Desktop\SQL Backup.

If you wish to import the data to an existing schema, make a selection in the Default Target Schema drop-down menu. We did not do this in our example.

When you selected the Dump Project Folder, the Database Objects list will have populated. Choose the schemas you wish to import here and specify whether you want to import the Dump Structure and Data, or one or the other.

Now click Start Import.

The log started with 12:36:56 Restoring sys (sys_config), and ended with 12:37:56 Import of C:\Users\Fasthosts\Desktop\SQL Backup has finished. This means the restore process succeeded, and your data should have appeared in the Schemas tab under the Navigator window.

Host your database with Fasthosts

Databases are performance-intensive and require bleeding-edge hardware to perform optimally. At Fasthosts, we offer lightning-fast Dedicated Servers, with unlimited 10GB/s bandwidth and NVMe storage technology. This ensures your database will experience no bottlenecks, ensuring the smooth flow of data to your digital services and websites.

Give your database the hardware it deserves. Get in touch with our sales team on 0808 1686 777, or via email at sales@fasthosts.co.uk to get started!