Connect to Mysql database from remote

Question

Problem: I have server where is install Laravel app and database. But i want share same database with another server. And i want enable remote access.

What i tried: I changed config file /etc/mysql/mysql.conf commented out bind-address = 127.0.0.1

Also changed to bind-address = 0.0.0.0 or bind-address = *, not helps (this is what everywhere i found to do)

ufw is disabled

netstat -ltn returns

Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

isn't 127.0.0.1:3306 suppose to be 0.0.0.1:3306?

I try connect from another server

mysql -h xxx.xxx.xxx.xxx -u pf_remote -p

i got error

Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (111)

I think that port is not open, but where else i can open. Do i miss something?

DId you [grant privileges](https://easyengine.io/tutorials/mysql/remote-access/) ? — Jamie_D, Jan 19 '20 at 12:55
@Jamie_D yes i grant ``` GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;``` and `FLUSH PRIVILEGES;` — napalias, Jan 19 '20 at 13:02

score 1 · Answer 1 · answered Jan 19 '20 at 13:11

1

Find mysql.conf and set

bind-address=0.0.0.0

Then login to MySQL and run below 2 commands

GRANT ALL PRIVILEGES ON *.* TO 'pf_remote'@'%' IDENTIFIED BY 'your_password' with grant option;
flush privileges;

then

sudo service mysql restart

Then also make sure you do not have any firewall blocking connections, check by

sudo ufw status

If its enabled, then allow remote connections to MySQL port

sudo ufw allow 3306 && sudo ufw reload

If you are using Amazon Web Services server then allowing connections from within shell won't work, you will have to allow connections from AWS Dashboard

answered Jan 19 '20 at 13:11

Umair Ayub

19,358
14
72
146

i tried, and after utf allow 3306 seems like port still is closed – napalias Jan 19 '20 at 13:20
Did you `sudo ufw reload`? please share output of `sudo ufw status verbose` – Umair Ayub Jan 19 '20 at 13:22
ufw show that port is active `3306 ALLOW IN Anywhere` `3306 (v6) ALLOW IN Anywhere (v6)` ``` But when i run port scan it still show that port is closed – napalias Jan 19 '20 at 13:27
Who is server provider? digitalocean? AWS? – Umair Ayub Jan 19 '20 at 13:27
neither - Flaunt7 – napalias Jan 19 '20 at 13:30

score 0 · Answer 2 · answered Jan 19 '20 at 13:04

0

Try to grant priviliges to user for remote access:

 mysql> GRANT ALL ON yourDatabase.* TO user@'1.2.3.4' IDENTIFIED BY 'yourPassword';

Also please remember to restart your mysql service after config changes:

sudo /etc/init.d/mysql restart

answered Jan 19 '20 at 13:04

deevee

1,550
15
21

You should check to see if your server is running [APF](https://www.rfxn.com/projects/advanced-policy-firewall/) or some other advanced firewall protection. – Jamie_D Jan 19 '20 at 13:52

Connect to Mysql database from remote

2 Answers2