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!
Pingback: Habilitar el Acceso remoto a MySQL, Centos 7. - gpsos.es
Pingback: Install MySQL 5.7 dan PHP 7.1 CentOS 7 - Hanya Trial
thankyou you help me to solve my server wont remote from php connection!
thankyou very much!
greetings from indonesia.
Thank you so much, this helped me!
Thanks man! my problem is solved!
me too. Thanks Bro