Install and Configure MySQL Workbench on Ubuntu Linux

MySQL workbench is a graphical application which let us manage data and perform administrative tasks on MySQL databases. In this tutorial we will see how to install the program on Ubuntu 18.04 (Bionic Beaver) and we will perform a brief tour of some of its most basic functionalities to make your Linux system administration job easier.

In this tutorial you will learn:

  • How to install and configure MySQL workbench
  • How to create a schema and a table via MySQL workbench
  • How to crate an E.R diagram by reverse-engineering an existing schema
  • How to execute SQL queries


MySQL workbench dashboard on Ubuntu Linux

MySQL workbench dashboard on Ubuntu Linux

Software Requirements and Conventions Used

Software Requirements and Linux Command Line Conventions
Category Requirements, Conventions or Software Version Used
System Ubuntu and many Debian derivatives such as Linux Mint.
Software Mysql-server and Mysql-workbench
Other Privileged access to your Linux system as root or via the sudo command and Active MySQL server
Conventions # – requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command
$ – requires given linux commands to be executed as a regular non-privileged user

Setting up MySQL server

As we already said, MySQL workbench is a graphical application for MySQL. To test its functionalities we need to connect to a working MySQL server. For the sake of this tutorial we will briefly see how to install the latter on Ubuntu 18.04 Bionic Beaver. It goes by itself that If you already have access to a database server, you can skip this part of the tutorial. To install MySQL on Ubuntu 18.04, all we have to do is to run the following command:

$ sudo apt-get update && sudo apt-get install mysql-server

The apt package manager will ask us to confirm we want to install the package and all its dependencies. After the installation process is complete, the mysql service will be automatically started by systemd and set to start automatically at boot. To test that the service is active, we can run:

$ systemctl is-active mysql
active


In this case the output of the command was 'active', meaning that the service, as expected, is currently running. We can use a similar command to verify that the service is enabled at boot:

$ systemctl is-enabled mysql
enabled<

Now that we have a working instance of MySQL, we want to run the secure_installation_scripts, which let us perform an interactive initial configuration of the server:

$ sudo mysql_secure_installation

If at this point we try to login from the mysql console with the root user credentials, we receive an error:

$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

The error happens because starting with MySQL version 5.7, if a root password is not provided during installation, the auth_socket plugin is used to authenticate the root user. With this plugin, MySQL just ignores the password we provide for a user (root in this case), and just checks that the user is connecting via UNIX socket and the username matches. To change such setup we must first login using the following command:

$ sudo mysql

Once logged in, to change the authentication method to mysql_native_password and set a native root password, we must execute the following queries:

mysql > ALTER USER 'root'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'our_super_secret_password';
mysql > FLUSH PRIVILEGES;

If we now exit the shell and try to login using the password we just set, we should receive no errors:

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

We should now have a configured MySQL database running on our system, and we can proceed to install and explore the MySQL workbench program and its functionalities.

Installation and connection setup

Installing MySQL workbench on Ubuntu 18.04 couldn’t be more easy: the package is included the official Ubuntu repositories, therefore we can install it by using the distribution package manager. The actual name of the package is mysql-workbench. To install it, all we need to do is to run the following command:

$ sudo apt-get install mysql-workbench

To launch the program, all we have to do is to go on the Ubuntu app drawer and click on the MySQL workbench launcher icon:


mysql-workbench-icon

The Ubuntu 18.04 app drawer

The program will be launched, and the main page will appear:


mysql-workbench-main

MySQL workbench main window

As we can see, the existing local connection automatically appears in the connection list as Local instance. We will use it to test the application. Before proceeding further, let’s see how we can create or modify an existing connection.

Create a new connection

There are many ways we can create a new connection, the most straightforward is by clicking on the “plus” button in the main application window. As soon as we do it, a new window will open: we can use it to setup the connection settings.


create-new-connection-window

Creating a new connection

