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=126.96.36.199/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
Join the Newsletter
Sign up for our personalized daily newsletter