5.1.2.3 Manage Server Connections

5.1.2.3.1 Standard TCP/IP Connection Method
5.1.2.3.2 Local Socket/Pipe Connection Method
5.1.2.3.3 Standard TCP/IP over SSH Connection Method
5.1.2.3.4 System Profile
5.1.2.3.5 Configure Server Management Wizard
5.1.2.3.6 The Password Storage Vault

The Manage Server Connections dialog is another way to manage MySQL connections. This dialog is invoked by either selecting Edit Connection or selecting Database, Manage Connections from the main menu. It can also be invoked from any of the wizards requiring access to a live database.

After the MySQL connection manager is launched, you are presented with the following dialog, with the Connection tab open:

Figure 5.15 Manage Server Connections: Connection Tab

Manage Server Connections: Connection Tab

Note

The Test Connection button will test the selected MySQL connection and report its connection status. It also reports whether or not SSL is enabled.

For testing remote connections, you might also use ping to check the hostname, or telnet to also check the port. If these fail, then also check the firewall settings on each host, and also that MySQL server is running on the remote host.

5.1.2.3.1 Standard TCP/IP Connection Method

This connection method enables MySQL Workbench to connect to MySQL Server using TCP/IP.

Note

The --skip-networking MySQL server configuration option affects the TCP/IP connection method. If disabled, use named pipes or shared memory (on Windows) or Unix socket files (on Unix).

Parameters tab

  • Hostname: The host name or IP address of the MySQL server.

    Note

    The host name "localhost" might resolve to "127.0.0.1" or "::1" on your host, so note this when checking permissions. For example, if a web application's user only has access to "127.0.0.1" on a host, and a defined connection uses "localhost" that resolves to "::1", this connection may lack the proper permissions to the aforementioned web application. Ping "localhost" on each host to determine where it resolves to.

  • Port: The TCP/IP port on which the MySQL server is listening (the default is 3306).

  • Username: User name to use for the connection.

  • Password: Optional password for the account used. If you enter no password here, you will be prompted to enter the password when MySQL Workbench attempts to establish the connection. MySQL Workbench can store the password in a vault (see Section 5.1.2.3.6, “The Password Storage Vault”).

  • Default Schema: When the connection to the server is established, this is the schema that will be used by default. It becomes the default schema for use in other parts of MySQL Workbench.

Advanced tab

More parameters can be set for the connection by using the Advanced tab.

Figure 5.16 Standard (TCP/IP) Connection: Advanced Tab

Standard (TCP/IP) Connection: Advanced Tab

The Advanced tab includes these check boxes:

  • Use compression protocol: If checked, the communication between the application and the MySQL server will be compressed, which may increase transfer rates. This corresponds to starting a MySQL command-line client with the --compress option. This option is unchecked by default.

  • Use ANSI quotes to quote identifiers: Treat " as an identifier quote character (like the ` quote character) and not as a string quote character. You can still use ` to quote identifiers with this mode enabled. With this option enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier. Note: If this option is checked, it overrides the server setting. This option is unchecked by default.

  • Enable Cleartext Authentication Plugin: Send the user password in cleartext. Required for some authentication methods. This option is unchecked by default.

  • Use the old authentication protocol: This option disables Connector/C++'s secure_auth option. This option unchecked by default.

It also includes these options:

SQL_MODE: Override the default SQL_MODE used by the server.

Others: Other options for Connector/C++ as option=value pairs, one per line.

SSL tab

More parameters can be set for the connection by using the SSL tab.

  • SSL: This dropdown provides options related to enabling SSL encryption. Choose No to disable SSL, If available if the client library supports it, or Require to require SSL support for the MySQL connection to succeed. This option defaults to If available.

  • SSL CA File: Path to the Certification Authority file for SSL.

  • SSL CERT File: Path the Certificate file for SSL.

  • SSL Key File: Path to the Key file for SSL.

  • SSL Cipher: Optional list of permissible ciphers to use for SSL encryption.

5.1.2.3.2 Local Socket/Pipe Connection Method

This connection method enables MySQL Workbench to connect to MySQL Server using a socket file (on Unix) or a named pipe (on Windows).

