Im trying to use node-red-node-mysql to connect to MariaDB on the same server using localhost.
The node does not connect, but gives the error:
Error: connect ECONNREFUSED 127.0.0.1:3306
I'm sure I have missed something simple, but I have been searching for a while now.
I have tried with root user and also created a user called node-red
I have set plugin=mysql_native_password on the node-red user
I found what I think is the config file under: /etc/mysql/mariadb.conf.d/50-server.cnf
# * Basic Settings
user = mysql
pid-file = /run/mysqld/mysqld.pid
socket = /run/mysqld/mysqld.sock
#port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
Some people had problems with the bind address when connecting from a remote machine, but my node-red server and the db are on the same machine, so am I correct in assuming I can leave it as is?
Server version: MariaDB version 10.3.34
How did you install node-red? Is it running in Docker for example? If so then it does not have direct access to localhost.
Can you access the database using a different client (mysql command line for example, or the equivalent for mariadb)?
Hi Colin. Thanks for getting back to me so quickly.
Node-Red and Mariadb are both running locally on a Dell Optiplex with Ubuntu 20. I am not using Docker. I installed using the raspberry Pi install script from the getting started page of node-red docs.
I am working remotely, over a VPN from a windows machine.
I can browse to the node-red flows editor using chrome.
I am able to access the db using the command line (via putty ssh).
Here is the output of some command line interactions with the db. You may recognise some table names from the getting started section of MariaDB docs. There is nothing important in the DB at this stage:
michael@linbox01:~$ mysql -u node_red -p
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 10.3.34-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use tests;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
MariaDB [tests]> show tables;
| Tables_in_tests |
| authors |
| books |
| series |
3 rows in set (0.001 sec)
output from inxi:
System: Kernel: 5.13.0-51-generic x86_64 bits: 64 compiler: N/A Console: tty 1 Distro: Ubuntu 20.04.4 LTS (Focal Fossa)
Machine: Type: Desktop System: Dell product: OptiPlex 9020 v: 00 serial: <filter>
Mobo: Dell model: 00V62H v: A01 serial: <filter> BIOS: Dell v: A20 date: 05/23/2017
CPU: Topology: Quad Core model: Intel Core i7-4790 bits: 64 type: MT MCP arch: Haswell rev: 3 L2 cache: 8192 KiB
flags: avx avx2 lm nx pae sse sse2 sse3 sse4_1 sse4_2 ssse3 vmx bogomips: 57465
Speed: 802 MHz min/max: 800/4000 MHz Core speeds (MHz): 1: 937 2: 1171 3: 1016 4: 799 5: 799 6: 847 7: 849 8: 799
Graphics: Device-1: Intel Xeon E3-1200 v3/4th Gen Core Processor Integrated Graphics vendor: Dell driver: i915 v: kernel
bus ID: 00:02.0
Display: server: X.org 1.20.13 driver: i915 tty: 189x49
Message: Advanced graphics data unavailable in console for root.
Audio: Device-1: Intel Xeon E3-1200 v3/4th Gen Core Processor HD Audio vendor: Dell driver: snd_hda_intel v: kernel
bus ID: 00:03.0
Device-2: Intel 8 Series/C220 Series High Definition Audio vendor: Dell driver: snd_hda_intel v: kernel
bus ID: 00:1b.0
Sound Server: ALSA v: k5.13.0-51-generic
Network: Device-1: Intel Ethernet I217-LM vendor: Dell driver: e1000e v: kernel port: f080 bus ID: 00:19.0
IF: eno1 state: up speed: 100 Mbps duplex: full mac: <filter>
Drives: Local Storage: total: 298.09 GiB used: 11.61 GiB (3.9%)
ID-1: /dev/sda vendor: Seagate model: ST320LT014-9YK142 size: 298.09 GiB temp: 25 C
RAID: Hardware-1: Intel SATA Controller [RAID mode] driver: ahci v: 3.0 bus ID: 00:1f.2
Partition: ID-1: / size: 291.92 GiB used: 11.61 GiB (4.0%) fs: ext4 dev: /dev/sda5
Sensors: System Temperatures: cpu: 23.0 C mobo: N/A
Fan Speeds (RPM): N/A
Info: Processes: 256 Uptime: 19d 15h 54m Memory: 15.53 GiB used: 914.7 MiB (5.8%) Init: systemd runlevel: 5 Compilers:
gcc: 9.4.0 Shell: bash v: 5.0.17 inxi: 3.0.38
I have the bind address set to 0.0.0.0
If the address is
0.0.0.0, the MySQL server accepts connections on all host IPv4 interfaces.
I also have mysql.user entries that allow the account to be able to access the database from
Restart the server after making any changes.
may be useful to have a look at.
Thanks Random. I got it to work
The KnowledgeBase link you posed allowed me to actually find my config file:
The bind address was not my problem, as I am connecting from the same machine, so 127.0.0.1 was fine for me. However, while I was trying your suggestion, I noticed that the line in the config file that sets the port number was commented out.
#port = 3306
I have no idea why this would be the default, but I removed the #, saved the config file and rebooted.
Now magically, my node connects!
No problem - glad to have helped.
Don't know if the solutions were 2 different ways of solving the same issue (bind address or uncommenting port line), as in my config the
#port = 3306 is also present.
Anyway - you've got it working
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.