The first thing to do is to set the  Connection Name, in this case we will  just use “test”. The second thing we must do is to select the Connection Method. In this case we will use the default option: Standard (TCP/IP). Further down we must setup the basic connection parameters: the hostname (name or IP address) of the machine on which the MySQL server is running,  and the port used for the connection, which by default is 3306.

We must also provide the name of the MySQL user we should connect with and its password. We can choose to store the password in the system keychain or clear an already stored password by clicking on the “Clear” button. If the password is not set here, the application will prompt us to provide it when we use the connection.

Finally, we can set the Default Schema to use when we connect to the server. If we leave this field blank, we will be able to select it later from the list of the available ones.

To test the connection we can click on the Test Connectionbutton; to store the connection we just created, instead, we click on the OK one. The connection should now appear in the list on the main program page.

Modify a connection

Modifying a connection is just as easy. We just right-click on an existing connection name and select Edit Connection from the contextual menu that appears.




connection-context-menu-edit

Edit connection entry in right-click context menu As soon as we do it, the same window we used to create the connection will open, letting us perform the changes we need.

Delete a connection

To delete an existing connection, we use a similar procedure: we right-click on the section dedicated to the connection in the main application window, but this time we select Delete Connection from the context menu. The application will ask for confirmation before actually deleting the connection:


delete-connection-confirmation-popup

Delete connection confirmation popup

Using the workbench

To use the workbench, we must connect to a running MySQL server by using one of the available connection. In our case, as we saw before, the application automatically created one connection for the local running server. We can use it just by clicking on the dedicate “box” on the main application page. Since we didn’t set a password for the connection, a prompt to provide it will be displayed:


connection-password-prompt

Connection passsword prompt

If the connection is successful a new tab will be opened:


connected-workbench-main-page

The connected workbench main page

As we can see, in the main toolbar we have a series of icons which let us access some functionalities. Among the others things we can:

  • open a new tab to execute SQL queries (one is already opened at the center of the window);
  • open an existing SQL Script in a new query tab;
  • create a new schema;
  • create a new table in a selected schema;
  • create a new view;
  • create a new procedure;
  • create a new function.

One the left part of the window we have a vertical menu divided in four main sections:

  • Management
  • Instance
  • Performance
  • Schemas

In the first section, Management, we can click on Server Status to check the overall server status, receiving generic information about the server, like the available features, directories, etc. Still in this section, we can access a page to manage the server users and their privileges, and we can import or export data from the server, for example, to create a schema backup.

In the Instance section we can shut down or startup the server, check logs and configuration files. In the Performance section we can, among the other things, access a Dashboard, where we can get status reports about outgoing and incoming server traffic, the number of SQL statements executed, etc.


workbench-dashboard

MySQL workbench dashboard

Finally in the Schemas sections, we have a list of all the existing schema in the database, and we can operate on them.

A simple use case: creating a schema

Creating a schema is one of the most basic operations we can perform from the MySQL workbench application. As a first thing we click on the fourth icon in the application toolbar. A new tab will be opened; in it, we will insert the schema name and its default collation. Suppose we want to create a schema named “book_store” and use utf8_general_ci as collation:


create-schema

Schema creation

When we are done, we must click on the Apply button. A popup window containing a description of the SQL query that will be used to create the schema will appear, in order for us to review it:


create-schema-popup

Create schema confirmation popup

Once we click on the “Apply” button again, the query will be executed. The newly created schema will now appear in the list in the Schemas section of the left column menu. If we right click on the name of the schema in the list, a context menu appears. From it, we can access options to modify, drop, open, or inspect the schema itself.

Our schema is currently empty. For the sake of this tutorial we will create two tables in it: the first will contain data about book authors, the second will contain data about book titles and will be linked to the first one by a foreign key. Let’s do it.

Creating tables

Creating a table is just as easy as creating a schema. First of all we double click on the name of the schema we want to create a table in to set is as the default one to be used (alternatively we can right click on the schema name, and click on “Set as Default Schema” in the context menu). After that, we click on the fifth icon in the toolbar, which let us create a new table.

