By default, the MySQL server listens for connections only from localhost, which means it can be accessed only by applications running on the same host.
However, in some situations, it is necessary to access the MySQL server from remote location. For example, when you want to connect to the remote MySQL server from your local system, or when using a multi-server deployment where the application is running on a different machine from the database server. One option would be to access the MySQL server through SSH Tunnel and another is to configure the MySQL server to accept remote connections.
In this guide, we will go through the steps necessary to allow remote connections to a MySQL server. The same instructions apply for MariaDB.
Configuring MySQL Server
The first step is to set the MySQL server to listen on a specific IP address or all IP addresses on the machine.
If the MySQL server and clients can communicate with each other over a private network, then the best option is to set the MySQL server to listen only on the private IP. Otherwise, if you want to connect to the server over a public network set the MySQL server to listen on all IP addresses on the machine.
To do so, you need to edit the MySQL configuration file and add or change the value of the
bind-address option. You can set a single IP address and IP ranges. If the address is
0.0.0.0, the MySQL server accepts connections on all host IPv4 interfaces. If you have IPv6 configured on your system, then instead of
The location of the MySQL configuration file differs depending on the distribution. In Ubuntu and Debian the file is located at
/etc/mysql/mysql.conf.d/mysqld.cnf, while in Red Hat based distributions such as CentOS, the file is located at
Open the file with your text editor:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Search for a line that begins with
bind-address and set its value to the IP address on which a MySQL server should listen.
By default, the value is set to
127.0.0.1 (listens only in localhost).
In this example, we’ll set the MySQL server to listen on all IPv4 interfaces by changing the value to
bind-address = 0.0.0.0 # skip-networking
If there is a line containing
skip-networking, delete it or comment it out by adding
# at the beginning of the line.
In MySQL 8.0 and higher, the
bind-address directive may not be present. In this case, add it under the
Once done, restart the MySQL service for changes to take effect. Only root or users with sudo privileges can restart services.
To restart the MySQL service on Debian or Ubuntu, type:
sudo systemctl restart mysql
On RedHat based distributions like CentOS to restart the service run:
sudo systemctl restart mysqld
Granting Access to a User from a Remote Machine
The next step is to allow access to the database to the remote user.
Log in to the MySQL server as the root user by typing:
If you are using the old, native MySQL authentication plugin to log in as root run the command below and enter the password when prompted:
mysql -uroot -p
From inside the MySQL shell, use the
GRANT statement to grant access for the remote user.
GRANT ALL ON database_name.* TO user_name@'ip_address' IDENTIFIED BY 'user_password';
database_nameis the name of the database that the user will connect to.
user_nameis the name od the MySQL user.
ip_addressis the IP address from which the user will connect. Use
%to allow the user to connect from any IP address.
user_passwordis the user password.
For example, to grant access to a database
dbname to a user named
foo with password
my_passwd from a client machine with IP
10.8.0.5, you would run:
GRANT ALL ON dbname.* TO foo@'10.8.0.5' IDENTIFIED BY 'my_passwd';
The last step is to configure your firewall to allow traffic on port
3306 (MySQL default port) from the remote machines.
If you are using iptables as your firewall, the command bellow will allow access from any IP address on the Internet to the MySQL port. This is very insecure.
sudo iptables -A INPUT -p tcp --destination-port 3306 -j ACCEPT
Allow access from a specific IP address:
sudo iptables -A INPUT -s 10.8.0.5 -p tcp --destination-port 3306 -j ACCEPT
UFW is the default firewall tool in Ubuntu. To allow access from any IP address on the Internet (very insecure) run:
sudo ufw allow 3306/tcp
Allow access from a specific IP address:
sudo ufw allow from 10.8.0.5 to any port 3306
FirewallD is the default firewall management tool in CentOS. To allow access from any IP address on the Internet (very insecure) type:
sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp
To allow access from a specific IP address on a specific port, you can either create a new FirewallD zone or use a rich rule. Well create a new zone named
sudo firewall-cmd --new-zone=mysqlzone --permanent
Verifying the Changes
To verify that the remote user can connect to the MySQL server run the following command:
mysql -u user_name -h mysql_server_ip -p
user_name is the name of the user you granted access to and
mysql_server_ip is the IP address of the host where the MySQL server runs.
If everything is setup up correctly, you will be able to login to the remote MySQL server.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.8.0.5' (111)"
The error below is indicating that the user you are trying to log in doesn’t have permissions to access the remote MySQL server.
"ERROR 1130 (HY000): Host ‘10.8.0.5’ is not allowed to connect to this MySQL server"
MySQL, the most popular open-source database server by default, listens for incoming connections only on localhost.
To allow remote connections to a MySQL server, you need to perform the following steps:
- Configure the MySQL server to listen on all or a specific interface.
- Grant access to the remote user.
- Open the MySQL port in your firewall.
If you have questions, feel free to leave a comment below.