CentOS Server Security | Connecting to a remote MySQL or MariaDB Database

If you rely on MySQL Workbench for Schema design, database administration or SQL development then at some point your gonna need to connect to a remote host. Today we take a look at Connecting to a remote MySQL or MariaDB Database and some of the things that may prevent you from doing so.

1. Can’t connect to local MySQL server through socket

–ERROR 2002 (HY000)

If a remote connection isn’t being established, be it from you application or DBMS then one of the first things you should check is your firewall. One clue that this may be the issue will be error 2002 ( HY000 ) upon attempting a connection. Some persons may want to turn off the firewall altogether to bypass this but isn’t really recommended, since the firewall is an important part of your servers overall security mechanism. Instead, add a rule to firewall like the one below. This rule establishes a new firewall zone and tells it to accept traffic on DB port 3306 from any IP/IP Subnet specified.

# Mysql/MariaDB remote database access

firewall-cmd --new-zone=myremoterule --permanent
firewall-cmd --reload
firewall-cmd --permanent --zone=myremoterule --add-source=210.10.4.0/24
firewall-cmd --permanent --zone=myremoterule --add-port=3306/tcp
firewall-cmd --reload

2. Host is not allowed to connect Problem

When a database is setup, usually various user accounts are created to carry out and separate functions. MySQL offers the ability to restrict the host that each user is allowed to connect through. For example, an application living on the same webserver as the db may be restricted to connecting via localhost only as a security mechanism. Often times to allow remote connections to the database we may need to adjust the host setting for that user account.

# Check host setting for DB user: iDez
# If you only see results with localhost and 127.0.0.1, you cannot connect from an external source.

SELECT host FROM mysql.user WHERE User = 'iDez';

# Give db user:iDez remote access to databasefrom anywhere

GRANT ALL PRIVILEGES ON *.* TO 'iDez'@'%';

OK so it’s still not working and your getting frustrated…

3. SELinux: allow httpd to connect to a specific port

If Apache handles your web server requests and it’s an app that just won’t connect to the database then chances are SELinux may be the culprit here. To allow the Apache HTTP Server to communicate with MariaDB enable the following SELinux Boolean.

# Mysql/MariaDB remote database access

setsebool -P httpd_can_network_connect_db on

Let me know in the comments if any of these worked for you. Also checkout CentOS Server Security | Steps to Configuring SELinux