Parameters

The unique field here is Socket/Pipe Path. Enter the name of the socket or pipe here. If the field is left blank, the default socket or pipe name is used. On Unix, the default socket name is /tmp/mysql.sock. On Microsoft Windows, the default pipe name is MySQL.

This option can be seen in the following screenshot.

Figure 5.17 Manage DB Connections - Socket/Pipe Parameters

Manage DB Connections - Socket/Pipe Parameters

Advanced

These are the same options discussed in Section 5.1.2.3.1, “Standard TCP/IP Connection Method”, except there is not the Use compression protocol option.

SSL

These are the same options discussed in Section 5.1.2.3.1, “Standard TCP/IP Connection Method”.

5.1.2.3.3 Standard TCP/IP over SSH Connection Method

This connection method enables MySQL Workbench to connect to MySQL Server using TCP/IP over an SSH connection.

Parameters

In addition to a number of parameters that are in common with Standard TCP/IP connections, this connection method features a number of specialized parameters. These are listed here:

  • SSH Hostname: This is the name of the SSH server. An optional port number can also be provided. For example, localhost:22.

  • SSH Username: This is the name of the SSH user name to connect with.

  • SSH Password: The SSH password. It is recommended that an SSH key file is also used.

  • SSH Key File: A path to the SSH key file.

If a remote host is missing from the system's list of known hosts, a prompt requires you to confirm the host's fingerprint before storing it. If your stored host fingerprint is different than the host's current fingerprint, then an error is generated and you will be required to handle the discrepancy from outside of MySQL Workbench before creating the connection. Prior to MySQL Workbench 6.1.6, the host SSH fingerprint was not saved by MySQL Workbench.

On Linux and OS X, SSH host fingerprints are stored in ~/.ssh/known_hosts. On Microsoft Windows, they are stored in a file created by MySQL Workbench under the user's application data folder (%appdata%), such as C:\Users\[username]\AppData\Roaming\MySQL\Workbench\known_hosts.

The SSH connection options are viewable in the following screenshot:

Figure 5.18 Manage DB Connections - SSH Parameters

Manage DB Connections - SSH Parameters

Advanced

The options here are the same as for the Standard TCP/IP connection. See Section 5.1.2.3.1, “Standard TCP/IP Connection Method”.

SSL

The options here are the same as for the Standard TCP/IP connection. See Section 5.1.2.3.1, “Standard TCP/IP Connection Method”.

5.1.2.3.4 System Profile

The System Profile tab enables you to specify host-specific information. This is achieved primarily through selecting a System Type, along with its corresponding Installation Type. These profile settings contain standard information that is used in managing the host's MySQL instance.

Here are some of the available installation types:

  • FreeBSD, MySQL package or Custom

  • Linux, including several distributions including Fedora, Oracle, RHEL, SLES, Ubuntu, Generic, and Custom

  • OS X, MySQL package or Custom

  • OpenSolaris, MySQL package or Custom

  • Windows, with different installation methods, MySQL versions, and build architectures

After you select the System Type and Installation Type, a number of default parameters will be set that includes: commands used to start and stop MySQL, commands to check the server status, the location of the my.ini or my.cnf configuration file, and on Windows, the Windows Service Name. You may also customize these preset default values.

Figure 5.19 Manage Server Connections: System Profile Tab

Manage Server Connections: System Profile Tab

The Remote Management tab is available when connecting to MySQL remotely.

Figure 5.20 Manage Server Connections: Remote Management Tab

Manage Server Connections: Remote Management Tab

5.1.2.3.5 Configure Server Management Wizard

Clicking the [+] icon from the Home page launches the Setup New Connection wizard. The wizard provides a MySQL connection form to create a new MySQL connection, and includes a Configure Server Management option as a step-by-step approach to creating a new MySQL server connection. This can also be executed later (on remote connections) when from the home page by clicking the top right corner of a MySQL connection tile:

Figure 5.21 Configure Remote Management

Configure Remote Management

Executing this wizard is required to perform tasks requiring shell access to the host. For example, starting/stopping the MySQL instance and editing the configuration file.

