Layer 7 API Management

Configure MySQL Replication to use SSL 

12-21-2017 01:23 PM

By default the CA API Gateway does not enable SSL traffic for MySQL replication. The following instructions outline the configurations necessary to enable MySQL SSL replication in an existing Gateway cluster. 

 

To configure MySQL Replication to use SSL (Please note the adding new nodes caveat on step 13): 

1. Create a private/private key pair either using the Policy Manager -> Create Private Key, or use the command: 
openssl req -new -newkey rsa:2048 -days 3650 -nodes -x509 -keyout e-key.pem -out cert.pem 

 

2. (Only if you use the Policy Manager method) Export the private key. This results in an encrypted *.p12 file that contains the 
key and its self-signed certificate. 

 

3. (Only if you use the Policy Manager method) Run the OpenSSL utility to unpack the *.p12 file: 
openssl pkcs12 -in EXAMPLE.p12 -out package.pem 

 

4. (Only if you use the Policy Manager method) Open package.pem in a text editor and separate the certificate and encrypted 
key into two files, for example e-key.pem and cert.pem. 

 

5. Run OpenSSL again to create the unencrypted key file, using the e-key.pem file 
created above: 
openssl rsa -in e-key.pem -out key.pem 

 

6. The following steps will need to be done on each DB node in the cluster: 
a. Open the /etc/my.cnf file in a text editor: 
Add the following lines to the server-side configuration in the “[mysqld]” section: 
# Add SSL to MySQL configuration 
ssl-ca=/var/lib/mysql/cert.pem 
ssl-cert=/var/lib/mysql/cert.pem 
ssl-key=/var/lib/mysql/key.pem 

 

Add the following lines to the client-side configuration in the bottom of the file: 
[client] 
ssl-ca=/var/lib/mysql/cert.pem 
ssl-cert=/var/lib/mysql/cert.pem 
ssl-key=/var/lib/mysql/key.pem 

 

b. cp cert.pem key.pem /var/lib/mysql/ 
c. chown mysql:mysql /var/lib/mysql/*.pem 

 

7. (Only if repluser and replication configured) Modify the grants for the replication user by running this command on both 
machines: 
grant replication slave, replication client on *.* to 'repluser'@'<gateway DB server name>' IDENTIFIED BY 'replpass' require ssl; 

 

8. Stop all Gateway nodes by running the command: service ssg stop 

 

9. Stop replication and restart MySQL service: 
mysql -e "stop slave" 
mysql -e "show slave status\G" 
Verify that these 2 lines state No 
Slave_IO_Running: No 
Slave_SQL_Running: No 

 

service mysql restart 

 

10. Modify the settings as follows in /opt/SecureSpan/Appliance/bin/add_slave_user.sh file: 
echo "GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT, LOCK TABLES, RELOAD ON *.* TO '$DBUSER'@'$SLAVE' IDENTIFIED BY '$DBPWD' require ssl;" > /tmp/mb_cmd.$$ 

 

11. Modify the settings as follows in both the /opt/SecureSpan/Appliance/bin/restart_replication.sh and /opt/SecureSpan/Appliance/bin/create_slave.sh files: 
CMD="CHANGE MASTER TO MASTER_HOST='$MASTER', 
MASTER_USER='$DBUSER', 
MASTER_PASSWORD='$DBPWD', 
MASTER_PORT=3307, 
MASTER_CONNECT_RETRY=10, 
MASTER_LOG_FILE='$File', 
MASTER_SSL=1, 
MASTER_SSL_CIPHER='DHE-RSA-AES256:AES128-SHA', 
MASTER_LOG_POS=$Position;" 

 

12. Rebuild replication using the create_slave.sh script. The replication restarts over the encrypted channel. 

 

13. Configure Gateway Process to Use SSL 

***the instructions assume the node was already apart of a functional cluster; to add additional nodes the node.properties from a working node must be copied to the new node; then edit the copied node.propeties file, increment the node ID and add the useSSL line outlined below; the node.properties file does not exist by default until the Gateway is running*** 
After MySQL replication has been configured, perform the following sequence of 
steps to enable SSL on the Gateway processes (without client authentication). 
To configure the Gateway processes to use SSL: 

 

a. Add the following string to the end of the /opt/SecureSpan/Gateway/node/default/etc/conf/node.properties file: 
l7.mysql.url.parameters.extra=&useSSL=true&verifyServerCertificate=false 

 

b. Modify MySQL to only permit SSL sessions: 
mysql -e "grant all on ssg.* to 'gateway'@'%' identified by '<password>' require ssl" 
mysql -e "grant all on ssg.* to 'gateway'@'localhost' identified by '<password>' require ssl" 
mysql -e "grant all on ssg.* to 'gateway'@'localhost.localdomain' identified by '<password>' require ssl" 
mysql -e "grant all on ssg.* to 'gateway'@'localhost6.localdomain6' identified by '<password>' require ssl" 
mysql -e "grant all on ssg.* to 'gateway'@'localhost6' identified by '<password>' require ssl" 

 

c. Start the Gateway process: service ssg start

Statistics
0 Favorited
14 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Related Entries and Links

No Related Resource entered.