If you perform your own database backups, it’s also possible to do your own database restoration without relying on a host or third party. Let’s see what it takes to restore a MySQL database with Workbench.
Before we get started, our tutorial, “Making a MySQL Database Backup With MySQL Workbench,” covers the backup part of the equation (using MySQL Workbench).
In this tutorial, we will go through the steps to restore a database from a backup. We will also cover the necessary configuration to connect to your database with MySQL Workbench. This will cover everything for you in one spot, in case you’ve never done so.
Configuring MySQL Workbench to Connect to Your Database
Many commercial hosts block outside database connections, so you may have to add your home or office IP address to a remote access list. Check with your host to see what their requirements are. If your website uses cPanel, you can set up a remote connection in Databases > Remote MySQL.
Open MySQL Workbench and click the + icon to start a new database connection.
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 available if your host requires it).
- Enter the MySQL server hostname or IP address.
- Enter the MySQL database username.
- Click the “Store in Vault…” button to enter the database password (if you don’t want to store the password, skip this field).
Click the “Test Connection” button.
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 and move on from there.
Now click the “OK” button in “Manage Server Connections” to close the connection test window.
Configuring MySQL Workbench to Restore (Import) Your Database
Click the box for the database connection that you just set up.
Click the “Data Import/Restore” link.
For this tutorial, we’re assuming you are restoring a “Self-Contained File” backup. See “Making a MySQL Database Backup With MySQL Workbench,” for an explanation of the difference between a self-contained file and a dump project folder.
Select “Import from Self-Contained File,” and locate the backup file that will be used for restoration.
Select the “Default Target Schema” from the drop-down. The drop-down should be pre-populated with the schema name from the backup file.
Since we are restoring the entire database from a self-contained file, “Select Database Objects to Import” is left blank because there is no need to select specific tables.
Make sure “Dump Structure and Data” is selected from the dropdown.
Click the “Import Progress” tab.
Click the “Start Import” button.
When the restoration is complete, you’ll see an “Import Completed” dialog.
That’s it! You have successfully restored a MySQL database from a backup with MySQL Workbench.
While you’re here, let’s talk a bit about what MySQL is and its interesting history.
What is MySQL?
Often pronounced “my sequel,” let’s take a quick look at where this came from. A Swedish company called MySQL AB that originally developed the open-source MySQL all the way back in 1994. The correct pronunciation is “MY-ES-KYOO-EL.” MySQL is considered open source, even though sometimes it feels like it isn’t.
As years went by, a United States tech company by the name of Sun Microsystems took full ownership when they bought MySQL AB back in 2008. After that, the massive US tech giant Oracle acquired Sun Microsystems itself, and MySQL has been owned by Oracle ever since.
Now that you have a little history on it, let’s take a look at the actual definition. Essentially, MySQL is an open-source relational database management system (RDBMS) with a client-server model. RDBMS is actually software that is used to create and manage databases on a relational model.
If you work with something like WordPress, then you will be at least a little familiar with a MySQL database. If you are still having a little trouble with some of the terms above, let’s take a quick look at them together.
Database
A database is a collection of structured data. In order to break it down into a very simple way to think about it let’s use an example.
If you take a video with your phone, that video is the data. Your phone’s video gallery would be the database. The database is the area where the data is stored and organized. In this case the video is the data, and it is being stored and organized by the gallery on your phone.
Open Source
Simply put, open-source means that anyone is free to use, add, subtract, and modify. Anyone can install t and use it for free. You can also learn to customize the source code.
Open source is a way to allow people to contribute and have input while keeping something strong in place.
Client-Server Model
Computers that install and run the above mentioned RDBMS software are called “clients.” Whenever they have to access data, they connect to the RDBMS server. That’s the “client-server” side of the term above.
MySQL is actually just one of many RDBMS software options. Many people think they are actually the same thing because of the popularity of MySQL. It actually seems like everyone is running it these days.
Large entities that we all know like Facebook, Twitter, YouTube, Google, and Yahoo use MySQL for data storage purposes. When it was first created, MySQL was built for limited usage. However, it is now compatible with many important computing platforms such as Linux, macOS, Microsoft Windows, and Ubuntu.
MySQL and SQL Differences
A little understanding can go a long way to help you restore a MySQL database. It is important to remember that MySQL and SQL are not the same and there are some very key differences. Before I show you how to restore a MySQL database from a backup, let’s take a quick look at the differences between SQL and MySQL.
- SQL is the language that is used to operate the database. MySQL is one of the first open-source databases that are available to use.
- The SQL is the way to access, update, and manipulate data. MySQL is an RDBMS that allows keeping the data that exists in a database organized, as described some above in video and phone example.
- SQL is a “Structured Query Language.” MySQL is an RDBMS to store, retrieve, modify, and administrate a database.
- SQL is a query language. MySQL is database software.
Final Thoughts
If you are someone who deals with your own database backups and restorations, then the Workbench tool we talk about above is going to be very helpful to you. It gives you the ability to do all sorts of things, including creating and restoring MySQL databases.
I hope this tutorial has given you a clear and easy way to restore a MySQL database. The Workbench tool can be very useful and is not too difficult to use. Performing the steps above should give you exactly what you need in just a few minutes. Remember, MySQL is extremely popular, so if you are dealing with a database, there is a good chance is MySQL.
Have you ever had to restore a database from a backup? Do you use backups for development purposes?