2.8.5. Connector SSL Example Procedure

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

2.8.5.1. Setup Environment and Paths

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

2.8.5.2. Configuring SSL for MySQL Server

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 -days 3600 \
    -key $MYSQL_CERTS_PATH/ca-key.pem \
    -out $MYSQL_CERTS_PATH/ca-cert.pem
  2. 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
  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-cert.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-cert.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_CONFIG_PATH/ yourDBhost:$MYSQL_CONFIG_PATH/
  6. 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/
  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-cert.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 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
  9. 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;
  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-cert.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.

2.8.5.3. Enable and Test SSL encryption from the Connector to the Database

  1. 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
  2. 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
  3. 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
  4. 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
  5. 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/
  6. Set proper ownership and permissions on ALL Connector nodes

    shell> sudo chown tungsten: $CONN_CERTS_PATH/tungsten_connector_*
  7. Add the new MySQL user to the Connector's user.map config file.

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

    shell> vi /opt/continuent/tungsten/tungsten-connector/conf/user.map
    ssl_user secret theSvcName
  8. 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=/tmp/tungsten_connector_truststore.ts \
      --java-connector-keystore-path=/tmp/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=/tmp/tungsten_connector_truststore.ts
      java-connector-keystore-path=/tmp/tungsten_connector_keystore.jks
      ...
      
      shell> tpm update
  9. Test SSL connectivity through the connector

    1. Connect as the default application user

      shell> tpm connector
    2. Check the connection status

      Note

      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)
    3. Check the SSL status

      Note

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

Important

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.

2.8.5.4. Test SSL encryption from the Application to the Database

  1. 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
  2. Check the connection status

    Note

    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)
  3. Check the SSL status

    Note

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

Important

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.