The MySQL connection enables SSL/TLS encryption to prevent data from being eavesdropped or tampered during transmission and ensures the security of communication between the client and the server. 1. First, confirm whether the MySQL version supports SSL, and check it through the SHOW VARIABLES LIKE 'have_ssl' command. If NO is returned, you need to install the OpenSSL component or use a distribution that supports SSL; 2. Prepare the CA certificate, server certificate and private key files, you can build your own CA and generate related files. The test environment can use a self-signed certificate. It is recommended to use a trusted CA to issue it in the production environment; 3. Specify the ssl-ca, ssl-cert and ssl-key paths in the MySQL configuration file, and restart the MySQL service to take effect. Verify whether it is enabled successfully through SHOW STATUS LIKE 'Ssl_cipher'; 4. Add REQUIRE when creating a user SSL forces SSL connection. When the client connects, SSL parameters such as --ssl-ca and --ssl-mode must be specified. Different tools need to manually enable SSL options; in addition, you need to pay attention to the certificate file permissions, path correctness and whether the client really enables SSL mode to ensure that the entire connection process is encrypted and reliable.

MySQL connection enables SSL/TLS encryption, mainly to prevent data from being eavesdropped or tampered during transmission. This step is critical if you are managing database services or are responsible for secure communication between applications and databases. Simply put, after SSL/TLS is enabled, all communications between the client and the MySQL server will be encrypted, and it is difficult to interpret the content even if someone intercepts the traffic.

Here are some practical suggestions to help you complete the configuration smoothly.

1. Ensure that MySQL supports SSL/TLS

Not all MySQL versions have SSL enabled by default. First, you need to confirm whether your MySQL version supports SSL connections. You can view it through the following command:

 SHOW VARIABLES LIKE 'have_ssl';

If the return value is YES , it means that MySQL already has SSL support; if it is DISABLED or NO , you may need to install or configure SSL-related components, such as OpenSSL, and recompile MySQL or use a distribution that supports SSL (such as Percona Server or MariaDB).

2. Prepare SSL certificate file

You need to prepare three core files:

• CA certificate (ca.pem)
• Server Certificate (server-cert.pem)
• Server private key (server-key.pem)

You can generate these files yourself or buy them from a trusted CA institution. Self-signed certificates are suitable for testing environments, but production environments recommend using formal certificates.

The basic steps for generating a self-signed certificate are as follows (openSSL is required):

• Generate CA private key and certificate:

   openssl genrsa 2048 > ca-key.pem
  openssl req -new -x509 -nodes -days 365 -key ca-key.pem -out ca.pem

• Generate server private key and certificate request:

   openssl req -newkey rsa:2048 -days 365 -nodes -keyout server-key.pem -out server-req.pem
  openssl x509 -req -in server-req.pem -days 365 -CA ca.pem -CAkey ca-key.pem -CAcreateserial -out server-cert.pem

After the generation is completed, put these three files in the location specified in the MySQL configuration, usually a directory like /etc/mysql/ssl/ .

3. Configure MySQL to enable SSL

Modify MySQL's configuration file (usually my.cnf or my.ini ) and add the following content in the [mysqld] section:

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

Then restart the MySQL service to make the configuration take effect:

 sudo systemctl restart mysql

You can use the following SQL statement to verify that SSL is loaded correctly:

 SHOW STATUS LIKE 'Ssl_cipher';

If you see an output value (non-empty), it means that SSL has been enabled successfully.

4. Force the client to use SSL connection

It is not enough to enable SSL, and it is also necessary to make sure that the client is indeed using encryption when connecting. You can add a forced SSL option when creating a user:

 GRANT USAGE ON *.* TO 'secure_user'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

This way, the user must connect via SSL, otherwise access will be denied.

In addition, SSL parameters must be specified when connecting to the client. For example, use the command line to connect:

 mysql -u secure_user -p --host=your.mysql.server --ssl-ca=/path/to/ca.pem --ssl-mode=VERIFY_IDENTITY

Different client tools (such as MySQL Workbench, Navicat, etc.) also have corresponding SSL settings. Remember to check "Use SSL" and import the CA certificate.

Basically that's it. Although the process is not complicated, there are several things that are easy to ignore: First, the permissions issue, ensuring that the MySQL process can read the certificate file; Second, whether the certificate path is correctly configured; Third, whether the client has truly enabled SSL mode. As long as these points are noticed, SSL/TLS can operate stably.

The above is the detailed content of Implementing SSL/TLS encryption for MySQL connections. For more information, please follow other related articles on the PHP Chinese website!