Multiple MySQL SSL configurations and mysqlssl configurations

Multiple MySQL SSL configurations and mysqlssl configurations

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

What does SSL give you?

You can use the replication feature of MySQL over the Internet or connect to MySQL over the Internet.
It is also possible that you connect through the enterprise network, which will be accessed by many people. If you use a self-built device (BYOD) network, this is even more of a problem.
SSL prevents targeted listening through encrypted networks. Man-in-the-middle attacks can be effectively used to interact with the correct server ).
You can also use the SSL client certificate to use it together with the password as the two elements of identity recognition.
SSL is not the only option. You can use SSH and many MySQL GUI clients, similar to the product provided by MySQL Workbench. However, the SSH python script or mysqldump is not that easy to use.

Notes:

Using SSL is better than not using SSL in most cases, so there are not many problems.
Note the following:
Estimation of security errors

You think that you are protected by SSL, but you may forget to set some options so that the program can accept non-SSL connections. Therefore, make sure that the settings must use SSL as the connection method. You can use Wireshark or other similar tools to check whether your traffic is actually encrypted.
Certificate not updated in time

You should notify yourself when the certificate is about to expire in some ways. It can be a nagios check, a tip in the calendar, or an email from the certificate issuing authority. If the certificate expires, your client cannot get a response normally.

Performance

If the performance is very important, you should perform a benchmark test to see if there is any impact without SSL. In OpenSSL and YaSSL, try different passwords and see which one performs the best.
Use traffic monitoring tools

If you are using pcap-based tools such as VividCortex and pt-query-digest, you should ensure that these tools are still usable after SSL is deployed and can provide keys for implementation. Then use a non-Diffie-Hellman (DH) password, or use other sources, such as performance_schema and slow query log. It depends on which application supports the password, it may also contain some Server Load balancer settings.

What is the difference between SSL in MySQL and SSL in browsers?

The browser has a CA Trust List by default, but MySQL does not. This is their biggest difference. MySQL and OpenVPN use SSL very similar.
Both MySQL server and Web server have enabled SSL and both require client certificates, which are the same for them.
There are some minor protocol support differences. For example, MySQL only supports TLS v1.0 and does not support host name verification by default. Therefore, your certificate may be sent to db1.example.com or db2.example, the browser may use OCSP, CRL's, or CRLsets to verify whether the certificate is valid. MySQL 5.6 and later only support CRL verification.

Configuration 1: Internal CA of the Server/Client

The most basic installation and the location where I created mysslgen.
First, create a key in PKCS #1 format on the server. If PKCS #8 does not work. Then create a CSR (Certificate Signing Request Certificate Signature Request) that can be deployed by CA ). The final result is a CA certificate server and a server key. I am used to certificates in PEM format
Server Configuration:
SSL Certificate Server and key (ssl-certandssl-key)
Ssl ca certificate (ssl-ca)
Client Configuration:
Ssl ca certificate (ssl-ca)
If the account is required to use SSL verification, your GRANT statement should use the require ssl option.

Configuration 2: Server/Client internal CA and Client certificate

First, you must enable SSL on the server and then create a certificate for the client.
Specify the client certificate and key ssl-cert and ssl-key on the client.
Now you must use REQUIRE X509 to request a valid certificate. If this CA is used not only on the MySQL server, it does not work because other people may also obtain valid certificates,
You can use require subject '/CN = myclient.example.com' to restrict the use of certificates.
The certificate is issued by your CA. You can control the certificate you issued. The result is that the certificate you issued is trusted.

Configuration 3: Server/Client and public CA

Solution 3 is similar to solution 2. However, a public CA will specify multiple certificates and revoke them.
You should understand -- ssl-verify-server-cert to ensure that the server has a certificate with a host name. Otherwise, someone may use a valid certificate without a host name to initiate Man-in-the-MiddleAttack (MITM attack ").
You should also use the REQUIRE X509 field, requiring each user to SUBJECT and ISSUER (optional) fields.

Configuration 4: Server/Client and multiple public cas

This solution is easy to use.
You can use a certificate bag (multiple certificates in one file) and use the ssl ca command to specify this file.
On Ubuntu, this certificate is provided by the ca-certificates package under/etc/ssl/certs/ca-certificates.crt
For other options, replace "specify assl-capath" with "use ssl-ca", so that it can be set to/etc/ssl/certs. To use the default CA directory, run the c_rehash program in OpenSSL to generate the correct symbolic connection (only available under OpenSSL ).

Configuration 5: Replication)

Set MASTER_SSL _ * in the change master to command.
If host name verification is enabled, make sure MASTER_SSL_VERIFY_SERVER_CERT is used.
Only in this way can we ensure that the SSL connection of the replication user is required. If SSL permits, the show slave status will display the output, rather than being unavailable.
Is each server a CA or a centralized CA?

You can use a CA on each server and specify a client certificate. However, each server client needs a different certificate to connect to the server. That is to say, you will issue many CA certificates.
Therefore, you may want to use a CA instead of a CA for each server.

What if an error occurs?

If one of the private keys (client, server, or CA) is corrupted, you can do the following:
Do nothing, as long as the certificate expires. This applies only to transient certificates (for example, the certificate is valid for 7 days ). In this way, you are vulnerable to attacks, and you may have other ways to mitigate risks. The problem is that MySQL needs to be restarted every seven days, and some automatic Certificate allocation mechanisms are also required.
Re-create all certificates and keys (including CA ). This is only applicable when the number of CAS, servers, and users is small.
Use CRL (Certificate Revocation list ). This method is only used when your certificate has a correct serial number. In addition, if you have multiple cas, you must bind CRL to each CA and specify ssl-crl in the directory of the symbolic connection created by c_rehash, specify the ssl-crl path (OpenSSL only ). If CRL is used, the MySQL version is 5.6 or later, then the CRL is updated, and all clients are updated.
Note: If the key is disclosed, you must re-create the private key. It is not enough to use the previous CSR (Certificate Signing Request.

Client configuration considerations

The ssl-ca parameter can be set in the [client] section of my. cnf. However, this setting does not apply to mysql binlog, so loose-ssl-ca is used on the client. The ssl-key and ssl-cert parameters must be in ~ The [client] section of the/. my. cnf file should be properly protected.
Currently, the 'login-path' created with my_config_editor cannot be added to the SSL settings.
Password considerations

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

Considerations for status variables of YaSSL and OpenSSL

If it is difficult to determine whether your MySQL uses OpenSSL or YaSSL, some methods can help you, such as using the command ldd/path/to/mysqld. By default, MySQL Community Edition uses YaSSL and MySQL Enterprise Edition uses OpenSSL.
Some status variables may be updated incorrectly based on SSL execution.
To ensure that you are not affected by any SSL vulnerability, you are advised to refer to the critical Oracle patch updates to keep your MySQL and OpenSSL updated in a timely manner.

Notes for running CA

In fact, it is not as easy as it looks. It is often very simple at the beginning and helpless in the future. You must be sure to know how to publish CRL, what is the X509 extension, and what you must use.
There is a website that tells you how to configure your own CA: How to Make an existing CA-jamielinux
There are also many public CAs that will provide hosts to your private CA.

The above is all the content of this article. I hope you will like it.

Please take a moment to share your article with your friends or leave a comment. Thank you for your support!