How to allow remote access to MySQL database server

This article explains how to set up and allow users to have remote access to your MySQL database server.

By default, the MySQL server listens to the connections requested from localhost, so applications running on the same host can only access it.

However, in some situations, MySQL server can also be configured to allow access from remote location.

MySQL by default runs on port 3306,

Configuring MySQL Server

• MySQL server to can listen for a specific IP address or all IP addresses on the machine.
• MySQL server to can listen for all IP addresses on the machine.

The MySQL server and clients can communicate with each other over:

• A local or private network, then MySQL server can be configured to listen only for the local or private IPs.
• A public network, allowing MySQL server to listen on all IP addresses on the machine.

Configuring a centralized MySQL database within your local network is much simpler tasks than it seems to be. It can be easily setup on either a workstation, desktop, laptop, or a any headless server.

First check status for mysql service with below command

sudo systemctl status mysql.service

Open up MySQL configuration file within your editor, here we’ll be using nano. The location of the file differs, depending on the distribution you’re using. In Ubuntu and Debian the file is located at /etc/mysql/mysql.conf.d/mysqld.cnf, while in Red Hat based distributions, the file is located at /etc/my.cnf.

Execute below command to edit your file for Ubuntu.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Execute below command to edit your file for Red Hat based distributions

sudo nano /etc/my.cnf

Search for a line that begins with bind-address directive. By default, the value will be 127.0.0.1 accepting request from only localhost.

In this example, we’ll change the value to 0.0.0.0 allowing the MySQL server to listen on all IPv4 interfaces

bind-address = 0.0.0.0
# skip-networking

If there is a line containing skip-networking, comment it out by adding # at the beginning of the line.

The bind-address directive may not be present, in MySQL 8.0 and higher versions, we’ve explicitly add in this case, under the [mysqld] section.

Granting Access to a User from a Remote Machine

Log in to the MySQL server as the root user with below command:

sudo mysql

Use the GRANT statement to grant access for the remote user, within the MySQL shell with below command.

GRANT ALL ON database_name.* TO user_name@'ip_address' IDENTIFIED BY 'user_password';

• database_name is the database name that the user will connect to.
• user_name is the name of the MySQL user through which user will connect to.
• ip_address is the IP address from which the user will connect. The % will allow the user to connect from any IP address.
• user_password is the user password.

GRANT ALL ON databaseName.* TO meshworld@'192.168.1.24' IDENTIFIED BY 'mesh-world2020';

Configuring Firewall

We’ve can configure firewall to allow traffic on port 3306 (default port for MySQL) from any IP address with below command. UFW is the default firewall tool in Ubuntu.

sudo ufw allow 3306

Now after all these changes we’ve to restart the MySQL service to take into effect. Only users with sudo privileges or root user can restart services.

To restart the MySQL service on Debian or Ubuntu:

sudo systemctl restart mysql

To restart the MySQL service on RedHat based distributions:

sudo systemctl restart mysqld

Now try accessing your database remotely from another machine:

mysql -u user_name -h database_server_ip_address -p

If you’re still unable to access the database remotely, then something else may be causing the issue. We will soon write about How To Set Up a Remote Database with MySQL on Ubuntu