Several different MySQL SSL configurations _mysql

In this blog post, I will describe different SSL configuration methods using the MySQL database.

What did SSL give you?

You can use the MySQL replication feature (replication) via the Internet or connect to MySQL via the internet.

It is also possible that you connect through the corporate network, so that there will be a lot of people to visit. If you use a self-contained device (BYOD) network, this is more of a problem.

SSL is here to prevent targeted eavesdropping by encrypting the network. When interacting with the correct server, you can effectively respond to man-in-the-middle attacks (man-in-the-middle attacks).

You can also use the SSL client certificate to make it a two element of identification with the password.

SSL is not the only option, you can use SSH and many MySQL GUI clients, similar to the one provided by MySQL Workbench. But the Python script for ssh, or mysqldump, is not so easy to use.

Things to be aware of:

Using SSL is better than not using SSL in most cases, so there are not too many possible problems.

But the following points need to be noted:

Error estimates for security

You think you are under the protection of SSL, but you may forget to set some options to cause the program to accept non-SSL connections, so make sure that the settings must use SSL as the connection. You can use Wireshark or other similar tools to detect whether your traffic is really encrypted.

Certificate not updated in time

You should notify yourself when the certificate is about to expire by some means. Can be a nagios check, can be a small tip in the calendar, can come from the certificate issued by the agency's email. If the certificate expires, it will cause your client to fail to receive a response correctly.

Performance

If performance is important, benchmark tests should be done to see what the impact is without SSL. Try different passwords under OpenSSL and Yassl, and see which performs the best.

Tools for using flow monitoring

If you are using a pcap based tool like Vividcortex,pt-query-digest, then you should make sure that after SSL is deployed, these tools are still available and can provide the key to implement. Then use a diffie-hellman (DH) password, or use a different source, such as Performance_schema, slow query log, which depends on which one your application supports, or it may contain some load-balancing settings.

What is the difference between SSL in MySQL and SSL in the browser

The browser defaults to a trusted list of CAs, but MySQL defaults are not. That's their biggest difference. MySQL and OpenVPN Use SSL very much alike.

Both MySQL server and WEB server have SSL enabled, and they all require client certificates, which is the same place.

There are some subtle protocol support differences, such as: MySQL only supports TLS v1.0, default does not support host name authentication, so your certificate may be to db1.example.com, or may be to db2.example, the browser may use OCSP, CRL ' s or Crlsets to verify that the certificate is valid. Only CRL validation is supported after MySQL 5.6.

Configuring internal CAs for 1:server/client

The most basic installation and the location where I created the Mysslgen.

First create a pkcs#1-formatted key on the server, if Pkcs#8 is not possible. Then create a CSR that can be deployed with the CA (Certificate signing request certificate signing requests). The final result is a CA Certificate Server and a server key. I am accustomed to using a PEM format certificate

Server configuration:

SSL Certificate Server and key (Ssl-certandssl-key)

SSL CA Certificate (SSL-CA)

Client Configuration:

SSL CA Certificate (SSL-CA)

Require the account to be authenticated with SSL, then your grant statement should use the Require SSL option.

Configuring internal CA and client certificates for 2:server/client

First you have to turn on SSL on the server and then create a certificate for the client.

Then specify the client certificate and key Ssl-cert and Ssl-key on the client.

Now you must use the REQUIRE X509 to require a valid certificate. If this CA is used not only on MySQL server, it will not work, because other people may also get a valid certificate,

You can use REQUIRE SUBJECT '/cn=myclient.example.com ' to limit the use of certificates.

The certificate you set up here is issued by your CA, and you can have a lot of control over the certificate you issued, and the result is that the certificate you issued is trusted.

Configuring 3:server/client and Public CAs

Programme 3 is similar to programme 2. However, the public CA specifies multiple certificates, and the certificates are also revoked.

You should understand--ssl-verify-server-cert to ensure that the server has a certificate with a hostname. Otherwise, someone might initiate a man-in-the-middle attack (man-in-the-middleattack, or "MITM attack") with a valid certificate without the hostname.

