Database Security – How to Fully SSL-Encrypt Your MySQL Galera Cluster

Data security is a hot topic for many companies these days. But for those who need to adhere to security standards like PCI DSS or HIPAA, security is not an option. We showed you sometime back how to encrypt Galera replication traffic, but for a more complete solution, you’ll want to encrypt all database connections from client applications and any management/monitoring infrastructure. With ClusterControl 1.2.9, we introduced a number of features to facilitate this, including the ability to add new nodes to an encrypted Galera Cluster.

The following are the new relevant configuration options:

• cmondb_ssl_key – path to SSL key, for SSL encryption between CMON and the CMON DB.
• cmondb_ssl_cert – path to SSL cert, for SSL encryption between CMON and the CMON DB
• cmondb_ssl_ca – path to SSL CA, for SSL encryption between CMON and the CMON DB
• cluster_ssl_key – path to SSL key, for SSL encryption between CMON and managed MySQL Servers.
• cluster_ssl_cert – path to SSL cert, for SSL encryption between CMON and managed MySQL Servers.
• cluster_ssl_ca – path to SSL CA, for SSL encryption between CMON and managed MySQL Servers.
• cluster_certs_store – path to storage location of SSL related files, defaults to /etc/ssl//

Details on the configuration options above is explained in our ClusterControl Administration Guide under Configuration File section.

In this blog post, we are going to show you how to deploy a fully encrypted Galera Cluster. This includes:

• MySQL clients to MySQL servers
• ClusterControl to managed MySQL servers
• ClusterControl to CMON DB
• Galera replication traffic

The following diagram shows our architecture, before and after the deployment of SSL:

Upgrade to ClusterControl latest version

Please upgrade to ClusterControl controller version 1.2.9-708 or above before performing the exercise explained in this blog post. Upgrade instructions are available here.

Generating SSL with OpenSSL

The following steps should be performed on the ClusterControl node.

1. To make things simpler, we are going to create keys and certificates under a directory, /etc/ssl/mysql on the ClusterControl node and transfer them over to the managed MySQL nodes. Firstly, create the directory:

   $ mkdir /etc/ssl/mysql
   $ cd /etc/ssl/mysql

2. Generate Certificate Authority (CA) key and certificate:

   $ openssl genrsa 2048 > ca-key.pem
   $ openssl req -new -x509 -nodes -days 3600 -key ca-key.pem > ca-cert.pem

3. Create the MySQL server’s certificate:

   $ openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem > server-req.pem
   $ openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

4. Create the MySQL client’s certificate:

   $ openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem > client-req.pem
   $ openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

5. Remove the passphrase for the key files:

   $ openssl rsa -in client-key.pem -out client-key.pem
   $ openssl rsa -in server-key.pem -out server-key.pem

