About Us Blog
Submit a request
How can we help?
  1. Engine Yard Support
  2. Engine Yard Cloud
  3. Manage your Database

MySQL SSL Support

Avatar AI Integration User
Follow

MySQL servers operating on Stable-v4 2.0.123, Stable-v5 3.0.30, or any versions of Stable-v6 and Stable-v7  stacks can support SSL based connections to the database. This document describes how to verify the server is SSL ready, as well as the steps to prepare, use, and enforce this feature. Topics include:

Caveats

Pre-existing MySQL 5.7

MySQL 5.7 hosts that pre-date this feature will have generated their own set of keys by default. When the stack is updated these pre-existing keys will conflict with the new keys requiring a restart of the database. To upgrade:

  • click the "Upgrade" button on the environment dashboard
  • when the run fails restart MySQL with the command /etc/init.d/mysql restart, in this case with the master before any attached replicas
  • when the restart has been completed on all your database instances, click the "Apply" button on the environment dashboard to restart the chef run.

Older Versions of 5.5 and 5.6

MySQL 5.5 versions 5.5.40 and below and 5.6 versions 5.6.22 and below are built with an older version of SSL and do not properly support the SSL feature; Stable-v4-2.0.124 disables the SSL feature for these versions. If you are currently using one of these versions we recommend performing a minor version upgrade to the latest supported MySQL version in that series.

Overview

SSL connections are designed to encrypt client/server traffic to and from your database for increased security. Your database components exist with your application within a virtual network, and the database communication ports by default are not externally accessible. The SSL feature is useful for customers that have specific compliance concerns for network traffic, or for cases where a security exception has been used to expose the database port in the AWS firewall.

When you upgrade to a stack that includes this feature the cookbooks run performed during the upgrade will generate self-signed SSL keys on the database master and distribute these keys automatically to the other databases within the environment. The database master will then generate a key for our administrative users as well as for your main application user; the admin keys will be distributed to all database instances in the environment, and the application keys will be distributed as needed to the home directory of your application user.

Note: The database service must be restarted before MySQL will be able to validate your SSL keys.

Verify SSL is Enabled

  1. Connect via SSH to the db_master instance
  2. Assume the role of the administrative user sudo su -
  3. Check the ca file that is in place mysql -e "show global variables like 'have_ssl'"
    • If the value is set to YES you are now running with SSL enabled, you can type exit and move on to Verifying SSL Connectivity.
  1. Verify the configuration file for MySQL has the ca file configured cat /etc/mysql/my.cnf | grep 'ssl-ca'.
    • If the configuration file shows SSL is on and the server indicated it was off you'll need to Restart MySQL. This will result in a brief downtime for your application so you'll probably want to do this during a maintenance window.
  1. Since SSL is not enabled in the configuration file, you'll need to upgrade your environment stack to at least Stable-v4 2.0.123 or Stable-v5 3.0.30.

Verify SSL Connections

Use the user, host, and database name from your database.yml to connect to the database:

mysql -u <user> -h <hostname> <dbname>

Enter the command \s; if SSL is enabled and your key has been authenticated you will see a line like the following in the output:

SSL:			Cipher in use is DHE-RSA-AES128-GCM-SHA256

If you do not see this line make sure you are not using localhost as the hostname in the mysql command.

Enforce SSL Connections

To enforce that MySQL uses SSL authentication instead of only Password Authentication you'll need to modify each user you want this setting to apply to. MySQL offers several options (see "Other Account Characteristics") to enforce various aspects of certificate authentication, but REQUIRE X509 is usually going to meet the needs of most configurations.

To enforce x509 for the deploy user:

sudo su -
mysql -e "GRANT USAGE ON *.* TO deploy@'%' REQUIRE X509;"
exit

Modify each host of the database.yml either by overriding our template (Stable-v5) or using the database_yml_custom cookbook (Stable-v4) to add:

sslca: '/home/deploy/.mysql/root.crt'
sslkey: '/home/deploy/.mysql/mysql.key'
sslcert: '/home/deploy/.mysql/mysql.crt'

To remove this setting you would rerun the same command using REQUIRE NONE. The database.yml customizations are expected to be included by default in a future release of the stack.

 

Verifying the validity of the SSL certificates

You may also run into database connectivity issues caused by the DB SSL certificates being expired. In order to check the validity:

  1. Connect via SSH to your database master and assume the role of the super user.
  2. Confirm the location of the SSL certificates in use by the DB: cat /etc/mysql/my.cnf | grep ssl-*. The output should be similar to:
    mceclip0.png
  3. Navigate with cd to the paths from the above output: cd /db/mysql/5.6/data
  4. Run the following command which would return the date of the certificate expiry: openssl x509 -enddate -noout -in server.crt
  5. If the certificate is expired, proceed with the Key Maintenance steps to renew the certificates

Keys for Named Users

If you have created additional OS level (Linux) named users on your instances you can generate and distribute database SSL keys for these users. In order to use this approach you'll also need to first add a MySQL user with a matching username and set its privileges as appropriate. Once the users are in place you can:

  1. Generate a new key with the command `sudo -i /engineyard/bin/db_user_ssl_keys.sh [expire_in_days]
    • [expire_in_days] will default to 5 years (1825) unless otherwise specified
    • the password is actually only a temporary value and is removed from the key for ease of use
  1. Create a custom chef cookbook that runs the following command for any users you have added /engineyard/bin/remote_key_copy.sh <username>

Key Maintenance

When booting an environment from snapshots it may be required to generate new server and user keys for your environment, due to a mismatch between old and new keys. This process can also be followed should you wish to regenerate the keys for any other reason. This will require a maintenance downtime, since once you distribute the new keys a database restart is required. Use the following steps:

  1. Connect via SSH to your database master and assume the role of the super user.
  2. If the environment is running the stable-v6 stack there is an Ubuntu bug that requires RANDFILE = $ENV::HOME/.rnd to be commented out, via vim /etc/ssl/openssl.cnf, press 'i' to enter insert mode, add a # to comment out the line, then press Escape to leave insert mode and finally type :wq to save and quit
  3. Run the following shell commands 
    mkdir -p /db/old_keys/server
mkdir -p /db/old_keys/user
mv /db/mysql/*/data/root.crt /db/old_keys/server/ 
mv /db/mysql/*/data/server.crt /db/old_keys/server/ 
mv /db/mysql/*/data/server.key /db/old_keys/server/ 
mv /db/mysql/keygen/mysql /db/old_keys/user/ 
mv /db/mysql/keygen/deploy /db/old_keys/user/
  4. Run /engineyard/bin/db_server_ssl_keys.sh
  5. Restart MySQL
  6. Click "Apply" on the cloud dashboard
Was this article helpful? 0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Article is closed for comments.