TLS for MySQL at large scale

1 .TLS for MySQL at Large Scale Jaime Crespo 

2 .

3 . Things we ● Security and encryption fundamentals are *NOT* ● “At rest” encryption ● Best practices for web/HTTP going to encryption ● How perfectly and good we are- we made mistakes and we will present talk about: them to you 

4 .● ● “On the wire” encryption Focused on for large scale web Things we *ARE* going applications ● Operational/DBA point of view to talk ● Feature requests for MySQL/MariaDB developers ● Failures that can serve as lessons learned for other ops about: 

5 . Why ● Privacy and security over cost- we aim for full stack encryption deploying ● Known, documented security threads TLS for ● Compliance with modern security standards; getting modern MySQL? authentication methods 

6 .● TLS is slow ● TLS doesn’t work at scale TLS Myths ● TLS is not needed on a private network/for databases ● TLS is hard - it is not, it is mostly an operational challenge 

7 .TLS on MySQL is easy * Latest MySQL versions even do this for you automatically 

8 .Thank you! Author: Jaime Crespo/Wikimedia Foundation License: CC-BY-SA-3.0 (except where noted) 

9 .TLS for MySQL at Large Scale Jaime Crespo 

10 . A clear plan Make TLS Make TLS Make TLS compulsory compulsory compulsory for inbound for cross-dc for intra-dc requests internal connections from Internet connections 

11 .”The greatest failure, teacher is” -- Yoda. Star Wars: The Last Jedi Author: GPS https://www.flickr.com/photos/zoxcleb/8732125673/ License: CC-BY-SA-2.5 

12 . ● We were going to activate a second datacenter for the first time - people on top wanted encryption rolled in ASAP We rushed ● We setup some initial configuration with some test certificates to ● We ended up working 3 times as much: first when we set them up, again to production ● remove it and setup it again Resources were limited: 1 full time employee (which were already in charge of all MySQL maintenance and firefighting); no external resources 

13 .● TLS at internal storage treated like We didn’t rolling public HTTPS - different use case and problems ● We didn’t have a proper certificate ● manager service Older OpenSSL version had frequent have ● security problems Every time OpenSSL or MySQL had to proper be upgraded, we had to restart the daemon orchestrati on in place ● If the change was incompatible (e.g. CA update), you had to sync client/server and master/replicas 

14 . ● MySQL/MariaDB older version (5.5) had Server problems with modern ciphers/protocols support ● Only OpenSSL-linked servers had proper modern TLS support (>=1.2) was poor ● OpenSSL was not GPL-compatible ● We had to deploy our own package (wmf-mariadb, wmf-mysql) 

15 .● Client libraries also had to be Client and upgraded/linked to OpenSSL ● Some problems with clients (Mono/Sharp) silently enabling TLS for ● “MySQL as a service” products Most issues related to TLSv1.2 support 3rd party ● Old client connectors (PHP5) incompatibilities support ● ● ProxySQL 1.X did not support TLSv1.2 Colleagues report mysql cli “no longer was poor works” 

16 . ● We rolled TLS at first opt-in- This allowed easy rollback. We defaulted to TLS enabled, Successes though. ● Communicated the change to fellow ops ● Organization support and ● We went for replication channel and administration encryption first- things we indetectable overhead due to almost no reconnections did right ● We went for TLSv1.2 from the beginning (2015) ● 100% coverage is not rushed- we can wait for CA, licensing and client library support 

17 .● ProxySQL to support TLS 1.2 Things got better ● Many other 3rd party tools following that ● TLS by default on MySQL 8.0 since we ○ Also improved authentication by default ● New TLS features that will improve started performance 

18 . ● Easier certificate/TLS library handling from the servers (#81461, #75404, #83758) MySQL ● Proper TLS 1.2, 1.3 support from connectors/clients/middleware (e.g. community ● ProxySQL #1247) Proper OpenSSL 1.1+ support (#83814, wishlist ● #12811) Sharing more tests/metrics/ performance benchmarks 

19 .● Same-DC, non-SSL: ○ 0.001132071018219 s/conn ○ 0.00024072647094727 s/query ● Same-DC, SSL: ○ 0.057012629508972 s/conn ○ 0.00025907039642334 s/query Metrics ● Cross-DC, non-SSL: ○ 0.1113884806633 s/conn ○ 0.036313643455505 s/query ● Cross-DC, SSL: ○ 0.22943157196045 s/conn ○ 0.036422135829926 sec/query ● Local ProxySQL+Cross-DC, non-SSL: ○ 0.0002328896522522 s/conn ○ 0.036425504684448 s/query 

20 . ● Substitute temporary IP-level tunnelization for native Pending application-supported TLS ● Enable TLS also for regular connections work for us ● Better monitoring (certificate expiration) ● Ban non-TLS connections at grant level ● Roll in modern authentication (sha256) 

21 .Thank you! Author: Jaime Crespo/Wikimedia Foundation License: CC-BY-SA-3.0 (except where noted)