The previous article on MySQL Tutorial mainly focused on the various commands and concepts related to SQL. In this tutorial on MySQL Workbench, you will learn the tools for MySQL to perform various operations.
The following topics will be covered along this blog:
- What is MySQL?
- MySQL Workbench and its Functionalities
- Install MySQL Workbench
- MySQL Workbench Editions
- Creating a connection
- SQL Development Editor
- Administrative Tasks
- Performance Dashboard
- Database Designing and Modeling
- Data Migration Wizard
- Advanced MySQL Capabilities
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 and Its Functionalities
MySQL Workbench is a designing or a graphical tool that 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.
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.
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)
| Features | Community Edition | Standard Edition | Enterprise Edition |
| Visual SQL Development | Yes | Yes | Yes |
| Visual Database Administration | Yes | Yes | Yes |
| Performance Tuning | Yes | Yes | Yes |
| User and Session Management | Yes | Yes | Yes |
| Connection Management | Yes | Yes | Yes |
| Object Management | Yes | Yes | Yes |
| Data Management | Yes | Yes | Yes |
| Visual Data Modeling | Yes | Yes | Yes |
| Reverse Engineering | Yes | Yes | Yes |
| Forward Engineering | Yes | Yes | Yes |
| Schema Synchronization | Yes | Yes | Yes |
| Schema and Model Validation 1 | No | Yes | Yes |
| DBDoc 1 | No | Yes | Yes |
| GUI for MySQL Enterprise Backup 1 | No | No | Yes |
| GUI for MySQL Enterprise Audit 1 | No | No | Yes |
| GUI for MySQL Enterprise Firewall 1 | No | Yes | Yes |
| Scripting and Plugins | Yes | Yes | Yes |
| Database Migration | Yes | Yes | Yes |
Now, once you download and install MySQL Workbench, you will see the following screen i.e. the home tab.
On 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 Modeling — 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.
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.
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.
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.
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
- Users and Privileges
- Data Export and Import
- MySQL Enterprise Backup Interface
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.
Users and 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.
Data Export and Import
There are majorly three ways to export and import data in MySQL Workbench which you can go through the below table.
| GUI Location | Data Set | Export Types | Import Types |
| Result Grid menu under the SQL editor | The result set (after performing an SQL query) | CSV, HTML, JSON, SQL, XML, Excel XML, TXT | CSV |
| Object Browser context menu | Tables | JSON, CSV | JSON, CSV |
| Management Navigator | Databases and/or Tables | SQL | SQL |
| Management Navigator | Databases and/or Tables | SQL | SQL |
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.
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 thePerformance section choose Dashboard. Refer to the snapshot below.
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 and Modeling
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.
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.
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.
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
I hope you enjoyed reading this tutorial on MySQL Workbench. We have seen the various functionalities and features of MySQL Workbench.
Topics:
database, workbench, tutorial, mysql, database tutorial, mysql workbench
Source: https://dzone.com/articles/mysql-workbench-tutorial-a-comprehensive-guide-to
Posted by: milanmilandouthite0268465.blogspot.com
