Hilscher Forum
Using MariaDB on NetPi - Printable Version

+- Hilscher Forum (https://forum.hilscher.com)
+-- Forum: netPI - Ruggedized Raspberry Pi with Industrial Ethernet (https://forum.hilscher.com/Forum-netPI-Ruggedized-Raspberry-Pi-with-Industrial-Ethernet)
+--- Forum: Software (https://forum.hilscher.com/Forum-Software)
+--- Thread: Using MariaDB on NetPi (/Thread-Using-MariaDB-on-NetPi)

Pages: 1 2 3


Using MariaDB on NetPi - philump - January-7th-2020

Hello,

I have only recently received our Netpi and we would like to start testing our product. Please forgive me as I am quite new to docker. 

I currently have a python program that runs on my PC that records information and saves it in a MariaDB database and is replicated on Azure cloud services. How do I go about getting this to run on a Rasp Pi or Netpi. 

The steps I have completed so far on Rasp Pi.

I have installed Raspbian Buster from image as per instructions on [/url][url=https://www.raspberrypi.org/documentation/installation/installing-images/]RaspberryPi.

Completed: 


Code:
sudo apt-get update
sudo apt-get -upgrade

curl -sSL https://get.docker.com | sh

sudo docker pull hilschernetpi/netpi-raspbian

Is this the correct image to be using for development on a rasppi? in the netiot FAQ it suggests using "resin/armv7hf-debian:jessie-20170916" however this is very old and I require debian buster as it allows MariaDB 10.3 to be installed.  Also resin states 'This repository is deprecated'

Once that image is installed what is the correct way to create a container?


On the NetPi,

I have followed the instruction video on creating the first container using raspbian as an example. 

I have the container running and am able to connect through putty.

I completed the following commands inside the container


Code:
sudo apt-get update
sudo apt-get -upgrade
sudo apt-get install mariadb-server



I now have the mariadb server running. How can I access this from outside of the container. How do I link the IP and Port to within the container?

I believe I may have missed a few key points but I can answer anything else as needed.


RE: Using MariaDB on NetPi - Armin@netPI - January-7th-2020

Hi there,

what I would recommend to you is using indeed the image https://hub.docker.com/r/hilschernetpi/netpi-raspbian that we prepared for netPI if you use the SD card preinstalled on your fresh netPI. If you do not want to use the default SD card and default image, you can use you own SD card and then you use the very official image from Raspberry.org. In this case using Docker is not necessarily a need. But based on what you wrote in your post you indeed want to use Docker. I do use Raspbian OS with Docker at home too by the way. So are you using the preinstalled SD card or your own.

If you use the container https://hub.docker.com/r/hilschernetpi/netpi-raspbian then this is a Raspbian OS container with Buster made by us . So once you have started this container it you can use it with port 22 and a terminal console (putty) to control it once you have mapped the port 22 during container start.

And yes of course you can use the commands 


Code:
sudo apt-get update
sudo apt-get -upgrade
sudo apt-get install mariadb-server

in our container.

But back to your mariadb. I do not know all details about the mariadb server and how it works. For me it is just a database. But let me ask you this ... does mariadb server have a web socket interface that allows access from remote stations over IP or it is just a local database that can only be used from local applications? I cannot answer this. Please answer this question first.

Thank you


RE: Using MariaDB on NetPi - Armin@netPI - January-7th-2020

I have another question. Why do you want to use mariadb as database? Is there a reason?

Also I want to mention that using a database on SD cards is always critical in terms of limited write cycles of SD card memory. Please read this thread: https://forum.hilscher.com/Thread-SD-card-duration first to understand the problem.

If you intend to run a database on netPI I strongly recommend to use another type of SD card with other technology like pSLC or SLC or an SD card of higher density.

P.S.: By the way I agree that the FAQ is little outdated and needs an update.


RE: Using MariaDB on NetPi - Armin@netPI - January-7th-2020

I read this article https://mariadb.com/kb/en/configuring-mariadb-for-remote-client-access/ carefully.

I understood that mariadb server supports client access from remote stations once it is enabled. I read that the default port in use is port 3306.

So if you want your PC to access the netPI mariadb container over TCP/IP then you have to "open" the port 3306 in the container. This is done using the "port mapping" feature used while a containe is started. It is the same like the port 22 to enable the console connection, but now for port 3306 for mariadb access from remote.

Since you are new to docker: port mapping can only be setup during the first container start. It is never ever possible to "open" other ports while a container is running. So all applications that will run in future in a container and their used ports need to be known from the beginning and handed over to Docker container start parameter.


RE: Using MariaDB on NetPi - philump - January-7th-2020

(January-7th-2020, 07:50 AM)Armin@netPI Wrote: Hi there,

what I would recommend to you is using indeed the image https://hub.docker.com/r/hilschernetpi/netpi-raspbian that we prepared for netPI if you use the SD card preinstalled on your fresh netPI. If you do not want to use the default SD card and default image, you can use you own SD card and then you use the very official image from Raspberry.org. In this case using Docker is not necessarily a need. But based on what you wrote in your post you indeed want to use Docker. I do use Raspbian OS with Docker at home too by the way. So are you using the preinstalled SD card or your own.

If you use the container https://hub.docker.com/r/hilschernetpi/netpi-raspbian then this is a Raspbian OS container with Buster made by us . So once you have started this container it you can use it with port 22 and a terminal console (putty) to control it once you have mapped the port 22 during container start.

And yes of course you can use the commands 


Code:
sudo apt-get update
sudo apt-get -upgrade
sudo apt-get install mariadb-server

in our container.

But back to your mariadb. I do not know all details about the mariadb server and how it works. For me it is just a database. But let me ask you this ... does mariadb server have a web socket interface that allows access from remote stations over IP or it is just a local database that can only be used from local applications? I cannot answer this. Please answer this question first.

Thank you

Thanks for your quick reply,

I will try to answer all of your questions accurately.

We are currently using the pre installed SD card that came with the NetPI. We are using docker as we were of the understanding that this is the most practical and secure way of using the NetPi? If the NetPi is intended to be on par with a Rasp Pi 3, wouldn't it just be easier to just load raspberry.org image in all cases? (I'm sure this isn't correct but can you explain why?)

 We want to use the raspberry Pi for development. If I pull the hilschernetpi/netpi-raspbian docker image on the raspberry pi. How do we access this container? My current process is this:

Using Putty from my windows 10 pc:
  • Connect to the IP of the Raspberry Pi
  • Login with user: pi PWD: raspberry
Code:
sudo docker images


returns:

REPOSITORY                     TAG                 IMAGE ID            CREATED             SIZE
hilschernetpi/netpi-raspbian   latest              bdc724e92997        4 days ago          801MB
mariadb/server                 10.3                d1ed196aa600        3 weeks ago         345MB

Code:
sudo docker ps

returns:
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS               NAMES

Code:
sudo docker run -i -t hilschernetpi/netpi-raspbian /bin/bash

returns:
starting ssh ...
[ ok ] Starting OpenBSD Secure Shell server: sshd.
starting dbus ...
[ ok ] Starting system message bus: dbus.

This is as far as I get and can't access the container, what am I missing?

On the NetPi I can access the container via SSH.


Quote:I have another question. Why do you want to use mariadb as database? Is there a reason?

Also I want to mention that using a database on SD cards is always critical in terms of limited write cycles of SD card memory. Please read this thread: https://forum.hilscher.com/Thread-SD-card-duration first to understand the problem.

If you intend to run a database on netPI I strongly recommend to use another type of SD card with other technology like pSLC or SLC or an SD card of higher density.

P.S.: By the way I agree that the FAQ is little outdated and needs an update.

I was using MySQL, however raspbian buster uses MariaDB by default. It is a local database that can be accessed remotely over IP. The idea behind this is running the local database on the NetPi which records the information in real time, a replication MariaDB server is running on Microsoft Azure. When the NetPI has an active internet connection it syncs with the Azure database. 

The data that is being recorded is vessel telemetry (GPS location, speed, engine data etc...) The data needs to be recorded for approx 30 days.

It is noted about the write cycles for these SD cards. Im not sure how to get around this issue as the vessel will not always have an internet connection. So while it is out of range it needs to record the information somewhere. If it had a constant internet connection we could just connect directly to the azure server.

Do you have any other suggestions for recording telemetry on the NetPi?

Quote:I read this article https://mariadb.com/kb/en/configuring-ma...nt-access/ carefully.

I understood that mariadb server supports client access from remote stations once it is enabled. I read that the default port in use is port 3306.

So if you want your PC to access the netPI mariadb container over TCP/IP then you have to "open" the port 3306 in the container. This is done using the "port mapping" feature used while a containe is started. It is the same like the port 22 to enable the console connection, but now for port 3306 for mariadb access from remote.

Since you are new to docker: port mapping can only be setup during the first container start. It is never ever possible to "open" other ports while a container is running. So all applications that will run in future in a container and their used ports need to be known from the beginning and handed over to Docker container start parameter.

This makes sense and I will try this today.

Can I stop a running container and open the port and reopen it? Or do I reinstall with the port mapping?

For arguments sake, If I were to have a container running on the NetPi with the local MariaDB server, if the NEtPi were to lose power and restart. When the NetPi restarted would this container lose all data in the MariaDB server?

I appreciate your time and effort with this.


RE: Using MariaDB on NetPi - Armin@netPI - January-8th-2020

To answer your question about the netPI preinstalled image or using the raspbian image I can answer as follows: with netPI we installed a special compiled version of Docker. We analyzed Docker in all details and all its options a normal user have to "open" a container in that ways that it is able to get root rights to the host system or to modify the host systems file system. In our understanding of security this is a "no go". This is why we come with a special Docker version on netPI.

But I agree I someone want to use the whole Docker functionality or want to have control of the complete update life cycle of his software in terms of OS too, then he could easily use Raspbian and installe Docker in one command. It is up to him.


RE: Using MariaDB on NetPi - Armin@netPI - January-8th-2020

Quote:REPOSITORY                     TAG                 IMAGE ID            CREATED             SIZE
hilschernetpi/netpi-raspbian   latest              bdc724e92997        4 days ago          801MB
mariadb/server                 10.3                d1ed196aa600        3 weeks ago         345MB

One word to your images you have loaded on your Raspberry Pi.

Yesterday I also checked the availability of a ready to use mariadb-server container like you did. But the problem of the "mariadb/server" is that it is compiled with x86 processors only. So you will never be able to start this container on ARM CPUs.

The reason why I was asking about "why mariadb?" is that from most users I know so far using databases are using "influxdb" time series database. And guess what the corresponding influxdb container is automatically detecting the type of CPU in use and this Docker image works right away on x86 or based ARM systems. But I have understood the reason of maria.db is the synchronization to Azure.


RE: Using MariaDB on NetPi - Armin@netPI - January-8th-2020

Quote:It is noted about the write cycles for these SD cards. Im not sure how to get around this issue as the vessel will not always have an internet connection. So while it is out of range it needs to record the information somewhere. If it had a constant internet connection we could just connect directly to the azure server.

Do you have any other suggestions for recording telemetry on the NetPi?

Well I don't know if the system can detect if the device has internet connectivity or not in any case.

My understanding is that if you are accessing the database from remote and call a write command from there I am pretty sure the mariadb will first of all do a write access to the database file on the file system and synching later. So the "bad" write for the SD card you will not be able to suppress. But correct me if I am wrong.

Fact is that if you know alreay today you have permament writes to the SD card it will fail. The last tests I did with the current used netPI SD from SANDISK showed me 20TBytes that I was able to write until I saw first write errors. But I can also tell you that I have written 1GByte big files during my tests always. With a database it will write some subsequent and smaller packages for sure and I cannot tell you how the SD card performs then.

So in this case I just can recommend to use a different type of SD card from company Swissbit for example of type pSLC. https://www.swissbit.com/en/


RE: Using MariaDB on NetPi - Armin@netPI - January-8th-2020

Quote:
Code:
sudo docker run -i -t hilschernetpi/netpi-raspbian /bin/bash

That this is not working is explainable. The "/bin/bash" at the end of your command line does not prevent the container from using the ENTRYPOINT path that is coming with the container and from calling it. The command at the end just overwrites the default CMD of a container if it has any.

Instead you have to change the image entrypoint when you call it on your Raspberry machine like

Code:
sudo docker run -i -t --entrypoint=/bin/bash hilschernetpi/netpi-raspbian

But please do not forget since now the startscript is no more called the container has not started the ssh server yet in this case.


RE: Using MariaDB on NetPi - Armin@netPI - January-8th-2020

Quote:Can I stop a running container and open the port and reopen it? Or do I reinstall with the port mapping?

The port mapping of a container is always handed over during the "docker run" command and hence can be called only one time in a containers life. There is not a command such as "docker add port" to open another port during a container is running. But every new "docker run" command starts another instance of the original image and all edits you did in your current container are lost.

Of course what you can do is the following: once you have finished your container development on your raspberry pi ... you call "exit" in the container and you are back on your host. Now you left the container in its last state and now you can make a new image of this container with the "docker commit" https://docs.docker.com/engine/reference/commandline/commit/. And then this committed container can be started again with the "docker run" and new port mapping parameters.