Guide To MySQL Workbench – Understand How It Works

Our guide to MySQL Workbench outlines its main program features. It also includes MySQL Workbench Linux and MySQL Workbench MacOS, and their respective installation procedures.

First, Let’s Define MySQL Workbench

MySQL Workbench is a unified visual tool that enables Database administrators or data architects to design, generate and manage a wide range of databases. MySQL Workbench includes a data modeler for creating complex ER models and provides for SQL development and administration tools for server configuration, user administration, and backup processes. 

MySQL Workbench is available in two editions; community and commercial edition. 

Community Edition

The community edition is available for FREE and you can get more information about it here.

Commercial Edition

The commercial edition is charged at a premium as it comes with a set of advanced features which includes management and technical support, geared towards providing you with top-notch levels of MySQL scalability and security among other features.

It provides database administrators with access to MySQL Enterprise Backup, MySQL Firewall and MySQL Audit. It reduces the risk, cost and complexity in developing, deploying and managing mission-critical business MySQL applications.

Main functions of MySQL Workbench

Data Modeling (Design)

Data modelling covers four main components:

• MySQL Workbench enables database administrators or data architects to design, model, generate and manage databases.
• It simplifies complex change management processes by including Schema synchronization and comparison utilities.
• It enables developers to reverse engineer existing databases or application to provide data architects with better insights into the database design.
• It provides a DBDoc that makes it possible for data developers to compile database documentation through an easy point-and-click process.

Develop

MySQL Workbench provides Database administrators and data architects visual tools which they can use to create, execute and optimize SQL queries with ease. These tools include:

• Visual SQL Editor which lets developers build, edit and run queries.
• Provides for object management through an object browser that enables developers to navigate database schemas and objects with ease.
• Database connections panel and connection wizards that enable developers to develop and manage standard database connections with ease.

Administer

My SQL Workbench is integrated with a visual console that enables the easy administration of MySQL environments, allowing data architects to gain better visibility into databases through a wide range of processes and tools which include:

• Its Workbench GUI for MySQL Enterprise Backup is an easy to set up backup and recovery tool which is extremely effective.
• MySQL Enterprise Audit enables DBAs to view which user did what, when, where and how. This is done by using inbuilt filters and text searches.
• It provides for easy user administration which includes granting user privileges, adding users, assigning passwords and setting up user profiles with ease.
• It enables DBAs to edit in an easy way both general and advanced parameters on their MySQL servers.
• It provides server logs which allow DBAs to view all the MySQL log files; error logs, binary logs, InnoDB logs, etc.
• It enables the importation and exportation of data by selecting the database schemas and/or tables from the object browser.
• It provides for real-time views of the status of your database through inbuilt health indicators.

Visual Performance Dashboard

MySQL Workbench provides suite tools geared towards improving the performance of MySQL applications which include:

• A performance dashboard that enables developers to view MySQL performance on key servers, network and InnoDB metrics.
• Provides for a number of performance reports.
• An Explain Plan feature that highlights the operations of MySQL when it runs SQL statements.
• Query statistics is a feature that provides instant statistics on SQL executed from the Workbench Editor.

Database Migration

MySQL Workbench provides for a migration wizard which is a visual, point-and-click process of configuring and managing various migration processes such as:

• Database migrations which include migrations from Microsoft SQL Servers, Ms Access, PostgreSQL, and among others.
• Provides for the migration of projects.
• Provides for the capabilities for users to define and target specific data source in advance of the migration process
• Enables users to migrate from older to newer MySQL versions.

For more in-depth information on the various program features, visit the below link:

https://www.mysql.com/products/workbench/

MySQL Workbench Mac

MySQL Workbench MacOS is distributed as a DMG file.  The DMG file is named: MySQL-workbench-community-version-osx.dmg where version is the MySQL Workbench version.

Installation

The installation and running of MySQL Workbench MacOS require the downloading of this DMG file, which is done by double-clicking on the file, which invokes an installation window. 

You can also drag MySQL Workbench icon onto the application icon as and it will self-install onto your system.

Launching

Once done, you’ll have two options of launching, either from the applications folder or from the command line as shown below:

 shell> /Applications/MySQLWorkbench.app/Contents/MacOS/MySQLWorkbench --help

Below is a sample and screenshot of how the command-line options works:

