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.
As the following figure shows, the MySQL for Excel task pane displays connection actions by default when it opens.
Description of MySQL for Excel Connection Elements
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
. Click to return to the list of schemas.Right-click an existing connection in the list to delete, edit, or refresh it.
New Connection opens a connection dialog in which you can define and test a new connection. For instructions, see Adding MySQL Connections.
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.
You can use MySQL for Excel or MySQL Workbench to add new MySQL connections. Adding new connections 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.
For each connection, provide the connection details, click
to confirm the MySQL connection is valid, and click 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
andSSH Password
onlySSH User Name
andSSH Key File
onlyYour 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
andSSH Key File
(SSH Passphrase
) andSSH 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.
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
from the context menu.The MySQL connection edit dialog is similar to the edit dialog in MySQL Workbench. Configure the changes and click
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.