Friday, December 30, 2011

How to install mysql server and configure SSL with it on linux?

■ Requirement: Install mysql-server & configure SSL for secure communication
■ OS Environment : Linux
■ Application : 

  • perl-DBD-MySQL-3.0007-2.el5
  • perl-DBI-1.52-2.el5
  • mysql-server-5.0.77-4.el5_6.6
  • mysql-5.0.77-4.el5_6.6
  • mysql-5.0.77-4.el5_6.6
  • openssl

■ Symptoms encountered : 

  •  ERROR 2026 (HY000): SSL connection error

■  Implementation Steps :

1. Download all above packages & install them :  

$ yum install mysql mysql-server openssl perl-DBD-MySQL perl-DBI -y
$ rpm -ivh  

2. Start mysql service :

$ service mysqld start

4. Change mysql root password :


$/usr/bin/mysqladmin -u root password 'mysql'

5. Configure SSL for mysql server and client(who will access server) :

$ mkdir -p /etc/mysql/newcerts
$ chown -R mysql:mysql /etc/mysql/newcerts


6. Creating certificate authority :

$cd /etc/mysql/newcerts
$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem


7. Creating certificate for server using above CA certificate :

$ openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
$ openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem


8. Creating certificate for client using above CA certificate(similar like server) :

$ openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
$ openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem


9. Make sure following entries are present in /etc/my.cnf file :

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
ssl 


10. Restart mysqld & Grant mysql user to use ssl :

$service mysqld restart
$ mysql
$ GRANT ALL ON *.* TO 'mysql'@'%' IDENTIFIED BY 'mysql' REQUIRE SSL;

11. Verification / Testing :

$cd /etc/mysql/newcerts

$ mysql --ssl-cert=/etc/mysql/newcerts/ca-cert.pem --ssl-key=/etc/mysql/newcerts/client-key.pem --ssl-cert=/etc/mysql/newcerts/client-cert.pem -u root -p -v -v -v

Enter password: <<

 pw = mysql 

Output will look like below :

 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.0.77 Source distribution Reading history-file /root/.mysql_history Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 

 mysql> show variables like '%%ssl%%';

--------------
show variables like '%%ssl%%'
--------------


+---------------+-------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/mysql/newcerts/ca-cert.pem |
| ssl_capath | |
| ssl_cert | /etc/mysql/newcerts/server-cert.pem |
| ssl_cipher | |
| ssl_key | /etc/mysql/newcerts/server-key.pem |
+---------------+-------------------------------------+
7 rows in set (0.01 sec)


mysql> SHOW STATUS LIKE 'Ssl_cipher';
--------------
SHOW STATUS LIKE 'Ssl_cipher'
--------------
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| Ssl_cipher | DHE-RSA-AES256-SHA | << Confirmed +---------------+--------------------+ 1 row in set (0.00 sec) mysql>

mysql> quit

3 comments:

  1. thanks for the explanation, i have been trying to install a ssl certificates for around a week, i am at work at the moment so i will give it a go when i get home and report back.

    ReplyDelete
  2. MySql Hosting India

    IndoUs Hosting is the leading MySql Hosting companies in India. The company provides dedicated as well as shared hosting services of MySql hosting. If you are looking for guaranteed MySql Hosting India, we can be the best option for you.

    ReplyDelete
  3. Nice Article for installation of mysql while configuring SSL certificate with it. We are going to share this blog on our education platform. Thanks for sharing it on web and SSL certificate user's. Cheap SSL | Cheap WildCard SSL | Thawte SSL

    ReplyDelete