The steps presented in the wizard are as follows:

  1. Test DB Connection

  2. Management and OS

  3. SSH Configuration

  4. Windows Management

  5. Test Settings

  6. Review Settings

  7. MySQL Config File

  8. Specify Commands

Test DB Connection

On this page, MySQL Workbench tests your database connection and displays the results. If an error occurs, you are directed to view the logs, which can be done by clicking the Show Logs button.

Management and OS

Used to specify a remote management type and target operating system, which is available when the Host Machine is defined as a remote host.

The SSH login based management option includes configuration entries for the Operating System and MySQL Installation Type.

SSH Configuration

If you specified a Remote Host on the Specify Host Machine page, you will be presented with the Host SSH Connection page, that enables you to use SSH for the connection to the server instance. This facility enables you to create a secure connection to remotely administer and configure the server instance. You must enter the host name and user name of the account that will be used to log in to the server for administration and configuration activities. If you do not enter the optional SSH Key for use with the server, then you will be prompted for the password when the connection is established by MySQL Workbench.

Note

This connection is to enable remote administration and configuration of the MySQL Server itself. It is not the same as the connection used to connect to a server for general database manipulation.

Note

You must use an SSH connection type when managing a remote server if you wish to start or stop the server or edit its configuration file. Other administrative functions do not require an SSH connection.

Windows Management

If a Windows server is used, then the Windows configuration parameters must be set. Windows management requires a user account with the required privileges to query the system status, and to control services. And read/write access to the configuration file is needed to allow editing of the file.

Test Settings

On the next page your settings are tested and the wizard reports back the results after attempting to connect to the server. If an error occurs, you are directed to view the logs, which can be done by clicking the Show Logs button.

MySQL Workbench must know where the MySQL Server configuration file is located to be able to display configuration information. The wizard is able to determine the most likely location of the configuration file, based on the selection made on the Operating System page of the wizard. However, it is possible to test that this information is correct by clicking the Check path and Check section buttons. The wizard then reports whether the configuration file and server configuration section can in fact be accessed. It is also possible to manually enter the location of the configuration file, and the section pertaining to MySQL Server data; these manually entered values should be tested using the buttons provided. Click the Next button to continue.

Review Settings

The modified settings may be reviewed, which also includes the default values. Check the Change Parameters checkbox if the MySQL Config File section will be edited, and then click Next to continue.

MySQL Config File

Allows configuration of the MySQL server version. It also allows the editing and validation of the configuration file path, and validation of the server instance section. Click Next to continue.

Specify Commands

This page enables you to set the commands required to start, stop, and check the status of the running server instance. It is possible to customize the commands if required, but the defaults should be suitable in most cases. The defaults are set based on the options selected in the Operating System page of the wizard. Click Next to continue.

Complete Setup

On this page, you finally assign a name to the server instance. This name is used in various parts of the GUI to enable you to refer to this instance. After setting a suitable name, click Finish to save the instance.

5.1.2.3.6 The Password Storage Vault

The vault provides a convenient secure storage for passwords used to access MySQL servers. By using the vault, you need not enter credentials every time MySQL Workbench attempts to connect to a server.

Note

The hostname is used for storing password information. For example, a local connection might use "localhost", "127.0.0.1", or "::1", but these are stored separately in the password storage vault, even if they all resolve to the same place.

The vault is implemented differently on each platform:

  • Windows: The vault is an encrypted file in the MySQL Workbench data directory. This is where connections.xml and related files are located. The file is encrypted using a Windows API which performs the encryption based on the current user, so only the current user can decrypt it. As a result it is not possible to decrypt the file on any other computer. It is possible to delete the file, in which case all stored passwords are lost, but MySQL Workbench will otherwise perform as expected. You then must re-enter passwords as required.

  • OS X: The vault is implemented using the OS X Secure Keychain. The keychain contents can be viewed using the Keychain Access.app utility.

  • Linux: The vault works by storing passwords using the gnome-keyring daemon, which must be running for password persistence to work. The daemon is automatically started in GNOME desktops, but normally is not in KDE and others. The gnome-keyring daemon can be used for password storage in MySQL Workbench on non-GNOME platforms, but must be started manually.