6. Create the same directory and copy over the generated keys and certs to all MySQL nodes:

   $ ssh 10.0.0.21 "mkdir -p /etc/ssl/mysql"
   $ scp -r /etc/ssl/mysql/* 10.0.0.21:/etc/ssl/mysql/
   $ ssh 10.0.0.22 "mkdir -p /etc/ssl/mysql"
   $ scp -r /etc/ssl/mysql/* 10.0.0.22:/etc/ssl/mysql/
   $ ssh 10.0.0.23 "mkdir -p /etc/ssl/mysql"
   $ scp -r /etc/ssl/mysql/* 10.0.0.23:/etc/ssl/mysql/

Enabling Galera replication traffic encryption

** This section has been updated on 28th January 2016 to reflect the new feature introduced in ClusterControl 1.2.12.

1. Go to ClusterControl > Actions icon > Enable SSL Galera Encryption and you should see the following dialog. You can change how long the certificate should expired. The default is 10 years (3650 days):

   

2. Click “Proceed” to start the job. Take note that this action will rolling restart the Galera cluster.

3. Once the cluster has restarted, you should see a padlock icon next to the Galera nodes:

4. Re-import the Galera cluster configuration files at ClusterControl > Manage > Configurations > Reimport Configuration so we will have our configuration template updated with the latest configuration version.

Our Galera cluster communication and replication are now running in an encrypted environment.

Activating SSL on MySQL nodes

The following steps should be performed on all MySQL nodes (including ClusterControl node) so they can accept client connections through SSL.

1. Add the following lines into my.cnf under [mysqld] directive:

   ssl-ca=/etc/ssl/mysql/ca-cert.pem
   ssl-cert=/etc/ssl/mysql/server-cert.pem
   ssl-key=/etc/ssl/mysql/server-key.pem

2. Restart the MySQL service one node at a time:

   $ service mysql restart

3. Grant CMON user with SSL support:

   $ mysql -uroot -p

   Run the following statements:

   mysql> GRANT ALL PRIVILEGES ON *.* TO 'cmon'@'' IDENTIFIED BY '' REQUIRE SSL WITH GRANT OPTION;
   mysql> GRANT ALL PRIVILEGES ON *.* TO 'cmon'@'127.0.0.1' IDENTIFIED BY '' REQUIRE SSL WITH GRANT OPTION;
   mysql> FLUSH PRIVILEGES;

   ** Replace and with respective values.

Our Galera nodes are now able to accept client’s connections through SSL.

Activating ClusterControl encryption configuration options

1. Add the following lines into /etc/cmon.cnf or /etc/cmon.d/cmon_.cnf for respective cluster ID:

   cluster_ssl_key=/etc/ssl/mysql/client-key.pem
   cluster_ssl_cert=/etc/ssl/mysql/client-cert.pem
   cluster_ssl_ca=/etc/ssl/mysql/ca-cert.pem
   cmondb_ssl_key=/etc/ssl/mysql/client-key.pem
   cmondb_ssl_cert=/etc/ssl/mysql/client-cert.pem
   cmondb_ssl_ca=/etc/ssl/mysql/ca-cert.pem

2. Restart CMON service:

   $ service cmon restart

3. Monitor the output of /var/log/cmon.log or /var/log/cmon_.log and ensure CMON does not throw any errors regarding connection to the CMON DB and the managed MySQL nodes. Our cluster is now running in fully encrypted mode, from ClusterControl controller process to CMON DB and the managed MySQL nodes.

4. At the time of writing, the ClusterControl UI has a limitation in accessing CMON DB through SSL using the cmon user. As a workaround, we are going to create another database user for the ClusterControl UI and ClusterControl CMONAPI called cmonui. This user will not have SSL enabled on its privilege table.

   mysql> GRANT ALL PRIVILEGES ON *.* TO 'cmonui'@'127.0.0.1' IDENTIFIED BY ' FLUSH PRIVILEGES;

5. Update the ClusterControl UI and CMONAPI configuration files located at $wwwroot/clustercontrol/bootstrap.php and $wwwroot/cmonapi/config/database.php respectively with the newly created database user, cmonui :

   # /clustercontrol/bootstrap.php
   define('DB_LOGIN', 'cmonui');
   define('DB_PASS', '/cmonapi/config/database.php
   define('DB_USER', 'cmonui');
   define('DB_PASS', '

   These files will not be replaced when you perform an upgrade through package manager.

Activating SSL on clients

To connect to the Galera nodes through SSL, ensure you have set up the user’s grant with ‘REQUIRE SSL’ syntax, similar to below:

$ CREATE SCHEMA testdb;
$ GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'127.0.0.1' IDENTIFIED BY 'password' REQUIRE SSL;
$ FLUSH PRIVILEGES;

You can test the console connections by using the following command:

$ mysql -u testuser -p -h 127.0.0.1 -P3306 --ssl-ca=/etc/ssl/mysql/ca-cert.pem --ssl-cert=/etc/ssl/mysql/client-cert.pem --ssl-key=/etc/ssl/mysql/client-key.pem

Or specify the SSL configuration options inside my.cnf (or .my.cnf for user’s option file) under [client] directive:

[client]
ssl-ca=/etc/ssl/mysql/ca-cert.pem
ssl-cert=/etc/ssl/mysql/client-cert.pem
ssl-key=/etc/ssl/mysql/client-key.pem

You should now able to connect the Galera nodes through SSL. All connections from client applications and ClusterControl are now fully encrypted.

Verifying connection

Go to ClusterControl > Performance > DB Variables and look for SSL related variables. Ensure all of them are defined correctly as per below example:

You can see from ClusterControl > Performance > DB Variables that the certificate and key are loaded inside wsrep_provider_options. This tells us that Galera is now communicating and replicating through a secured channel.

That’s it. You are now running a fully SSL-encrypted Galera Cluster!