How to Install and Use MySQL Workbench

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.

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

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:

Now we can move on to creating the database.

Clients

In managed 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.

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.

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."

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

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:

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

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".

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).

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".

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:

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. 

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.

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;

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.

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)

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.

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.

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.

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.