CODEWITHSUNDEEP
mysqltcp
Giselle Freude
Giselle Freude

Reputation:

Send data to a MySQL server over an internet connection

I'm a total beginner to MySQL, I'm more of a firmware specialist. I'm working on an application where I will be getting GPS coordinates from a microcontroller + cellular device and I would like some way to store the coordinates and do processing on them. I figured a database hosted on a server made the most sense, which is what has brought me to MySQL.

Basically, I'm wondering what the basic protocol is for sending data to a MySQL server over an internet connection (my device has data). Like how do I connect to the server and publish data to it?

I'm experienced with MQTT and I think I could do TCP as well but I'm looking for a protocol that is not super power-intensive and I can't use anything that requires an operating system, like a python script.

To be clear, I am NOT asking you to tell me every step for how this is done, but basically what protocol and what tools could I use? Anything you can tell me would be appreciated.

I was thinking that I could use the MySQL client C code to help write a driver that could allow me to connect to the server. I'm experienced with writing drivers and the microcontroller I'm using uses C.

Upvotes: 1

Views: 585

Answers (2)

Paul White
Paul White

Reputation: 334

Build a simple server that communicates with whatever gathered data and then use the server so send the data to MySQL with the help of MySQL connector. Building part of the protocol will quite time consuming. - nbk

If you "can't use anything that requires an operating system" you need some middleware that can run the MySQL client driver to talk to the database, you will then use MQTT to pass data between your sensor and the middleware. If you don't want to write this middleware yourself, something like Node-RED might come handy.

You certainly can reimplement the driver for your MC, though I personally would not want to waste the time on something like this when I can assemble a solution from existing components. Database protocols are typically chatty, synchronous, and sensitive to network quality, and I wouldn't want to waste my MC cycles on that when I can make middleware do that asynchronously. - mustaccio

Simply "reverse ssh port forwarding"? That can be done, I think, with a single ssh command at one (or both) end of the connection. MySQL, by default, needs the client to connect on port 3306 to the server. - rick-james

Upvotes: 0

Kondybas
Kondybas

Reputation: 696

You need no direct connection to the DB at all. Your cellular device should be able to establish tcp connection to the ipaddress/port and to send the byte-stream through the connection. It can be the dumb unidirectional protocol with losses.

You need some service that can listen on the other side, that can parse your byte-stream, can fetch the correct packets from it and then send the data to the database. Speaking frankly that service can even be written in linux shell:

nc -lk 1234 | collector.sh

where collector.sh is a script like that:

#!/bin/sh
while read LINE
do 
  # $LINE parsing and all the staff
  mysql -e "INSERT INTO mygps.nmea (lat,lon,dtime) VALUES ($LAT, $LON, $DTIME);"
done <<< /dev/stdin
####

Sure it isn't a best solution but it was really helpful for me at the very beginning. Then you can proceed the gathered data in any desired way.

Upvotes: 1

Related Questions