| Title | MySQL 과 Tomcat 을 SSL 로 연결 Connecting MySQL and Tomcat with SSL | ||||||
| Writer | 이지섭 | Write Date | Nov 16 2018 | Modify Date | Sep 22 2024 | View Count | 7633 |
The OPENSSL program must be installed to connect MySQL and Tomcat with SSL. The Openssl program is used in MySQL, and the keytool program in Java is used on the Tomcat side.
1) See below 2) to generate a certificate. Run the following command on the shell. If you do not have the uid option, the certificate is not recognized, so make sure it is created with mysql account. mysql_ssl_rsa_setup --uid=mysql
2) The files created during certificate creation are as follows. (folder location : /var/lib/mysql/ ) If you create a new one, delete the .pem files except the two files : private_key.pem and public_key.pem. The following two files, private_key.pem and public_key.pem, must not be deleted : The Automatic SSL and RSA File Generation MySQL documentation page says that the RSA file will not be created if the above two files are not present. Of course, the automatic generation does. Unless you are using automatic generation to generate files, you should have no problem generating all of your files without these two files. ca.pem, ca-key.pem, client-cert.pem, client-key.pem, server-cert.pem, server-key.pem, private_key.pem, public_key.pem
3) Use the following command to determine the validity of a certificate, for example: (2) The number of times is a brief look at only the information about the validity period. (1) sudo openssl x509 -in /var/lib/mysql/server-cert.pem -noout -text (2) sudo openssl x509 -in /var/lib/mysql/server-cert.pem -noout -dates
4) In the MySQL setup file, type and restart the following: Use the original certificate file whenever possible,
but if you want to copy it, be sure to add the -p option to the cp command.
[mysqld] ssl-ca=/var/lib/mysql/ca.pem ssl-cert=/var/lib/mysql/server-cert.pem ssl-key=/var/lib/mysql/server-key.pem
5) After restarting, verify that the SSL connection is YES with the command : show variables like '%ssl%'; SSL connection is only possible when YES is released. mysql> show variables like '%ssl%'; +--------------------+--------------------------------+ | Variable_name | Value | +--------------------+--------------------------------+ | have_openssl | YES | | have_ssl | YES | | mysqlx_ssl_ca | | | mysqlx_ssl_capath | | | mysqlx_ssl_cert | | | mysqlx_ssl_cipher | | | mysqlx_ssl_crl | | | mysqlx_ssl_crlpath | | | mysqlx_ssl_key | | | ssl_ca | /var/lib/mysql/ca.pem | | ssl_capath | | | ssl_cert | /var/lib/mysql/server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_fips_mode | OFF | | ssl_key | /var/lib/mysql/server-key.pem | +--------------------+--------------------------------+ 17 rows in set (0.06 sec) mysql>
6) The command below (1) ensures that the username1 account is connected to SSL only. To ensure that you do not need to connect to SSL when testing, do as follows (2) : (1)
7) When setting up SSL connections in MySQL client tool, use the file ca.pem, client-cert.pem, and client-key.pem.
8) If you do not use an auto-generated certificate and you want to use a separate certificate, Copy the certificate into the /var/lib/mysql directory. And set the owner of the copied certificate file to mysql. chown mysql:mysql *.pem
9) Register, generate, and set a password for the certificate to be used to link MySQL in the Tomcat with the command below. The java and openssl programs must be installed. Enter the password for each process. The generated files are the truststore and mysql-client-keystore.p12 and keystore files. Use truststore files and keystore files among them. (1) (1), (3) work on the server where the Tomcat resides, and (2) on the server where MySQL resides.
10) When generating the certificate with the openssl program in (2) above, You must specify the path /var/lib/mysql/ to display the certificate to refer to.
11) Enter the following syntax in Tomcat's catalina.sh file to have Tomcat refer to the certificate. YourPassword uses the password you entered in step 9). JAVA_OPTS="$JAVA_OPTS -Djavax.net.ssl.trustStore=/home/ubuntu/mysql_certs/truststore" JAVA_OPTS="$JAVA_OPTS -Djavax.net.ssl.trustStorePassword=yourPassword" JAVA_OPTS="$JAVA_OPTS -Djavax.net.ssl.keyStore=/home/ubuntu/mysql_certs/keystore" JAVA_OPTS="$JAVA_OPTS -Djavax.net.ssl.keyStorePassword=yourPassword"
However, if you do this above, the certificate is applied to the entire application, The connection may fail when trying to establish HTTP Connection. So it should be changed to make sure that the certificate is used only in the jdbc connection.
jdbc:mysql://127.0.0.1:3306/rg?autoReconnect=true &verifyServerCertificate=true &useSSL=true &requireSSL=true &trustCertificateKeyStoreUrl=file:///home/ubuntu/mysql_certs/truststore &trustCertificateKeyStorePassword=yourPassword &clientCertificateKeyStoreUrl=file:///home/ubuntu/mysql_certs/keystore &clientCertificateKeyStorePassword=yourPassword
If you connect the jdbc connection URL to the DB as above, The certificate can be used only for DB connections without affecting the application.
12) Options for connecting the Tomcat are as follows. Work with the file %TOMCAT_HOME%/conf/context.xml. DataSource settings. MySQL uses the 3306 port as the default for SSL connections. <Context> Library required (version is not very important, place in %Tomcat_Home%/lib.) : mysql-connector-java-8.0.11.jar, commons-pool2-2.6.0.jar, commons-dbcp2-2.2.0.jar, commons-logging-1.2.jar
13) Restart the Tomcat to confirm connection to MySQL. Simply test with a JSP file as follows: <%@ page pageEncoding="UTF-8" contentType="text/html; charset=UTF-8" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="javax.naming.Context" %>
<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.sql.DataSource" %>
<%
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/MySQLDB");
Connection conn = ds.getConnection();
[Web Page Referenced] https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html https://dev.mysql.com/doc/refman/8.0/en/mysql-ssl-rsa-setup.html https://dev.mysql.com/doc/connector-j/en/connector-j-reference-using-ssl.html http://stove99.tistory.com/153 https://www.snoopybox.co.kr/1736 https://docs.wavemaker.com/learn/how-tos/mysql-connection-using-ssl/
| |||||||