How to generate a MySQL SSL certificate using OpenSSL

How to use OpenSSL to generate a MySQL SSL certificate

Introduction:
MySQL is a widely used relational database system that uses SSL in actual production environments ( Secure Sockets Layer) protocol is very important for encrypted communication. This article describes how to use the OpenSSL tool to generate a MySQL SSL certificate and provides corresponding code examples.

Steps:

1. Install OpenSSL:
   First, make sure the OpenSSL tool is installed on your computer. On Linux systems, you can use the following command to install:

   sudo apt-get install openssl

   On Windows systems, you can download the installation program for Windows from the OpenSSL official website (https://www.openssl.org) and follow the installation wizard to install.

2. Generate SSL certificate:
   Use OpenSSL to generate a MySQL SSL certificate. You need to perform the following steps:

2.1 Generate private key:

openssl genpkey -algorithm RSA -out private_key.pem

This will generate a private key file named private_key.pem.

2.2 Generate certificate signing request (CSR):

openssl req -new -key private_key.pem -out certificate_request.csr

When executing this command, you will be prompted to enter relevant information about the SSL certificate, such as organization name, department name, etc. After entering the appropriate information as prompted, a certificate signing request file named certificate_request.csr will be generated.

2.3 Generate a self-signed certificate:

openssl x509 -req -in certificate_request.csr -signkey private_key.pem -out certificate.pem

This command will use the private key and certificate signing request file to generate a self-signed certificate. The generated self-signed certificate is saved as a certificate.pem file.

3. Configure MySQL server:
   Now that we have generated the SSL certificate, we need to configure SSL support in the MySQL server.

3.1 Copy the private key and certificate to the MySQL server:
Copy the private_key.pem and certificate.pem files to the secure directory of the MySQL server. On Linux systems, this is usually the /etc/mysql/ssl/ directory.

3.2 Edit the MySQL configuration file:
Open the MySQL configuration file (usually /etc/mysql/my.cnf) and add the following lines:

[mysqld]
ssl-ca=/etc/mysql/ssl/certificate.pem
ssl-cert=/etc/mysql/ssl/certificate.pem
ssl-key=/etc/mysql/ssl/private_key.pem

Please make sure the path and file name match The actual SSL certificate file is consistent.

3.3 Restart the MySQL server:
After saving and closing the MySQL configuration file, restart the MySQL server to make the configuration take effect:

sudo systemctl restart mysql

4. Client connection:
   Now, MySQL The server is configured to accept SSL connections. In order to test the SSL connection, we need to connect to the MySQL server using an SSL-enabled client.

4.1 Download MySQL Connector/J:
Go to the MySQL official website (https://dev.mysql.com/downloads/connector/j/) to download MySQL Connector/J suitable for Java development .

4.2 Add SSL configuration:
In the Java code connecting to MySQL, you need to add SSL configuration in order to establish an SSL connection:

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class MySQLSSLConnectionExample {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Properties props = new Properties();
            props.setProperty("user", "username");
            props.setProperty("password", "password");
            props.setProperty("useSSL", "true");
            props.setProperty("verifyServerCertificate", "false");
            props.setProperty("requireSSL", "true");
            props.setProperty("clientCertificateKeyStoreUrl", "file:///path/to/certificate.pem");
            props.setProperty("clientCertificateKeyStorePassword", "password");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database", props);
            // 此处替换为实际的数据库连接信息
            // 这将建立一个 SSL 连接到 MySQL 服务器
            // 进行后续数据库操作
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Please make sure to add username, password, file in the code Replace :///path/to/certificate.pem with the actual information.

5. Conclusion:
   With OpenSSL, we can generate a MySQL SSL certificate and configure SSL support in the MySQL server. With an SSL-enabled client, we can establish an encrypted SSL connection to ensure secure transmission of data.

The above are the complete steps and code examples for using OpenSSL to generate a MySQL SSL certificate. We hope this article is helpful to developers and administrators using MySQL SSL connections.

The above is the detailed content of How to generate a MySQL SSL certificate using OpenSSL. For more information, please follow other related articles on the PHP Chinese website!