API Gateway / API Manager default configuration and documentation only shows MySQL being used in non SSL mode for the historical transaction monitoring.

However using an SSL connection to the database may be mandatory now in some environments and this article shows the steps necessary for this to be achieved.

Testing was done with API Manager 7.5.3 and MySQL Community Server 5.6.40 running on CentOS 7 but it should work with other versions.

Resolution

MySQL should be running in secure mode - to find out call the following

mysql> SHOW GLOBAL VARIABLES LIKE '%ssl%';
--------------
SHOW GLOBAL VARIABLES LIKE '%ssl%'
--------------
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| have_openssl  | YES                                        |
| have_ssl      | YES                                        |
| ssl_ca        | /var/lib/mysql/mysql-certs/ca.pem          |
| ssl_capath    |                                            |
| ssl_cert      | /var/lib/mysql/mysql-certs/server-cert.pem |
| ssl_cipher    |                                            |
| ssl_crl       |                                            |
| ssl_crlpath   |                                            |
| ssl_key       | /var/lib/mysql/mysql-certs/server-key.pem  |
+---------------+--------------------------------------------+
9 rows in set (0.00 sec)

If it is not using SSL then see the relevant MySQL documentation to enable it. A good guide to setting up SSL is found here

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-using-ssl.html

and for CentOS 7 see the tutorial

https://www.howtoforge.com/tutorial/how-to-enable-ssl-and-remote-connections-for-mysql-on-centos-7/

The database driver should already have been installed in ext/lib as part of the standard DB monitoring instructions

e.g.

/opt/Axway-7.5.3/apigateway/ext/lib/mysql-connector-java-5.1.39-bin.jar

Update the Node Manager with the new SSL secure JDBC URL

/opt/Axway-7.5.3/apigateway/posix/bin/managedomain --edit_host node-manager-host --metrics_enabled=True --metrics_dburl=jdbc:mysql://localhost:3306/DefaultDb?autoReconnect=true&useSSL=true --metrics_dbuser=root --metrics_dbpass=changeme --username admin --password changeme"<span class="redactor-invisible-space"></span>

If the database schema needs to reapplied or upgraded run

/opt/Axway-7.5.3/apigateway/posix/bin/dbsetup --dburl=jdbc:mysql://localhost:3306/DefaultDb?autoReconnect=true&useSSL=true --dbuser=root --dbpass=changeme --reinstall<span class="redactor-invisible-space"></span>

or

mysql -v -h localhost -u root --ssl --ssl-ca=/var/lib/mysql/mysql-certs/ca.pem --ssl-cert=/var/lib/mysql/mysql-certs/client-cert.pem --ssl-key=/var/lib/mysql/mysql-certs/client-key.pem -p < /opt/Axway-7.5.3/apigateway/posix/bin/apigateway/system/conf/sql/mysql/analytics.sql

The nodemanager needs to have access to the JRE truststore (and keystore if client authentication will be used). This is specified in the jvm.xml file. Uncomment javax.net.debug to show JRE debug traces in the Node Manager console.

$ cat /opt/Axway-7.5.3/apigateway/conf/jvm.xml
<ConfigurationFragment>
<VMArg name="-Djavax.net.ssl.trustStore=/etc/certs/truststore.jks"/>
<VMArg name="-Djavax.net.ssl.trustStorePassword=changeme"/>
<VMArg name="-Djavax.net.ssl.keyStore=/etc/certs/keystore.jks"/>
<VMArg name="-Djavax.net.ssl.keyStorePassword=changeme"/>
<!--- <VMArg name="-Djavax.net.debug=all"/> --->
</ConfigurationFragment>

Troubleshooting should be done according to the errors found in the following :-

• Node Manager console (started in the foreground)
• Node Manager trace file which is the latest file written in
  
  • /opt/Axway-7.5.3/apigateway/trace/
• MySQL server trace log usually /var/log/mysqld.log