How to configure MySQL and MariaDB to accept remote connections

Track down connectivity issues to MySQL and MariaDB servers running on Linux.

Connection -- refused

Recently while reviewing MySQL client tools, I discovered a Linux MySQL server on my local network refusing all incoming connections.

To diagnose the problem, I first wanted to determine if MySQL was running on the machine, using the ps command to list processes. I discovered MySQL was running, but unfortunately it was running with command option known as --skip-networking enabled. Reviewing the official documentation for this option, I found that --skip-networking instructs MySQL not to listen for remote TCP/IP connections. This obviously is not ideal if MySQL is needed for use as a database server on a local network.

ps command mysql skip-networking

Because I run Slackware, I suspected that the -skip-networking option was being specified inside the /etc/rc.mysqld script. But as I've often found in similar situations diagnosing Linux issues, knowing beforehand the names of all of script files that execute commands in such a particular way becomes invaluable when tracking down problems.

Finding and fixing

So, I decided to use KDE's built-in Find Files/Folders tool, starting at root (/) to search all files (*.*) and subfolders containing "--skip-networking". After scanning, the search tool verified that rc.mysqld was indeed the only script file containing text matching this particular option.

Searching Linux for files with matching text

With a right-click, I opened rc.mysqld…

Open rc.mysqld for editing

…and quickly found the reference to "--skip-networking" and commented it out, inserting "#" at the beginning of the line.

Use # to comment --skip-networking line

Next, I edited MySQL's configuration file /etc/my.cnf to match the text listed below. I ensured bind-address was set to -- this address instructs MySQL to accept connections from any IP address on my network.

# The following options will be passed to all MySQL clients

 Then, I ran commands to check if the Linux server itself was configured to accept requests on MySQL's port 3306. I used the iptables --list command, but found no references to INPUT port 3306.

iptables --list

So, I added input port 3306, issuing another iptables command:

# iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

iptables adding port 3306

Finally, I restarted MySQL so that the new settings would take effect:

# /etc/rc.d/rc.mysqld restart

Testing the new settings

To test MySQL, I wanted to connect from a different machine on my network. So I turned to a Windows machine running the SQLyog MySQL client. I used the same username and password credentials -- along with port 3306 -- matching the options placed in the my.cnf file on my Linux machine earlier. The MySQL Host Address, is the IP address of my Linux server.

Windows MySQL client settings.

With a click of the Connect button -- all databases on the MySQL server are available for use by the database client app.

List of MySQL databases

This story, "How to configure MySQL and MariaDB to accept remote connections" was originally published by ITworld.

Copyright © 2014 IDG Communications, Inc.

Shop Tech Products at Amazon