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.
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
my.cnf file running the command below:
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
[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-addressexists, comment it with a leading
- If the parameter
skip-networkingexists, comment it with a leading
[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
The first thing here is to find your active zone(s), this can be done by the following command:
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
<mysql_port> with your system’s current setup:
firewall-cmd --zone=<zone> --add-port=<mysql_port>/tcp --permanent
firewall-cmd --zone=public --add-port=3306/tcp --permanent
Then reload the firewall configuration
As stated above, if it didn’t work because of
command not found errors, open the port with the command below, replacing
<mysql_port> with your system setup:
iptables -I INPUT -i <interface> -p tcp --destination-port <mysql_port> -j ACCEPT
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.
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 220.127.116.11, 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!