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
As we see, main port is 3306. Let's allow it in firewall settings:
ufw allow 3306
Find the configuration file which listen address is declared:
grep -r "bind" /etc/mysql/
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
Restart the service:
service mysql restartNext 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
Access checking
To check the result login to remote server console and run:
mysql -h <database_server_IP> -u<login> -pThen type or paste your database password to "fall" into the database server.
Now you can e.g. look at the database content:
show databases;
show tables in <database_name>;
select * from <database_name>.<table_name>;
Summary
After this article reading you knew how to access outside connections to your MySQL server and manage your data remotely.
