How to configure remote and secure connections for MySQL on Ubuntu 16.04 VPS

في هذه المقالة, we will show you how to configure remote and secure connections for MySQL on Ubuntu 16.04 VPS. If you are using an unencrypted connection to connect to your remote MariaDB/MySQL server, then everyone with access to the network could monitor and analyze the data being sent or received between the client and the server. This guide should work on other Linux VPS systems as well but was tested and written for an أوبونتو 16.04 VPS.

Login to your VPS via SSH

ssh [email protected]_server

The steps in this tutorial assume that you have installed MySQL 5.7 with the default configuration.

Enable SSL Connections

Create a new local certificate authority

We will use the local certificate authority as a self-signed root certificate which we will use to sign both server and client certificates. To create a new local certificate authority file run the following commands:

sudo openssl genrsa -out /etc/mysql/ca-key.pem 2048
سودو شمود 600 /etc/mysql/ca-key.pem
sudo openssl req -new -key /etc/mysql/ca-key.pem -out /etc/mysql/ca-csr.pem -subj /CN=mysql-CA/
sudo openssl x509 -req -in /etc/mysql/ca-csr.pem -out /etc/mysql/cacert.pem -signkey /etc/mysql/ca-key.pem -days 3650
sudo echo 01 > /etc/mysql/cacert.srl

Generate Server Certificate and Key

To generate a server certificate and key issue the following commands:

sudo openssl genrsa -out /etc/mysql/server-key.pem 2048
سودو شمود 600 /etc/mysql/server-key.pem
sudo openssl req -new -key /etc/mysql/server-key.pem -out /etc/mysql/server-csr.pem -subj /CN=mysql/
sudo openssl x509 -req -in /etc/mysql/server-csr.pem -out /etc/mysql/server-cert.pem -CA /etc/mysql/cacert.pem -CAkey /etc/mysql/ca-key.pem -days 365

Generate Clients Certificate and Key

التالى, we need to generate the client certificate. Run the following commands to generate a client certificate and key:

sudo openssl genrsa -out /etc/mysql/client-key.pem 2048
سودو شمود 600 /etc/mysql/client-key.pem
sudo openssl req -new -key /etc/mysql/client-key.pem -out /etc/mysql/client-csr.pem -subj /CN=mysql/
sudo openssl x509 -req -in /etc/mysql/client-csr.pem -out /etc/mysql/client-cert.pem -CA /etc/mysql/cacert.pem -CAkey /etc/mysql/ca-key.pem -days 365

If you want to have a CA signed SSL certificate, you can purchase a trusted SSL certificate هنا.

Enable MySQL Server SSL Connections

Open the the MySQL configuration file with your text editor:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

and uncomment the following lines:

ssl-ca=/etc/mysql/cacert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

Save the file and restart the MySQL service:

sudo systemctl restart mysql 

To verify that SSL is enabled, login to the MySQL server

mysql -uroot -p

and run the following command:

mysql> show variables LIKE '%ssl%';

The output should be similar to the following one:

+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| have_openssl  | YES                        |
| have_ssl      | YES                        |
| ssl_ca        | /etc/mysql/cacert.pem      |
| ssl_capath    |                            |
| ssl_cert      | /etc/mysql/server-cert.pem |
| ssl_cipher    |                            |
| ssl_crl       |                            |
| ssl_crlpath   |                            |
| ssl_key       | /etc/mysql/server-key.pem  |
+---------------+----------------------------+
9 rows in set (0.15 sec)

Enable Remote Connections via SSL

بشكل افتراضي, MySQL only listens for connections on localhost. To enable remote connections you need to reconfigure MySQL on your server to listen on all interfaces, to do that open the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

and change

bind-address            = 127.0.0.1

إلى

bind-address            = 0.0.0.0

In order for the changes to take effect, restart the MySQL server with the following command:

sudo systemctl restart mysql 

Verify that your MySQL server listens on all interfaces:

sudo netstat -anp | grep 3306

the output should be similar to the following one:

tcp        0      0 0.0.0.0: 3306            0.0.0.0:*               LISTEN      938/mysqld

To enable your MySQL client to connect to the MySQL server, you need to grant the database user access to the database on the remote server.

For example if you want to grant access to the dbuser user to the database_name database and to force SSL, login to the MySQL shell as root and run the following command:

GRANT ALL ON database_name.* TO [email protected] IDENTIFIED BY 'dbuserpassword' REQUIRE SSL;

أين 192.168.1.10 is your MySQL client machine IP address.

التالى, you need to configure your MySQL client to use the previously generated SSL certificate. Copy the following files from your MySQL server to your MySQL client machine:

/etc/mysql/cacert.pem
/etc/mysql/client-cert.pem
/etc/mysql/client-key.pem

open your MySQL client configuration and add the following lines:

[client]
ssl-ca=/etc/mysql/cacert.pem
ssl-cert=/etc/mysql/client-cert.pem
ssl-key=/etc/mysql/client-key.pem

You can now test your connection from the client machine to the MySQL database server:

mysql -udbuser -pdbuserpassword -h 192.168.1.5

أين 192.168.1.5 is your MySQL database server IP address.

هذا هو. You have successfully configured remote and secure connections for MySQL on your Ubuntu 16.04 VPS. For more information about MySQL secure connections, please refer to the official MySQL documentation.

مصدر

اترك رد

يستخدم هذا الموقع أكيسمت للحد من البريد المزعج. تعلم كيفية معالجة البيانات تعليقك.

نحن نستخدم الكوكيز لتعطيك أفضل تجربة على الانترنت. من خلال الموافقة قبولك استخدام ملفات تعريف الارتباط وفقا لسياسة الكوكيز.