Hey there! Welcome to Hostman! 🎉

How to Install and Use MySQL Workbench

11.12.2023
Reading time: 7 min
Hostman Team
Technical writer

To create a small database, you only need to write a short SQL script. It may be enough for learning or testing purposes. However, "real" databases, even in small projects, consist of dozens of tables and views and can be very difficult to work with using only SQL. It is hard to keep dozens of entities in your head without getting confused. That's when MySQL Workbench becomes a solution.

MySql Workbench is software for creating and designing databases using schemas and other visual tools. Today, we will show you what Workbench is, how to install it and connect to a cluster, how to create tables and models, and how to import and export data.

How to install MySQL WorkBench

To install MySQL Workbench, go to the official website and choose MySQL Enterprise Edition ->Workbench among the products. Or you can follow this link.

Image7 (1)

Click on "Download Now" and go to the page with options:

Image5 (2)

Here, select the operating system. In our case it is Windows 10 64-bit. After downloading and installing, the application is ready to work. 

How to create a database in MySQL Workbench

Before creating databases, let's define the entities and their attributes. Let's think of a database for an abstract hosting service with three tables: clients, servers, services.

The "Clients" will have the following columns: contract number as primary key, client's full name, service ID, and server ID.

The columns of the "Servers" table will display information about the servers themselves:

  • Server ID

  • IP address

  • Size of both RAM and permanent memory

  • Processor and video card parameters

In the "Services" table, we will specify their cost, name, and description.

Of course, our database will cover only a few of the theoretical hosting activities, but for introduction purposes, such a database will be enough.

To create a database, choose "File"->"New Model" in the main menu:

Image9 (1)

Now we can move on to creating the database.

Clients

In MySQL, table creation is implemented in several ways. We will create the "Clients" table without using the visual model. 

To do this, click "Add Table" in our database menu, after which a menu with settings will open.

  • Table Name;

  • Column Name is our attribute name;

  • Datatype is attribute type (number, time, letters, etc.);

  • PK, NN, UQ, etc. are column constraints. PK stands for primary key, NN stands for Not Null. The rest of the constraints are not important to us yet.

19d2f68f 6f50 482e 9bb3 2b81225ff88d

Servers

We create the "Servers" table in the same way. Here, we will focus on column attributes and their selection.

  • Server ID is a primary key with the Integer type. It is essentially a regular number; in practice, the server ID would be the server number;

  • IP is a set of up to 15 ASCII characters. We assume storage of an IPv4 address, for example 255.255.255.255.255; 

  • RAM, GB is the size of RAM expressed using the Float data type (floating point numbers);

  • Disk space, GB is similar to RAM;

  • Processor and GPU (video card) are where we'll place equipment names with a maximum length of 45 characters.

8be54a44 B772 405b 9559 56293058e976

Services

For a change, let's create "Services" using the visual functionality of MySQL Workbench. 

To do this, click on the "Add Diagram" button above the name of our database, "mydb."

88736c0c 4976 4ca9 Af80 Bdff7cbc51a9

Now click on the diagram that will appear, and you'll get to a new tab.

Bb1c960c 18c5 4f4e B4de C1b0797b4b9f

In the left menu of the "Catalog Tree" we can see the already created "Clients" and "Servers" tables. We can transfer them to the visual diagram:

F5d68b88 Ecee 49ac A55e Ec74b2686767

To add a new table to the diagram, select "Place a New Table" from the left menu or press "T". 

652d1de0 Ccf5 4bcb A4fa B61888d9aa88

By clicking twice on the window that appears, we go to the settings. Our "Services" table will contain the columns "Service ID", "Name", "Description" and "Price".

D2204ee4 Ecc1 4856 8b93 13eed33e5d39

Creating relations

The database we are creating is called a relational database. Tables do not exist in a vacuum and are connected to each other: a client rents a certain server or buys a certain service. It is necessary to trace this relationship in our database. 

