Generate New Self-Signed Certificate Files For MySQL

By default, MySQL has its own SSL certificate files in the '/var/lib/mysql' directory.
If required: generate New Self-signed SSL Certificate Files.
Identity verification with VERIFY_IDENTITY does not work with self-signed certificates that are created automatically.
Generate SSL Certificate files:

1. We need 3 certificates:
1. CA Certificate
2. Server Certificate and Key
3. Client Certificate and Key.
2. We will create them with OpenSSL.
3. After creation, we will configure the files with MySQL

Generate SSL Certificate files
output files: ca-key.pem, ca.pem

1. Create a new directory for the certificate files

mkdir -p /etc/certs

cd /etc/certs

2. Generate new CA certificate ca.pem file

openssl genrsa 2048 > ca-key.pem

openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

Output files: ca-key.pem, ca.pem

3. Generate the server-side certificates

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

Output files: server-req.pem, server-key.pem, server-cert.pem

4. Generate certificate files for the client

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem

openssl rsa -in client-key.pem -out client-key.pem

openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Output files: client-req.pem, client-key.pem, client-cert.pem

5. Verify certificate files

openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

The CA certificate info must be different from the client and server info.

Common name should be equal in client and server files (steps 3, 4).

For VERIFY_IDENTITY ssl-mode: Common name like the host name for the server and the client (steps 3, 4).

Configure the Certificate files with MySQL

1. Change the owner of the certs directory to mysql user :

chown -R mysql:mysql /etc/certs/

2. Change permissions of all key files

chmod 600 client-key.pem server-key.pem ca-key.pem