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
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.
bind-address directive may not be present, in MySQL 8.0 and higher versions, we’ve explicitly add in this case, under the
Granting Access to a User from a Remote Machine
Log in to the MySQL server as the root user with below command:
Use the GRANT statement to grant access for the remote user, within the MySQL shell with below command.
GRANT ALL ON database_name.* TO [email protected]'ip_address' IDENTIFIED BY 'user_password';
database_nameis the database name that the user will connect to.
user_nameis the name of the MySQL user through which user will connect to.
ip_addressis the IP address from which the user will connect. The
%will allow the user to connect from any IP address.
user_passwordis the user password.
GRANT ALL ON databaseName.* TO [email protected]'192.168.1.24' IDENTIFIED BY 'mesh-world2020';
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
- With Chrome Music Lab make Songs in your browser
- Stop websites from asking the most annoying to show notifications
- Install GitHub Desktop on Ubuntu 20.04 or Ubuntu-based distributions
- Install OBS Studio on Ubuntu 20.04 or Ubuntu-based distributions
- Install OpenShot on Ubuntu 20.04 or Ubuntu-based distributions