Enable SSL and Remote Connections for MySQL -

Enable SSL and Remote Connections for MySQL

Last updated on: 2021-05-21

Authored by: John Abercrombie

Note: This article assumes that you have installed and configured MySQL® on your server.

Enable SSL for MySQL

Before installing your SSL certificate on your MySQL database, backup and remove the default certificate files. You can choose your own directory name for this step. This example uses the mysql-backup-certs directory. To perform the following steps, switch to the root user.

Create a new backup directory:
```
mkdir -p ~/mysql-backup-certs/
```

Go to the /var/lib/mysql directory and move every default certificate to your newly created backup directory:

cd /var/lib/mysql
mv ca.pem ca-key.pem ~/mysql-backup-certs/
mv server-key.pem server-cert.pem ~/mysql-backup-certs/
mv client-key.pem client-cert.pem ~/mysql-backup-certs/
mv private_key.pem public_key.pem ~/mysql-backup-certs/

Restart MySQL by running one of the following commands:
```
service mysqld restart
```
or
```
systemctl restart mysqld
```
Use one of the following commands to check the mysqld status:
```
service mysqld status
```
or
```
systemctl status mysqld
```
Check the SSL status from inside the MySQL shell. To do this, log into MySQL as the root user:
```
mysql -u root -p
(Enter MySQL root password)
```
Run the following query to check the SSL status in MySQL:
```
SHOW GLOBAL VARIABLES LIKE '%ssl%';
STATUS;
```
Note: You do not need to use capital letters. The example uses them to differentiate command syntax from what you’re querying.

In the preceding command, you want to see the SSL status as disabled, and the root user is connected without SSL.
To enable SSL connection with our own certificate by editing the my.cnf file, run one of the following commands:
```
vim /etc/my.cnf
```
or
```
nano /etc/my.cnf
```

Find the [mysqld] section and add the following configuration settings:

# You will use your own SSL certificates directory for these. The following are examples only.
ssl-ca=/etc/pki/tls/certs/ca.pem
ssl-cert=/etc/pki/tls/certs/server-cert.pem
ssl-key=/etc/pki/tls/private/server-key.pem

Save and exit the file. Then, restart MySQL by running one of the following commands:
```
service mysqld restart
```
or
```
systemctl restart mysqld
```
After MySQL is back up, log back into the MySQL shell:
```
mysql -u root -p
(Enter MySQL root password)
```
Run the SSL query again:
```
SHOW VARIABLES LIKE '%ssl%';
STATUS;
```
If the SSL section value shows yes, the SSL is enabled for MySQL. However, you still see that the root user is not connected by using SSL. This is normal. If you want to change this, you can force all localhost user connections to use SSL. You need to edit the my.cnf file again by running one of the following commands:
```
vim /etc/my.cnf
```
or
```
nano /etc/my.cnf
```

Enter the following configuration at the end:

[client]
ssl-ca=/etc/pki/tls/certs/ca.pem
ssl-cert=/etc/pki/tls/certs/client-cert.pem
ssl-key=/etc/pki/tls/private/client-key.pem

Restart MySQL. After MySQL is back up, log into the MySQL shell again:
```
mysql -u root -p
(Enter MySQL root password)
```
Run the following query again:
```
SHOW VARIABLES LIKE '%ssl%';
STATUS;
```

If you just want to enable SSL connections from localhost connections, then you’re finished. However, if you also want to enable remote logins for MySQL, read on.

Enable remote Ccnnections for MySQL

The following instructions enable remote connections for MySQL. In addition, these remote users need to have certificate files signed by your certificate authority (CA) to connect.

This example denotes (by using three asterisks: ***) the setting for forcing the CA signature if you want to allow remote connections without the CA.

Use your preferred command line text editor to edit the /etc/my.cnf file again:
```
vim /etc/my.cnf
```
or
```
nano /etc/my.cnf
```

Include the following at the bottom of the [mysqld] section:

bind-address = *
require_secure_transport = ON 		***

Save and quit the file using :wq.
Restart MySQL. Confirm MySQL is running again. You need to log into the MySQL shell again.

AFter you log in, create the user by running one of the following commands:

CREATE USER 'example'@'%' IDENTIFIED BY 'password';

CREATE USER 'example'@'%' IDENTIFIED BY 'password' REQUIRE X509; 	***

After you grant this user the permissions to the databases you want them to have permissions to, flush the privileges to update MySQL permissions from within the shell:
```
flush privileges;
```

You might have noticed that the % located after the @ symbol in the preceding create user command is what actually denotes the user as a remote user.

As a bonus, if you have a user that accesses MySQL from only a static IP address, you can enter that IP address in place of the % to allow that user to access MySQL from their specific IP address. However, note that the user can access the database from only that IP address. If their IP address changes, they no longer have access. That is why most admins use the general remote setting of % instead.

You’ve now enabled SSL connections as well as remote access to your MySQL database.

Cloud Servers