Detailed explanation of the steps for MySQL SSL connection

MySQL SSL connection steps detailed explanation

MySQL is a commonly used relational database management system, which supports SSL (Secure Sockets Layer, Secure Sockets Layer) Make a secure connection to the database. For some application scenarios that need to protect data transmission, using SSL connections is a very important security measure. This article will detail the steps for MySQL SSL connection and provide corresponding code examples.

1. Generate SSL certificate and private key

Before using SSL connection, you need to generate a certificate and private key. You can generate an SSL certificate and private key through the following steps:

1. Open a command line terminal and enter the bin folder of the MySQL installation directory.

2. Run the following command to generate the CA (Certificate Authority) certificate and private key:

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

3. Run the following command to generate the server certificate and private key:

   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-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

4. Copy the generated certificate and private key files to the appropriate location in the MySQL installation directory, for example, copy the server certificate and private key files to the data directory.

2. Configure the MySQL server

Before making an SSL connection on the MySQL server, you need to perform the corresponding configuration. You can open the MySQL configuration file (my.cnf) and add the following configuration:

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

Please replace the file names in the above configuration with the real certificate and private key file names, and ensure that these files are correctly stored in the specified Location. After the configuration is complete, restart the MySQL server.

3. Connect to MySQL database

When the client connects to the MySQL database, you need to specify the SSL connection option. You can add the following options to the connection string:

--ssl-ca=ca-cert.pem
--ssl-cert=client-cert.pem
--ssl-key=client-key.pem
--ssl-cipher=DHE-RSA-AES256-SHA

where --ssl-ca, --ssl-cert and --ssl- key respectively specify the CA certificate, client certificate and client private key used by the client. --ssl-cipher can be used to specify the SSL encryption algorithm, the example here uses DHE-RSA-AES256-SHA.

The following is a sample code for using PHP to connect to a MySQL database:

<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "database";

$ca_cert = "/path/to/ca-cert.pem";
$client_cert = "/path/to/client-cert.pem";
$client_key = "/path/to/client-key.pem";

$mysqli = new mysqli($servername, $username, $password, $dbname, null, null, MYSQLI_CLIENT_SSL);
$mysqli->ssl_set($client_key, $client_cert, $ca_cert, null, null);

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

echo "Connected successfully";

$mysqli->close();
?>

Please change $servername, $username, $password Replace and $dbname with your real connection information and $ca_cert, $client_cert and $client_key with your real ones File paths to the certificate and private key. After a successful connection, "Connected successfully" will be output.

The above are the detailed steps and corresponding code examples of MySQL SSL connection. Through SSL connection, you can protect the security during the transmission of MySQL database and improve the confidentiality and integrity of the data.

The above is the detailed content of Detailed explanation of the steps for MySQL SSL connection. For more information, please follow other related articles on the PHP Chinese website!