MySQL Workbench Tutorial – A Comprehensive Guide To The RDBMS Tool

Last updated on May 22,2019 2.9K Views
1 / 1 Blog from MySQL GUI

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assement report and video recording

The previous blog on MySQL Tutorial mainly focused on the various commands and concepts related to SQL. In this blog on MySQL Workbench Tutorial, you will learn the tool for MySQL to perform various operations.

The following topics will be covered along this blog:

MySQL Workbench Tutorial: What is MySQL?

MySQL is an open-source relational database management system, which works on many platforms. It provides multi-user access to support many storage engines.

MySQL has various features that enable us to do many tasks such as comprehensive application development, providing availability and scalability.

Now, obviously, when you work at an industry level, you can’t do everything on the terminal right? You need some kind of dashboard which makes it easy for you to work with large databases and create models.

Well, the dashboard that can perform these actions is the MySQL Workbench.

MySQL Workbench Tutorial: MySQL Workbench & its Functionalities

MySQL Workbench is a designing or a graphical tool, which is used for working with MySQL servers and databases. This tool compatible with the older server 5.x versions and does not support the 4.x server versions.

The functionalities of MySQL Workbench are as follows:

  • SQL Development: This functionality provides the capability to execute SQL queries, create and manage connections to database servers using the built-in SQL Editor.
  • Data Modeling (Design): This functionality enables you to create models of your database schema graphically, perform reverse and forward engineer between a schema and a live database, and edit all aspects of your database using the comprehensive Table Editor.
  • Server Administration: This functionality enables you to administer MySQL server instances by administering users, performing backup and recovery, inspecting audit data, viewing database health, and monitoring the MySQL server performance.
  • Data Migration: This functionality allows you to migrate from Microsoft SQL Server, Microsoft Access, and other RDBMS tables, objects, and data to MySQL.
  • MySQL Enterprise Support: This functionality provides support for Enterprise products such as MySQL Enterprise Backup, MySQL Firewall, and MySQL Audit.

Now that you know about MySQL Workbench, let me next tell you the basic requirements and the steps to install MySQL Workbench.

Want to get certified as Database Administrator?

MySQL Workbench Tutorial: Install MySQL Workbench

The basic system requirements to install MySQL Workbench is you should have MySQL installed on your system.

Now, since MySQL Workbench is available for many operating systems. Each of these systems, have their own basic requirement that you can refer from here.

Apart from this, to download the MySQL Workbench, you have to click on the Downloads tab and then choose the edition that you want to download.

MySQL Workbench Download - MySQL Workbench Tutorial - Edureka

So, for example, if you want to download the Community edition of Workbench on Windows, you can refer to the link here.

Now, that you know how to install, let me tell you the editions of MySQL Workbench Editions.

MySQL Workbench Tutorial: MySQL Workbench Editions

MySQL Workbench is mainly available in three editions:

  • Community Edition (Open Source, GPL) 
  • Standard Edition (Commercial)
  • Enterprise Edition (Commercial)
FeaturesCommunity EditionStandard EditionEnterprise Edition
Visual SQL DevelopmentYesYesYes
Visual Database AdministrationYesYesYes
Performance TuningYesYesYes
User and Session ManagementYesYesYes
Connection ManagementYesYesYes
Object ManagementYesYesYes
Data ManagementYesYesYes
Visual Data ModelingYesYesYes
Reverse EngineeringYesYesYes
Forward EngineeringYesYesYes
Schema SynchronizationYesYesYes
Schema & Model Validation 1NoYesYes
DBDoc 1NoYesYes
GUI for MySQL Enterprise Backup 1NoNoYes
GUI for MySQL Enterprise Audit 1NoNoYes
GUI for MySQL Enterprise Firewall 1NoYesYes
Scripting & PluginsYesYesYes
Database MigrationYesYesYes

 Now, once you download and install MySQL Workbench, you will see the following screen i.e. the home tab.

MySQL Workbench Dashboard - MySQL Workbench Tutorial - EdurekaOn the left side of the  Home tab, you see 3 different icons right?

Well, these are the mainly the 3 modules:

  • SQL Development  –  This section consists of the SQL editor through which you can create and manage databases.
  • Data Modelling –  This section enables you to model your data according to your need.
  • Server Administration – This section is used to Migrate your databases between connections.

Now, before you get into these modules, to use their functionalities. You have to first start by creating a connection.

MySQL Workbench Tutorial: Creating a connection

Now, to create a connection you have to click on the plus sign that you see on the home tab that you see.

Steps To Create Connections In MySQL - MySQL Workbench Tutorial - Edureka

Once you click on that, you will see this dialog box, wherein you have to mention the connection name, connection method and other details that you can see in the dialog box. After you mention the details, just click on OK.

Steps To Create Connections In MySQL - MySQL Workbench Tutorial - Edureka

Once you click on OK, you will see that your connection has been created.

Now, let’s continue our discussion by getting into the SQL Editor.

Interested to crack interviews for DBA?

MySQL Workbench Tutorial: SQL Editor

The SQL Editor is used consist of a set of specialized set of editors such as query, schema, and table. Apart from this, the editor also consists of four panes that you can see on the screen.

Panes Of SQL Editor - MySQL Workbench Tutorial - Edureka

So, the queries and the panes together let you create and edit data, perform basic administrative tasks, view and export results, and run queries.

