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 SSL Wizard (Certificates)
5.1.2.3.5 System Profile
5.1.2.3.6 Configure Server Management Wizard
5.1.2.3.7 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.7, “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

Advanced parameters are less common and include:

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

SSL parameters include:

  • Use 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.

  • SSL Wizard: Generate SSL certificates for both the MySQL server and MySQL client. Requires access to OpenSSL binaries in the system's PATH. For additional information, see Section 5.1.2.3.4, “SSL Wizard (Certificates)”.

  • Files: Opens a system's file browser that points to the generated SSL files by the SSL Wizard.

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.

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 SSL Wizard (Certificates)

This wizard helps create SSL certificates for both MySQL clients and MySQL servers. Connections in MySQL Workbench are updated with the certificates by the wizard. This wizard requires OpenSSL to create the certificates. An example my.cnf / my.ini file is also generated that utilizes the generated certificates.

Note

The OpenSSL binary should be in the system's PATH.

Figure 5.19 SSL Wizard: Welcome

SSL Wizard: Welcome

Figure 5.20 SSL Wizard: Options

SSL Wizard: Options

Figure 5.21 SSL Wizard: Results

SSL Wizard: Results

5.1.2.3.5 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 distributions such as 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

Choose the appropriate System Type and Installation Type to set default parameters 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. These default values are customizable.

Figure 5.22 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.23 Manage Server Connections: Remote Management Tab

Manage Server Connections: Remote Management Tab

5.1.2.3.6 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.24 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, click Show Logs to view the related logs.

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 setting the Windows configuration parameters is mandatory. 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

The wizard now attempts a connection to your server and reports the results. If an error occurs, click Show Logs to view the related logs.

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

Optionally set the commands required to start, stop, and check the status of the running MySQL server instance. Commands can be customized, if required, but the defaults are suitable in most cases. The defaults depend on the selected options on the Operating System page of the wizard. Click Next to continue.

Complete Setup

Name the MySQL server instance on the final step. This name is used throughout MySQL Workbench as a reference to this MySQL connection. After setting a suitable name, click Finish to save the instance.

5.1.2.3.7 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 is also viewable from the native 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 stores passwords for MySQL Workbench on non-GNOME platforms, but it must be started manually.