# Node red on RPi refuse to connect to MySQL DB running on another RPi on local LAN

**URL:** <https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990>\
**Category:** General\
**Tags:** node-red-contrib-mssql-plus\
**Created:** [27 March 2023 15:57 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990 "2023-03-27T15:57:13Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![fritserasmus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/fritserasmus/32/274_2.png) [@fritserasmus](https://discourse.nodered.org/u/fritserasmus)\
**Post date:** [27 March 2023 15:57 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/1 "2023-03-27T15:57:13Z")

</div>

I have my Node-RED on a RPi 192.168.1.23 and MySQL on another RPi 192.168.1.24

I used to have Node-RED, MQTT server and MySQL DB server on one RPi , but it struggled so I am splitting it up to two RPi with Node-RED and MQTT running on a RPi 4B 8 GB - Works perfect with more connections now.

My next phase is to connect to a newly created DB on a separate RPi but I just cannot figure out how to change privileges to allow the connection.

My injection appears to be just fine:-

```auto
\var temperature = msg.payload.DS18B20.Temperature;
var string1 = "INSERT INTO `TestTemp`(`index`, `create_date`, `main_source`, `source`, `reading`)"; // string1:
var string2 = " VALUES (NULL, current_timestamp(),'1856',"; // string2
var string3 = "'Office',"; // string3
var string4 = temperature; // string4
var string5 = ")"; // string5
msg.topic = string1 + string2 + string3 + string4 + string5; // Concatenate strings
return msg;

```

But the connection node fails with this message:

```auto
3/27/2023, 5:48:51 PMnode: MySQL on 24
msg : string[22]
"Database not connected"
3/27/2023, 5:48:57 PMnode: MySQL_RPi
msg : error
"Error: connect ECONNREFUSED 192.168.1.24:3306"

```

The MySQL connection

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/b/7be035e8055adcd54c74238614a4ad586c380109.png)

The PHPmyAdmin where I successfully inserted data using the myPHPAdmin interface

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/6/e6226904982cf8c5d05dce435ca5357c9081308b.png)

User privileges using commandline

MariaDB [(none)]\> SELECT User,Host FROM mysql.user WHERE User='erasmi';  
+--------+--------------+  
| User | Host |  
+--------+--------------+  
| erasmi | 6mtchurchill |  
| erasmi | localhost |  
+--------+--------------+  
2 rows in set (0.006 sec)

MariaDB [(none)]\>

User privileges from phpMyAdmin

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/e/9e24ce143386ae2923d9dc141b1b8c09c878dc63.png)

I need more simplistic guidence than these detailed websites as it confuses me just more

