Use MySQL Workbench to connect to the Sitehost MySQL servers

On this page:

Overview
Connect to the MySQL for Sitehost servers
Call stored procedures in the SQL Editor

Overview

MySQL Workbench is a graphical environment for working with MySQL databases and servers. MySQL Workbench enables you to create and manage connections to database servers, and execute SQL queries on those database connections using the built-in SQL Editor.

Developed and distributed by Oracle Corporation, MySQL Workbench is available in several commercial editions for use at the enterprise level. Oracle also distributes an open source Community Edition, which is available free of charge (for Windows, macOS, and Linux) from Oracle's Download MySQL Workbench page.

Indiana University students, faculty, and staff can use a virtualized version of MySQL Workbench on their personal workstations and mobile devices through IUanyWare. Launch MySQL Workbench in IUanyWare from APPS > Categories > Databases; for help, see Set up and use IUanyWare.

For complete documentation, see the official MySQL Workbench Manual, including:

Chapter 1 General Information: Learn more about MySQL Workbench functionality.
Chapter 2 Installation: View system requirements and instructions for installing MySQL Workbench Community Edition on Windows, macOS, and Linux workstations.

Note:

MySQL Workbench for Windows (8.0.19) requires the Visual C++ Redistributable for Visual Studio 2019. The MySQL Workbench installer does not include this prerequisite package, but you can download it from Microsoft's Visual Studio Downloads page.
Chapter 3 Configuration View details about configuring settings and preferences.

Note:

For users at IU, particularly account managers and owners, UITS strongly recommends reviewing the following sections:

Chapter 8.1 Visual SQL Editor: Learn how to use the visual SQL Editor to build, edit, and run queries, create and edit data, and view and export results.
Chapter 8.2 Object Management: Learn how to use the Object Browser to navigate database schemas and objects, select tables and fields to query, edit tables, create or drop tables and databases, and perform searches.

Connect to the MySQL for Sitehost servers

To configure a connection to the MySQL for Sitehost servers:

Note:

If you're connecting to the MySQL environment through Sitehost, you will first need to connect to the IU Groups VPN.

If prompted to select a role, choose the IU-Linux-Hosting-Users option.

Make sure your MySQL Workbench is updated to the latest version. The MySQL for Sitehost server requires TLS 1.2 if the connection is secured with SSL, which is not supported by older versions of the MySQL Workbench.

The IUAnyWare version of the MySQL Workbench will need to have SSL turned off for connections. To do this, go to the SSL tab of the connection settings, find the Use SSL option, and select No.

In MySQL Workbench, to the right of "MySQL Connections", select + (the plus sign) to open the "Setup New Connection" screen.
In the "Connection Name" field, enter a descriptive name of your choosing for the connection (for example, MySQL-Test or MySQL-Prod).
For "Connection Method", make sure Standard (TCP/IP) over SSH is selected.
On the parameters tab:
- In the "SSH Hostname" field, enter the server address for Sitehost and the port number: ssh.sitehost.iu.edu:22.
- In the "SSH Username:" field, enter your personal username.
- For "SSH Password":
  - To store your password in the application's vault, choose Store in Vault, and select OK.
  - Recommended: To be prompted for your password every time you log in, skip this step.
- In the "MySQL Hostname" field, enter the fully qualified domain name (FQDN) for the test or production server:
  - Test: mysql-test.uits.iu.edu
  - Production: mysql.uits.iu.edu
- Make sure "MySQL Server Port" is set to 3306.
- In the "Username" field, enter the MySQL username issued to you (it should be the name of your Sitehost account); for example: music_root.
- For "Password":
  - To store your password in the application's vault, choose Store in Vault, enter your MySQL account password, and select OK.
  - Recommended: To be prompted for your password every time you log in, skip this step.
- In the "Default Schema" field, enter a default schema if you have one, or leave the field blank.
To test your credentials, select Test Connection. If you did not store your password in the vault, you will be prompted to enter your MySQL password.
When you are finished, select OK to close the "Setup New Connection" screen.

When your connection is set up, it will appear under your list of connections.

Call stored procedures in the SQL Editor

IU's single-instance MySQL environment relies on stored procedures to automate many common MySQL tasks and ensure compliance with naming conventions.

In MySQL Workbench, you use the SQL Editor to execute stored procedures by entering CALL statements in the SQL Query panel. The CALL statement uses the following syntax (replace procedureName with the name of the stored procedure you are invoking and include the required parameters inside the parentheses):

CALL api.procedureName(parameter01, parameter02, ...)

The results from executed statements appear in the Result Grid below the SQL Query panel. When a result produces several rows of information, it is displayed and accessible all at once.

In some cases, a statement's result may contain too much data to display inside one Result Grid cell. To reveal the full data, select the cell, and open the Result Grid field context menu. Select Open Value in Viewer.

With the viewer set to Text (not Binary), you can view the results, select and copy the text as needed, or save the result as a text file (at the bottom, select Save).

Note:

System messages, warnings, and some errors also are displayed in the Result Grid. If the content of a message is too large, you can use the Open Value in Viewer option described above to view it.