How to Install and Use MySQL Workbench on Ubuntu

MySQL is an open-source Database Management System (DBMS) powered by Oracle Corporation. MySQL is a cost-effective and potent tool that helps with the management of large data while providing security. Our concerned topic today is MySQL workbench, a graphics tool that deals with MySQL servers and databases. This tool can be used to visualize modules for creating, executing, and optimizing several queries.

Prerequisites

Before installing MySQL workbench, make sure that the MySQL server is installed on your system. In this guide, we will be using the community server. If you already have a MySQL community server installed on your system, you can skip the following section and proceed directly to install MySQL Workbench.

MySQL Server Installation

First, we will make sure that your system packages are all up-to-date. Run the following command to do that.

If your packages are already up-to-date, there is no need to type the second command.

Now to install the MySQL server, type the following command.

Once you are done with the installation, let us configure the community server. Go to the terminal and type the following command.

Every new user has a default username, “root”, which is unprotected. To set the password, enter the following command.

mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘root’;

We set our password to “root”. You can set it to anything you like by replacing “root” (after password BY) in the above command.

After completing this step, type the following command.

mysql> flush privileges;

Now, you may exit MySQL by typing exit.

mysql> exit;

After you are done with these steps, move forward to the installation of MySQL Workbench.

MySQL Workbench Installation

Enter the following command to check whether your MySQL community server is up and running.

Move forward if the server is active, as can be seen above; otherwise, you might have to start your server again.

Enter the following command to download and install MySQL workbench.

Using MySQL Workbench

Go to the Activities menu, and in the search box, type “MySQL workbench” and run it.

You will see the following screen.

Creating a local connection is very simple, and provided that you have the community server installed in the default location, this task is just a matter of minutes. When you start the workbench for the first time, you get a window with only a few options. For each locally installed server, a connection tile is automatically created for you. To add more connections, click on the plus icon right beside the main MySQL Connections heading.

If you are to add a connection, the following dialogue box appears. Default values for a simple locale are pre-entered.

Give your connection a name in the Connection Name box. The most important feature here is the Hostname which is the server host IP address. MySQL server uses port 3306

On a fresh installation, only the root user with full access to everything in the server is available. Here, we will connect with the root and click Test Connection.

After you enter the password and hit enter, the following message will be shown.

Now go back to the home screen. You will see another connection tile with your connection name.

Clicking on this tile will open the new connection.

In MySQL Workbench, there is a lot that you can do. You can create schemas and tables. Add data into these tables or view specific data.

Now in the above screenshot, the section labeled “1” is where you enter your SQL queries. The section marked “2” is a sidebar where you manage your Databases and schemas. The section labeled “3” is where the output of the SQL queries you typed in the section marked “1” is displayed.

Connecting to a Schema

You can either create a new schema or import an already existing one. As you can see in the picture below, there is only a default schema called sys. Let us add our new schema.

Step 1
To load a schema from a file, go to the Server tab in the top navigation bar and click Data Import.

Step 2
The following dialogue box pops up. Check the box “Import from Self-Contained File” and browse the SQL file from your system. Now click the Start Import button.

Now, if your schema already has data, you do not need to import it. But if your schema is empty and the data for your schema is in another file, perform this next step.

Step 3
Click on Data Import in the Servers tab, check the Import from Self-Contained File box and give the location of your data file. Now in the Default Target Schema, select the target schema of this data and click the Start Import button. This step is very similar to step 2.

Viewing the Tables

Press the refresh button so that you can see your imported schema. In our case, we see a new schema called “sakila”.

You can expand “sakila” to view its content. You can view its tables by expanding the tables section.

The corresponding SQL query for this action and the resulting table appear in the query section by clicking on the calendar icon to the right of the name of the tables. While a success message is shown.

You can write queries to view specific rows or columns, e.g., the following query selects the first name column from the actor table of an actor whose actor_id is 1.

Conclusion

This brings an end to our guide to installing and working with MySQL workbench. MySQL Workbench is a convenient tool for manipulating and managing databases using MySQL servers. As we’ve seen, users can write SQL queries to perform different operations on their schema. Hopefully, it has been an informative read!