0

I have a set of data rolling out of Node-Red that I want to send to a remote MYSQL database. The Node-Red system is running on a Raspberry Pi. How do I make this work? I know how to it using Node.JS but im not sure how to do this in Node-Red. The IP-adress of the Pi is dynamic so simply authorizing its Ip address does not work sadly.

Thanks in advance!

EDIT for clarification:

I want to connect to a remote MYSQL database that is hosted by my webhosting. I have connected a Raspberry Pi to a battery, and I want to save this information in the aforementioned database. Since there will be several battery setups in different locations, I cannot save the data locally. So, one way or another I need to access the remote database through Node-Red. Authorizing one IP-address does't work, since the IP of the Raspberry Pi network is dynamic and thus changes. I think a SSH-Tunnel might be the solution, but I have no idea how to this in Node-Red, and google isnt very helpful.

Tom van Nes
  • 27
  • 8
  • 1
    You are going to have to explain in more detail what you are trying to achieve here. You can always set MySQL user/passwords to be accepted from any address (%) or a subnet – hardillb May 27 '18 at 18:35

1 Answers1

1

OK, so as I said in the comments you can make a Username/Password pair for MySQL can be granted permission to any IP address (which is less secure if the username/password is compromised. Set the host to '%' to allow all hosts when setting up the grant options).

To reduce the risk you can restrict the Username/Password to a specific subnet. This could be a wifi network or the subnet associated to the piblic IP (it needs to be the public range as nearly all cellular ISPs use CGNAT) range of the cellular provider you may be using. (See this question for details How to grant remote access to MySQL for a whole subnet?).

If you want to use a SSH tunnel then this will normally be done outside Node-RED with the ssh command line e.g.

ssh -L localhost:3306:localhost:3306 remote.host.com

Then configure the Node-RED MySQL node to point to localhost.

Since the connection will look like it's coming from localhost on the MySQL machine you need make sure the Username/Password is locked down to a that host.

You will probably also want to set up public/private key authentication for the ssh connection.

You may be able to run the ssh command in the node-red-daemon node, which should restart the connection if it gets dropped.

hardillb
  • 54,545
  • 11
  • 67
  • 105