Prior to MySQL 5.7 client communications with a MySQL instance were unencrypted by default. This plaintext capability allowed for various tools including pt-query-digest to analyze TCP/IP traffic. Starting with MySQL 5.7 SSL is enabled by default and will be used in client communications if SSL is detected on the server.
We can detect and confirm this easily with the ngrep command.
Default client connection traffic (5.6)
On a MySQL 5.6 server we monitor the default MySQL port traffic.
mysql56$ sudo ngrep -d eth1 -wi -P ' ' -W single -l port 3306 interface: eth1 (192.168.42.0/255.255.255.0) filter: ( port 3306 ) and ((ip || ip6) || (vlan && (ip || ip6))) ...
We connect to this server using TCP/IP.
host$ mysql -uexternal -p -h192.168.42.16 mysql56> select 'unencrypted';
We can observe the communication to and from the server (in this example 192.168.42.16) is plaintext.
... # T 192.168.42.1:47634 -> 192.168.42.16:3306 [AP] select 'unencrypted' # T 192.168.42.16:3306 -> 192.168.42.1:47634 [AP] ! def unencrypted ! ! unencrypted #
SSL System Variables (5.6 default)
A default 5.6 installation does not have SSL enabled as verified by system variables.
mysql56 >SHOW VARIABLES LIKE '%ssl%'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | | +---------------+----------+ 9 rows in set (0.02 sec)
Default client connection traffic (5.7)
Running the same example client connection with MySQL 5.7 you will observe that communications to and from the server (in this example 192.168.42.17) are not in plaintext.
mysql57$ sudo ngrep -d eth1 -wi -P ' ' -W single -l port 3306 interface: eth1 (192.168.42.0/255.255.255.0) filter: ( port 3306 ) and ((ip || ip6) || (vlan && (ip || ip6))) host$ mysql -uexternal -p -h192.168.42.17 mysql57> select 'encrypted'; T 192.168.42.1:36781 -> 192.168.42.17:3306 [AP] @ F l d iVr H b ^ s t Z ( 2d " ? | ) # T 192.168.42.17:3306 -> 192.168.42.1:36781 [AP] p% s` 3u5!%P] v= r # x E a y '! )Z 8 Js z. \t (r H@ 0 2 5k\ < M @)E& b q|q@ h
SSL System Variables (5.7 default)
A new MySQL 5.7 installation will have SSL enabled by default as seen in the MySQL variables.
mysql57 > SHOW GLOBAL VARIABLES LIKE '%ssl%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | ca.pem | | ssl_capath | | | ssl_cert | server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | server-key.pem | +---------------+-----------------+ 9 rows in set (0.00 sec)
-no-ssl Client connection traffic (5.7)
If you want to emulate the unencrypted nature of MySQL 5.6 within any SSL enabled MySQL version (e.g. MySQL 5.7) you specify the --ssl option for mysql client connections. In MySQL 5.7 this option is also deprecated and --ssl-mode=disabled should be used>.
host$ > mysql -uexternal -p -h192.168.42.17 --ssl=0 host >select '-ssl=0 unencrypted'; T 192.168.42.1:36785 -> 192.168.42.17:3306 [AP] select '-ssl=0 unencrypted' # T 192.168.42.17:3306 -> 192.168.42.1:36785 [AP] ' def -ssl=0 unencrypted ! 3 -ssl=0 unencrypted
References
https://wiki.christophchamp.com/index.php?title=Ngrep
http://infoheap.com/ngrep-quick-start-guide/
Encrypted Connections - MySQL 5.7 Reference Manual
Implementing MySQL Security Features - Tutorial at Percona Live Europe 2017.
