Enable MySQL Remote Access

Last Updated on 14/11/2020

This tutorial will show how to enable MySQL remote access in order to make it accept connections from the network instead of just local connections.

The Problem

A fresh MySQL installation has its remote access disabled by default for security reasons, however, it can be enabled by taking a few simple steps.
It is important to mention that the following solution was tested against CentOS 7.2, but it’s very likely to work on other Linux versions and/or distributions.
I am going straight to the solution to make your MySQL avaiable for remote access, but you can find a detailed explanation at the end of this post.

The Solution in Three Steps

This is what we are going to do:

• Bind MySQL to external interfaces
• Open MySQL firewall port
• Grant remote access to a MySQL user

1 – Bind MySQL to external interfaces

Locate your my.cnf file running the command below:

locate my.cnf

Which might print something like the following:

/etc/my.cnf
/etc/my.cnf.d
/etc/my.cnf.d/mysql-clients.cnf

Then edit the file we’ve just found:

sudo vi /etc/my.cnf

Locate the [mysqld] section in the file, which may look like the following:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
...

Do the following modifications:

• If the parameter bind-address exists, comment it with a leading #.
• If the parameter skip-networking exists, comment it with a leading #.

Example:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#bind-address=127.0.0.1
#skip-networking

Save and close the file, then restart MySQL:

service mysqld restart

2 – Open MySQL firewall port

MySQLs default port is 3306, but if you are running your MySQL installation on a different port, replace 3306 at the next steps with your current port.

If your operational system is CentOS 7 or superior, you might be able to control your firewall rules through firewall-cmd commands, but if it fails with a command not found error, move straight to the iptables command.

The first thing here is to find your active zone(s), this can be done by the following command:

firewall-cmd --get-active-zones

Which could print a single public zone or a few, like public and dmz. Run the command below for any zone shown on the screen, replacing <zone> and <mysql_port> with your system’s current setup:

firewall-cmd --zone=<zone> --add-port=<mysql_port>/tcp --permanent

Example:

firewall-cmd --zone=public --add-port=3306/tcp --permanent

Then reload the firewall configuration

firewall-cmd --reload

As stated above, if it didn’t work because of command not found errors, open the port with the command below, replacing <interface> and <mysql_port> with your system setup:

iptables -I INPUT -i <interface> -p tcp --destination-port <mysql_port> -j ACCEPT

Example:

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

3 – Grant remote access to a MySQL user

Log into your MySQL:

mysql -u root -p

Optionally, create an user to give remote access to, or just skip this step and use an existing one:

CREATE USER 'foouser'@'%' IDENTIFIED BY 'barpassword';

Grant the privileges you need to this user on the desired schemas and tables, or just grant all (strongly not recommended for production environments, select just the needed privileges instead):

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

That would be all.

The Explanation

About binding MySQL to external interfaces

We needed to bind MySQL to both localhost and a network interface, but according to the official documentation, MySQL can be bound to just one address at once in order to accept connections, this address’ value being one of the following:

If the address is *, the server accepts TCP/IP connections on all server host IPv6 and IPv4 interfaces if the server host supports IPv6, or accepts TCP/IP connections on all IPv4 addresses otherwise. Use this address to permit both IPv4 and IPv6 connections on all server interfaces. This value is the default.

If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.

If the address is ::, the server accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces.

If the address is an IPv4-mapped address, the server accepts TCP/IP connections for that address, in either IPv4 or IPv6 format. For example, if the server is bound to ::ffff:127.0.0.1, clients can connect using –host=127.0.0.1 or –host=::ffff:127.0.0.1.

If the address is a “regular” IPv4 or IPv6 address (such as 127.0.0.1 or ::1), the server accepts TCP/IP connections only for that IPv4 or IPv6 address.

Given the statments above, if we bind our MySQL directly to one of our network interfaces such as 192.160.0.10, it is going to start refusing connections from localhost! Then our best shot is to use the wildcard “*” or just disable this parameter to use its default value in order to accept connections from both localhost and all the network interfaces. Don’t worry, any security breach is covered through Firewall and MySQL user’s privileges.

About openning MySQL firewall port

Newer CentOS versions have Firewalld installed by default, in this case, we should opt to deal with firewall settings using its firewall-cmd commands, otherwise, the old iptables does the trick.

Hope it helps. See ya!