How to connect to MySQL database using IP not "localhost"?

Question

If I have a node js server on my Raspberry pi. And a MySQL database on my laptop. Both the raspberry pi and my laptop are connected to the same LAN.

Laptop IP: 192.168.1.2

Raspberry pi IP: 192.168.1.3

Part 1, How can I send data from the raspberry pi server to the MySQL database on my laptop?

At first I thought that all I have to do is to change the host field from "localhost" to "192.168.1.2", but this does not seem to work because:

I created a node js server on my laptop (as I do not have a raspberry pi right now) and tried to connect to the database on it to store data, It worked perfectly. But when I tried to change the host field from "localhost" to "192.168.1.2" which is the laptop address on the LAN it failed to connect and threw an error:

ER_HOST_NOT_PRIVILEGED: Host 'Anwar-PC' is not allowed to connect to this MySQL server

The code:

var connection = mysql.createConnection({
  host: 'localhost',
  user: 'user1',
  password: 'password1',
  database: 'database1'
});

And I created a user by doing the following:

drop user user1@localhost;
flush privileges;
create user user1@localhost identified by 'password1';
GRANT USAGE ON *.* to user1@localhost;
GRANT ALL ON database1.* TO user1@localhost;

Part 2, I would like to do the same scenario if both the raspberry pi and the database are connected to the Internet rather than LAN.

I hope that I explained the problem correctly. Thanks in advance.

You should improve your question in a way that makes it better understandable. If I'm not wrong you want to know where to change the MySQL server address in your Node js server setup, correct? This has nothing to do with MySQL Workbench. — Mike Lischke, Feb 22 '17 at 09:27
@MikeLischke I edited the question, I hope it is better now. Thank you — A.Kallas, Feb 22 '17 at 23:48
What's the exact error message you got? It could be that you have no user that is allowed to connect from your IP address (which is not used when you connect via localhost). See also https://dev.mysql.com/doc/refman/5.7/en/create-user.html — Mike Lischke, Feb 23 '17 at 08:55
@MikeLischke I did create a user and used GRANT ALL with it, still the same error: ER_HOST_NOT_PRIVILEGED: Host 'Anwar-PC' is not allowed to connect to this MySQL server — A.Kallas, Feb 23 '17 at 09:47

score 1 · Accepted Answer · edited May 23 '17 at 12:00

1

For Part 1: I checked the following question and the problem was solved

Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server.

I think it will work the same way when using a raspberry pi.

edited May 23 '17 at 12:00

Community

1
1

answered Feb 23 '17 at 10:19

A.Kallas

75
1
10

Part 2 is a bit more tricky since you would have to open your MySQL installation to the internet. Better use an SSH tunnel instead to secure the connection. You should then also be able to connect like you did in LAN (if both SSH and MySQL server run on the same box you can even use localhost then). – Mike Lischke Feb 23 '17 at 10:38
@MikeLischke Thank you so much for your help, I would appreciate if you recommend any resource to learn about using SSH tunnel. – A.Kallas Feb 23 '17 at 11:08
Don't have any at hand, but your preferred search engine should come up with links :-D – Mike Lischke Feb 23 '17 at 11:37

score -1 · Answer 2 · answered Feb 21 '17 at 09:20

-1

Instead of localhost give the IP using cli

shell> mysql --host=192.168.1.2 --user=myname --password=mypass mydb OR

shell> mysql -h 192.168.1.2 -u myname -pmypass mydb

refer here

answered Feb 21 '17 at 09:20

Yaman Jain

1,254
11
16

How to connect to MySQL database using IP not "localhost"?

2 Answers2