Serverspace Black Friday
VB
November 15, 2022
Updated November 21, 2022

How to allow remote access to the MySQL server

Databases

What is MySQL and why remote access could be needed

MySQL is famous database management system. The often using case - database and other project files are stores on the same server. So, restrict access from the localhost only is normal, no needs to allow network access by security reasons. But if you have multi-server infrastructure, you must change default settings.

Requirements

To allow "outside" access to the database server you need:

  • Server under actual operation system (e.g. Ubuntu 20.04);
  • Access to the "root" system account;
  • Privileged access to the database service or possibility to change database permission.

Network access unlocking

To allow "outside" connections to the database server please do follow:

Login to your server as privileged user and ensure, which port is using by MySQL:

netstat -tlnp | grep my

pic1

As we see, main port is 3306. Let's allow it in firewall settings:

ufw allow 3306

pic2

Find the configuration file which listen address is declared:

grep -r "bind" /etc/mysql/

pic3

Replace bind-address directive value to 0.0.0.0 and check the result:

sed -i.bak -e 's/127.0.0.1/0.0.0.0/g' /etc/mysql/mysql.conf.d/mysqld.cnf
cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep bind

pic4

Restart the service:

service mysql restart

Next step is optional. You can authenticate into the database as exist user or especially create  the new one and grant him privileges:

mysql  # enter to the service
CREATE USER <username>;  # new account creation
ALTER USER <username> IDENTIFIED WITH mysql_native_password BY '<password>';  # set password
GRANT ALL ON testdb.* TO remoteuser;  # grant permissions
FLUSH PRIVILEGES;  # apply privileges

pic6

Access checking

To check the result login to remote server console and run:

mysql -h <database_server_IP> -u<login> -p

Then type or paste your database password to "fall" into the database server.

pic7

Now you can e.g. look at the database content:

show databases;
show tables in <database_name>;
select * from <database_name>.<table_name>;

pic8

Summary

After this article reading you knew how to access outside connections to your MySQL server and manage your data remotely.

Vote:
5 out of 5
Аverage rating : 5
Rated by: 1
1101 CT Amsterdam The Netherlands, Herikerbergweg 292
+31 20 262-58-98
700 300
ITGLOBAL.COM NL
700 300
We use cookies to make your experience on the Serverspace better. By continuing to browse our website, you agree to our
Use of Cookies and Privacy Policy.