Configure SSL secure connections for MySQL

SSL (SecureSocketsLayer Secure Sockets Layer) is a security protocol that provides security and data integrity for network communication. It uses Encryption technology, ensure that data is not intercepted or eavesdropped during network transmission. SSL provides the following services: authenticate users and servers to ensure that data is sent to the correct customers.

SSL (Secure Sockets Layer) is a security protocol that provides security and data integrity for network communication. It uses the Encryption technology, ensure that data is not intercepted or eavesdropped during network transmission. SSL provides the following services: authenticate users and servers to ensure that data is sent to the correct customers.

SSL (Secure Sockets Layer) is a security protocol that provides security and data integrity for network communication. It uses the Encryption technology, ensure that data is not intercepted or eavesdropped during network transmission.

SSL provides the following services:

Authenticate users and servers to ensure that data is sent to the correct client and server;
Encrypt data to prevent data from being stolen;
Maintain data integrity and ensure that data is not changed during transmission.
To establish an SSL connection between the MySQL server and the client, the server system must meet the following requirements:

The operating system is installed with OpenSSL or yaSSL;
The installed MySQL version must support SSL.
OpenSSL is used here.

1. Check whether the requirements are met:

Shell> rpm-qa | grep openssl # Check whether OpenSSL is installed. MySQL requires an openssl shared library.
Openssl-1.0.0-20.el6.x86_64
Openssl-devel-1.0.0-20.el6.x86_64
Openssl098e-0.9.8e-17.el6.x86_64

Mysql> show global variables like 'have % ssl ';

# Check whether ssl is supported. NO indicates not supported, DISABLE indicates supported but not used.

+ ----- + ---- +
| Variable_name | Value |
+ ----- + ---- +
| Have_openssl | DISABLED |
| Have_ssl | DISABLED |
+ ----- + ---- +
2 rows in set (0.00 sec)
If the compiled binary is used, it is supported by default. If you compile it by yourself, you must use the cmake.-DWITH_SSL = system option for Version 5.5.

To enable the client to connect using SSL, you must configure an appropriate certificate and key file and grant users the appropriate permissions.

Start the configuration file my. add ssl to the [mysqld] section of cnf. If you want to use ssl connection for mysqldump backup, add ssl in the [mysqldump] section and restart the database, use the above command mysql> show global variables like 'have % ssl '; Check that the status has changed to yes, indicating that the ssl secure connection has been enabled.

Ii. Generate certificates and keys for MySQL

Shell> mkdir-p/db/ssl
Shell> cd/db/ssl
# Create a digital certificate for the Certification Authority. The certificate for the server and client will be signed by the certification authority.
Shell> openssl genrsa 2048> ca-key.pem
Generating RSA private key, 2048 bit long modulus
......... ++
................................................................................................................. ++
E is 65537 (0 × 10001)
Shell> openssl req-new-x509-nodes-days 3600-key ca-key.pem-out ca-cert.pem
You are about to be asked to enter information that will be ininitialized
Into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
--
Country Name (2 letter code) [GB]: CN
State or Province Name (full name) [Berkshire]: Shanghai
Locality Name (eg, city) [Newbury]: Shanghai
Organization Name (eg, company) [My Company Ltd]: CA
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:
# Create a server certificate
Shell> openssl req-newkey rsa: 2048-days 3600-nodes-keyout server-key.pem-out server-req.pem
Generating a 2048 bit RSA private key
................... ++
............ ++
Writing new private key to 'server-key. pem'
--
You are about to be asked to enter information that will be ininitialized
Into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
--
Country Name (2 letter code) [GB]: CN
State or Province Name (full name) [Berkshire]: Shanghai
Locality Name (eg, city) [Newbury]: Shanghai
Organization Name (eg, company) [My Company Ltd]: CH
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []: mysqlserver
Email Address []:

Please enter the following 'extra 'attributes
To be sent with your certificate request
A challenge password []: abc123
An optional company name []:
Shell> openssl rsa-in server-key.pem-out server-key.pem # Remove passphrase from server-key [Optional]
Writing RSA key
Shell> openssl x509-req-in server-req.pem-days 3600-CA ca-cert.pem-CAkey ca-key.pem-set_serial 01-out server-cert.pem # sign the server certificate
Signature OK
Subject =/C = CN/ST = Shanghai/L = Shanghai/O = CH/CN = mysqlserver
Getting CA Private Key
# Create a client certificate
Shell> openssl req-newkey rsa: 2048-days 3600-nodes-keyout client-key.pem-out client-req.pem
Generating a 2048 bit RSA private key
................................................................................................... ++
... ++
Writing new private key to 'client-key. pem'
--
You are about to be asked to enter information that will be ininitialized
Into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
--
Country Name (2 letter code) [GB]: CN
State or Province Name (full name) [Berkshire]: Shanghai
Locality Name (eg, city) [Newbury]: Shanghai
Organization Name (eg, company) [My Company Ltd]: CH
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []: mysqlclient
Email Address []:

Please enter the following 'extra 'attributes
To be sent with your certificate request
A challenge password []: abc123
An optional company name []:
Shell> openssl rsa-in client-key.pem-out client-key.pem # Remove passphrase from client-key [Optional]
Writing RSA key
Shell> openssl x509-req-in client-req.pem-days 3600-CA ca-cert.pem-CAkey ca-key.pem-set_serial 01-out client-cert.pem # sign client certificate
Signature OK
Subject =/C = CN/ST = Shanghai/L = Shanghai/O = CH/CN = mysqlclient
Getting CA Private Key
# Verify after generation
Shell> openssl verify-CAfile ca-cert.pem server-cert.pem client-cert.pem
Server-cert.pem: OK
Client-cert.pem: OK

After the above steps, the following file is generated:

The ca-cert.pem both on the server side and on the client side uses-ssl-ca = ca-cert.pem
Server-cert.pem, server-key.pem server end specifying-ssl-cert = server-cert.pem and-ssl-key = server-key.pem
Client-cert.pem, client-key.pem clients specify-ssl-cert = client-cert.pem and-ssl-key = client-key.pem
3. Configure an SSL connection

You can use SSL for configuration and authorization in the following two solutions.
Solution 1]
Server:
Add the following parameters to the configuration file my. cnf on the server:
[Mysqld]
Ssl-cert =/db/ssl/server-cert.pem
Ssl-key =/db/ssl/server-key.pem
Restart mysqld.

GRANT permissions by using the require ssl option of the GRANT statement
For example:
Mysql> create user @ localhost identified by 'abc ';
Mysql> grant select on testdb. * to user @ localhost require ssl;

Client:
Mysql-u user-pabc-P 3300-ssl-ca = ca-cert.pem

Solution 2]
Server:
Add the following parameters to the configuration file my. cnf on the server:
[Mysqld]
Ssl-ca =/db/ssl/ca-cert.pem
Ssl-cert =/db/ssl/server-cert.pem
Ssl-key =/db/ssl/server-key.pem
Restart mysqld.

GRANT permissions by using the REQUIRE x509 option of the GRANT statement.
For example:
Mysql> create user @ localhost identified by 'abc ';
Mysql> grant select on testdb. * to user @ localhost require x509;

Client:
Mysql-u user-pabc-P 3300-ssl-ca = ca-cert.pem-ssl-key = client-key.pem-ssl-cert = client-cert.pem
Obviously, the verification requirements of solution 2 are stricter, and the key and cert need to be specified.

Iv. Check

After the configuration is complete, you can view your ssl support as follows:
Mysql> show global variables like '% ssl %'; # Check whether the server supports SSL connections
+ ----- + --------- +
| Variable_name | Value |
+ ----- + --------- +
| Have_openssl | YES |
| Have_ssl | YES |
| Ssl_ca |/db/ssl/ca-cert.pem |
| Ssl_capath |
| Ssl_cert |/db/ssl/server-cert.pem |
| Ssl_cipher |
| Ssl_key |/db/ssl/server-key.pem |
+ ----- + --------- +
7 rows in set (0.00 sec)
Mysql> show status like 'ssl _ cipher '; # Check whether the connection is an Ssl-encrypted connection.
+ ----- + ------- +
| Variable_name | Value |
+ ----- + ------- +
| Ssl_cipher | DHE-RSA-AES256-SHA |
+ ----- + ------- +
1 row in set (0.00 sec)

Appendix: How the SSL protocol works
The client needs to send and receive several handshakes:

Send a "ClientHello" message, indicating that it supports the list of cryptographic algorithms, compression methods, and the maximum Protocol version, as well as the random number to be used later.
Then, you receive a "ServerHello" message, which contains the connection parameters selected by the server, which is derived from the "ClientHello" provided at the beginning of the client ".
When both parties know the connection parameters, the client exchanges certificates with the server (depending on the selected public key system ). These certificates are generally based on X.509, but the draft already supports certificates based on OpenPGP.
The server requests the public key of the client. The client has a certificate, that is, two-way identity authentication. When there is no certificate, the Public Key is randomly generated.
The client and the server negotiate the primary and private keys through public key confidentiality (both parties negotiate randomly), which is achieved through the pseudo-random number function carefully designed. The result may be Diffie-Hellman exchange or simplified public key encryption. Both parties use the private key for decryption. The CMK is used for encryption of all other key data ".
Refer:

Http://baike.baidu.com/view/16147.htm

Http://zh.wikipedia.org/zh-cn/SSL

Http://linux.chinaitlab.com/safe/731541.html

Http://www.sqlparty.com/mysql%E9%85%8D%E7%BD%AEssl/

Error during use:

Verification key:
Shell> openssl verify-CAfile ca-cert.pem server-cert.pem client-cert.pem
Server-cert.pem: C = IN, ST = KERALA, L = plain IN, O = ABCD, OU = OPERATIONAL, CN = SATHISH, emailAddress = salley@126.com
Error 18 at 0 depth lookup: self signed certificate
OK
Client-cert.pem: C = IN, ST = KERALA, L = plain IN, O = ABCD, OU = OPERATIONAL, CN = sathish, emailAddress =? Salley@126.com
Error 18 at 0 depth lookup: self signed certificate
OK
ERROR 2026 found during Client Login

At last, we found that the Common Name configuration on the server and client cannot be the same. If the same configuration is used, an error will be reported and the connection fails. You only need to set different Common names to connect.

Refer:

Whatever method you use to generate the certificate and key files, the Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. otherwise, the certificate and key files will not work for servers compiled using OpenSSL

Original article address: Configure an SSL secure connection for MySQL. Thank you for sharing it with me.