Now, let’s look into the administrative tasks section.

MySQL Workbench Tutorial: Administrative Tasks

Under this section, you will be going through the following sections:

Server Status

This tab gives you an immediate view into the basic health indicators and counters for your MySQL environment. As you can see in the below snapshot, this tab includes the view of the server’s running rate, available features, server directories and security settings for authentication and SSL.

Server Status - MySQL Workbench Tutorial - Edureka

Users & Privileges

This tab provides a list of all users and privileges who relate to an active MySQL server instance. So, with this tab, you can add and manage user accounts, adjust privileges, and expire passwords. Refer to the snapshot below.

User Privileges - MySQL Workbench Tutorial - Edureka

Data Export & Import

There are majorly three ways to export and import data in MySQL Workbench which you can go through the below table.

GUI LocationData SetExport TypesImport Types
Result Grid menu under the SQL editorThe result set (after performing an SQL query)CSV, HTML, JSON, SQL, XML, Excel XML, TXTCSV
Object Browser context menuTablesJSON, CSVJSON, CSV
Management NavigatorDatabases and/or TablesSQLSQL
Management NavigatorDatabases and/or TablesSQLSQL

Now, to export/ import data, you have to choose the option of Data Export / Data Import from the Navigator Pane.

After you choose the option, you have to mention the path name of the folder from/to which you want to import/export.  Refer to the snapshot below.

Data Export Or Data Import - MySQL Workbench Tutorial - Edureka

MySQL Enterprise Backup Interface

The commercial releases of MySQL Workbench enable us to use a MySQL Enterprise Backup (MEB) functionality so that we can safeguard the data from any loss.

There are mainly two MySQL Enterprise Backup operations available from MySQL Workbench:

  • Online Backup: This operation establishes a backup profile to define what should be backed up, where the backup should be stored, and when(the frequency) MySQL should be backed up.
  • Restore: This operation restores the MySQL server to a specific point in time, by restoring a backup that was created by the Online Backup feature in MySQL Workbench.

MySQL Workbench Tutorial: Performance Dashboard

The Performance Dashboard of MySQL Workbench gives you a statistical view of the server performance. To open the dashboard, go to the Navigator Pane and under the Performance section choose Dashboard. Refer to the snapshot below.

Performance Dashboard - MySQL Workbench Tutorial - Edureka

Apart from this, the performance section enables you to provide insight into the MySQL server operations through Performance Schema Reports and also lets you see the key statistics of Queries executed through Query Statistics.

MySQL Workbench Tutorial: Database Design & Modelling

Database design enables you to visualize requirements and resolve design issues. This enables you to create valid and well-performing databases while providing the flexibility to respond to evolving data requirements.

As you can see in the below snapshot, you mainly have 3  options.

Data Modelling - MySQL Workbench Tutorial - Edureka

From the left side, the plus sign lets you add a new EER Diagram. The folder sign allows you to add saved EER models, on your PC to base the Workbench. The arrow sign that you see, lets you either create an EER Model from Database or EER Model from a Script.

The below snapshot is the basic view MySQL Workbench.

Data Modelling View - MySQL Workbench Tutorial - Edureka

In the database modeling, you can create an EER diagram using the model editor. So, you can add a table, add a view, add a routine, edit the data in the table, highlight a specific part of the model.

Well, guys, this is not an end to the functionalities, I leave the rest of them so that you explore.

MySQL Workbench Tutorial: Data Migration Wizard

The MySQL Workbench provides the ability to migrate ODBC-compliant databases to MySQL. It allows you to migrate to different database types, including MySQL, across servers. It also enables to convert tables and copy data, but will not convert stored procedures, views, or triggers.

Apart from working on many platforms, the migration allows customization and editing during the migration process.

Migration Wizard - MySQL Workbench Tutorial - Edureka

The following are the steps  performed by Migration Wizard while migrating a database to MySQL:

  • Initially, it connects to the source RDBMS and retrieves a list of available databases.
  • Performs Reverse engineering of the selected database into an internal representation specific to the source RDBMS. So, with this step, all the objects are renamed based on the type of object name mapping method that is chosen.
  • Then, it automatically starts migrating the source RDBMS objects into MySQL specific objects.
  • After that, it allows us to review the changes so that we can edit and correct errors in the migrated objects.
  • Then it creates the migrated objects in the target MySQL server. You can always go back to the previous step and correct the errors if any occur.
  • Finally, the data of the migrated tables are copied from the source RDBMS to MySQL.

MySQL Workbench Tutorial: Advanced MySQL Capabilities

Provides an extension system that enables the developer to extend MySQL Workbench capabilities.  It also provides access to a cross-platform GUI library, MForms, and enables the creation of extensions that feature a graphical user interface.

The advanced features of Workbench enable the following capabilities:

  • You can create tools and plugins
  • You can manipulate schemas and automate common tasks
  • You can extend the Workbench user -interface and create custom Workbench features

So, that’s an end to this blog!

I hope you enjoyed reading this blog on MySQL Workbench Tutorial blog. We have seen the various functionalities and features of MySQL Workbench.

Want to learn more about MySQL?

If you wish to learn more about MySQL and get to know this open source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of ”MySQL Workbench Tutorial” and I will get back to you.

Comments
0 Comments

Browse Categories

Subscribe to our Newsletter, and get personalized recommendations.