In the tab that will appear we can graphically create a new table. The first thing to provide is the table name. A new column with the name “idnew_table” will be created by default: we just need to modify it. We can set the column Datatype, and use the checkboxes to set column properties as for example, PK (Primary Key), NN (NOT NULL), AI (AUTO_INCREMENT) etc.



As a first thing we will create the “author” table. The table will be composed by three columns:

  • Id (It will be the primary key)
  • name
  • birthdate

create-table

“Author” table creation

Once we are satisfied with our setup, we can click on the “Apply” button. Just like when we created the schema, a popup will open, showing us the SQL query that will be executed. Just click on “Apply” again to make the changes effective.


create-table-popup

Create table confirmation popup

The second table we will create, will contain data about book titles. We will named it “title”:


create-title-table

“Title” table creation

Our table is set, now we must add the foreign key that will establish the relation between the “title” and “author” tables. The columns involved are the “author_id” column in the title table and the “id” column in the author table.

To do that we switch on the “Foreign Keys” tab on the bottom of the page and provide the foreign key name and the referenced table; we then select the column and the referenced column. In the Foreign Key Options section we can optionally select “On Update” and “On Delete” actions and also add a comment.


title-table-foreign-key

Foreign key setup

Once again, when we are ready, we click on the “Apply” button to review the SQL query before actually executing it:


create-title-table-popup

Create table confirmation popup

Create an entity relationship model

At this point we have a schema populated with two tables, linked by a foreign key. Another very useful functionality of the MySQL workbench, is the creation of a diagram from an existing database schema. Here is how, in few easy steps, we can obtain a graphical representation of our schema and the relationships between its tables.

First of all we click on the main menu Database -> Reverse Engineer (we can also use the Ctrl-R shortcut). This will open a connection wizard. In the first page of the wizard we will be prompted to select between one of the existing connections, or to create a new one. The database we use for this example is on the “local instance” connection, so we just click on the “Next” button to proceed further.


reverse-engineer-wizard-connection

Connection selection wizard

The wizard will try to connect and retrieve a list of the existing schemas available via the selected connection. Once the connection is established, we click on the Next button again. At this point we will be prompted to select the schema we want to include, in this case we will select book_store and click on “Next” again.


reverse-engineer-wizard-schema-selection

Schema selection

The program will retrieve information on the selected schema. When ready, click on the “Next” button again. In the next page we will be prompted to select the objects we want to reverse-engineer. In this case we select “Import MySQL Table Objects”, ensure that the “Place imported objects” on diagram checkbox is checked and click on the “Execute” button.


reverse-engineer-wizard-object-selection

Objects selection

The program will execute the operations. When it is ready, just click on the “Next” button and a summary of the performed operations will be displayed:


reverse-engineer-wizard-summary

Wizard summary

At this point we click on the “Close” button. On the page that will be opened, we double click on the diagram icon, in the “EER Diagram” section, to visualize the generated diagram:


reverse-engineer-wizard-eer-selection

EER selection

reverse-engineer-wizard-result

The generated diagram In this case we created a diagram from an existing schema, however, the MySQL workbench makes possible to create a schema from scratch using diagrams.


Executing queries

As we mentioned before, when the MySQL workbench application opens, a query tab is already available; it is called “Query 1”. We can use the editor-like interface (which among the other things, provide auto-completion features) to execute SQL queries. Let’s say for example we want to execute an INSERT query to populate our “author” table with some authors information:


insert-query

INSERT query

To execute the query we just click on the “thunder” icon. We also have the option to select and execute only a part of the query. By default the entire query it’s executed. The result of the query is logged in the dedicated section:


insert-query-execution

INSERT query execution

Conclusions

In this tutorial we just scratched the surface of the several functions provided by the MySQL workbench utility, since it would be impossible to mention them exhaustively here. We saw how to install the program, how to create, modify or delete a connection to a MySQL database, how to create a schema and its tables graphically and how to reverse-engineer an existing schema to produce an entity-relationship diagram. Finally we saw how to issue a query via the dedicated editor. Feel free to explore the application to discover its many functionalities!



Comments and Discussions
Linux Forum