Enabling Database SSL

The steps outlined below explain how to enable security within MySQL (If it is not already enabled by default in the release your are using). There are different approaches depending on the version/distribution of MySQL you are using. If in any doubt, you should consult the appropriate documentation pages for the MySQL release you are using.

Generate the Database Certs

The first step is to create the new database certificates using one of the methods shown below. Once the database certs are generated, follow the common steps to deploy them on all of the database nodes.

Generate Database Certs Using the tpm cert gen mysqlcerts Command

Version availability

Available in 7.1.0 and later.

Available as of Version 7.1.0, the tpm cert gen mysqlcerts command can perform the database certificate generation steps for you, along with handling directory creation, ownership and permissions.

The tungsten_mysql_ssl_setup command must be available (shipped with Tungsten v7.1.1 onwards).

shell> tpm cert gen my --datadir /etc/mysql/certs
About to execute Write Action `gen mysqlcerts`,
Ready to proceed (y/N)? y

===========================================================================
>>> doGen processing typeSpec: mysqlcerts
===========================================================================

gen::genMysqlCerts: Using datadir /etc/mysql/certs from the command line
gen::genMysqlCerts: datadir /etc/mysql/certs does not exist - attempting to create...SUCCESS
EXECUTING /usr/bin/sudo /opt/continuent/tungsten/tools/tungsten_mysql_ssl_setup --nols --datadir /etc/mysql/certs --extra
...
---------------------------------------------------------------------------
-rw-r----- 1 mysql mysql 1765 Jul 23 13:02 /etc/mysql/certs/ca-key.pem
-rw-r--r-- 1 mysql mysql 1387 Jul 23 13:02 /etc/mysql/certs/ca.pem
-rw-r--r-- 1 mysql mysql 1371 Jul 23 13:02 /etc/mysql/certs/client-cert.pem
-rw-r----- 1 mysql mysql 1679 Jul 23 13:02 /etc/mysql/certs/client-key.pem
-rw-r----- 1 mysql mysql 1890 Jul 23 13:02 /etc/mysql/certs/private_key.pem
-rw-r--r-- 1 mysql mysql 451 Jul 23 13:02 /etc/mysql/certs/public_key.pem
-rw-r--r-- 1 mysql mysql 1371 Jul 23 13:02 /etc/mysql/certs/server-cert.pem
-rw-r----- 1 mysql mysql 1679 Jul 23 13:02 /etc/mysql/certs/server-key.pem

Generate Database Certs Using the mysql_ssl_rsa_setup Utility

The mysql_ssl_rsa_setup command must be available.

This tool is shipped with MySQL 5.7 onwards and makes the creation of all of the certificates much easier. If you have this tool available, then you can follow these steps:

• Invoke mysql_ssl_rsa_setup on one of the hosts. This will generate the SSL certificates and RSA keys by default in /var/lib/mysql. These files should be copied to the other hosts. The mysql_ssl_rsa_setup supports the --datadir=/my/custom/path/ option if the one you want to use is in a different location. Continuent recommends using /etc/mysql/certs as the location.

Note

The generated pem files should be readable by the tungsten and mysql OS users.

Manually Creating Database Certificates

Important

The "Common Name" field for the Server and Client certificates MUST be different than the "Common Name" specified for the CA Cert.

1. Generate CA Cert

   shell> openssl genrsa 2048 > $MYSQL_CERTS_PATH/ca-key.pem
   
   shell> openssl req -sha256 -new -x509 -nodes -days 3650 \
   -key $MYSQL_CERTS_PATH/ca-key.pem \
   -out $MYSQL_CERTS_PATH/ca.pem

2. Generate Server Cert

   shell> openssl req -sha256 -newkey rsa:2048 -nodes -days 3650 \
   -keyout $MYSQL_CERTS_PATH/server-key.pem \
   -out $MYSQL_CERTS_PATH/server-req.pem
   
   shell> openssl rsa -in $MYSQL_CERTS_PATH/server-key.pem -out $MYSQL_CERTS_PATH/server-key.pem
   
   shell> openssl x509 -sha256 -req -in $MYSQL_CERTS_PATH/server-req.pem -days 3650 \
   -CA $MYSQL_CERTS_PATH/ca.pem \
   -CAkey $MYSQL_CERTS_PATH/ca-key.pem \
   -set_serial 01 \
   -out $MYSQL_CERTS_PATH/server-cert.pem

