How to generate CA certificate for server & client communication?

■ Requirement : Generate CA certificate for server & client communication.
■ OS Environment : Linux
■ Application : openssl 
■ Implementation Steps :

1. Create certification authority :

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

NOTE: Last command will ask for details of certificate provider. So, provide short names

2. 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

NOTE: First command may ask for a password. Don't provide it. Just press enter key for two times.

3. 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

NOTE : Provide details of client owner who will contact server.  Client will be able to contact to server using client-cert.pem and server will consult it its server-cert.pem and approve encryption.

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