MySQL WorkBench is the successor to the formerly known ‘MySQL Administrator’, which has been discontinued for quite some time. With MySQL WorkBench you have a newer GUI packed with awesome new features and capabilities to manage your MySQL/MariaDB databases.
MySQL is owned and developed by Oracle, which is also the application developer for this MySQL WorkBench application. Since MariaDB is just a drop-in replacement for MySQL, some functionality/features of MySQL WorkBench may not be supported and it’s best to use another application for MariaDB database management for these cases. There are a few applications out there that are fully supported by MariaDB management (such as HeidiSQL).
In order to connect to MySQL Workbench, you will need to download this application and install this on your local computer. The installer for MySQL workbench can be obtained from the dev.mysql.com 57 site and you can choose your proper OS type (such as MacOS or Windows).
You’ll also need to obtain the following information in order to connect:
Hostname - The hostname of the MySQL/MariaDB server that you need to connect to. If in the windows environment you can view the guide mentioned below for finding the hostname of the server. If on cPanel you can use the IP Address of the server that your cPanel site is hosted on.
Username - The username of the MySQL/MariaDB database you’re wanting to connect to.
Password - The password of the MySQL/MariaDB database you’re wanting to connect to.Note: If needed… see our guide on managing MySQL/MariaDB databases for our windows environment. If on Linux/cPanel then you can view cPanels guide on managing MySQL/MariaDB databases 29.
Once you have the above-mentioned criteria and have MySQL WorkBench installed then you can follow the steps below to make a connection:
Open the ‘MySQL WorkBench’ application.
Once the application is opened you should see a list of existing connections (if any), along with the button to add a new connection ( ‘+’ ). Click this button to add a new connection.
3. You will now be prompted to fill out the new connection information. This will ask for the following:
Connection Name - The connection name determines the name used to display in your list of existing connection options. When you open Workbench the next time this connection should be available to choose from and it will automatically attempt to connect with the information stored in this connection configuration settings.
Connection Method - The type of connection being made. This should remain as ‘Standard (TCP/IP)’ for all normal-use cases.
Hostname - The Hostname can either be the IP Address of the MySQL/MariaDB server, or a FQDN (Fully Qualified Domain Name) that points back to that servers IP Address.
Username - The username of the MySQL/MariaDB database you’re wanting to connect to.
Password - The password of the MySQL/MariaDB database you’re wanting to connect to.
Note: Skip this ‘password’ option if you want to enter the password each time you attempt to connect to this connection config. If you enter a password to the applications ‘Keychain’ then the password will be stored and will allow easy access (without having to log in) each time a connection attempt is made.
Default Schema - The schema (or database) that the connection should default into selecting. If the user is managing several databases then it may be best to leave blank and just select the schema each time you need to perform a task. Otherwise, enter the database name here that this user will be managing.
Example: If you leave this field blank, then when performing a query you may have to first state [Use ‘tutorial_db’], then run your query. Whereas, if you state the default schema to use then you wouldn’t have to enter in this ‘Use’ clause since the connection defaults to the database already.
4. The ‘SSL’ and ‘Advanced’ sections can be left to their default settings. Once you have confirmed you have entered in the correct settings click the Test Connection button to confirm the settings are able to connect properly. If not, then double check the Hostname, Username, & Password to ensure you have entered the correct settings.
Once the connection verifies click the ‘OK’ button to save your new connection.
5. You should now be able to see the new connection (under the connection name you just entered) within the list of available connections. Select the connection to attempt to make the connection.
If you left the password field blank then it will prompt for the password to make the connection. Once connected you should now be in the management GUI for the database where you can perform a task such as SQL Queries, Backups, Restores, & more.
Backing up your database via MySQL WorkBench is a pretty simple process, which we will go ahead and walk through. Please follow the steps listed below:
Open MySQL WorkBench and connect to the desired database. See the above section on connecting if needed.
Now on the left-hand navigation under the Management section find and click on the option listed as ‘Data Export’.
3. You can now select the schema (database) you wish to back up, as shown in the screenshot below.
4. Once you select the database to export, choose the tables you wish to export (By default all should be selected), as shown in the screenshot below.
Note: You’ll want to make sure that ‘Dump Structure and Data’ is selected as well so that all tables and their respective data is exported.
5. Underneath the ‘Objects to Export’ choose to dump any additional content for the database you desire such as stored procedures and functions, events, and triggers.
6. Underneath the ‘Export Options’ choose to export the file as a self-contained file, as shown in the screenshot below. Also, select the desired path on your local computer that you wish to export the file to.
7. The last step is to click on the ‘Start Export’ button and then wait patiently for the export to complete. The GUI will advise you of any errors/issues as well as when the export is complete.
Restoring your database via MySQL WorkBench is a simple process, however, there can be many avenues of failure in any MySQL restore attempt no matter what application you’re using to restore if a pre-restore prerequisite is not followed. For this reason, let’s first walk through a checklist prior to performing the restore.
Make sure the tables you’re going to be restoring don’t already exist with data, as the restore will fail with duplicate table data errors.
For Example:
CREATE TABLE `example_table` (
`meta_id` bigint(20) UNSIGNED NOT NULL,
`meta_value` longtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8
MySQL Said: #1050 - Table 'example_table' already exists
If you exported from a database with a different name than the database you’re restoring to… update the beginning of the .SQL backup file to use the new database name instead of the older… otherwise you’ll get a permission to create database failed error. Alternatively you can just change the name of the new database back to the same as the old database you exported the backup from.
For Example:
CREATE DATABASE IF NOT EXISTS tutorial_db /* Needs changed to new DB name */;USE tutorial_db; /* Needs changed to new DB name */;
If you are restoring to a database with already pre-existing content it might be best to setup a stage / testing database with the same content and attempt restoring to this stage database first. This will allow you to see any issues you may come across such as errors, overwriting content, etc.
Once you have completed the pre-restore prerequisite and are ready to perform the restore then you can follow the steps below:
Open MySQL WorkBench and connect to the desired database. See the above section on connecting if needed.
Now on the left-hand navigation under the Management section find and click on the option listed as ‘Data Import/Restore’.
3. Underneath the ‘Import Options’ section select the ‘Import from Self-Contained File’, then choose the path to the backup (.sql) file that you are wanting to restore from.
4. Then select the schema (database) that this restore should be performed on with the drop-down next to ‘Default Target Schema’.
5. Once ready to perform the restore to the selected database click the ‘Start Import’ button.
6. The final step is to wait for the restore to complete. You should see any errors/issues that arise as a result of the restore in the ‘WorkBench Progress’ section that you should now be under after clicking the start import button. You will also see when the restore completes under this same section .