DEV Community

Tr.Ra
Tr.Ra

Posted on

setup database for remote connect in vps

beware of security
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD';
mysql> GRANT ALL PRIVILEGES ON . TO 'root'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

if there’s a firewall installed, one should open the port on the firewall;

Ubuntu:
sudo ufw allow 3306/tcp
sudo service ufw restart

Run this:

netstat -plant|grep ":3306"|grep "LISTEN"

If it comes back with something like this:

tcp 0 127.0.0.1:3306 0.0.0.0:* LISTEN 1881/mysql
This means that it’s bound to local only. To fix this, follow the appropriate guide below for your distro.

If it shows something like this:

tcp 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1881/mysql
This means it’s a firewall issue. Follow the Firewall guide below for your distro.

For 127.0.0.1 result

Ubuntu:

edit /etc/mysql/my.cnf, and look for this:
(in my case my ubuntu version is 20.* so my.cnf is /etc/mysql/mysql.conf.d/mysqld.cnf )

bind-address=127.0.0.1

and change it to this:
bind-address=0.0.0.0

Restart MySQL:
service mysql restart
And you should be good to go

Discussion (0)