2.7.5. Connector SSL Example Procedure | ||
---|---|---|
Prev | 2.7. Deploying SSL Secured Replication and Administration | Next |
The below procedures accomplish the following objectives:
Create, activate and test SSL keys for the MySQL server
Enable and test SSL encrypted traffic between the MySQL server and the Connector
Enable and test SSL encrypted traffic between the Application/Client and the Connector
Use these environment values on all Database & Connector nodes.
shell>export CONN_CERTS_PATH=/opt/continuent/share
shell>export MYSQL_CONFIG_PATH=/etc/mysql
shell>export MYSQL_CERTS_PATH=$MYSQL_CONFIG_PATH/certs
The certs
directory is required
on all Database nodes to hold the MySQL server certificates and keys.
shell>sudo mkdir -p $MYSQL_CERTS_PATH
shell>sudo chown mysql: $MYSQL_CERTS_PATH
shell>sudo chmod 775 $MYSQL_CERTS_PATH
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 -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
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
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-cert.pem
Verify SSL:
mysql> status
...
SSL: Cipher in use is DHE-RSA-AES256-SHA
...
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.
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
Staging Method
Update all nodes (DB & Connector) in the cluster
shell>tpm query staging
shell>cd {STAGING_DIR}
shell>tools/tpm configure {yourServiceName} \ --connector-ssl=true \ --java-connector-keystore-password=secret \ --java-connector-truststore-password=secret \ --java-connector-truststore-path=$CONN_CERTS_PATH/tungsten_connector_truststore.ts \ --java-connector-keystore-path=$CONN_CERTS_PATH/tungsten_connector_keystore.jks
shell>tools/tpm update
INI Method
Repeat these two steps on each node (DB & Connector)
shell>vi /etc/tungsten/tungsten.ini
[defaults] ... # enable SSL from the connector to the DB connector-ssl=true java-connector-keystore-password=secret java-connector-truststore-password=secret java-connector-truststore-path=/opt/continuent/share/tungsten_connector_truststore.ts java-connector-keystore-path=/opt/continuent/share/tungsten_connector_keystore.jks ... shell>tpm update
Test SSL connectivity through the connector
Connect as the default application user
shell> tpm connector
Check the connection status
Expecting "SSL.IN=false SSL.OUT=true"
SSL.IN is false because the the tpm connector command calls the mysql client in non-SSL mode.
SSL.OUT is true because the connection to the database is encrypted, even if the connection from the mysql client is not.
This can be verified with the "sudo tcpdump -X port 13306" command. Without the encryption, queries and responses are sent in plaintext and are visible in the output of tcpdump. When encryption is enabled, the queries and results are no longer visible.
mysql> tungsten connection status;
+-----------------------------------------------------------------------------+
| Message |
+-----------------------------------------------------------------------------+
| db1@east(master:ONLINE) STATUS(OK), QOS=RW_STRICT SSL.IN=false SSL.OUT=true |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
Check the SSL status
Expecting "SSL: Not in use"
SSL is not in use because the the tpm connector command calls the mysql client in non-SSL mode.
The connection to the database is encrypted, even if the connection from the mysql client is not.
This can be verified with the "sudo tcpdump -X port 13306" command. Without the encryption, queries and responses are sent in plaintext and are visible in the output of tcpdump. When encryption is enabled, the queries and results are no longer visible.
mysql> status
--------------
mysql Ver 14.14 Distrib 5.5.42-37.1, for Linux (x86_64) using readline 5.1
Connection id: 70
Current database:
Current user: app_user@app1
SSL: Not in use
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 27 min 53 sec
Threads: 4 Questions: 41474 Slow queries: 0 Opens: 47
Flush tables: 2 Open tables: 10 Queries per second avg: 4.674
--------------
If you are able to login to MySQL and see that the "tungsten connection status;" is SSL.OUT=true, then you have successfully configured the communication between the Connector and MySQL to use SSL.
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
Expecting "SSL.IN=true SSL.OUT=true"
SSL.IN is true because the mysql client was invoked in SSL mode. Communications from the mysql client to the connector are encrypted.
SSL.out is true because the connection to the Database from the Connector is encrypted.
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
Expecting "Cipher in use is
XXX-XXX-XXXXXX-XXX
"
SSL is in use because the mysql client was invoked in SSL mode.
The connection from the mysql client to the database is encrypted.
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
--------------
If you are able to login to MySQL and see that the "tungsten
connection status;" is "SSL.IN=true
SSL.OUT=true", and the "status;" contains
"Cipher in use is
XXX-XXX-XXXXXX-XXX
", then you
have successfully configured SSL-encrypted communication between the
Application/Client and MySQL through the Connector.