Enable Remote Connections For MySQL on Centos 7

In this article, we want to teach you How To Enable Remote Connections For MySQL on Centos 7.

MySQL is an Oracle-backed open-source relational database management system (RDBMS) based on Structured Query Language (SQL). MySQL runs on virtually all platforms, including Linux, UNIX, and Windows.

Although it can be used in a wide range of applications, MySQL is most often associated with web applications and online publishing.

In this guide, you will learn to enable remote connections for clients who use SSL.

what you read in this post?

How To Enable Remote Connections For MySQL on Centos 7

How To Enable Remote Connections For MySQL on Centos 7

Before you start to enable the remote connections for your MySQL you need some requirements first.

Requirements

First, you need to have MySQL installed on your server. To do this, you can follow our article How To Install MySQL on Centos 7.

Also, you need to secure your MySQL connection with SSL. To do this, you can follow our article Secure MySQL Connections with SSL on Centos 7.

In these articles, you will enable SSL for the MySQL server, and local connections are forced to use SSL.

Now you can follow the steps below to enable the remote connections for MySQL, but we allow only clients who have certificate files signed by our CA to connect to the MySQL server.

Mysql Allow Remote Connections

First, you need to edit the MySQL configuration file. Open the file with your favorite text editor, here we use vi:

vi /etc/my.cnf

Add the following contents at the end of the ‘[mysqld]‘ section:

bind-address = *
require_secure_transport = ON

When you are done, save and close the file.

Then, restart the MySQL service on Centos 7 to apply the changes:

systemctl restart mysqld

Mysql Allow Remote Connections For User

Here you need to create a user for the remote connection. Here we create a user named orca and give it a password named Orca#123 and grant all the privileges to it. The orca user will only be able to connect with the Certificate key.

Connect to your MySQL console with the following command:

mysql -u root -p

Next, from your MySQL shell run the commands below to create your new user:

# create user 'orca'@'%' identified by 'Orca#123@' REQUIRE X509;
# grant all privileges on *.* to 'orca'@'%';
# flush privileges;

At this point, your new MySQL user for remote connections has been created.

Now you can test if the new user named ‘orca’ is able to connect to the MySQL server remotely with certificate files.

Three certificates for the client must be downloaded.

CA certificate – ca.pem
Client certificate – client-cert.pem
Client key – client-key.pem

Here we have logged into another Linux system and installed the mysql-client packages there.

Then we downloaded all client certificate files with SCP.

Now we will connect to the MySQL server with the certificate files:

mysql -h 10.0.15.11 -u orca \
--ssl-ca=ca.pem \
--ssl-cert=client-cert.pem \
--ssl-key=client-key.pem -p

10.0.15.11 = IP address of MySQL Server.

You can see the status of the connections from your MySQL shell on Centos 7 with the command below:

mysql> STATUS;
--------------
mysql Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id: 8
Current database:
Current user: [email protected]
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Protocol version: 10
Connection: 10.0.15.11 via TCP/IP
...

As you can see, user orca with IP address 10.0.15.10 Connected to the MySQL server with IP address 10.0.15.11, the connection via TCP/IP on port 3306 and using SSL.

When you try to connect without Certificate files, you will see the access denied error.