--query []
--admin 
--upgrade-mysql-dbs
--model 
--script 
--run--script 
--run 
--run-python 
--migaration
--quit-when-done
--log-to-stderr
--help, -h
--log-level=
--verbose, -v
--version
--open 

‍

‍

‍

Uninstalling

Uninstalling requires locating the MySQL Workbench MacOS in the applications folder, right-clicking it and deleting it.

Please Note:

Uninstalling doesn’t remove your Workbench configuration directory from your system. This directory is the one that has the MySQL connections, configuration settings, cache files, SQL snippets, history tags, among others. These files are stored under your user’s MySQL/Workbench folder.

For more in-depth information on MySQL Workbench Mac, visit this link

https://dev.mysql.com/doc/workbench/en/wb-mac.html

MySQL Workbench Linux

MySQL Workbench Linux contains a number of great features such as; data modeling, configuration and administration tools which you can use for backup and restore procedures. It provides for a couple of binary distributions for several variants of Linux, Fedora, Oracle Linux and Ubuntu.

Installation

The installation process can be done in a number of the following ways:

• Official MySQL Yum or APT Repository Packages
In this option, the binaries and packages are built and maintained by the MySQL release team and contain the newest versions of MySQL Workbench. The package is named mysql-workbench.
• Your Linux distributions repository packages
Unlike the APT repository packages, these particular binaries are built and maintained by the Linux distribution team and not by the MySQL team. They are stable but unfortunately, their releases always lag behind. Its package is named: mysql-workbench
• Download official MySQL packages
You can access these downloads at https://dev.mysql.com/downloads/workbench
• Download the source code and do the compiling yourself
The source code is available at https://dev.mysql.com/downloads/workbench

It is stored as a tar.gz or RPM package.

Requirements for Linux Installation

• Each of the methods available for Linux installation has got their own unique installation processes.
• The save password in vault functionality requires gnome-keyring-daemon to store the passwords. KDE systems have their own ksecretservice implementation services which they use. 
• The MySQL server administration feature requires sudo command privileges to execute multiple commands for Linux and MacOS. The sudo user must be able to execute the following commands below:

/usr/bin/sudo
/usr/bin/nohup
/usr/bin/uptime
/usr/bin/which
/usr/bin/stat

/bin/bash
/bin/mkdir
/bin/rm
/bin/rmdir
/bin/dd
/bin/cp
/bin/ls

The sudo user must keep the home environment variable when executing the system commands. This will entail adding the following entry to the /etc/sudoers file safely by using the visudo command.

Defaults env_keep +="HOME"

Launching

After installing the MySQL Workbench, the next step is launching the application.

This is done by selecting Applications, Programming, MySQL Workbench option from the main menu.

It can also be launched from the command line on Linux.  The command is written as:

shell>/user/bin/mysql-workbench-help

This will display the available command-line options.

mysql-workbench [] []
--force-sw-render
--force-opengl-render
--query [|]
--admin 
--upgrade-mysql-dbs
--model 
--script 
--run-script 
--run 
--run-python 
--migration
--quit-when-done
--log-to-stderr
--help, -h
--log-level=
--verbose -v
--version
--open 

Uninstalling

The uninstallation process depends on the installation process you had used when installing it.

Please Note:

The official package when using the apt option at dev.mysql.com mysql-workbench-community, however, most Linux distributions use the name mysql-workbench, therefore your commands should be adjusted accordingly.

shell> sudo apt-get remove mysql-workbench-community

Or, alternatively:

shell sudo dpkg -r mysql-workbench-community

However, the above commands do not remove the configuration files.

Use the command below if you want to remove the configuration files.

shell> sudo dpkg --purge mysql-workbench-community

The default Workbench configuration directory is ~username/mysql/workbench where “~ username” is the path to your user’s home directory.

For more in-depth information on MySQL Workbench Linux, visit the following websites:

https://dev.mysql.com/doc/workbench/en/wb-linux.html

https://www.linode.com/docs/databases/mysql/install-and-configure-mysql-workbench-on-ubuntu/

Final Thoughts

MySQL Workbench stands among the best FREE graphical user interface tools developed for MySQL. With a multitude of training materials consisting of video tutorials and guides, the implementation process of MySQL Workbench across multiple operating systems and platforms is well covered.