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 newcerts && cd newcerts

  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/newcerts/client-key.pem
mysql_ssl_cert /etc/newcerts/client-cert.pem
mysql_ssl_ca /etc/newcerts/ca.pem
mysql_ssl_capath /etc/newcerts/
mysql_ssl_cipher ALL:!ADH
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 mysql

  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/newcerts/client-key.pem
mysql_ssl_cert /etc/newcerts/client-cert.pem
mysql_ssl_ca /etc/newcerts/ca.pem
mysql_ssl_capath /etc/newcerts/
mysql_ssl_cipher ALL:!ADH
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.