How to configure remote and secure connections for MySQL on CentOS 7

install MySQL

Introduction

MySQL is an open source SQL database management system developed by Oracle Corporation.

SQL stands for Structured Query Language, which is a standardized language used to access databases. The current version of the language follows the SQL:2003 standard.

MySQL is a relational database management system (RDBMS). This means that the database stores data in separate tables, with structures organized into physical files optimized for speed. Users set up rules governing the relationships between different data fields, using SQL.

In this tutorial we will talk about how to install MySQL and configure a secure remote connection in a CentOS 7 environment.

Install MySQL

The latest stable MySQL version is 5.7, so that’s the version we will install and configure in this tutorial.

The package is the first thing you’ll need to add, and it is available in the MySQL repository. Execute the following command to get started:

# yum localinstall -y https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

This command will add the MySQL repository which can then be used to install the database system:

# yum install -y mysql-community-server

At the end of the installation process, start MySQL using systemd tools:

# systemctl start mysqld

Check MySQL status:

mysqld.service - MySQL Server
 Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
 Active: active (running) ...

MySQL listens on port 3306, as you can see by executing the netstat tool:

# netstat -plntu | grep mysql
tcp6 0 0 :::3306 :::* LISTEN 8776/mysqld

MySQL configuration

At the initial start up of the MySQL server, a superuser account ( 'root'@'localhost ) is created with a default password set and stored in the error log file. Reveal this password by executing the following command:

# grep 'temporary password' /var/log/mysqld.log

The output being:

[Note] A temporary password is generated for [email protected]: en>_g6syXIXq

The first step is to change this root password.

Log in to the MySQL shell:

# mysql -u root -p

Enter the automatically generated password that was revealed with the previous command.

Next, change the password with the following query:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'RootStrongPassword1!';

Flush privileges and exit:

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> EXIT;
Bye

Enable SSL for MySQL

By default, MySQL has its own SSL certificates, stored in /var/lib/mysql. For the purpose of this tutorial, these certificates are good enough.

Note: in production, always use more secure and “personal” certificates.

Check SSL from the MySQL shell.

# mysql -u root -p

mysql> SHOW GLOBAL VARIABLES LIKE '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.01 sec)

Check the SSL status:

mysql> STATUS;
--------------
mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper

Connection id: 4
Current database: 
Current user: [email protected]
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 27 min 25 sec

Threads: 1 Questions: 12 Slow queries: 0 Opens: 113 Flush tables: 1 Open tables: 106 Queries per second avg: 0.007
--------------

As you can see, SSL is not in use. So, the next step is to enable it.

Enable SSL in MySQL configuration file

Edit the MySQL configuration file:

# $EDITOR /etc/my.cnf

In the [mysqld]section, paste the following content:

ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem

Save, exit and restart MySQL service.

# systemctl restart mysqld

Check again the SSL status in MySQL shell.

# mysql -u root -p

mysql> STATUS;
mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper

Connection id: 5
Current database: 
Current user: [email protected]
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 min 2 sec

Threads: 1 Questions: 6 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 0.096

Enable clients

SSL is still not in use at this point. This is because we need to force all our client connections through SSL. So, exit the MySQL shell and edit the my.cnf file again.

# $EDITOR /etc/my.cnf

There, at the end of the file, paste the following content:

[client]
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/client-cert.pem
ssl-key=/var/lib/mysql/client-key.pem

Save, exit and restart MySQL once again:

# systemctl restart mysqld

Check the MySQL status as explained above:

mysql> STATUS
--------------
mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper

Connection id: 3
Current database: 
Current user: [email protected]
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 min 32 sec

Threads: 1 Questions: 6 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 0.065

SSL is now enabled and connections are secured through it.

Enable remote connections

Last step of the tutorial is to enable remote connections. As every sysadmin knows, it is fundamental to allow only verified clients.

Edit the MySQL configuration file:

# $EDITOR /etc/my.cnf

At the end of the [mysqld] section paste the following lines:

bind-address = *
require_secure_transport = ON

Save, exit and restart MySQL.

# systemctl restart mysqld

Create a new user for remote connections

At this point, SSL and remote connections are enabled. Next thing to do is to create a new MySQL user:

# mysql -u root -p

Create a new user:

mysql> CREATE USER 'gmolica'@'%' IDENTIFIED BY 'Unixmen1!' REQUIRE X509;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'gmolica'@'%' IDENTIFIED BY 'Unixmen1!' REQUIRE X509;
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Conclusion

With the previous step we concluded the MySQL configuration. Now, it is possible to log in to the database system remotely, using the credentials created. Of course, the client must have copies of the certificates in order to connect through SSL.

  • I have been having a hard time trying to configure mine.. Thanks for the solution, much appreciated.

  • Giuseppe Molica

    This is why we are here 🙂

  • Can this be accomplished with a single plugin in wordpress?