This is purely for testing purposes, so there was no MySQL performance tuning done for the VM; the only relevant tuning was existing tuning, just making sure that the Linux kernel did not try and treat the virtual disk, provided from a backing store as a file in the outer filesystem, as a spinning disk but instead as just a dumb backend (even more appropriate since the laptop has an SSD):
# /etc/rc.local
echo 0 > /sys/block/sda/queue/rotational
echo deadline > /sys/block/sda/queue/schedulerGetting MySQL installed on Ubuntu is easy, thanks to the Debian heritage:
$ sudo apt-get install mysql-server mysql-client
[ supply a MySQL root password when prompted ]Getting MySQL using SSL was far from easy. I assume for this post that the reader can create an RSA private key and an X.509v3 certificate and install them to be accessible to the MySQL runtime user. The problem is the pathetically dire diagnostics from MySQL, the lack of clarity over OpenSSL vs yaSSL as an SSL provider (and the impact on the ssl-cipher control statement) and the settings which used to work, are documented to work, but which cause SSL to not work anymore when set. I'd last set up MySQL with SSL back in the 2005/2006 timeframe, on FreeBSD, and still had my install notes, so am confident in my assertion of what used to work.
Ubuntu uses “/etc/mysql/my.cnf” for global MySQL configuration.
First gotcha: the need to remember that AppArmor is filtering access to files by MySQL. So to install a certificate, there's an apparently undocumented requirement to install the key and cert in a particular location with particular names: they must match /etc/mysql/*.pem. Sod that. If you have TLS set up for various services, with keys and certs sensibly managed instead of scattered about, and want to actually enable certificate verification, then your first step is to edit “/etc/apparmor.d/usr.sbin.mysqld” to grant read-access as appropriate. Note: most configuration options for MySQL's SSL support do not result in meaningful error messages when access to a file is blocked. It took setting ssl-ca to get a hint appear in “/var/log/mysql/error.log”.
Useful points to bear in mind: use \s in the mysql client to check connection status and use:
mysql> show variables like '%ssl%';to get variables and settings. Note that a value of DISABLED means that the server has been built with support for SSL but the run-time configuration has not yet been sufficiently blindly poked with goat-blood infused wands.
Put this into “/etc/mysql/my.cnf”:
[client]
ssl
ssl-ca = /path/to/certauthority.pem
#tempdisable# ssl-verify-server-cert
[mysqld]
ssl-ca=/path/to/certauthority.pem
ssl-cert=/path/to/services/mysql/server.crt
ssl-key=/path/to/services/mysql/server.key
ssl-cipher=DHE-RSA-AES256-SHAIn particular: use of [mysqld]ssl-capath is not only insufficient, its use appears to break SSL setup. Use ssl-ca instead. You must set [mysqld]ssl-cipher these days, there's no reasonable default. The accepted values depend upon the backend and, even when OpenSSL is in use, appears to not accept cipher specs per the ciphers(1) manual page, as accepted by every other user of OpenSSL. Instead, you have to manually provide the specific ciphers allowed. I repeatedly attempted to use something as simple as ALL or DEFAULT in disbelief that these could have been disabled. Failures here will result in an error message of “SSL error: SSL_CTX_new failed” and no further information.
Note that by default, MySQL does not verify the server certificate identity, as hinted at by the “[client]” section above. Alas, there is also the problem that the client code appears to ignore the X.509v3 subjectAltNames extension, so the only identity from the certificate used for checking is that of the CN field in the certificate Subject.
Further, the default rules only allow the MySQL root user to connect from localhost, even after granting access to other IPs. So enabling certificate validation means that you need a certificate with CN=localhost in the Subject.
So first leave [client]ssl-verify-server-cert commented out. Then run this, where you should replace foobar.local with the hostname corresponding to the public IP of the machine; this allows you to connect from the local machine to the local machine, via the machine's hostname.
$ mysql -u root -p
[ supply the root password you used at install time ]
mysql> GRANT USAGE ON *.* TO ''@'foobar.local';
mysql> FLUSH PRIVILEGES;After this, you can uncomment the ssl-verify-server-cert in the client section, provided that you remember that this breaks localhost access and root can still only connect from localhost, so you'll have to comment it out for major administrative tasks.
If you enable validation, a connection to localhost will yield:
ERROR 2026 (HY000): SSL connection error: SSL certificate validation failure
So use “mysql -h foobar.local” thereafter.
After all this, I can't believe that folks still perpetuate the myth that PostgreSQL is harder to set up than MySQL. PostgreSQL actually documents the settings accurately (instead of telling you about directives that apparently have broken configuration for several releases now but none of the maintainers appear to care, ssl-capath), provides reasonable diagnostics and lets you get up and running with TLS enabled connections in a matter of minutes, the only potential issue being how you configure pg_hba.conf. I've lost interest in making sure that MySQL can use multiple ciphers for compatibility with various clients, as is default behaviour in every other application.
You should now be able to do something like:
mysql> GRANT ALL PRIVILEGES ON foo.*
TO 'fooadmin'@'adminhost.tld'
IDENTIFIED BY 'raw_password_here'
REQUIRE SSL;-A grumpy troll
