yumupgrades for production use, this is the repository for you.
Active subscription is required.
Note about MySQL versions
This post maybe applicable to other MySQL services. I just thought I’d state it clearly that this tutorial was tested with Percona MySQL 5.6.
Note that MySQL 5.7 series will generate self signed SSL certificates for you automatically at startup which may be sufficient for most users. But do check my notes about paid SSL certificates option below.
Times are, you’d need remote connectivity to your MySQL server instance. As per client of mine the requirement was due to:
… most of the analytic services I’ve worked with like Tableau/Google/Microstrategy work this way since it allows their services to scan and cache data effectively vs. hitting your DB directly on every query
While this sounded like a bummer to me (I mostly preferred to have MySQL listen on localhost and work with it using SSH tunnel, if I needed to use MySQL GUI tools), I realised that there are third party services that won’t do SSH tunnelling for connecting to your MySQL instance.
Allowing remote access to MySQL without encryption is a bad idea. So let’s setup some MySQL SSL, shall we?
SSL certificates for MySQL
As with any SSL (TLS, really), you need some SSL certificates in the first place. You have several options on where to get those.
1. Paid SSL Certificates
You may want to use regularly purchased SSL certificates.
The benefit for using those as compared to self signed ones, is clear certificate trust chain.
If you use certificates generated on the server, your Certification Authority would be self signed.
MySQL 5.7 would issue a warning during its startup:
[Warning] CA certificate ca-cert.pem is self signed
To avoid this, you may want to purchase SSL certificates.
You may be itchy to use LetsEncrypt certificates for MySQL SSL setup. However, LetsEncrypt certs are valid for 90 days and have to be renewed.
MySQL cannot reload SSL certificates dynamically without restart, so you’re risking some short downtime periods every 90 days just for pushing LetsEncrypt use in every area 🙂
3. Your own SSL
Using SSL certificates generated elsewhere is typically not required for MySQL SSL setup. Generating SSL certificates of your own is the way to go if strict security isn’t required.
Without wasting your time, I give you the script to quickly generate the necessary SSL certificates.
#!/bin/bash cd /var/lib/mysql # 1. generate your own CA openssl genrsa 2048 > ca-key.pem # 2. generate server key / cert openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem -subj "/CN=$(hostname) CA" > ca-cert.pem openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem -subj "/CN=$(hostname)" > server-req.pem openssl rsa -in server-key.pem -out server-key.pem openssl x509 -sha1 -req -in server-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem # 3. generate client key / cert openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout client-key.pem -subj "/CN=$(hostname)-client" > client-req.pem openssl rsa -in client-key.pem -out client-key.pem openssl x509 -sha1 -req -in client-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem chown mysql *.pem chmod 0600 *.pem
The script above generates:
- Certificate Authority (CA) certificate and private key file
- MySQL server SSL certificate (signed by that CA)
- MySQL client SSL certificate (signed by the same CA)
Save it as
mysql-setup-ssl.sh. You can run with e.g.
./mysql-setup-ssl.sh and it won’t require any input from you. All the certificates will be saved in MySQL data directory,
Now you should specify the names of your generated certificates in MySQL configuration and specify secure TLS versions. In
[mysqld] ... tls_version=TLSv1.1,TLSv1.2 ssl-ca=ca-cert.pem ssl-cert=server-cert.pem ssl-key=server-key.pem
You would also likely need to remove existing
bind-address directive from your configuration in order for MySQL to listen on all network interfaces.
Restart your service, or better, reload your MySQL configuration in a safe way.
Now you can connect to MySQL with
mysql and check SSL configuration status with:
SHOW VARIABLES LIKE '%ssl%';
+---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | ca-cert.pem | | ssl_capath | | | ssl_cert | server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | server-key.pem | +---------------+-----------------+
Configuring third party services to use SSL straightforward is easy (if they do support SSL). Simply download the
.pem files from
/var/lib/mysql and upload/use them in your remote MySQL client/service:
- Server certificate = ca-cert.pem
- Client certificate = client-cert.pem
- Client private key = client-key.pem
Next, you can create a separate MySQL user with remote connectivity:
mysql -e "CREATE USER 'username'@'%' IDENTIFIED BY 'secret';"
Now you can test SSL connectivity from your GUI client and if things work correctly, allow the connection only using the generated client certificate:
mysql -e "GRANT USAGE ON *.* TO 'username'@'%' REQUIRE SUBJECT '/CN=$(hostname)-client' AND ISSUER '/CN=$(hostname) CA';"
Confirm the change with:
mysql -e "SHOW GRANTS FOR 'username'@'%';"
At this point, the MySQL user can connect from any host. If the remote service in question has a known IP address, it is best to create a user entry with specific host definition:
mysql -e "CREATE USER 'username'@'18.104.22.168' IDENTIFIED BY 'secret';"
Needless to say, you also need to
GRANT actual permissions to specific database that you want this user to work with:
mysql -e "GRANT ALL PRIVILEGES ON example. * TO 'username'@'22.214.171.124';"
Now the user is limited to connect from specific IP, and only over SSL, only with generated client side SSL certificate.
What else can you wish for? 🙂