6.8. 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 two 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.

6.8.1. Using mysql_ssl_rsa_setup utility

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 the steps below:

  • 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 you want to use a different location.

    Note

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

  • Add the following to the [mysqld] stanza in your my.cnf

    [mysqld]
    ssl_ca=/my/custom/path/ca.pem
    ssl_cert=/my/custom/path/server-cert.pem
    ssl_key=/my/custom/path/server-key.pem
    require_secure_transport=ON

  • Add the following to the >[client] stanz in your my.cnf

    [client]
    ssl_ca=/my/custom/path/ca.pem
    ssl_cert=/my/custom/path/client-cert.pem
    ssl_key=/my/custom/path/client-key.pem
    ssl_mode=REQUIRED

    This will allow the mysql client will connect through SSL to the server.

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

    repl-datasource-mysql-ssl-ca=/my/custom/path/ca.pem
    repl-datasource-mysql-ssl-cert=/my/custom/path/client-cert.pem
    repl-datasource-mysql-ssl-key=/my/custom/path/client-key.pem

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

6.8.2. Manually Creating 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 -new -x509 -nodes \
    -key $MYSQL_CERTS_PATH/ca-key.pem \
    -out $MYSQL_CERTS_PATH/ca.pem
  2. Generate Server Cert

    shell> openssl req -newkey rsa:2048 -nodes \
    -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 -req -in $MYSQL_CERTS_PATH/server-req.pem -days 3600 \
    -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 -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 -req -in $MYSQL_CERTS_PATH/client-req.pem -days 3600 \
    -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
  5. 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/
  6. 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/
  7. Update the my.cnf file to include the SSL certificates you just created (add three lines to the [mysqld] stanza)

    shell> vi /etc/my.cnf
    [mysqld]
    ...
    port=13306
    # add three lines for SSL support
    ssl-ca=/etc/mysql/certs/ca.pem
    ssl-cert=/etc/mysql/certs/server-cert.pem
    ssl-key=/etc/mysql/certs/server-key.pem
    ...
  8. Restart MySQL on all nodes using the standard rolling maintenance procedure - see Section 7.13.3, “Performing Maintenance on an Entire Dataservice” for more information.

    cctrl> ls
    cctrl> datasource db3 shun
    db3# service mysql restart
    cctrl> recover
    
    cctrl> datasource db2 shun
    db2# service mysql restart
    cctrl> recover
    
    cctrl> switch to db2
    
    cctrl> datasource db1 shun
    db1# service mysql restart
    cctrl> recover
    
    cctrl> switch to db1
    cctrl> ls
  9. 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;
  10. 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.