The MySQL server supports a variety of client connection methods. To summarize: you have TCP/IP (v4 and v6) on all OSes (with or without TLS/SSL encryption), Unix Domain Sockets on Unix/Linux, and Named Pipes and/or Shared Memory on Windows.

Each of these connection methods has its own set of pros and cons: speed, security, portability, and ease-of-use. You want to carefully pick the right combination for your needs, and you may wish to turn off the ones you will not be using in order to potentially save resources and avoid surprises.

Let’s look at an overview of all the methods.

Before disabling any of these methods you’d need to know if clients are actually using them or not within your MySQL deployments. But how would you determine that?

Prior to MySQL 5.7 RC2 this was not trivial to answer. For your own connection this was not hard: “\s” is all that is needed in the mysql command line tool. But when you want to check other connections it was much harder, even if all you needed was to check the other currently active connections. Even then, netstat was your best bet, coupled with some status variables like e.g. Ssl_client_connects etc. Not to mention that this leaves out any connection history altogether …

Luckily the excellent work done by Todd Farmer and Marc Alff on WL#7729 made answering these questions very easy!

This work introduces:

• A new CONNECTION_TYPE column in PERFORMANCE_SCHEMA.threads table. This answers the question regarding what connection methods are being used by currently active sessions.
• A CONNECTION_TYPE attribute in the MySQL Enterprise Audit’s CONNECT AUDIT_RECORD XML element. This answers the question regarding what connection types have been used in the past.
• A mention of the connection type in the “connect” row of the General Query Log. This also answers the questions about what connection types have been previously used in cases where you don’t have the MySQL Enterprise Audit enabled.

So now the MySQL server provides all information needed for both the currently active connections and for historical connections. And you won’t have to rely on any external methods to collect it!

But let’s look at this in practice. To get some extra variety I’ve started mysqld.exe on my Windows box with the following extra options: --enable-named-pipe --shared-memory

Now I was able to connect with the command line client using different transports as follows:

• TCP/IP: mysql.exe --protocol=tcp -u root --ssl=off
• SSL over TCP/IP: mysql.exe --protocol=tcp -u root --ssl
• Shared Memory: mysql.exe --socket=mysql -u root
• Named Pipe: mysql.exe --protocol=pipe  -u root

As a result I get the following output in MySQL Workbench:

Furthermore I get entries like this in the general log (if enabled):

And finally, in the Enterprise Audit log I get entries like the following:

or

Conclusion

The visibility added in MySQL Server 5.7 into the connections used by clients will be useful to DBAs wanting to evaluate and monitor the connection methods used in their MySQL deployments and it demonstrates another real-life application of the enhanced Performance Schema capabilities in MySQL 5.7.

If you have any comments or questions about this new work, please let us know. As always, THANK YOU for using MySQL!