You should also use the REQUIRE X509 field to require each user SUBJECT, issuer (optional) field.

Configuring 4:server/client and multiple public CA ' s

This scheme is easy to use.

You can use a card bag (multiple certificates for a file) and specify this file with the SSL CA command.

On the Ubuntu system, this certificate is provided by the Ca-certificates package under/ETC/SSL/CERTS/CA-CERTIFICATES.CRT

Other options, use "Specify Assl-capath" instead of "use Ssl-ca", so you can set it to/etc/ssl/certs. If you want to use the CA's default directory, you must run the C_rehash program in OpenSSL to generate the correct symbolic connection (available only under OpenSSL).

Configure 5:replication (copy)

Master_ssl_* can be implemented by setting the change MASTER to command.

If host name verification is turned on, Master_ssl_verify_server_cert is ensured.

This is the only way to really ensure that the SSL connection of the replication user is the required SSL connection. If SSL allows, show SLAVE status displays the output instead of being unavailable.

is each server a CA or a centralized CA?

You can use one CA per server and then specify a client certificate, but each server client needs a different certificate to connect, meaning you will issue many CA certificates.

So you might want to use a CA instead of a single CA per server.

What if something goes wrong?

If one of the private keys (client, server, or CA) is corrupted, there are several ways to handle this:

Do nothing, just wait until the certificate expires. This applies only to short certificates (for example, a certificate is valid for 7 days). This is an easy way to attack, and you may have other ways to mitigate the risk. The problem is that MySQL has to be restarted every 7 days, and some mechanisms for automatic certificate allocation are required.

Recreate all certificates and keys (including CAs). This applies only to a small number of CAs, servers, and users.

Use CRLs (certificate revocation list Certificate revocation lists). Doing this only works if your certificate has the correct serial number. In addition, if you have multiple CAs, you must bundle the CRL for each CA and specify SSL-CRL in the directory connected by C_rehash to specify the SSL-CRL path (OpenSSL only). If you use a CRL, the version of MySQL is 5.6 or higher, and then the CRL is updated and all clients are updated.

Note: If the key is compromised, you must re-create the private key, which is not sufficient only with the previous CSR (certificate signing request).

Client Configuration Considerations

The SSL-CA parameter can be set in the [Client] section of the MY.CNF, but the MySQL binlog is not used, so the client uses LOOSE-SSL-CA instead. The Ssl-key and Ssl-cert parameters should be in the [client] section of the ~/.my.cnf file, which should be properly protected.

The ' Login-path ' currently created with My_config_editor cannot be added to the SSL settings.

Password considerations

With SSL encryption, you can set a certain password, with Yassl is very limited. When using OpenSSL, you can use a password suite, which is a set of specific sets of passwords, some filters, and a rule.

YASSL, OpenSSL state variables considerations

If it's hard to tell if your MySQL is OpenSSL or yassl, there are some ways to help you, such as using command ldd/path/to/mysqld. MySQL Community Edition By default is Yassl, MySQL Enterprise Edition with OpenSSL.

Depending on the SSL execution, some state variables may be updated incorrectly.

To ensure that you are not affected by any SSL vulnerabilities, you may want to keep your MySQL and OpenSSL up-to-date by referencing the Oracle critical patch update.

Considerations for running a CA

In fact, it is not so easy to look, often beginning very simple, backward very helpless. Be sure to know how to release the crl,x509 extension and what you have to use.

There is a website that tells you how to configure your own CA: How to be a proprietary certification authority (CA)-Jamielinux

There are also many public CAs that provide hosts to your private CA.

SSL/TLS Book recommendation

I recommend a book on SSL: Bulletproof SSL and TLS "SSL and TLS bulletproof vests"

There is also a book on the history of cryptography, very interesting: the Code books

The translator has read the Chinese version of the book "Password Story" in high school, which is really good.