Establish SSL Connection

From AgileApps Support Wiki

SSL connection prerequisites

1. Open command prompt and connect to mysql server by running below command and run \s to see the SSL connection that is established. If SSL is Not in Use then the connection established is unencrypted. mysql -u{user_name} -p{password}
mysql> \s


2. To establish the secure connection, run the below command with either "PREFERRED" or "REQUIRED" ssl mode. mysql -u{user_name} -p{password} --ssl-mode={PREFERRED | REQUIRED}
mysql> \s


3. Generating SSL/TLS Certificates and Keys
a. To enable SSL connections to MySQL, you first need to generate the appropriate certificate and key files. MySQL versions 5.7 and above provide a utility called mysql_ssl_rsa_setup that helps simplify this process.
mysql-server$ sudo mysql_ssl_rsa_setup --uid=mysql --verbose
The MySQL process must be able to read the generated files, so use the --uid option to declare mysql as the system user that should own the generated files.
b. The above command will produce the output that is similar to the following attached screenshot.
c. These new files will be stored in MySQL’s data directory, located by default at /var/lib/mysql. Check the generated files by typing:
mysql-server$ sudo find /var/lib/mysql -name '*.pem' -ls
d. These files are the key and certificate pairs for the certificate authority (starting with “ca”), the MySQL server process (starting with “server”), and for MySQL clients (starting with “client”). Additionally, the private_key.pem and public_key.pem files are used by MySQL to securely transfer passwords when not using SSL.

4. Enabling SSL connection on MySQL Server: