Check TLS is compiled into MySQLNever overlook the obvious! This is enabled by default in the packaged versions of MySQL in Debian and Ubuntu, but if you obtained MySQL elsewhere it’s worth checking.
Create/copy certificate into locationMySQL requires certificate keys in PKCS #1 format, whereas recent versions of OpenSSL default to PKCS #8 format. You’ll also need to ensure that MySQL has access to the certificate and key. If you have a certificate and key from Let’s Encrypt, here’s how to repurpose them for MySQL:
cp /etc/letsencrypt/live/example.com/fullchain.pem /etc/mysql/fullchain.pem openssl rsa -in /etc/letsencrypt/live/example.com/privkey.pem -out /etc/mysql/privkey.pem chmod 444 /etc/mysql/fullchain.pem chmod 400 /etc/mysql/privkey.pem chown mysql:mysql /etc/mysql/fullchain.pem /etc/mysql/privkey.pemNote that you’ll probably want to use a hook to automatically do this every time that the certificate is renewed if you’re using Let’s Encrypt. If your MySQL instance runs as a different user, modify the
Update MySQL configEdit
/etc/mysql/my.cnfand add the following lines to the
bind-address = 220.127.116.11 ssl-cert=/etc/mysql/fullchain.pem ssl-key=/etc/mysql/privkey.pemSubstitute
18.104.22.168with the IP address of your server.
systemctl restart mysql
Add firewall exception
ufw allow from 22.214.171.124 to 126.96.36.199 port 3306 proto tcpReplace
188.8.131.52as required. You can replace
anyto allow connections from anywhere, but I don’t recommend it.
Create user with REQUIRE SSL
CREATE USER 'user'@'%' IDENTIFIED BY 'password'; GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY 'password' REQUIRE SSL; GRANT ALL PRIVILEGES ON `database`.* TO 'user'@'%';
Make sure to replace
database with the correct values for your setup.
Alternatively, if you’re using phpMyAdmin, you can create the user as usual. Then go into the
mysql.users table, edit the user, and change
ANY. You may need to execute
FLUSH PRIVILEGES; afterwards.
TestYou’re now ready to test. Log in to the server from your remote IP address using a command like
mysql -u user -p -h 184.108.40.206. Assuming that the login works, execute the command
mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper Connection id: 3666 Current database: Current user: email@example.com SSL: Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.55-0+deb8u1 (Debian) Protocol version: 10 Connection: 220.127.116.11 via TCP/IP Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 TCP port: 3306 Uptime: 2 hours 15 min 16 sec Threads: 11 Questions: 308701 Slow queries: 0 Opens: 1758 Flush tables: 1 Open tables: 600 Queries per second avg: 38.036 --------------Note the
SSL: Cipher in use is DHE-RSA-AES256-SHAline. Test further with
SHOW VARIABLES LIKE '%ssl%';:
mysql> SHOW VARIABLES LIKE '%ssl%'; +---------------+--------------------------+ | Variable_name | Value | +---------------+--------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | | | ssl_capath | | | ssl_cert | /etc/mysql/fullchain.pem | | ssl_cipher | | | ssl_key | /etc/mysql/privkey.pem | +---------------+--------------------------+ 7 rows in set (0.03 sec)
have_ssl are both
YES. If the value is
NO, MySQL was compiled without TLS support. If it’s
DISABLED, TLS isn’t correctly set up.
If there are problems, check
/var/log/mysql/error.log. If you forgot to convert your private key to PKCS #1 format, you may well see lines like the following:
SSL error: Unable to get private key from '/etc/mysql/privkey.pem' 170707 12:39:23 [Warning] Failed to setup SSL 170707 12:39:23 [Warning] SSL error: Unable to get private keySimilarly, if you didn’t correctly set the permissions so that the
mysqluser can access the certificate, you may see something like this:
SSL error: Unable to get certificate from '/etc/mysql/fullchain.pem' 170707 12:14:56 [Warning] Failed to setup SSL 170707 12:14:56 [Warning] SSL error: Unable to get certificate