What are MySQL Workbench Administration tools?

[vishrut-singhal]

MySQL Workbench Administration Tool

The Administration Tool plays an important role in securing the data of the company. Here, we are going to discuss the user’s management, Server configuration, Database backup and restorations, Server logs, and many more.

User Administration

It is a visual utility that allows for managing the user that relate to an active MySQL Server instance. Here, you can add and manage user accounts, grant and drop privileges, view user-profiles, and expire passwords.

Server Configuration

It allows for advanced configuration of the Server. It provides detailed information about the Server and status variable, a number of threads, buffer allocation size, fine-tuning for optimal performance, and many more.

Database backup and restorations

It is a visual tool, which is used for importing/exporting MySQL dump files. The dump files contain SQL scripts for creating databases, tables, views, and stored procedures.

Server Logs

It displays log information for the MySQL Server by each connection tab. For each connection tab, it includes an additional tab for the general error logs.

Performance Dashboard

This tab provides the statistical view of the Server performance. You can open it by navigating to the Navigation tab, and under the Performance section, choose Dashboard.

MySQL Workbench Create, Alter, Drop Database

In this section, we are going to see how a database is created, altered, and drop by using the MySQL Workbench. Let us see in detail one by one.

Create Database

To create a database, do the following steps:

1. Open the MySQL Workbench and logged in using username and password. Then, go to the Navigation tab and click on the Schema menu . Here, you can see all the previously created databases.

2. If you want to create a new database, right-click under the Schema menu and select Create Schema or click the database icon (red rectangle), as shown in the following screen.

3. The new Schema window screen open. Enter the new database name (for example, mytestdb) and use default Collation . Collation is used to store specific data characters, mainly useful for storing foreign languages. Now, click on the Apply button as shown in the screen below:

4. A new popup window appears, click Apply->Finish button to create a new database.

5. After the successful creation of the database, you can see this new database in the Schema menu. If you do not see this, click on the refresh icon into the Schema menu.

6. If you want to see more information about the database, select mytestdb database, and click on the ‘i’ icon. The information window displays several options, like Table, Column, Functions, Users, and many more.

7. MySQL Workbench does not provide an option to rename the database name, but we can create, update, and delete the table and data rows from the database.

Drop Database

1. To delete a database, you need to choose the database, right-click on it, and select the Drop Schema option. The following screen appears:

2. Select Drop Now option in the popup window and the database including table, data rows will be deleted from the database Server.

MySQL Workbench Create, Alter, Drop Table

In this section, we are going to see how a table is created, altered, and drop by using the MySQL Workbench. Let us see in detail one by one.

Create Table

To create a table, do the following steps:

1. Open the MySQL Workbench and logged in using username and password. Then, go to the Navigation tab and click on the Schema menu. Here, you can see all the previously created databases. You can also create a new database.

2. Select the newly created database, double click on it, and you will get the sub-menu under the database. The sub-menu under the database are Tables, Views, Functions, and Stored Procedures, as shown in the below screen.

3. Select Tables sub-menu, right-click on it and select Create Table option. You can also click on create a new table icon (shown in red rectangle) to create a table.

4. On the new table screen, you need to fill all the details to create a table. Here, we are going to enter the table name (for example, student) and use default collation and engine.

5. Click inside the middle window and fill the column details. Here, the column name contains many attributes such as Primary Key(PK), Not Null (NN), Unique Index (UI), Binary(B), Unsigned Data type(UN), Auto Incremental (AI), etc. The following screen explains it more clearly. After filling all the details, click on the Apply button.

6. As soon as you click on the Apply button, it will open the SQL statement window. Again, click on the Apply button to execute the statement and Finish button to save the changes.

7. Now, go to the Schema menu and select the database which contains the newly created table, as shown in the screen below.

Alter Table

To alter a table, do the following steps:

1. Select the table you want to modify, click on the ‘i’ icon, and you will get the following screen.

2. In the above screen, you can modify the column name, data type, and other table settings.

Drop a Table

1. To delete a table, you need to choose the table, right-click on it, and select the Drop Table option. The following screen appears:

2. Select Drop Now option in the popup window to delete the table from the database instantly.

MySQL Workbench Insert, Read, Update, Delete Data Rows

In this section, we are going to see how we can insert, read, update, and delete data rows by using the MySQL Workbench. Let us see in detail one by one.

1. Open the MySQL Workbench and logged in using username and password. Then, go to the Navigation tab and click on the Schema menu. Here, we have successfully created a database (mystudentdb) and student table using MySQL Workbench.

2. Select the table, and when we hour a mouse pointer over the student table, you can see the table icons appears here. Click the table, which will open a new window where the upper section shows the MySQL statement, and the lower section shows the data rows.

3. To enter a data row, select the respected column, and insert the data value. Inserting data value in rows is similar to the Microsoft Excel Worksheet.

4. After entering the data rows, click on the Apply->Apply>Finish button to save the data rows.

5. Similarly, we can edit or modify the previously saved data rows. After modification, save new value, click on the Apply button to save changes. It will generate an SQL update statement save the changes to the database.

Delete Row

1. To delete an individual row from the table, you need to select a data row, right-click on the right icon in front of the row and select Delete Row(s) option.

2. Now, click Apply->Apply->Finish button to save changes to the database.

MySQL Workbench Export and Import Database(Table)

In this section, we are going to learn how we can export and import the database or table by using the MySQL Workbench.

Export Databases(Tables)

1. To export databases or tables, go to the Menu bar, click on Server, and select the Data Export option, as shown in the following screen. It will open a new window of data export settings and options.

2. Select any database, and it will display all the corresponding tables under the selected database. Here, we can also select one or multiple database checkboxes to include the database in the Export file. Similarly, we can select one or multiple tables from the left section of the window.

3. Let us select two databases, namely (myproductdb and mystudentdb), including all tables under this database. Now, go to the drop-down setting, we can select ‘Dump Structure and Data’, ‘Dump Data Only’, and ‘Dump Structure Only’ option.

• Dump Data and Structure: It will save both table structure and data rows.
• Dump Data Only: It will save only the inserted rows in the tables.
• Dump Structure Only: It will save only the table structure, which are database columns and data types defined by us.

4. In the Export option, you can select the export path of your choice. Here, I will keep the default setting. Also, there are two radio buttons that are explained below.

• Export to Dump Project Folder: It will save all the tables as separate SQL files under one folder. It will be useful when you import or restore the export file one by one table.
• Export to Self-Contained File: It will store all the databases and tables in a single SQL file. It is a good option when you want to import all the databases, tables, and data rows using a single SQL file.

5. Click the Start Export button, which displays the progress bar and log. Now, open the Document folder in your system to locate the export files.

Import Databases (Tables)

1. To import databases or tables, go to the Menu bar, click on Server, and select the Data Import option. It will open a new window of data import settings and options.

2. Here, you can see the two radio options to import databases and tables, which are:

• Import from Dump Project Folder
• Import by using Self-Contained File

3. We are going to select ‘Import from Dump Project Folder’ and click on ‘Load Folder Content’ to display all the available databases in the project folder.

4. Select myproductdb database from the Data Import option and also select the corresponding product table.

5. Choose the ‘Dump Structure and Data’ option and click the Start Import button to import the databases and tables from the backup file.

6. Now, go to the Schema->myproductdb->table and refresh it to see the currently imported database or table.