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
Software Requirements and Conventions Used
|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
|Conventions||# – requires given linux commands to be executed with root privileges either directly as a root user or by use of
$ – 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
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:
The program will be launched, and the main page will appear:
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.
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
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.
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:
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:
If the connection is successful a new tab will be opened:
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:
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.
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.
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:
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:
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 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)
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.
The second table we will create, will contain data about book titles. We will named it “title”:
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.
Once again, when we are ready, we click on the “Apply” button to review the SQL query before actually executing it:
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.
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.
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.
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:
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:
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:
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:
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!