Wanted to create database

General
1

Hi there , I want to dump a data (at this moment single data) (i'm new to database)
in mysql or postgresql(which needs lots of work like docker or something)

can any one explains how to start with working on database??
Any step by step process for getting and dumping data to my raspberry pi 3 model b+.

2

Google is your friend, do a search and maybe look at some youtube video's

2 Likes
3

If it is a simple database for storing only a few thousand records then have a look at sqlite. It is very easy to use with node-red and does not need the installation of a db server.
If you want to store time-series data, so for example temperature data stored over a period of time, then have a look at influxdb, also very easy to interface with node-red.
If you want to store a large amount of data, something like stock control in a factory for example then you will need a database such as mysql or postgresql.

1 Like
4

I wanted work with mysql and i googled up all the details,
I also installed the node-mysql in node red but the thing is that it is not connecting
image

I checked all the things but i came with installation of mysql with php, can some explain that why php and why sql if i already install its node?

5

Can you connect using the command line mysql tool?

6

Let’s start by getting some basic info

  1. Are you running MySQL on the Pi?
  2. Have you defined your database?
  3. have you created your table(s)?
  4. have you installed phpMyAdmin and seen you can talk to your database and access the tables?

if you have done all that please export your flow and attach it to a reply after reading this thread How to share code or flow json

7

If you have to ask that question then that probably means you should not be using mysql. Setting up and managing a mysql database server is not trivial. You would probably be better to start with sqlite (which does not have a server so is much easier to setup and use) and once you get something going with that you can decide whether you need mysql.
What sort of system are you building and what size of database are you expecting to have? Do multiple users/applications need to access the database at the same time?

8

No , I'm at zero step nothing done,

1.for clarity I want to use mysql
2. I seen sqllite but have large amount of data so it can not be useful
3. I want to use mysql because after that i want to publish on heroku where it is supported.
4. For installing process of mysql tool and all stuff i got an error like this

image
image709Ă—262 33.1 KB

9

for better clarification

  1. Yes Wanted sql in rpi3 model b+
  2. No ( I'm still on installation stage , lots of error while installing not a single installation is done.)
    3.same as above
  3. same as above

As i mention i'm getting errors why is it so?

image
image709Ă—262 33.1 KB

10

If you read the error: mysql is not available/obsolete, use mariadb-server instead:

sudo apt-get install mariadb-server

11

ya! but what after that? complete process so that there will be no future error.

12

I don't understand your question.

There are various guides on how to install mariadb on a raspberry pi.

Databases are fields of study:

I seen sqllite but have large amount of data so it can not be useful

Why can sqlite not it not be useful you think ? This all depends on your needs of the database capabilities, large amounts of data is not directly one of them, they are both just files with records inside it.

You say you are new to databases, sqlite might be an easier way forward.

13

Actually I will have real time data which is suitable for mysql or postgresql.

The end goal is to publish on online server(like AWS, Heoku etc) but for now I wanted to run it locally.

also for mysql installation i found that it uses phpmyadmin, which can be GUI (i don't know what it is)

But It keeps throwing me error so I can not figure out How to install it for mariadb-server.
I searched but confused if it got wrong installation.

For this it is not supported in Heroku so wasting of time if mysql is supported.

I even search for Postgresql but the thing is still same. I can not find how to install the postgre in command line.

So I'm moving to mysql.

**

Can I get the steps to the end so that i can not get this again.

**

14

Actually I will have real time data which is suitable for mysql or postgresql.

mysql/mariadb is similar to sqlite, except sqlite has no server and no multi-user environment.

For realtime (streaming?) data, both are not suitable out of the box. Then again, a raspberry pi is not either.
For timestamped data you will be better off by using influxdb instead.

15

Please give us more information on what your system is intended to do so that we can make appropriate suggestions. Otherwise we are just thrashing about in the dark.

16

I want mysql to be connected to my local sysyem and I can make queries on it.

Thing is it is showing error which i posted earlier.

if i successfully make out of mysql all the possible queries will go to online server like heroku or other,

So From RPi i will send data to online database and make queries from there.

But from node red it is showing not connected.

So What type of line should user use because

sudo apt-get install mysql-server

is giving me error.

will this one line solve my issue? or any other thing i needed?

I just want all the steps so that i can not miss any installation, If any installation miss then I think i have give more time to it to recover (If I'm not wrong).

17

That is not what I meant, I meant what is the whole system doing? What sort of data? How much? How many users will be accessing the data concurrently? Why mysql? What do you want use the data for after storing it?
You mentioned realtime data I think, is that things like temperature and so on?
All these factors determine the best way to go. Mysql is generally not the best database for storing (and retrieving) real time process data.
If you are convinced that you know enough about the subject to decide that mysql/mariadb is the way to go (mariadb is an opensource version of mysql) then you need to learn about mysql. That is nothing to do with node-red. Since you apparently are starting from zero knowledge of databases I think you should spend a couple of days working through some database primers and then move onto mysql tutorials.

1 Like