How to configure Ezeelogin to use SSL for MySQL database connections on ubuntu ?

Overview: This article provides step-by-step instructions to configure Ezeelogin to use SSL for MySQL database connections on Ubuntu, ensuring secure communication between the Ezeelogin jump server and the MySQL server.

Mysql - SSL setup on Ubuntu Mysql server

Step 1. Check the Current SSL/TLS Status

Log into MySQL session

root@gateway:~# mysql -u root -p -h 127.0.0.1

Show the state of the SSL/TLS variables by typing:

mysql> SHOW VARIABLES LIKE '%ssl%';

The have_openssl and have_ssl variables are both marked as DISABLED. This means that SSL functionality has been compiled into the server, but that it is not yet enabled.

Step 2. Generate SSL/TLS Certificates and Keys

To enable SSL connections to MySQL, first we need to generate the appropriate certificate and key files

We can use the following command to generate the necessary files.

The files will be created in MySQL’s data directory, located at /var/lib/mysql

root@gateway:~# mysql_ssl_rsa_setup --uid=mysql

Check the generated files by typing:

root@gateway:~# find /var/lib/mysql -name '*.pem' -ls

output

256740 4 -rw-r--r-- 1 mysql mysql 1078 Mar 17 17:24 /var/lib/mysql/server-cert.pem
256735 4 -rw------- 1 mysql mysql 1675 Mar 17 17:24 /var/lib/mysqlsql/ca-key.pem<^>
256739 4 -rw-r--r-- 1 mysql mysql 451 Mar 17 17:24 /var/lib/mysqlsql/public_key.pem<^>
256741 4 -rw------- 1 mysql mysql 1679 Mar 17 17:24 /var/lib/mysqlsql/client-key.pem<^>
256737 4 -rw-r--r-- 1 mysql mysql 1074 Mar 17 17:24 /var/lib/mysqlsql/ca.pem<^>
256743 4 -rw-r--r-- 1 mysql mysql 1078 Mar 17 17:24 /var/lib/mysqlsql/client-cert.pem<^>
256736 4 -rw------- 1 mysql mysql 1675 Mar 17 17:24 /var/lib/mysqlsql/private_key.pem<^>
256738 4 -rw------- 1 mysql mysql 1675 Mar 17 17:24 /var/lib/mysqlsql/server-key.pem<^>

Enable SSL Connections on the MySQL Server

Restart the MySQL service

root@gateway:~# systemctl restart mysql

After restarting, open up a new MySQL session using the same command as before.

root@gateway:~# mysql -u root -p -h 127.0.0.1

Check the state of the SSL/TLS variables by typing:

mysql> SHOW VARIABLES LIKE '%ssl%';

The have_openssl and have_ssl variables read "YES" instead of "DISABLED" this time.

Check the connection details by the following command:

root@gateway:~# mysql -u ezlogin_database_username -p -h hostname or ip --ssl-ca=/var/lib/mysql/ca.pem --ssl-cert=/var/lib/mysql/client-cert.pem --ssl-key=/var/lib/mysql/client-key.pem

example :

root@gateway:~# mysql -u ezlogin_xxxx -p -h 10.11.1.11 --ssl-ca=/var/lib/mysql/ca.pem --ssl-cert=/var/lib/mysql/client-cert.pem --ssl-key=/var/lib/mysql/client-key.pem

In Case the certificate verification has been failed, refer SSL certificate failed with MYSQL SSL

mysql> \s

---------------

. . .

SSL: Cipher in use is DHE-RSA-AES256-SHA

. . .

Connection: 127.0.0.1 via TCP/IP

. . .

----------------

SSL cipher is displayed, indicating that SSL is being used to secure our connection.

Step 3. Configure ezeelogin jump server to use SSL for Mysql

Add mysql_ssl_key,mysql_ssl_cert,mysql_ssl_ca to /usr/local/etc/ezlogin/ez.conf

Edit the /usr/local/etc/ezlogin/ez.conf file add the following

root@gateway:~# vi /usr/local/etc/ezlogin/ez.conf

#Add the following

system_folder /var/www/ezlogin/
force_https no
uri_path /ezlogin/
db_host 10.10.1.11
db_port 3306
db_name ezlogin_qzms
db_user ezlogin_edcjwz
db_pass dsH)$s5xAE[QgFms
db_prefix aqvo_
cookie_encryption_key ASvs8^pnu^^X9
cookie_name lcrrfs
cookie_path /ezlogin/
www_folder /var/www/html/ezlogin/
admin_user admin
mysql_encrypt yes
mysql_ssl_key /var/lib/mysql/client-key.pem
mysql_ssl_cert /var/lib/mysql/client-cert.pem
mysql_ssl_ca /var/lib/mysql/ca.pem
mysql_ssl_capath /var/lib/mysql
mysql_ssl_verify no

Note: Make sure that you have changed db_port to 3306 & db_host to the IP Address of your host

Step 4. Change the bind-address & allow the Ezeelogin jump server user to access the database.

Edit the /etc/mysql/mysql.conf.d/mysqld.cnf & change bind-address

root@gateway:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf

Change bind-address to host ip(server ip)or 0.0.0.0

bind-address x.x.x.x (Host ip or 0.0.0.0)

Restart the MySQL service

root@gateway:~# systemctl restart mysql

You can find out Ezeelogin jump server dbname and mysql username from the ez.conf file

root@gateway:~# cat /usr/local/etc/ezlogin/ez.conf

system_folder /var/www/ezlogin/
force_https no
uri_path /ezlogin/
db_host 10.10.1.11
db_port 3306
db_name ezlogin_qzms
db_user ezlogin_edcjwz
db_pass dsH)$s5xAE[QgFms
db_prefix aqvo_
cookie_encryption_key ASvs8^pnu^^X9
cookie_name lcrrfs
cookie_path /ezlogin/
www_folder /var/www/html/ezlogin/
admin_user admin
mysql_encrypt yes
mysql_ssl_key /var/lib/mysql/client-key.pem
mysql_ssl_cert /var/lib/mysql/client-cert.pem
mysql_ssl_ca /var/lib/mysql/ca.pem
mysql_ssl_capath /var/lib/mysql
mysql_ssl_verify no