6.3.1 MySQL Connections in Excel

MySQL for Excel provides several options to create and manage MySQL connections. You must open a connection to a MySQL server before you can configure global options, add a new schema, or perform operations that move data between Excel worksheets and MySQL tables. This section describes how to add or modify connections to MySQL.

• Adding New MySQL Connections

• Editing MySQL Connections

As the following figure shows, the MySQL for Excel task pane displays connection actions by default when it opens.

Figure 6.2 MySQL for Excel: MySQL Connections

Description of MySQL for Excel Connection Elements

1. Open a MySQL Connection lists the existing local and remote MySQL server connections. When you install MySQL for Excel, it creates a local connection automatically for each local MySQL server instance that you have configured on the system. After the initial installation, you can add new local or remote connections to the list.

   Double-click a connection from the list to open it. The MySQL server associated with the connection must be started on the local or remote host before you can open the connection. Select a schema to view and then click Next. Click Back to return to the list of schemas.

   Right-click an existing connection in the list to delete, edit, or refresh it.

2. New Connection opens a connection dialog in which you can define and test a new connection. For instructions, see Adding MySQL Connections.

3. Manage Connections launches MySQL Workbench if it is installed on the local host.

   MySQL for Excel shares its MySQL connections with MySQL Workbench, although it is optional to have MySQL Workbench installed. Creating and editing MySQL connections in either application will edit the MySQL connection information for both applications.

Adding New MySQL Connections

You can use MySQL for Excel or MySQL Workbench to add new MySQL connections. Adding a new connection from MySQL for Excel is not permitted when MySQL Workbench is open.

To add a new connection, click New Connection in the MySQL for Excel task pane to open the MySQL Server Connection dialog. Connection names must be unique. An alert icon (!) indicates that an option value is required. The figure that follows shows the connection dialog with the Parameters tab selected.

Figure 6.3 MySQL for Excel: Add a New MySQL Connection Dialog

For each connection, provide the connection details, click Test Connection to confirm the MySQL connection is valid, and click OK to save the new connection. The type of connection you create can vary depending on the configuration of the server, the client host computer, and the level of security you want. MySQL for Excel supports the following connection types:

Basic connections.  A basic connection is either unencrypted or encrypted (in MySQL 8.0, SSL is enabled by default) and the connection is made using standard TPC/IP, which is the default connection method in MySQL for Excel to connect to the MySQL RDBMS. Basic connections are easy to configure, particularly if MySQL for Excel and the MySQL server are on the same host computer or operate within the same local area network. To configure a basic connection, set the Connection Method option to TCP/IP (standard) and use the Parameters tab to configure the connection.

SSL connections.  Both the MySQL server and the client must be configured to enable SSL encryption (see Using Encrypted Connections). To configure this type of connection, set the Connection Method option to TCP/IP (standard) and use the Parameters tab to configure the basic connection. Next, select the SSL tab to identify the appropriate files. MySQL Server uses the PEM format for certificates and private keys. In addition to providing the paths to certificate files, you can specify the SSL mode to use for your connection. The following table describes each Use SSL option value and indicates which files are required.

Table 6.1 Use SSL Option Values

Option value	Description
No	Do not use SSL. No SSL files are required.
If Available	Use SSL if the server supports it, but allow connection in all cases. No SSL files are required; however, providing the SSL CA file is the best practice for connections made to MySQL 8.0 servers.
Require	Always use SSL and deny a connection if the server does not support SSL. Do not perform server certificate validation. No SSL files are required.
Require and Verify CA	Always use SSL. Validate the certificate authorities (CA), but tolerate a name mismatch. Requires the SSL CA file.
Require and Verify Identity	Always use SSL and fail if the host name is not correct. Requires valid SSL CA, SSL Cert, and SSL Key files.

SSH connections.  SSH tunnels permit you to connect to a MySQL database from behind a firewall when the MySQL server port is blocked. To configure this type of connection, set the Connection Method option to Standard TCP/IP over SSH and use the Parameters tab to configure the connection.

Additional considerations:

• The SSH server communicates with a MySQL server instance in an unencrypted or encrypted mode, based on the value selected for the SSL mode (see Table 6.1, “Use SSL Option Values”). Select the SSL tab to specify a value for the Use SSL option.

• The account you use to run MySQL for Excel must be authorized on the SSH server in advance. To authenticate your account (specified by SSH User Name), you can use one of the following strategies:

  • SSH User Name and SSH Password only

  • SSH User Name and SSH Key File only

    Your SSH server may require that you to also provide the SSH Passphrase property when using a key file. An invalid pass phrase generates an exception.

  • SSH User Name and SSH Key File (SSH Passphrase) and SSH Password

    The combination of user name + key file (passphrase) + password can perform fallback authentication when the key file and pass phrase are valid, but an error occurs on the server. Specifically, the first attempt to connect uses the key file, and if it fails, the next attempt to connect uses the password instead. If the SSH key file is null or empty, but the SSH password is provided, MySQL for Excel attempts to connect using the SSH password only.

Editing MySQL Connections

You can use MySQL for Excel or MySQL Workbench to edit existing MySQL connections.

• Editing MySQL Connections in MySQL for Excel

  To edit a MySQL connection, right-click the connection you want to modify and select Edit Connection from the context menu.

  The MySQL connection edit dialog is similar to the edit dialog in MySQL Workbench. Configure the changes and click OK to save your changes.

• Editing MySQL Connections in MySQL Workbench

  Alternatively, you can edit your MySQL for Excel MySQL connections using MySQL Workbench. To do this, open MySQL Workbench, edit a MySQL connection, and then refresh the connection list in MySQL for Excel.

  For information about editing MySQL connections in MySQL Workbench, see Connections in MySQL Workbench.