Remote Connections Mysql Ubuntu

Question

For some reason, I've been unable to connect remotely to my MySQL server. I've tried everything and I'm still getting errors.

root@server1:/home/administrator# mysql -u monty -p -h www.ganganadores.cl
Enter password:
ERROR 1045 (28000): Access denied for user 'monty'@'server1.ganganadores.cl' (using password: YES)

Now, I've tried running

 GRANT ALL ON *.* to monty@localhost IDENTIFIED BY 'XXXXX'; 
 GRANT ALL ON *.* to monty@'%' IDENTIFIED BY 'XXXXXX';`

and still nothing! What I'm doing wrong?

EDIT: my.cnf has commented out the bind ip .

what does this command return? ubuntu~$ sudo lsof -i -P | grep :3306 — apesa, Mar 28 '13 at 13:38

score 359 · Accepted Answer · edited Feb 20 '21 at 18:02

359

To expose MySQL to anything other than localhost you will have to have the following line

For mysql version 5.6 and below

uncommented in /etc/mysql/my.cnf and assigned to your computers IP address and not loopback

For mysql version 5.7 and above

uncommented in /etc/mysql/mysql.conf.d/mysqld.cnf and assigned to your computers IP address and not loopback

#Replace xxx with your IP Address 
bind-address        = xxx.xxx.xxx.xxx

Or add a bind-address = 0.0.0.0 if you don't want to specify the IP

Then stop and restart MySQL with the new my.cnf entry. Once running go to the terminal and enter the following command.

lsof -i -P | grep :3306

That should come back something like this with your actual IP in the xxx's

mysqld  1046  mysql  10u  IPv4  5203  0t0  TCP  xxx.xxx.xxx.xxx:3306 (LISTEN)

If the above statement returns correctly you will then be able to accept remote users. However for a remote user to connect with the correct priveleges you need to have that user created in both the localhost and '%' as in.

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

then,

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';

and finally,

FLUSH PRIVILEGES; 
EXIT;

If you don't have the same user created as above, when you logon locally you may inherit base localhost privileges and have access issues. If you want to restrict the access myuser has then you would need to read up on the GRANT statement syntax HERE If you get through all this and still have issues post some additional error output and the my.cnf appropriate lines.

NOTE: If lsof does not return or is not found you can install it HERE based on your Linux distribution. You do not need lsof to make things work, but it is extremely handy when things are not working as expected.

UPDATE: If even after adding/changing the bind-address in my.cnf did not work, then go and change it in the place it was originally declared:

/etc/mysql/mariadb.conf.d/50-server.cnf

edited Feb 20 '21 at 18:02

Avik

371
4
15

answered Mar 28 '13 at 14:23

apesa

12,163
6
38
43

1

Thanks, even though I've followed this steps correctly the first time, I did them again as you posted and worked like a charm! – Cristian Eduardo Lehuede Lyon Mar 28 '13 at 20:00
15

For anyone else struggling with `lsof -i -P | grep :3306`, that did not work but remote connection still works fine without that confirmation, disregard if you don't see the proper output. – Mike S Jan 09 '15 at 16:01
@Mike Slutsky Thanks for the note, I edited my answer. You can install lsof if it is not already installed. – apesa Jan 09 '15 at 22:01
After all the commands, we should reload the privileged using this command: ~> FLUSH PRIVILEGES; – ben Feb 07 '15 at 10:57
After type `lsof -i -P | grep :3306` I just only see `mysql-wor 3226 varick 15u IPv4 51066 0t0 TCP EDNAWS-2.local:49978->EDNAWS-2.local:3306 (CLOSE_WAIT)` but nothing about my IP. Is that problem? – gamo Jul 10 '15 at 03:26
mysql-wor is MySQL Workbench not the MySQL daemon which would show up as mysqld. Try restarting MySQL – apesa Jul 10 '15 at 04:38
Thanks. In addition, to stop and start mysql on Ubuntu is the command: sudo service mysql restart – Henry May 27 '16 at 08:47
Found a detailed review on http://preprogrammer.com/remote-connections-mysql-ubuntu/ – Jun 10 '16 at 09:03
3

FWIW on many systems `sudo lsof -i -P` will be much more useful than `lsof -i -P` (assuming you're logged in as an unprivileged user). – Dan Passaro Dec 06 '16 at 18:39
Hmm, I followed the steps here, but I'm still unable to access the mysql database from a computer on the same network. Oddly, I can connect to it using telnet (it asks for authentication and then exits). Mysql is bound to an IP, it's running, and iptables is accepting traffic on port 3306. Is it a good idea to grant access to all like that? I've been restricting it to the particular database that needs to be accessed. – Sophie Jun 15 '17 at 00:05
You will have to grant users correct access on any remote database you want to connect to. If you're having problems connecting refer to the MySQL Logs and post the error you're getting. Thanks and good luck. – apesa Jun 15 '17 at 14:33
2

`lsof -i -P | grep :3306` doesn't return anything! – Green Jul 13 '17 at 09:59
If it comes back empty and you in fact have lsof installed then mysqld is not running on 3306, may not be running at all. Can you describe your problem in a couple sentences? – apesa Jul 14 '17 at 19:05
For Ubuntu 16.04 you may have to open the firewall with `sudo ufw allow 3306` – AmigaAbattoir Jul 07 '18 at 19:49
This is neat, I can't believe I've spent the entire day to make this work on a MYSQL server inside Google Compute Engine. If you are there, you have to create a rule in firewall manager, inside network menus, set your remote computer IP to have access to it. – Paulo Henrique Apr 04 '19 at 00:53

score 41 · Answer 2 · edited Jan 30 '20 at 21:34

41

Add few points on top of apesa's excellent post:

1) You can use command below to check the ip address mysql server is listening

netstat -nlt | grep 3306

sample result:

tcp 0  0  xxx.xxx.xxx.xxx:3306  0.0.0.0:*   LISTEN

2) Use FLUSH PRIVILEGES to force grant tables to be loaded if for some reason the changes not take effective immediately

GRANT ALL ON *.* TO 'user'@'localhost' IDENTIFIED BY 'passwd' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'user'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION;
FLUSH PRIVILEGES; 
EXIT;

user == the user u use to connect to mysql ex.root
passwd == the password u use to connect to mysql with

3) If netfilter firewall is enabled (sudo ufw enable) on mysql server machine, do the following to open port 3306 for remote access:

sudo ufw allow 3306

check status using

sudo ufw status

4) Once a remote connection is established, it can be verified in either client or server machine using commands

netstat -an | grep 3306
netstat -an | grep -i established

edited Jan 30 '20 at 21:34

ctf0

6,991
5
37
46

answered May 05 '15 at 06:13

Jonathan L

9,552
4
49
38

3

This saved my whole day! Thanks for `sudo ufw status`!! – Riddhiman Adib Mar 29 '17 at 06:46
`netstat -nlt | grep 3306` returns empty – João Pimentel Ferreira Mar 10 '18 at 14:12
@João Pimentel Ferreira make sure mysql is running first, use command "/etc/init.d/mysql status" to verify – Jonathan L Mar 13 '18 at 03:28
Thanks for `sudo ufw allow 3306` – KrmX Feb 01 '21 at 16:25

score 8 · Answer 3 · answered Sep 20 '17 at 09:13

8

MySQL only listens to localhost, if we want to enable the remote access to it, then we need to made some changes in my.cnf file:

sudo nano /etc/mysql/my.cnf

We need to comment out the bind-address and skip-external-locking lines:

#bind-address = 127.0.0.1
# skip-external-locking

After making these changes, we need to restart the mysql service:

sudo service mysql restart

answered Sep 20 '17 at 09:13

Vinh Lee

101
1
3

1

make sure to comment `skip-external-locking` – calebeaires Apr 24 '18 at 18:41
1

I didn't comment out skip-external-locking and it still works. – Marcia Ong Jun 07 '18 at 09:34
I had to change it in /etc/mysql/mysql.conf.d – Bartando May 13 '19 at 16:53
mine is this location:`sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf` – Lester Jan 18 '20 at 15:42

score 2 · Answer 4 · answered Aug 05 '20 at 12:39

You are using ubuntu 12 (quite old one)

First, Open the /etc/mysql/mysql.conf.d/mysqld.cnf file (/etc/mysql/my.cnf in Ubuntu 14.04 and earlier versions

Under the [mysqld] Locate the Line, bind-address = 127.0.0.1 And change it to, bind-address = 0.0.0.0 or comment it

Then, Restart the Ubuntu MysQL Server systemctl restart mysql.service

Now Ubuntu Server will allow remote access to the MySQL Server, But still you need to configure MySQL users to allow access from any host.

User must be 'username'@'%' with all the required grants

To make sure that, MySQL server listens on all interfaces, run the netstat command as follows.

netstat -tulnp | grep mysql

Hope this works !

In my case, i was "bind-address = 0.0.0.0" and restart mysql service. — javed, Jul 27 '21 at 14:00

score 1 · Answer 5 · answered Sep 30 '17 at 11:58

1

If testing on Windows, don't forget to open port 3306.

answered Sep 30 '17 at 11:58

marw

2,939
4
28
37

Ah you're a genius, this is SUPER important if you're using the Windows subsystem for Linux, you need to open port 3306 for inbound traffic through the Windows firewall, and none of the above things to check if MySQL is listening work with the subsystem – Brian Leishman Aug 25 '18 at 21:44

score 0 · Answer 6 · answered Jan 24 '21 at 11:53

0

In my case I was using MySql Server version: 8.0.22

I had to add

bind-address        = 0.0.0.0

and change this line to be

mysqlx-bind-address = 0.0.0.0

in file at /etc/mysql/mysql.conf.d

then restart MySQL by running

sudo service mysql restart

answered Jan 24 '21 at 11:53

Karim Samir

1,470
17
17

score 0 · Answer 7 · answered Feb 20 '21 at 16:19

I was facing the same problem when I was trying to connect Mysql to a Remote Server. I had found out that I had to change the bind-address to the current private IP address of the DB server. But when I was trying to add the bind-address =0.0.0.0 line in my.cnf file, it was not understanding the line when I tried to create a DB.

Upon searching, I found out the original place where bind-address was declared.

The actual declaration is in : /etc/mysql/mariadb.conf.d/50-server.cnf

Therefore I changed the bind-address directly there and then all seems working.

Remote Connections Mysql Ubuntu

7 Answers7

Linked

Related