In practice, it is realized with the help of the foreign key (FK) or foreign key column attribute. FK defines the set of possible column values in a row as those from another table. Suppose we have two tables: "Employees" and "Shifts". "Shifts" has columns "Time" and "Employee (FK)". So, in the column "Employee (FK)," we can put only the employee who is in the table "Employees".   

It is easy to create MySQL links. In order to link two tables, you need to define the type of link between them. Our tables will be linked, but they will not identify each other. In practice, this means that an FK is not a PK. Therefore, we will use the Non-Identifying relationship. Let's move directly to creating the relationships.

Clients-Servers

The linking column here will be the "Server ID". Can the same server be in several clients in our database? Yes, it can, but not at the same time. In order to create such a relationship, we select "Place a New 1:n Non-Identifying Relationship" in the left menu and click first on "Clients" (where the relation goes to) and then on "Servers" (where the relation comes from).

02e532e2 Ba36 4809 942b 41cfb10b7ec5

We have a new column in "Clients," but we would like to link the relation to an existing column. To do this, right-click on "Clients" and select "Edit Clients".

203cb732 17f3 4073 Bb3a Ec137bb50291

Then go to the "Foreign Keys" tab below. Here we can select the "Server ID" column as the "Foreign key" and then delete the resulting column. We perform the same procedure with the Clients-Services link and eventually have the following result:

D961d309 0bae 4bf1 8b5b 7324f8033bf4

Views

In the menu on the left, you might have noticed the "Views" section. Views are the display of the required data based on the available tables. For example, you may need information about clients and the cost of services they use. In this case, you will need a View. Let's try to create one. 

First, we need to fill the database with information. To do this, go to the table parameters and the "Inserts" tab. 

Ed46938f C220 40b6 Bd6e C9d2365f583f

Then fill in the "Servers" and "Clients" in the same way and add a View to the diagram using the "Place a New View" button or the "V" key.

5eb59988 Fed2 4689 81ea 6d4e0800ccfe

Double-click on "view1" and in the window that appears, write the SQL query that corresponds to our mapping.

CREATE VIEW `Full Name + Services` AS
SELECT Name, ServiceName, Price From Clients
JOIN Services ON Clients.service_id = Services.service_id;

E467987f D19f 4a4e B272 46728818be91

But to see the result of the query, we need to connect to the cluster and import our database there.

Connecting to a cluster

The first thing you need is a cluster. We already have one, so we will connect to it. To create a connection, you need to go to the main page and click the plus sign.

7d959594 1171 4e34 A440 21caa0f27498

In the window that appears, we will need the following settings:

  • Connection name is useful if there are several connections;

  • Hostname is our cluster IP address;

  • Port is the cluster port. The default is 3306;

  • Username is the name of your account;

  • Password (if available)

51111fb8 D432 4d9e Bb33 89a9af8fa395

Click "OK," and you will connect to the cluster.

Importing and exporting the database

To work with the database and execute SQL queries, you must first import it into the cluster. To do this, click on the "File" button on the diagram tab and select "Export"->"Forward Engineering SQL CREATE Script" in the pop-up menu.

4b31ecc5 Ecbf 466f 9571 5bff5e5d23ff

Remember to click the checkboxes in the corresponding fields to avoid losing the filled tables. The resulting script will allow us to import the database into the cluster.

Go back to the cluster tab, select "Data Import/Restore" in the "Management" menu.

164d216a 9918 4fd9 B57f 46beee6932e0

Here, we need to select the "Import from Self-Contained File" option, select the script, and click "Start Import".

How to dump a MySQL WorkBench database

To create a dump, you need to click "Data Export" in the "Management" section and select the required parameters.

9844bdc1 B56f 46c0 8107 32b96fdde49b

We will save the entire database: its logical structure and stored data. To complete the process, click "Start Export".

Conclusion

In this article, we have learned the program's basic functionality, created a small database and imported it to the server. Of course, we could only cover some of the features. But this should be enough to start creating your own MySQL databases from scratch and study this tool more deeply.