Table of Contents
Manage and create MySQL connections.
To add a connection, click the [+] icon to the
right of the MySQL Connections title on the
Home screen. This opens the
Setup New Connection form:
The button (bottom left) opens an optional configuration wizard for setting shell commands on the host. For example, commands to start/stop the MySQL instance, or to edit configuration file. For more information, see Section 5.3.6, “Configure Server Management Wizard”.
Fill out the connection details and optionally click to execute the Server Management wizard. Click to save the connection.
When opening connections, MySQL Workbench automatically sets the
client character set to utf8. Manually changing
the client character set, such as using SET NAMES
, may cause MySQL Workbench to
not correctly display the characters. For additional information
about client character sets, see
Connection Character Sets and Collations.
...
New MySQL connections are added to the Home screen as a tile, and the Section 8.2.1, “Object Browser and Editor Navigator” describes several MySQL Workbench features to monitor and configure each connected MySQL server. A single MySQL Workbench instance can open one or multiple MySQL connections into individual tabs.
For a more detailed overview of this process, see the tutorial titled Section 5.2, “Creating A New MySQL Connection (Tutorial)”.
Launch MySQL Workbench. You will be presented with the Home window.
Our example already has two connections created, but let us create a new connection. From the MySQL Workbench Home window, click the [+] icon near the MySQL Connections label. This opens the Setup New Connection wizard.
Define the Connection Name value, which we will set to "MyFirstConnection" in this example.
The default connection values are for a typical local setup, so check them and enter the appropriate values. If you are unsure, click the button to check the connection parameters. Do not press .
Next, optionally click , which opens up the Configure Local Management wizard:
Read the Configure Local Management introduction, and press to begin defining the new connection parameters.
The connection will now be tested. You should see that the connection was successful. If not, click and check that you have entered the information correctly.
Toggle the to view additional details about the tested connection, then click .
Optionally, you may configure a method for remote management if a Remote Host was specified. Setting these options enables MySQL Workbench to determine the location of configuration files, and the correct start and stop commands to use for the connection.
SSH login based management and Native Windows remote management types are available. The Operating System and MySQL Installation Type are configured for the SSH login variant.
We are creating a local MySQL connection in this tutorial, so are skipping the Management and OS and SSH Configuration options, as they are used for configuring a remote MySQL connection.
On Microsoft Windows, select the appropriate MySQL service for the MySQL connection.
The wizard will now check its ability to access the start and stop commands, and check access to the MySQL Server configuration file.
You now have a chance to review the configuration settings. The information displayed varies slightly depending on platform, connection method, and installation type.
At the Review Settings prompt, choose "I'd like to review the settings again" to review the settings. Choosing "Continue" closes the "Configure Server Management" dialog.
Check the Change Parameters if you want to check or edit information about the MySQL configuration file. In our example we will check it, and click to continue.
Review the MySQL configuration file information. Click the Check buttons to perform the described checks, or optionally change the configuration file path.
Optionally, enter your own commands for starting, stopping, and checking the MySQL connection. Typically the default values are used, which means leaving these optional values blank.
Click to close the "Configure Server Management" dialog, which reveals the original Setup New Connection window.
After reviewing the Setup New Connection information, press again to make sure it still functions, and then to create the new MySQL connection.
Your new MyFirstConnection MySQL connection is now listed on the Home window.
From the Home window, click the new MySQL connection to open the SQL editor for this connection. The SQL editor is the default page, so now select the Server Status from the left Navigator panel to display the connected MySQL server's current status.
Test the other Navigator panel options that relate to your new MySQL connection. Check its status, MySQL logs, and measure its performance statistics from the Dashboard.
Notice the Management and Schemas tabs on the bottom of the Navigator panel. The Schemas view displays the schemas that are associated with your new MySQL connection. Alternatively, you can merge the Schemas and Management tabs by either clicking the merge icon on the top right of the Navigator panel, or by enabling the Show Management Tools and Schema Tree in a single tab SQL Editor preference.
This concludes the "Creating a MySQL connection" tutorial. For additional information about MySQL connections, see Chapter 5, MySQL Connections.
The Manage Server Connections dialog is another way to manage MySQL connections. This dialog is invoked by either selecting Edit Connection or selecting , 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:
Connection Name: The name used to refer to this connection. This connection can then be selected from a list in other wizards requiring a connection.
Connection Method: Method used to connect to the RDBMS.
After you select a connection method, the fields available in the Parameters, SSL, and Advanced tabs change accordingly. More details about these options and parameters are available below.
The 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.
Simultaneous client connections: Opening a MySQL connection from the MySQL Workbench home page opens a new connection tab in MySQL Workbench for that connection. Each of these tabs requires two MySQL connections to perform basic tasks, such schema discovery and SQL execution. Additionally, performing management related tasks, such as Server Status, requires two additional MySQL connections. Essentially, this means that each MySQL connection tab in MySQL Workbench requires four available connections to MySQL. For additional information about "Too many connection" related errors, see Too many connections.
This connection requirement doubles with each connection tab opened in MySQL Workbench, even if the two connection tabs point to the same MySQL server. SQL editor tabs share their connections, so having multiple SQL editor and SQL results tabs does not affect the number of required connections.
This connection method enables MySQL Workbench to connect to MySQL Server using TCP/IP.
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.
NoteThe 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.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:
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
--compressoption. 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.
: 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.3.4, “SSL Wizard (Certificates)”.
: Opens a system's file browser that points to the generated SSL files by the SSL Wizard.
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.
Advanced
These are the same options discussed in Section 5.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.3.1, “Standard TCP/IP 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:
Advanced
The options here are the same as for the Standard TCP/IP connection. See Section 5.3.1, “Standard TCP/IP Connection Method”.
SSL
The options here are the same as for the Standard TCP/IP connection. See Section 5.3.1, “Standard TCP/IP Connection Method”.
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.
The OpenSSL binary should be in the system's PATH.
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.
The Remote Management tab is available when connecting to MySQL remotely.
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 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:
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:
Test DB Connection
Management and OS
SSH Configuration
Windows Management
Test Settings
Review Settings
MySQL Config File
Specify Commands
Test DB Connection
On this page, MySQL Workbench tests your database connection and displays the results. If an error occurs, click 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.
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.
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 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 and 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 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 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 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 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 to save the instance.
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.
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
datadirectory. This is whereconnections.xmland 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.apputility.Linux: The vault works by storing passwords using the
gnome-keyringdaemon, 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. Thegnome-keyringdaemon stores passwords for MySQL Workbench on non-GNOME platforms, but it must be started manually.
Browse, view status, and connect to any MySQL instance in a Fabric Cluster.
This requires Connector/Python and MySQL Fabric 1.5 and above installed, including the Python module. This feature also requires MySQL Workbench 6.3+.
Fabric 1.5 support was added in MySQL Workbench 6.3, and due to incompatible protocol changes, Fabric 1.4 support was dropped. Also, MySQL Workbench 6.2 added Fabric 1.4 support.
To set up a managed Fabric connection, create a new MySQL connection with the new connection method. The connection tiles have a different look:
Clicking the new fabric group tile shows the managed connections:
The client connection browser lists the active and sleeping MySQL client connections, and adds the ability to kill statements and connections, and view additional connection details and attributes.
The connection details viewer requires MySQL 5.6 or greater. Only basic connection information is available for previous versions of MySQL, such as the connection hosts, database, and state.
Client Connections and Metadata locks
The Client Connections management window includes a for connections to MySQL 5.6 and above. These details are separated into three tabs:
Details: connection details such as Process ID, Type, User, Host, Instrumented, and additional information.
Locks: MySQL uses metadata locking to manage access to objects such as tables and triggers. Sometimes a query might be blocked while being manipulated by another connection from another user. The Locks feature utilizes these MySQL metadata locks (MDL) to show the locked connections that are blocked or being waiting on, and shows information about the locks, what they are waiting for, and what they hold.
NoteThe metadata lock information is provided in the performance schema as of MySQL server 5.7.3.
Attributes: these are connection attributes such as OS, Client Name, Client Version, and Platform.
