Settingup MYSQL
Generating the Keys and Signing them
Execute following commands to generate necessary keys and to sign them.openssl genrsa 2048 > ca-key.pem openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem openssl rsa -in server-key.pem -out server-key.pem openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem openssl rsa -in client-key.pem -out client-key.pem openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Now open my.cnf and add the following configurations. Its located at /etc/mysql/my.cnf in Ubuntu.
[mysqld] ssl-ca=/etc/mysql/ca.pem ssl-cert=/etc/mysql/server-cert.pem ssl-key=/etc/mysql/server-key.pem
An sample my.cnf would look like following.
Now restart mysql server. You can use the following command to do this.
sudo service mysql restart
Now to check whether SSL certificates are properly set. Login to MySQL and execute the following query.
Above will give the below output.
SHOW VARIABLES LIKE '%ssl%';
Above will give the below output.
+---------------+----------------------------+
| Variable_name | Value |
+---------------+----------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/mysql/ca.pem |
| ssl_capath | |
| ssl_cert | /etc/mysql/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /etc/mysql/server-key.pem |
+---------------+----------------------------+
Now MYSQL configurations are done. Now lets configure WSO2 products to connect to MYSQL via SSL.
1. First, we need to import client and server certificates to the client-truststore of WSO2 server. You can do this with following commands. (The certificates we created when configuring MySQL)
2. Now specify the SSL parameters in the connection URL. Make sure you specify both options useSSL and requireSSL.
The Full datasource will look like following.
3. Now you can start the server. If everything is set properly, the server should start without errors.
Connecting WSO2 Products to secured MySQL Server
1. First, we need to import client and server certificates to the client-truststore of WSO2 server. You can do this with following commands. (The certificates we created when configuring MySQL)
keytool -import -alias wso2qamysqlclient -file /etc/mysql-ssl/server-cert.pem -keystore repository/resources/security/client-truststore.jks
keytool -import -alias wso2qamysqlserver -file /etc/mysql-ssl/client-cert.pem -keystore repository/resources/security/client-truststore.jks
2. Now specify the SSL parameters in the connection URL. Make sure you specify both options useSSL and requireSSL.
jdbc:mysql://192.168.48.98:3306/ds21_carbon?autoReconnect=true&useSSL=true&requireSSL=true
The Full datasource will look like following.
<configuration> <url>jdbc:mysql://192.168.48.98:3306/ds21_carbon?autoReconnect=true&useSSL=true&requireSSL=true</url> <username>root</username> <defaultAutoCommit>false</defaultAutoCommit> <password>root</password> <driverClassName>com.mysql.jdbc.Driver</driverClassName> <maxActive>80</maxActive> <maxWait>60000</maxWait> <minIdle>5</minIdle> <testOnBorrow>true</testOnBorrow> <validationQuery>SELECT 1</validationQuery> <validationInterval>30000</validationInterval> </configuration>
3. Now you can start the server. If everything is set properly, the server should start without errors.
