How to set up SSL for MySQL server and clients on Linux

In this tutorial, I will introduce how to use SSH connection encryption to establish a secure connection to the MySQL server, so that the data in the database is safe and hackers cannot steal the data. SSL is used to verify SSL certificates, which can prevent phishing attacks. This will also show you how to enable SSL on your MySQL server.

Enable SSL support

Connect to the MySQL server and check the 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

Generate an SSL certificate for MySQL

Create a file to store the certificate directory

# mkdir /etc/certificates
# cd /etc/certificates

Generate server certificate

# 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 certificate

# 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 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)

Create User 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 the server side, we need to copy client-cert.pem client-key.pem client-req.pem from the server to the 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

After the file is transferred to the client, it will connect to the client and attempt to connect to MySQL using an SSL certificate.

# 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 settings in the /etc/my.cnf file so that when connecting permanently 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 completing this configuration and setup, you can now connect to the MySQL server from the client using an SSL key to protect your data from being stolen and also protected from hackers.

The above is the detailed content of How to set up SSL for MySQL server and clients on Linux. For more information, please follow other related articles on the PHP Chinese website!