This MySQL Workbench Tutorial explains how to download, install and use the MySQL RDBMS:
MySQL is an open-source relational database management system (RDBMS) that works on many platforms (like Windows, Mac, Linux, etc.)
MySQL Features
It allows data to be stored and accessed across multiple storage engines. As MySQL users can access their data using SQL, there is no need to learn new commands. We can replicate data and partition of tables can be done in a better way.
=> Read Through ALL the MySQL Tutorials
Table of Contents:
MySQL Workbench Tutorial
MySQL enables host-based verification (i.e. provides access privilege and encrypted password). MySQL supports many clients, command-line programs, utility programs, and administration tools (Example: MySQL Workbench).
MySQL Workbench Introduction
- Workbench is a visual database design tool for working with MySQL servers and databases, using which we can integrate SQL development, administration, database design, creation, and maintenance into a single environment for the MySQL database.
- It is a GUI (graphical user interface tool) for MySQL. It allows users to visualize the module to create, execute and optimize queries. Using MySQL Workbench, we can also design, create databases and browse database schemas.
Before proceeding ahead, please note, that we are using MySQL version 8.0. One can download it from here.
MySQL Workbench Features
#1) SQL Development: Users can execute SQL queries. Using a built-in SQL editor, we can create and manage database servers.
#2) DATA Modelling:
- Users can create models of schemas.
- Perform Reverse/Forward Engineering.
- Using Comprehensive Table Editor, we can edit the database.
- Create tables, columns, indexes, views, partitions, triggers, routines, privileges, and options.
#3) Server Administration: By administering users, inspecting audit data, monitoring MySQL server performance, performing backup and recovery.
#4) Data Migration: We can migrate data, tables, and objects from different databases (such as Microsoft SQL Server, PostgreSQL, SQL Anywhere, SQLite, and Sybase ASE) to MySQL.
#5) Performance Monitoring: Provide data access and views into optimizing query which can help developers to have a better understanding.
#6) Database Administration:
- We can Configure Instances and browse log files.
- We can Start/Stop database instances.
- Import/Export from dumped data.
MySQL Workbench Editions
Workbench comes in different Editions:
- Community Editions – Available at free of cost and can be easily downloaded.
- Commercial Users can choose from the below Editions depending on their requirements.
- MySQL Standard Edition
- MySQL Enterprise Edition
- MySQL Cluster Carrier Grade Edition
Let’s learn how to install Community Edition.
MySQL Workbench Download And Installation
Firstly, MySQL is to be available in the system on which Workbench will be installed.
We can download it from the link.
Upon clicking on the download option, it will direct the user to the MySQL Community Downloads page.
Here users can create a new account or login with an existing account else users can click on the “No thanks, just start my download” option to start downloading directly.
After download, we can right-click on the MSI file and choose the install option (or simply double click).
Under Setup Type, you can choose the Complete or Custom option. (If we choose the Complete option, all the features will be available for use).
Once the installation is over, we will be able to see the home page of MySQL Workbench.
Steps to Launch MySQL Workbench
- Click on Start.
- Go to Programs.
- Click on MySQL.
- Select MySQL Workbench.
Uninstallation of Workbench
- Go to the Control Panel.
- Choose Add or Remove Programs
- Search for MySQL Workbench.
- Select Remove
Creating a Connection with Workbench
To create a new connection, follow the steps below:
- Click on the “+” option.
- “Setup New Connection” page gets opened.
- Fill in the details.
- Click on “Test connection”.
- The below pop-up will appear where we need to give the password and click “OK”.
- “Successfully made the MySQL connection” message will appear.
- Click on “OK”.
SQL Development Editor
SQL editor consists of 3 panels and a set of specialized editors. Panels consist of the sidebar, secondary sidebar, and output area. Panels can be either hidden or shown. The editor consists of Query, Schema, and Tables. The editor opens with an active MySQL connection in the secondary sidebar.
Using these we will be able to edit data, create data, export results, view results, build query, edit query, run queries, and perform basic RDBMS administrative activities. To write and debug query, context help, color syntax highlighting and code completion would be useful.
SQL Administrative Tool
There are multiple areas that we can learn and understand as far as SQL Administrative Tool is concerned.
Given below are the key topics that we will be covering:
- Server Administration
- Users and Privileges
- Performance Dashboard
Let’s dig into the details of the above topics:
#1) Server Administration
The following figures show different elements of Server Administration.
[image source]
Given below is an explanation of each of the number points mentioned in the figure.
- Connection Tab: A connection tab can be opened for Active or Inactive servers. For every connection made to the server, a separate connection tab is opened.
- Server Menu: This includes the Administration tab within the Navigator area, Management Access Settings, and Reset saved Passwords for connections.
- Administration Tab: Each new tab opened replaces the active tab, so only 1 Administration tab can be opened at a time. To close the Administration tab, click on “x”.
- Operation Tab: This includes Administration – Users and Privilege’s, Login, Schema Privilege, Account Limits, Administrative Roles.
- Sidebar Panel: This includes Information Areas and Navigator.
- Operation Buttons: Depending on the selected tab, the set of buttons changes.
Following are a few more terminologies related to Server Administration:
- Server Logs: Used to display the login information of the server. We can access logs from 3 different places i.e. Navigator area, Server, Server logs (Provided there is a valid connection and connection tab for it to be opened).
- Service Control: We can perform a Start-up message log, Start and Stop MySQL Instance, View the status of the MySQL Instance.
- Configuration (Options File): We can configure the MySQL configuration file using the Options File.
#2) Users and Privileges
This feature provides the list of users and the privileges those users have on the different database objects under MySQL. These objects include databases, schemas, tables, views, or row-level access on the tables.
Enlisted below are the different tasks that one can perform using the User and Privileges tab:
- User Accounts: This tab consists of a list of all users associated with MySQL connections. We can add or delete an account associated with a user.
- Login Tab: This tab consists of login credentials. We can create or connect to multiple accounts to connect to different hosts.
- Account Limits Tab: This tab can be used to establish restrictions on the accounts. The maximum number of queries, updates, or connections an account is entitled to can all be declared here.
- Administrative Roles Tab: This tab is very useful in determining the access a user is entitled to. However, the point to be noted here is that the accesses are given to roles, and users are assigned to these roles.
Say for example, if you need to be able to perform all the tasks in the database, then you should not request separate accesses, say, CREATE, SELECT, ADD USERS, etc. One can simply request access to a DBA role that has access to all the activities on the MySQL server.
If we need access to maintain user accounts, then you should seek a UserAdmin role.
- Schema Privileges Tab: This tab helps in defining the way in which a user’s account can access one or more schemas. The feature allows us to apply the rules to all the schemas or a pattern of schemas or to a particular schema.
#3) Performance Dashboard
To open the dashboard, go to the sidebar panel, and in the performance section click on Dashboard. Here you can see the server performance statistics. As you can see from the image below, the dashboard section consists of Network Status, MySQL Status, and InnoDB status.
Now, let’s go through a few terminologies related to the Performance Dashboard.
- Network Status: Here, Incoming Network Traffic, Outgoing Network Traffic, and Client Connection Data Points are present. It highlights statistics for incoming and outgoing traffic (from MySQL server to client-server).
- MySQL Status: This includes data points for Table Open Cache, SQL Statement Executed and counts of SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER and DROP statements.
- InnoDB Status: Here are the three groups of data points namely Usage, Write, and Read. The InnoDB storage engine generates an overview of Disk Activity and InnoDB Buffer Pool, which can be seen.
Database Design and Modelling
Frequently Asked Questions
Q #1) What is MySQL Workbench used for?
Answer: MySQL Workbench is a visual database design tool. This tool has features like database design, SQL development, administration, creation, and maintenance integrated into a single IDE.
Q # 2) What is the difference between MySQL and MySQL workbench?
Answer: MySQL is a database where we can store, retrieve, access, and manipulate data. MySQL Workbench is a software tool from which we can access MySQL. We can access MySQL through an interface rather than a command line.
Q #3) Is MySQL Workbench a DBMS?
Answer: It is a tool by which the users can perform queries on DBMS.
Q #4) How do I get MySQL Workbench?
Answer: Refer to the Installation section of this article.
Q #5) Does MySQL Workbench install MySQL?
Answer: We can download MySQL workbench using MySQL installer which installs and updates all the MySQL products on windows.
Q #6) Do I need a MySQL server for the MySQL workbench?
Answer: To use the workbench, we need a MySQL server either installed locally or available over a TCP/IP connection.
Q #7) How do I start MySQL workbench from the command line?
Answer:
- Click on Start
- Go to Programs
- Click on MySQL
- Select MySQL Workbench
- Start MySQL Workbench from the command line.
Q #8) What are MySQL workbench and MySQL servers?
Answer: MySQL Server is a relational database. while Workbench is a visual database design and modeling tool.
Q #9) How do I download MySQL Workbench?
Answer: You can Download MySQL workbench from here
Q #10) How do I start MySQL workbench after Installation?
Answer:
- Click on Start
- Go to Programs
- Click on MySQL
- Select MySQL Workbench
- Start MySQL Workbench from the command line.
Conclusion
So, we were able to learn about the various functionalities and features like: What is MySQL? Introduction to MySQL Workbench, Install Workbench, MySQL, Workbench Editions, Creating a connection, SQL Development Editor, Administration Tool, Performance Dashboard.
=> Click Here for Complete MySQL Tutorial Series