MySQL Tutorial: Establishing an encrypted connection

Encrypted connections can improve the security of your data, but degrade performance. To make an encrypted connection, the following requirements must be met:

The user permission table must have an associated SSL data column. If the installed MySQL server is 4.0.0 version, the user permission table already contains the relevant SSL data columns, otherwise we can also use the Mysql_fix_privilege_tables script to upgrade the permission table.

Both the server and client programs have been compiled with OpenSSL support. To install OpenSSL first, add--with-vio and--WITH-OPENSSL options plus OpenSSL support at compile-time MySQL server. Use the following statement to query whether the server supports SSL:

Mysql> Show variables like ' Have_openssl ';

Use the option to indicate the location of the certificate file and key file when starting the server. Before establishing an encrypted connection, prepare three files, a CA certificate, a certificate issued by a trusted third party to authenticate the certificate provided by the client and server side. A CA certificate can be purchased from a commercial organization or generated by itself. The second file is the certificate file that is used to prove your identity to the other person when you connect. The third file is the key file, which is used to encrypt and decrypt the data transmitted over the encrypted connection. The MySQL server-side certificate file and key file must be installed first, and there are several sample files for reference in the SAMPDB release's SSL directory: CA-CERT.PEM (CA certificate), SERVER-CERT.PEM (server certificate), SERVER-KEY.PEM (server public key). Copy the files to the server's data directory and add the following in the options file:

[mysqld]
ssl-ca=/usr/local/mysql/data/ca-cert.pem
ssl-cert=/usr/local/mysql/data/server-cert.pem
ssl-key=/usr/local/mysql/data/server-key.pem
重启服务器，使配置生效。

In order for a client program to establish an encrypted connection, you must use the option to tell it where to find its certificate file and key file when invoking the client program. The CLIENT-CERT.PEM (client certificate file), CLIENT-KEY.PEM (client key file) is provided in the SSL directory of the sampdb release, and the CA certificate uses the same CA-CERT.PEM as the server. Copy them to the personal directory and indicate the file location in the. my.cnf option file, such as:

[mysql]
ssl-ca=/home/mysql/ca-cert.pem
ssl-cert=/home/mysql/client-cert.pem
ssl-key=/home/mysql/client-key.pem

After the configuration is complete, call the MySQL program to run \s or show the STATUS like ' ssl% ' command, if you see SSL: The information line is the encryption connection. If the SSL-related configuration is written into the option file, the default is encrypted connection. You can also use the MySQL program's--SKIP-SSL option to cancel the encryption connection. If you enable a cryptographic connection by using a command-line method, you can write this:

% MySQL--ssl-ca=ca-cert.pem--ssl-cert=client-cert.pem--ssl-key=client-key.pem

You can force a user to use an encrypted connection by using the Require SSL option of the GRANT statement.

You can establish an encrypted connection using the SAMPDB release certificate, but because the file is public, it is not safe, and we can improve security by establishing our own certificate or purchasing a commercial certificate after the test is successful. The documentation of how to establish your own SSL certificate is documented in the Ssl/readme file in the sampdb release.