6.8. Enabling Database SSL

6.8. Enabling Database SSL
Prev	^Up	Chapter 6. Deployment: Security	Next

6.8. Enabling Database SSL

6.8.1. Using mysql_ssl_rsa_setup utility
6.8.2. Manually Creating Certificates

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.

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

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

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

Verify All Certificates

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

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/
```

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/

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

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

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;

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.

Prev	Up	Next
6.7. Removing TLS Encryption from a Staging Directory	^Level	6.9. Configure Tungsten<>Database Secure Communication

Continuent Documentation