Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Setup SSL for MySQL Server and Client on Linux
In this tutorial, I will be explaining about – how to set up a secure connection to MySQL server using an SSH connection for encryption so that data in the database will be in safe and which is impossible for hackers to steal the data. SSL is used to verify the means of SSL certificates which can protect against phishing attacks. This will also show you – how to enable SSL on MySQL server also.
Enabling SSL Support
Connect to the MySQL server and check that SSL status of the MySQL server
# mysql -u root -p mysql> show variables like '%ssl%'; Output: +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | +---------------+----------+ 7 rows in set (0.00 sec) mysql> \q Bye
Generating SSL certificates for MySQL
Create a directory for storing the certificate files
# mkdir /etc/certificates # cd /etc/certificates
Generating Server Certificates
# openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
...................................................................................+++
..........+++
e is 65537 (0x10001)
# openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
Generating a 2048 bit RSA private key
..................+++
..............................................................................................+++
writing new private key to 'server-key.pem'
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
# openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd
Error opening CA Certificate ca-cert.pem
139991633303368:error:02001002:system library:fopen:No such file or directory:bss_file.c:398:fopen('ca-cert.pem','r')
139991633303368:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:400:
unable to load certificate
Generating client certificates
# openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
Generating a 2048 bit RSA private key
...............................................+++
.................+++
writing new private key to 'client-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:
Please enter the following 'extra' attributes openssl x509 -req -in client-req.pem -days 1000 -CA ca-# cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd
Error opening CA Certificate ca-cert.pem
140327140685640:error:02001002:system library:fopen:No such file or directory:bss_file.c:398:fopen('ca-cert.pem','r')
140327140685640:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:400:
unable to load certificate to be sent with your certificate request
A challenge password []:
An optional company name []:Now open the my.cnf file and add the certificates
# vi /etc/my.cnf [mysqld] ssl-ca=/etc/certificates/cacert.pem ssl-cert=/etc/certificates/server-cert.pem ssl-key=/etc/certificates/server-key.pem
Restart the MySQL server and Check the Certificate’s Status
#service mysqld restart #mysql -uroot -p mysql>show variables like '%ssl%'; +---------------+-----------------------------------+ | Variable_name | Value | +---------------+-----------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca |/etc/certificates/cacert.pem | | ssl_capath | | | ssl_cert | /etc/certificates/server-cert.pem | | ssl_cipher | | | ssl_key | /etc/certificates/server-key.pem | +---------------+-----------------------------------+ 7 rows in set (0.00 sec)
Creating Users with SSL access
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘ssl_user’@’%’ IDENTIFIED BY ‘password’ REQUIRE SSL; mysql> FLUSH PRIVILEGES;
Configure SSL for MySQL Client
From server side we needed to copy client-cert.pem client-key.pem client-req.pem from server to client.
# scp /etc/ certificates/client-cert.pem root@192.168.87.158:/etc/certificates # scp /etc/ certificates/client-key.pem root@192.168.87.158:/etc/certificates # scp /etc/ certificates/client-req.pem root@192.168.87.158:/etc/certificates
Once the files transferred to client connect to the client and try to connect to the MySQL using SSL certificates.
# mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -h 192.168.87.156 -u ssluser -p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.1.73 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> status -------------- mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1 Connection id: 3 Current database: Current user: root@localhost SSL: Clipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.73 Source distribution Protocol version: 10 Connection: 192.168.87.158 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 11 min 13 sec Threads: 1 Questions: 8 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.11 -------------
Later, add the settings in the /etc/my.cnf file, to permanently so that when we connect to the MySQL server we should connect using SSL.
# vi /etc/my.cnf [client] ssl-ca=/etc/certificates/ client-cert.pem ssl-cert=/etc/certificates/client-cert.pem ssl-key=/etc/certificates/client-key.pem
After this configuration and set up now you can be able to connect to MySQL server from the client side using the SSL key to protect the data from stealing data and which also secures the data from hackers.
Updated on: 20-Jan-2020
660 Views
Advertisements