configure jump server to use SSL for MySQL

How to configure ezeelogin jump server to use SSL for MySQL database connections on centos ?

Mysql-SSL setup on Centos 7,mysql server 5.5 version

1. Check the Current SSL/TLS Status

   Log into a MySQL session 

[email protected]:~# mysql -u root -p -h 127.0.0.1

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

mysql> SHOW VARIABLES LIKE '%ssl%';

Output
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_crl       |          |
| ssl_crlpath   |          |
| ssl_key       |          |
+---------------+----------+
9 rows in set (0.01 sec)

 

  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.

 

2. Generate SSL/TLS Certificates and Keys

  Create clean environment

[email protected]:~# mkdir /etc/certs && cd /etc/certs

  Create CA certificate

[email protected]:~#openssl genrsa 2048 > ca-key.pem


[email protected]:~#openssl req -new -x509 -nodes -days 3600 \
-key ca-key.pem -out ca.pem


 Create server certificate, remove passphrase, and sign it

[email protected]:~#openssl req -newkey rsa:2048 -days 3600 \
-nodes -keyout server-key.pem -out server-req.pem


[email protected]:~#openssl rsa -in server-key.pem -out server-key.pem


[email protected]:~#openssl x509 -req -in server-req.pem -days 3600 \
-CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Create client certificate, remove passphrase, and sign it

[email protected]:~#openssl req -newkey rsa:2048 -days 3600 \
-nodes -keyout client-key.pem -out client-req.pem

[email protected]:~#openssl rsa -in client-key.pem -out client-key.pem


[email protected]:~#openssl x509 -req -in client-req.pem -days 3600 \

-CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem


After generating the certificates, verify them: 

[email protected]:~#  openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

output

server-cert.pem: OK
client-cert.pem: Ok

 

Enable SSL for MySQL

We have to edit the MySQL configuration file  '/etc/my.cnf'

In the '[mysqld]' section, paste the configuration below.

 

[email protected]:~# vi /etc/my.cnf

 

ssl-ca=/etc/certs/ca.pem

ssl-cert=/etc/certs/server-cert.pem

ssl-key=/etc/certs/server-key.pem

 

Restart the MySQL service

[email protected]:~# systemctl restart mysql

 

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

[email protected]:~# mysql -u root -p -h 127.0.0.1

 

Check state of the SSL/TLS variables by typing:

 

mysql> SHOW VARIABLES LIKE '%ssl%';

Output
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| have_openssl  | YES            |
| have_ssl      | YES            |
| ssl_ca        | Ca.pem         |
| ssl_capath    |                |
| ssl_cert      | server-cert.pem|
| ssl_cipher    |                |
| ssl_crl       |                |
| ssl_crlpath   |                |
| ssl_key       | server-key.pem |
+---------------+----------------+
9 rows in set (0.01 sec)

 

 

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

 

Check the connection details by:

 

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.

 

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

 

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

 

[email protected]:~# 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 /etc/certs/client-key.pem
mysql_ssl_cert /etc/certs/client-cert.pem
mysql_ssl_ca /etc/certs/ca.pem
mysql_ssl_capath /etc/certs/
mysql_ssl_cipher DHE-RSA-AES256-SHA
mysql_ssl_verify no

 

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

 

 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 

 

[email protected]:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf

 

Change bind-address to host ip(server ip)

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

 

  Restart the MySQL service

[email protected]:~# systemctl restart mariadb

  you can find out ezeelogin jump server dbname and ezeelogin mysql username from the ez.conf file

 

[email protected]:~# 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 /etc/certs/client-key.pem
mysql_ssl_cert /etc/certs/client-cert.pem
mysql_ssl_ca /etc/certs/ca.pem
mysql_ssl_capath /etc/certs/
mysql_ssl_cipher DHE-RSA-AES256-SHA
mysql_ssl_verify no

 
Login to mysql 

[email protected]:~# mysql -u root -p 

[Enter password]

mysql> grant all on ezlogin_databasename.* to 'mysql_username'@'%' identified by 'password';

example : mysql > grant all on ezlogin_xxx.* to 'ezlogin_xxxx'@'%' identified by 'dsH)$s5xAE[QgFmfsfgg';

mysql > flush privileges;

mysql > exit 

 Check if you can login to mysql using ezeelogin databases

 

[email protected]:~# mysql -u ezeelogin_databasename -h 10.11.1.11 -p

Enter Password:

mysql >

mysql > exit

 

If you have any difficulties please contact support 

 

 

 

0 (0)
Article Rating (No Votes)
Rate this article
    Attached Files
    There are no attachments for this article.
    Related Articles RSS Feed
    How to reset cluster keys in ezeelogin Master-slave Configuration ?
    Viewed 661 times since Fri, Jun 8, 2018
    Configure Nginx webserver on Jump server / Bastion host
    Viewed 5843 times since Fri, Nov 3, 2017
    How do i configure Ezeelogin to authenticate using OpenLdap(Pam-Ldap) in centos ?
    Viewed 3156 times since Mon, Feb 5, 2018
    Add a server using ssh key pair in ezeelogin
    Viewed 389 times since Fri, Mar 22, 2019
    Setting in web panel when SSH Daemon is listening on non standard ports.
    Viewed 1450 times since Thu, Dec 21, 2017
    How can i disable MySQL strict mode ?
    Viewed 697 times since Tue, Feb 12, 2019
    Setting session time out for the webinterface
    Viewed 2487 times since Wed, Jun 14, 2017
    How do i change the web url / uri of my installation?
    Viewed 1607 times since Thu, Jun 15, 2017
    Setting to be changed when sshd is listening on a custom port instead of default port in the ssh jumphost server
    Viewed 1221 times since Sat, Mar 10, 2018
    How to enable google 2FA (Two factor Authentication) in ezeelogin ?
    Viewed 2935 times since Mon, Oct 9, 2017