[javascript - Node.js Error: connect ECONNREFUSED - Stack Overflow](https://stackoverflow.com/questions/14168433/node-js-error-connect-econnrefused)

> **[How to Fix ECONNREFUSED - connection refused by server Error](https://www.hostinger.com/tutorials/how-to-fix-econnrefused-connection-refused-by-server-in-filezilla)**
>
> If you're looking for a way to fix the ECONNREFUSED ECONNREFUSED - Connection refused by server error, check out our detailed tutorial now!

[https://community.progress.com/s/article/Connection-Error-ECONNREFUSED-Connection-refused-by-the-server](https://community.progress.com/s/article/Connection-Error-ECONNREFUSED-Connection-refused-by-the-server)

Running the command: sudo service mysql status

```auto
 mariadb.service - MariaDB 10.5.15 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2023-03-24 11:17:12 SAST; 3 days ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 507 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 524 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 535 ExecStartPre=/bin/sh -c [! -e /usr/bin/galera_recovery] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [$? -eq 0] && systemctl set-enviro>
    Process: 653 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 655 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 609 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 14 (limit: 1595)
        CPU: 1min 6.376s
     CGroup: /system.slice/mariadb.service
             └─609 /usr/sbin/mariadbd

Mar 24 11:17:12 MySQLPi /etc/mysql/debian-start[661]: There is no need to run mysql_upgrade again for 10.5.15-MariaDB.
Mar 24 11:17:12 MySQLPi /etc/mysql/debian-start[661]: You can use --force if you still want to run mysql_upgrade
Mar 24 11:17:12 MySQLPi /etc/mysql/debian-start[673]: Checking for insecure root accounts.
Mar 24 11:17:12 MySQLPi /etc/mysql/debian-start[679]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables
Mar 25 12:51:44 MySQLPi mariadbd[609]: 2023-03-25 12:51:44 571 [Warning] Access denied for user 'erasmi'@'localhost' (using password: YES)

```

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [27 March 2023 16:39 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/2 "2023-03-27T16:39:21Z")

</div>

Hi @fritserasmus

**ECONNREFUSED** means the port **3306** is not offering any services on the IP Address of **192.168.1.24**

- MySQL is not configured to listen on **192.168.1.24** (check **my.ini** )
- Some OS/Router level firewall setting, that actively rejects the connection.

This is quite different to **ACCESS DENIED** - this is a network level refusal to gain access to the MySQL instance

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [27 March 2023 16:43 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/3 "2023-03-27T16:43:03Z")

</div>

Have you made any changes to /etc/mysql/mariadb.conf.d/50-server.cnf to allow connections from other devices?

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [27 March 2023 16:45 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/4 "2023-03-27T16:45:39Z")

</div>

> [@jbudd](#):
>
> /etc/mysql/mariadb.conf.d/50-server.cnf

Is that the **my.ini** equivalent? - I have only ever used MySQL - but know they are almost the same thing right? 🤷‍♂️

EDIT:  
Oh hang-on **my.ini** is windows only 😅

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [27 March 2023 17:27 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/5 "2023-03-27T17:27:25Z")

</div>

I found I had to enter the IP address of the remote RPi that is accessing the dB on the main RPi.  
I couldn't get it to recognise the 'host-name'. Hope this helps?  
 ![db_access](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/5/05d792a88f2cc20b0d92ca3b5cd035e3cac8846d.png)

---

<div class="post-metadata">

**Author:** ![fritserasmus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/fritserasmus/32/274_2.png) [@fritserasmus](https://discourse.nodered.org/u/fritserasmus)\
**Post date:** [27 March 2023 18:40 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/7 "2023-03-27T18:40:30Z")

</div>

Thanks for checking,

Yes, I did.

---

<div class="post-metadata">

**Author:** ![fritserasmus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/fritserasmus/32/274_2.png) [@fritserasmus](https://discourse.nodered.org/u/fritserasmus)\
**Post date:** [27 March 2023 18:54 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/8 "2023-03-27T18:54:52Z")

</div>

@dynamicdave ,

This is how have the connection configured:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/d/fd1dc395342fd726f2088faac765635f641b8fc1.png)

Is this what you meant?

---

<div class="post-metadata">

**Author:** ![fritserasmus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/fritserasmus/32/274_2.png) [@fritserasmus](https://discourse.nodered.org/u/fritserasmus)\
**Post date:** [27 March 2023 19:00 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/9 "2023-03-27T19:00:38Z")

</div>

I still get the error:

```auto
3/27/2023, 8:59:23 PMnode: MySQL on 24
msg : string[22]
"Database not connected"
3/27/2023, 8:59:30 PMnode: MySQL_RPi
msg : error
"Error: connect ECONNREFUSED 192.168.1.24:3306"

```

Any further suggestions?

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [27 March 2023 19:12 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/10 "2023-03-27T19:12:36Z")

</div>

I think you are showing me the settings in the Node-RED mysql node.  
I was refering to the User accounts overview inside 'phpmyadmin' you showed near the start of the thread.  
I think you need an entry in the User Accounts for 192.168.1.23 in order for it to access 192.168.1.24

Although I use the command line` $sudo mysql -u root -p` to manage my users/privileges/passwords, I'm sure you can do the same thing using 'phpmyadmin' from a web browser.

For what its worth - I seem to remember I went through a lot of trial-and-error before I got things to work!!

---

<div class="post-metadata">

**Author:** ![fritserasmus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/fritserasmus/32/274_2.png) [@fritserasmus](https://discourse.nodered.org/u/fritserasmus)\
**Post date:** [27 March 2023 20:30 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/11 "2023-03-27T20:30:20Z")

</div>

> [@fritserasmus](#):
>
> MariaDB [(none)]\> SELECT User,Host FROM mysql.user WHERE User='erasmi';

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/8/4885b244a4d812572fae4d2d465284f82d86aec7.png)  
Got that now

```auto
 SELECT User,Host FROM mysql.user WHERE User='erasmi';
+--------+--------------+
| User | Host |
+--------+--------------+
| erasmi | 192.168.1.23 |
| erasmi | 6mtchurchill |
| erasmi | localhost |
+--------+--------------+
3 rows in set (0.005 sec)

```

But still no connection.

```auto
3/27/2023, 10:30:57 PMnode: MySQL_RPi
msg : error
"Error: connect ECONNREFUSED 192.168.1.24:3306"
3/27/2023, 10:31:03 PMnode: MySQL on 24
msg : string[22]
"Database not connected"

```

---

<div class="post-metadata">

**Author:** ![fritserasmus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/fritserasmus/32/274_2.png) [@fritserasmus](https://discourse.nodered.org/u/fritserasmus)\
**Post date:** [27 March 2023 20:33 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/12 "2023-03-27T20:33:12Z")

</div>

BTW:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/8/4885b244a4d812572fae4d2d465284f82d86aec7.png)  
the User name, is the user I use to log onto the DB, is that correct?

---

<div class="post-metadata">

**Author:** ![fritserasmus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/fritserasmus/32/274_2.png) [@fritserasmus](https://discourse.nodered.org/u/fritserasmus)\
**Post date:** [28 March 2023 02:30 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/13 "2023-03-28T02:30:12Z")

</div>

Just to make the feedback more complete by adding first few lines of "50-server.cnf  
":-

```auto
#
# * Basic Settings
#

user = mysql
pid-file = /run/mysqld/mysqld.pid
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
lc-messages = en_US
skip-external-locking

# Broken reverse DNS slows down connections considerably and name resolve is
# safe to skip if there are no "host by domain name" access grants
#skip-name-resolve

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.

bind-address = 192.168.1.23

```

---

<div class="post-metadata">

**Author:** ![fritserasmus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/fritserasmus/32/274_2.png) [@fritserasmus](https://discourse.nodered.org/u/fritserasmus)\
**Post date:** [28 March 2023 02:36 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/14 "2023-03-28T02:36:30Z")

</div>

I changed bind-address to 0.0.0.0 and Node-RED connected successfully.

Problem SOLVED!!

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [28 March 2023 07:25 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/15 "2023-03-28T07:25:40Z")

</div>

Pleased you got it working.

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [28 March 2023 07:39 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/16 "2023-03-28T07:39:13Z")

</div>

> [@marcus-j-davies](#):
>
> MySQL is not configured to listen on **192.168.1.24** (check **my.ini** )

Cough cough 😉

glad you got to the bottom of it!

Although, your trying to connect to **192.168.1.24** not **192.168.1.23** - I mean its working so 🤷‍♂️

You _may_ need to adjust PMA to connect to the lan address, instead of localhost - I'm not sure.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [11 April 2023 07:40 UTC](https://discourse.nodered.org/t/node-red-on-rpi-refuse-to-connect-to-mysql-db-running-on-another-rpi-on-local-lan/76990/17 "2023-04-11T07:40:15Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.