Generate CA Cert

shell> openssl genrsa 2048 > $MYSQL_CERTS_PATH/ca-key.pem

shell> openssl req -new -x509 -nodes -days 3600 \
-key $MYSQL_CERTS_PATH/ca-key.pem \
-out $MYSQL_CERTS_PATH/ca-cert.pem

Generate Server Cert

shell> openssl req -newkey rsa:2048 -days 3600 -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-cert.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-cert.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-cert.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_CONFIG_PATH/ yourDBhost:$MYSQL_CONFIG_PATH/

Set proper ownership and permissions on ALL DB nodes

shell> sudo chown -R mysql: $MYSQL_CONFIG_PATH/
shell> sudo chmod -R g+w $MYSQL_CONFIG_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-cert.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 5.14.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 Master and let it propagate to the slaves.

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

Convert MySQL Client Cert to pkcs12 format

shell> openssl pkcs12 -export \
-inkey $MYSQL_CERTS_PATH/client-key.pem \
-in $MYSQL_CERTS_PATH/client-cert.pem \
-out $MYSQL_CERTS_PATH/client-cert.p12 \
-passout pass:secret

Create tungsten_connector_keystore.jks

shell> keytool -importkeystore \
-srckeystore $MYSQL_CERTS_PATH/client-cert.p12 \
-srcstoretype PKCS12 \
-destkeystore $CONN_CERTS_PATH/tungsten_connector_keystore.jks \
-deststorepass secret \
-srcstorepass secret

Import the CA Cert into the KeyStore

shell> keytool -import -alias mysqlServerCACert -file $MYSQL_CERTS_PATH/ca-cert.pem \
-keystore $CONN_CERTS_PATH/tungsten_connector_keystore.jks \
-storepass secret -noprompt

Import the CA Cert into the TrustStore

shell> keytool -import -alias mysqlServerCACert -file $MYSQL_CERTS_PATH/ca-cert.pem \
-keystore $CONN_CERTS_PATH/tungsten_connector_truststore.ts \
-storepass secret -noprompt

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

shell> rsync -av $CONN_CERTS_PATH/ yourDBhost:$MYSQL_CONFIG_PATH/

Set proper ownership and permissions on ALL Connector nodes

shell> sudo chown tungsten: $CONN_CERTS_PATH/tungsten_connector_*

Add the new MySQL user to the Connector's user.map config file.

See Section 6.6.1, “user.map File Format” for more information.

shell> vi /opt/continuent/tungsten/tungsten-connector/conf/user.map
ssl_user secret theSvcName

Update the Connector configuration to enable SSL

Test SSL connectivity through the connector

Connect as the SSL-enabled application user through the Connector host

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

Check the connection status

mysql> tungsten connection status;
+----------------------------------------------------------------------------+
| Message                                                                    |
+----------------------------------------------------------------------------+
| db1@east(master:ONLINE) STATUS(OK), QOS=RW_STRICT SSL.IN=true SSL.OUT=true |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

Check the SSL status

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.42-37.1, for Linux (x86_64) using readline 5.1

Connection id:      68
Current database:   
Current user:      ssl_user@app1
SSL:         Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:   ;
Server version:      5.5.42-37.1-log-tungsten Percona Server (GPL), Release 37.1, Revision 39acee0
Protocol version:   10
Connection:      app1 via TCP/IP
Server characterset:   latin1
Db     characterset:   latin1
Client characterset:   latin1
Conn.  characterset:   latin1
TCP port:      3306
Uptime:         2 hours 33 min 32 sec

Threads: 4  Questions: 43065  Slow queries: 0  Opens: 47 
Flush tables: 2  Open tables: 10  Queries per second avg: 4.674
--------------