11. MySQL Workbench
MySQL Workbench is Oracle’s graphical user interface for querying and managing MySQL Server. It can be seen as one of the two Swiss army knives for working with MySQL, with the other being MySQL Shell that is discussed in the next chapter.
The main feature of MySQL Workbench is the query mode where you can execute queries. There are however also several other features such as the performance reports, Visual Explain, the ability to manage the configuration and inspect the schema, and more.
If you compare MySQL Workbench with MySQL Enterprise Monitor, then MySQL Enterprise Monitor is dedicated to monitoring and is a server solution, whereas MySQL Workbench is a desktop solution which is primarily a client for working with MySQL Server. Similarly, the monitoring that is included in MySQL Workbench is all ad hoc monitoring, whereas MySQL Enterprise Monitor as a server solution includes support for storing historical data.
This chapter will introduce MySQL Workbench and go through installation, basic usage, and how to create EER diagrams. The performance reports and Visual Explain will be covered in later chapters.
If you are already familiar MySQL Workbench, you can consider skipping this chapter or skim it.
You install MySQL Workbench in the same way as other MySQL programs except there is only support for using the package manager (thus no standalone installations). The MySQL Workbench version numbers follow the MySQL Server versions so that MySQL Workbench 8.0.18 is released at the same time as MySQL Server 8.0.18. A MySQL Workbench version supports the MySQL Server versions that are still being maintained at the time of release, so MySQL Workbench 8.0.18 supports connecting to MySQL Server 5.6, 5.7, and 8.
It is best to use the latest MySQL Workbench release. You can see the compatibility of the MySQL tools at https://dev.mysql.com/doc/mysql-compat-matrix/en/.
This section will show examples of how to install MySQL Workbench on Microsoft Windows, on “Enterprise Linux 7” (Oracle Linux, Red Hat Enterprise Linux, and CentOS), and on Ubuntu 19.10. Other Linux platforms are similar in concept to the two Linux examples.
If you are a MySQL customer, it is recommended to download MySQL Workbench from Patches & Updates in My Oracle Support (MOS). This will give you access to the commercial version of MySQL Workbench which has some extra features such as an audit log inspector and a graphical user interface for MySQL Enterprise Backup (MEB).
On Microsoft Windows, the preferred way to install MySQL Workbench is to use MySQL Installer for Windows. If you have other MySQL products installed, you may already have MySQL Installer installed in which case you can skip the first steps of these instructions and instead click Add on the main screen which takes you to the point of Figure 11-5.
There are two choices for the installer. The first called the web installer (mysql-installer-web-community-188.8.131.52.msi) is just MySQL Installer, whereas the second (mysql-installer-community-184.108.40.206.msi) also includes MySQL Server. If you plan to install MySQL Server as well, it makes sense to choose the download that includes both MySQL Installer and MySQL Server as you avoid waiting for the installer to download the MySQL Server installation files later. This example assumes you choose the web installer.
If you already have an account, you can sign in. Otherwise, you can choose to sign up to an Oracle account. You can also choose to download the installer without logging in by clicking the No thanks, just start my download link.
If you do not have any MySQL programs installed, you are taken to a screen that asks you to confirm that you agree with the license terms. Please read the license terms carefully before proceeding. If you can accept the license, tick the I accept the license terms check box and click the button labelled Next ➤ to continue.
You can choose between several bundles such as the developer bundle (called Developer Default) which installs the products typically used in a development environment. When you choose a setup type, the description in the right of the screen includes a list of the products that will be installed. For this example, the custom installation type will be used.
You find MySQL Workbench in the available products list under Applications. Click the arrow pointing to the right to add MySQL Workbench to the list of products and features to be installed. Feel free to choose additional products; for this book, it is recommended to also include MySQL Shell. When you have added all the products you need, click Next ➤ to proceed.
The following screen provides a summary of the products that will be installed. Click Execute to start the installation. The installation process includes downloading the product if MySQL Installer does not already have a local copy. The installation may take a little while to complete. When it has completed, click Next ➤ to continue. The final screen lists the installed programs and gives you the option to launch MySQL Workbench and MySQL Shell. Click Finish to close MySQL Installer.
Add: Install products and features.
Modify: Change the installation of an existing product. This is mainly useful for MySQL Server.
Upgrade: Upgrade a product that is already installed.
Remove: Uninstall a product.
Catalog: Update MySQL Installer’s list of available MySQL products.
These five actions allow you to perform all steps required during the lifecycle of the MySQL products.
Enterprise Linux 7
If you are using Linux, you install MySQL Workbench using the package manager. On Oracle Linux, Red Hat Enterprise Linux, and CentOS 7, the preferred package manager is yum as it will help resolve dependencies of the packages that you install or upgrade. MySQL has a yum repository for its community products. This example will show how to install that and use it to install MySQL Workbench.
Installing the MySQL community repository
Installing MySQL Workbench on Enterprise Linux 7
Your output will likely look different, for example, depending on which packages you already have installed, dependencies may be pulled in. The first time you install a package from the MySQL repository, you will be asked to accept the GPG key used to validate the downloaded packages. If you installed the EPEL repository from Fedora, then you will also need to accept the GPG key from that repository.
Debian and Ubuntu
Installing MySQL Workbench on Debian and Ubuntu follows the same principles as in the previous example. For the steps demonstrated here, Ubuntu 19.10 will be used.
See https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/ for the full documentation on using the MySQL APT repository.
Installing the DEB package definition
The default is to enable MySQL Server and Cluster as well as the tools and connectors. For MySQL Server and Cluster, you can also choose which version you want to use with the default being 8. In order to install MySQL Shell, you need to ensure that MySQL Tools & Connectors is set to be enabled. Select Ok when you have made your changes.
Installing MySQL Workbench from the APT repository
The output is quite verbose and includes a list of changes to other packages that are required to install MySQL Workbench. The list of packages depends on what you have installed already.
You are now ready to start using MySQL Workbench.
The first time you launch MySQL Workbench, you will need to define the connection to the MySQL Server instance. If you have MySQL Notifier1 installed, MySQL Workbench will automatically create a connection for the root user to each instance monitored by MySQL Notifier.
The connections screen is accessed by clicking the icon at the upper left showing a database with a dolphin. The icon below with the tables connected by lines takes you to the database modeling feature, and the last of the three icons opens a tab for the data migration feature.
The screenshot shows the connections screen with the welcome message and with one connection already present. You can right-click the connection to access the options for the connection – these include opening the connection (creating a connection to the MySQL instance), editing the connection, adding it to a group, and more.
You can name the connection with a name of your choice. It is a free-form string that is just used to make it easier to identify the purpose of the connection. The rest of the options are the usual connection options.
Once you have your connection, you can double-click it from the connections screen to create a connection.
Using MySQL Workbench
The most used feature in MySQL Workbench is the ability to execute queries. This is done from the query tab which includes several features in addition to the ability to execute the queries. These features include showing the result set, obtaining a visual representation of the query plan called Visual Explain, getting context help, reformatting queries, and more. This section will look at some of the features starting with an overview.
The query tab consists of two areas with one being an editor where you write your queries and the other the query result. There are also support for showing context help and query statistics. These two additional areas are technically not part of the query tab, but since they are mostly used with the query tab, they will also be discussed here.
The area marked as ① is where you write your queries. You can keep several queries here, and MySQL Workbench will save them, so they are restored when you open the connection again. This makes it convenient as a scratch pad to store your most frequently used queries.
You execute the query or queries using one of the three lightning icons marked as ②. The left icon is a plain lightning symbol and executes the query or queries selected in the query editor part. This is the same as using the keyboard shortcut Ctrl+Shift+Enter. The middle icon with the lightning symbol and a cursor executes the query where the cursor is. Using this icon is the same as using shortcut Ctrl+Enter while in the editor. The third icon has a magnifier in front of the lightning symbol and creates the query plan in form for the query where the cursor is currently placed. The default way to display the query plan is as a Visual Explain diagram. You can also obtain the query plan by using the keyboard shortcut Ctrl+Alt+X.
The result is displayed below the query editor ③, and you can choose between several formats by using the items to the right of the query result. The last of these items is Execution Plan ④ which brings up the query plan for the query in the same way if you had requested it directly from the query editor.
Below the query tab is the output frame ⑤ which by default shows statistics for the last executed query. This includes when the query was executed, the query, the number of rows found, and how long it took to execute it. To the right there is a frame with SQL additions ⑥ which by default shows context help. You can enable automatic context help or request it manually using the icons above the help text.
There are several settings that can be changed for MySQL Workbench ranging from the colors to the behavior and paths to programs such as mysqldump that MySQL Workbench depends on.
The General Editors settings include settings such as the SQL mode to consider for the syntax checker and whether to use spaces or tabs for indentation. The SQL Editor settings include whether to use the safe settings, whether to save the editors, and the general behavior of the editor and query tab. The Administration settings specify the paths to use including for mysqldump if you do not want to use the bundled binary. The Modeling settings are for the database modeling feature. The Fonts & Colors settings allow you to change the visual appearance of MySQL Workbench. The SSH settings are used when you use a feature that requires an SSH connection to a remote host. Finally, the Others settings include a few settings that do not fit in the other categories such as whether the welcome message should be displayed on the start screen with the connections.
The settings include safe settings. What are those?
MySQL Workbench has two safe settings enabled by default to help prevent changing or deleting all rows in a table and to avoid fetching too many rows. The safety settings mean that UPDATE and DELETE statements are blocked if they do not have a WHERE clause, and SELECT statements have LIMIT 1000 added (the maximum number of rows can be configured). The WHERE clause for UPDATE and DELETE statements cannot be a trivial one.
Do not become complacent just because the safety settings are enabled. UPDATE and DELETE statements can still do a lot of damage with a WHERE clause, and a SELECT query with LIMIT 1000 can still require MySQL to examine many more rows.
Changing the limit this way updates the same setting as if you go through the preferences.
The UPDATE and DELETE safe setting can be changed in the SQL Editor settings furthest down. It is recommended to keep it on unless you really need to update or delete all rows in a table. Note that disabling the setting requires reconnecting.
One nice feature of MySQL Workbench that does not usually get a lot of attention is the query beautifier tool. This can also be useful for query tuning as a well-formatted query can make it easier to understand what the query is doing.
The first query is the original query with the whole query in a single line. The second query is the reformatted query. For a simple query like in this example, the beautification is of little value, but for a more complex query, it can make the query much easier to read.
The beautification by default includes changing SQL keywords to uppercase. You can change whether that should happen in the Query Editor submenu of the SQL Editor settings in the preferences.
The last feature that will be explored is the support for reverse engineering a schema and creating an enhanced entity-relationship (EER) diagram. This is a useful way to get an overview of the schema you are working with. If foreign keys have been defined, MySQL Workbench will use the definitions to link the tables together.
In this example, the world schema has been chosen. The next steps fetch the schema objects and allow you to filter which objects to include. Finally, the objects are imported and placed in the diagram, and a confirmation is shown. The resulting EER diagram is shown in Figure 11-21.
If MySQL Workbench crashes when creating the diagram, try opening Edit ➤ Configuration… ➤ Modelling in the menu and check the Force use of software based rendering for EER diagrams option.
The diagram shows the three tables in the world database . When you hover over a table, the relations to the other tables will be highlighted in green for a child table and in blue for a parent table. This allows you to quickly explore the relations between the tables to give you knowledge that can be crucial when you need to tune queries.
This chapter introduced MySQL Workbench which is MySQL’s graphical user interface solution. It was shown how to install MySQL Workbench and create connections. Then an overview of the main query view was given, and it was shown how you can configure MySQL Workbench. By default, you cannot execute UDPATE and DELETE statements without a real WHERE clause, and SELECT queries are limited to 1000 rows.
Two features that were discussed are query beautification and EER diagrams. These are not the only features, and later chapters will show examples of the performance reports and the Visual Explain query plan diagrams.
The next chapter will discuss MySQL Shell which is the second of the two “Swiss army knives” provided by MySQL.