At IU, how do I connect to the Webserve MySQL servers with MySQL Workbench?

On this page:


About MySQL Workbench

MySQL Workbench is a graphical environment for working with MySQL databases and servers. MySQL Workbench features enable 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, OS X, 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 via IUanyWare. Launch MySQL Workbench in IUanyWare from APPS > Categories > Databases; for help, see How do I set up and use IUanyWare?

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

Note:
For end-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.

Connecting to the MySQL for Webserve servers

To configure a connection to the IU MySQL for Webserve servers:

  1. In MySQL Workbench, to the right of "MySQL Connections", click  +  (the plus sign) to open the "Setup New Connection" screen:
  2. Use this screen to set up a new connection in MySQL Workbench (a103l)

  3. In the "Connection Name" field, enter a descriptive name of your choosing for the connection (e.g., MySQL-Test or MySQL-Prod).
  4. For "Connection Method", make sure Standard (TCP/IP) over SSH is selected.
  5. On the parameters tab:
    • In the "SSH Hostname" field, enter the server address for Webserve and the port number: webserve.iu.edu:22.
    • In the "SSH Username:" field, enter the webserve group account username for your account.
    • For "SSH Password":
      • To store your password in the application's vault, select Store in Vault, enter your MySQL account password, and then click 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 Webserve account); for example: music_root.
    • For "Password":
      • To store your password in the application's vault, select Store in Vault, enter your MySQL account password, and then click 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.
  6. To test your credentials, click Test Connection. If you did not store your password in the vault, you will be prompted to enter your MySQL password.
  7. When you are finished, click OK to close the "Setup New Connection" screen.

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

When your new connection is set up, it will appear under 'MySQL Connections'

Calling 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. For example, the screenshot below shows the result of executing CALL api.listprocedures():

Execute the stored procedure in the Query panel; view the results in Result Grid

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

Right-click the result cell, and then select 'Open Value in View' from the context menu

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, click Save):

Result cell data displayed in viewer; click 'Save' to save it as a text file

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.

This is document aopw in the Knowledge Base.
Last modified on 2017-08-08 14:06:20.

  • Fill out this form to submit your issue to the UITS Support Center.
  • Please note that you must be affiliated with Indiana University to receive support.
  • All fields are required.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.

  • Fill out this form to submit your comment to the IU Knowledge Base.
  • If you are affiliated with Indiana University and need help with a computing problem, please use the I need help with a computing problem section above, or contact your campus Support Center.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.