How to view and manage SSL connections in MySQL

How to view and manage SSL connections in MySQL

SSL (Secure Sockets Layer) is a commonly used network communication protocol used between clients and servers. Establish a secure encrypted connection between them. In a database server, using SSL connections protects the transmission of sensitive data from being stolen or tampered with. MySQL also supports SSL connections and provides tools and commands to view and manage SSL connections.

1. Check whether MySQL supports SSL connection
   Before using SSL connection, first make sure that the MySQL server has enabled the SSL function. You can view it in the MySQL client by executing the following command:

SHOW VARIABLES LIKE 'have_ssl';

If the result is 'YES', it means that MySQL supports SSL connections; if the result is 'DISABLED', it means that the SSL function is disabled; if The result is 'NO', which means MySQL is not installed or does not support SSL.

2. Generate SSL certificate and private key
   To use SSL connection, you need to generate an SSL certificate and private key. Can be generated using OpenSSL tools. Here are the steps to generate a certificate and private key:

• Open a command line terminal.
• Enter the directory where the certificate and private key are stored.
• Execute the following command to generate the private key file:

openssl genrsa -out private_key.pem 2048

• Execute the following command to generate the certificate request file:

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

• Enter relevant information according to the prompts, such as country/region, province/city, organization name, unit name, etc.
• Execute the following command to generate a self-signed certificate file:

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

• Now, you have generated the SSL certificate file certificate.pem and the private key file private_key.pem.

3. Configure MySQL server to use SSL connection
   To enable SSL connection on MySQL server, the following configuration is required:

• Open MySQL The server's configuration file (usually my.cnf or my.ini).
• Add the following configuration items in the [mysqld] section:

ssl-ca=/path/to/certificate.pem
ssl-cert=/path/to/certificate.pem
ssl-key=/path/to/private_key.pem

Replace "/path/to/" with the path to the actual certificate and private key files.

• Save the configuration file and restart the MySQL server.

4. View and manage SSL connections
   Once the MySQL server has enabled an SSL connection, you can use the following commands to view and manage the SSL connection:

• Check whether the current connection uses SSL:

SHOW STATUS LIKE 'Ssl_cipher';

• View the SSL certificate information of the current connection:

SHOW STATUS LIKE 'Ssl_cert';

• View the SSL certificate issuance of the current connection Organization:

SHOW STATUS LIKE 'Ssl_ca';

• View the number of SSL connections established on the server side:

SHOW STATUS LIKE 'Ssl_sessions_reused';

• Close SSL connection:

SET GLOBAL ssl_cipher='';

• Prompt the client to use SSL connection:

GRANT ALL ON database.* TO 'username'@'%' REQUIRE SSL;

Replace "database" with the name of the database to be authorized and "username" with the user name to be authorized.

Summary
Using SSL connections can increase the security of data transmission between the database server and the client. SSL connections can be easily enabled and managed by properly configuring your MySQL server and SSL certificate. Through the above steps, you can view and manage SSL connections in MySQL.

1500 words

The above is the detailed content of How to view and manage SSL connections in MySQL. For more information, please follow other related articles on the PHP Chinese website!