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
ITWorld DealPost: The best in tech deals and discounts.
Shop Tech Products at Amazon