3. Generate Client Cert

   shell> openssl req -sha256 -newkey rsa:2048 -days 3600 -nodes \
   -keyout $MYSQL_CERTS_PATH/client-key.pem \
   -out $MYSQL_CERTS_PATH/client-req.pem
   
   shell> openssl rsa -in $MYSQL_CERTS_PATH/client-key.pem -out $MYSQL_CERTS_PATH/client-key.pem
   
   shell> openssl x509 -sha256 -req -in $MYSQL_CERTS_PATH/client-req.pem -days 3650 \
   -CA $MYSQL_CERTS_PATH/ca.pem \
   -CAkey $MYSQL_CERTS_PATH/ca-key.pem \
   -set_serial 01 \
   -out $MYSQL_CERTS_PATH/client-cert.pem

4. Verify All Certificates

   shell> openssl verify -CAfile $MYSQL_CERTS_PATH/ca.pem \
   $MYSQL_CERTS_PATH/server-cert.pem $MYSQL_CERTS_PATH/client-cert.pem

Common Steps for Enabling Database SSL

These are the common steps needed to deploy database SSL for all three of the above cert generation methods.

1. Update the configuration files on all Database nodes, and repeat as needed so that every Database node has the same database cert configuration (my.cnf and optionally tungsten.ini).

   Add the SSL certificates you just created to the [mysqld] stanza in your my.cnf

   [mysqld]
   ssl_ca=/etc/mysql/certs/ca.pem
   ssl_cert=/etc/mysql/certs/server-cert.pem
   ssl_key=/etc/mysql/certs/server-key.pem
   require_secure_transport=ON

2. Add the following to the [client] stanza in your my.cnf

   [client]
   ssl_ca=/etc/mysql/certs/ca.pem
   ssl_cert=/etc/mysql/certs/client-cert.pem
   ssl_key=/etc/mysql/certs/client-key.pem
   ssl_mode=REQUIRED

   This will enable client SSL, and also require the mysql client to connect through SSL to the server. You may adjust the client ssl_mode option to suit your needs (DISABLED, PREFERRED (default), REQUIRED, VERIFY_CA, VERIFY_IDENTITY).

3. The tpm command will parse the my.cnf file and retrieve the certificates paths. It is still possible to specify different paths via the following /etc/tungsten/tungsten.ini settings:

   datasource-mysql-ssl-ca=/etc/mysql/certs/ca.pem
   datasource-mysql-ssl-cert=/etc/mysql/certs/client-cert.pem
   datasource-mysql-ssl-key=/etc/mysql/certs/client-key.pem

   tpm install will add these client certificates to the tungsten truststore, keystore.

4. Copy certs to all Database nodes (repeat as needed so that every Database node has the same certificates)

   shell> rsync -av $MYSQL_CERTS_PATH/ yourDBhost:$MYSQL_CERTS_PATH/

5. Set proper ownership and permissions on ALL DB nodes

   shell> sudo chown -R mysql: $MYSQL_CERTS_PATH/
   shell> sudo chmod -R g+w $MYSQL_CERTS_PATH/

6. Restart MySQL on all nodes, taking into account any possible disruption to your service

7. Add a new user to MySQL that requires SSL to connect. Do this just once on the current Primary and let it propagate to the Replicas.

   shell> tpm mysql
   mysql> DROP USER ssl_user;
   mysql> CREATE USER ssl_user@'%' IDENTIFIED BY 'secret';
   mysql> GRANT ALL ON *.* TO ssl_user@'%' REQUIRE SSL WITH GRANT OPTION;
   mysql> flush privileges;

8. Verify that MySQL is working with SSL

   1. Expect this to fail, because the ssl_user is only allowed to connect to the database using SSL:

      shell> mysql -u ssl_user -psecret -h 127.0.0.1 -P 13306

   2. Expect this to pass, because we have supplied the proper SSL credentials:

      shell> mysql -u ssl_user -psecret -h 127.0.0.1 -P 13306 --ssl-ca=/etc/mysql/certs/ca.pem

   3. Verify SSL:

      mysql> status
      ...
      SSL:  Cipher in use is DHE-RSA-AES256-SHA
      ...

Important

If you are able to login to MySQL and see that the status is SSL: Cipher in use, then you have successfully configured MySQL to use SSL.

Enabling Database Level SSL with Amazon AWS Aurora

To enable Tungsten Replicator to communicate with Amazon Aurora, via SSL, the following simple steps can be followed.

• Obtain the certificate from Amazon appropriate for the region that your Aurora instance in hosted. More information can be found here

• Copy the file to the Tungsten Replicator host into a directory of your choice.

• Add the following properties to your configuration. (In this example our certificate is within /opt/continuent/share.
  Adjust to suit your environment)

  property=replicator.datasource.global.connectionSpec.urlOptions=noPrepStmtCache=true&serverCertificate=/opt/continuent/share/rds-ca-2019-eu-west-1.pem
  datasource-enable-ssl=true

• You can now install, or if the replicator was already installed, issue an update