MySQL Workbench Tutorial: What is, How to Install & Use

What is MySQL?

MySQL is an open source relational database.

MySQL is cross-platform, which means it runs on a number of different platforms such as Windows, Linux, and macOS.

Why use MySQL?

There are a number of relational database management systems on the market.

Examples of relational databases include Microsoft SQL Server, Microsoft Access, Oracle, DB2, etc.

One may ask why we would choose MySQL over the other database management systems.

The answer to this question depends on a number of factors.

Let us look at the strengths of MySQL compared to other relational databases such as SQL Server:

• MySQL supports multiple storage engines, each with its own specifications, while other systems like SQL Server support only a single storage engine. To appreciate this, consider two of the storage engines supported by MySQL:
  • InnoDB — the default storage engine provided with MySQL as of version 5.5. InnoDB supports foreign keys for referential integrity and also supports ACID-standard transactions.
  • MyISAM — the default storage engine for MySQL prior to version 5.5. MyISAM lacks support for transactions. Its advantages over InnoDB include simplicity and high performance.
• MySQL has high performance compared to other relational database systems. This is due to its simplicity in design and support for multiple storage engines.
• Cost-effective — it is relatively cheaper in terms of cost when compared to other relational databases. In fact, the community edition is free. The commercial edition has a licensing fee that is also cost-effective compared to licensing fees for products such as Microsoft SQL Server.
• Cross-platform — MySQL works on many platforms, which means it can be deployed on most machines. Other systems such as MS SQL Server only run on the Windows platform.

In order to interact with MySQL, you will need a server access tool that can communicate with the MySQL server. MySQL supports multiple user connections.

What is MySQL Workbench?

MySQL Workbench is a visual database designing and modeling access tool for the MySQL server relational database. It facilitates the creation of new physical data models and the modification of existing MySQL databases with reverse/forward engineering and change-management functions. The purpose of MySQL Workbench is to provide an interface to work with databases more easily and in a more structured way.

MySQL Workbench brings together three primary sets of functionality — data modeling and design, SQL development, and server administration — each described below.

Modeling and Design Tool

Models sit at the core of most valid, high-performance databases. MySQL Workbench has tools that allow developers and database administrators to visually create physical database design models that can be easily translated into MySQL databases using forward engineering.

• MySQL Workbench supports the creation of multiple models in the same environment.
• It supports all objects — such as tables, views, stored procedures, and triggers — that make up a database.
• MySQL Workbench has a built-in model-validation utility that reports any issues that might be found to the data modeler.
• It also allows for different modeling notations and can be extended by using Lua, a scripting language.

The figure below shows the modeling window for MySQL Workbench.

SQL Development Tool

Structured Query Language (SQL) allows us to manipulate our relational databases. SQL is at the heart of all relational databases.

• MySQL Workbench has a built-in visual SQL editor.
• The visual SQL editor allows developers to build, edit, and run queries against MySQL server databases. It has utilities for viewing data and exporting it.
• Its syntax color highlighters help developers easily write and debug SQL statements.
• Multiple queries can be run and results automatically displayed in different tabs.
• The queries are also saved in the history panel for later retrieval and running.

The figure below shows the SQL development window for MySQL Workbench.

Administration Tool

Server administration plays a critical role in securing the data of the company. The major issues concerning server administration are user management, server configuration, server logs, and many more. MySQL Workbench has the following features that simplify the process of MySQL server administration:

• User administration — a visual utility for managing users that lets database administrators easily add new and remove existing users if the need arises, grant and drop privileges, and view user profiles.
• Server configuration — allows advanced configuration of the server and fine-tuning for optimal performance.
• Database backup and restoration — a visual tool for exporting and importing MySQL dump files. MySQL dump files contain SQL scripts for creating databases, tables, views, and stored procedures and for inserting data.
• Server logs — a visual tool for viewing MySQL server logs. The logs include error logs, binary logs, and InnoDB logs. These logs come in handy when performing diagnosis on the server.

The figure below shows the Admin panel for MySQL Workbench.

The next section covers installing MySQL Workbench on Windows.

How to Install MySQL Workbench (for Windows)

In this section, we will learn how to install and use MySQL Workbench.

Installing MySQL Workbench is a 2-step process.

1) Install MySQL Community Server.

2) Install MySQL Workbench — you can install the workbench using a zip file or an MSI installer (recommended).

Note: You will require Administrator or Power User privileges to perform the installation.

Getting Started

Once you have finished installing the above, you need to set up MySQL Workbench as shown below. Following is a step-by-step process on how to install MySQL Workbench.

Step 1) Open Home Window. The first step is launching MySQL Workbench. What you see is called the Home Window.

Step 2) Open New Connection Wizard. Next, you need to create your MySQL server connection, which contains details about the target database server, including how to connect to it. Click “+” in the MySQL Workbench Home Window. This will open the Setup New Connection wizard.

Step 3) Click Configure Server Management button. As a beginner, you can create a connection for a locally installed server. Click the Configure Server Management button in the Setup New Connection window to check the configuration of the MySQL server.

Step 4) Click Next button to continue. A new window opens named Configure Local Management. Click the Next button to continue.

Step 5) Enter your password and press OK. Next, the wizard will test the connection to the database. If the test fails, go back and correct the database connection parameters. It will then open a pop-up window asking for your root password to test your connection with the local MySQL server instance. The password is the one you set during installation of MySQL. Enter your password and press OK.

Step 6) Click Next to continue. Next, the wizard will test the connection to the database. If the test fails, go back and correct the database connection parameters. If all tests are successful, click Next to continue.

Step 7) Click Next. After that, a new wizard will open about Local Service Management — it lets you switch between multiple MySQL servers installed on one machine. As a beginner, you can bypass this and click Next to continue.

Step 8) Select MySQL Server Configuration File. The wizard will then check the ability to access the MySQL Server Configuration File, and test start/stop commands.

Step 9) Click Finish to finish server configuration. Next, you can review the current configurations. After reviewing the configurations, click Finish to finish server configuration.

Step 10) Click on Test Connection. The next step is to set up a connection, which can be used to connect to the server. If you have not created a connection already, you can use the default values given. Click on Test Connection [ 2 ] after entering the Connection Name [ 1 ].

Step 11) Click OK. A new dialog box will open asking you for the password to the root/selected user. If your MySQL root user has a password, you can enter it using the Store in Vault feature. Click OK.

If the entered password for the user is correct, then the following screen will show. Click on both OK buttons and you will be good to go.

A new instance is shown in the homepage.

Double-click and start querying.

FAQs