Backyard Meteorological Instrumentation
Raspberry Pi 4 Set Up
Setup MariaDB Database
The MariaDB database has been installed on the Raspberry Pi and the 8GB version of the Pi has enough memory to store a significant amount of sensor data, but in this instance the sensor data will be stored on the main Linux computer which has a RAID disk set and automated backup procedures for the database. The instructions relating to the database will essentially be the same whether it is on the Raspberry Pi or the desktop server.
(1) From the command line start up the database. If you use the root account with sudo, you do not need a password.
$ sudo mariadb
(2) Create the database that is going to be used to hold all of the observations. In this case, it is WeatherStation. This database already exists my system as it contains the readings from the HP2550 Personal Weather Station (PWS). If the database exists, but you want a clean start, use "replace" instead of create and the old database will be dropped before a new one is created.
MariaDB [(none)]> create database WeatherStation comment 'Repository of Weather Sensor Data';
(3) Create a user to manage the database. Note that the password is the plain text version of the password. Note that localhost means that the user has to logon using the computer holding the database.
MariaDB [(none)]> create user 'sysdba'@'localhost' identified by 'secure_password';
(4) Check that this was a success by issuing the following command and confirming that user sysdba from host localhost exists.
MariaDB [(none)]> select user, host from mysql.user;
(5) Grant privileges to the sysdba user to all that user to manage the system without using the root account.
MariaDB [(none)]> grant all on WeatherStation.* to sysdba@localhost;
(6) If you want to allow the sysdba to grant privileges to other users, use the following command:
MariaDB [(none)]> grant grant option on WeatherStation.* to sysdba@localhost;
(7) Confirm that the user sysdba has the privileges you expect by running this command. Note that this password shown is the encrypted password.
MariaDB [(none)]> show grants for sysdba@localhost;
(8) Download the table creation script to make the first database table ws_nodes.
(9) Select the WeatherStation database as the default database to use in following commands:
MariaDB [(none)]> use WeatherStation;
(10) Run the script to create the ws_nodes table that will hold the nodes used by this project.:
MariaDB [WeatherStation]> source create_ws_nodes.sql;
(11) Confirm that the table have been created. It should match the fields and properties in the script file.
MariaDB [WeatherStation]> desc ws_nodes;
(12) Load the ws_nodes table with the example list of nodes. NOTE: The example provided DOES NOT match the nodes on this system. Authorised users may access the actual list of nodes.
MariaDB [WeatherStation]> source load_example_ws_nodes.sql;
(13) Download the ws_node_obs table creation script that will hold information about the project node; and run it.
MariaDB [WeatherStation]> source create_ws_node_obs.sql;
(14) Confirm that the table have been created. It should match the fields and properties in the script file.
MariaDB [WeatherStation]> desc ws_node_obs;
(15) These tables will be used later to record the CPU temperatures of the nodes on the local network using the Qt-based program cputempmonitlor that can be compiled with source code changes for the X86-64 Linux nodes and the Raspberry Pi nodes.
(16) The Weatherstation database files can be backed using the the following commands:
$ sudo mariabackup --backup --target-dir=<TARGET_DIRECTORY> --user=<USERNAME> --password=<PASSWORD>
where <TARGET_DIRECTORY> is the empty directory to place the backup, <USERNAME> is the name of the user with enough database privileges; and <PASSWORD> is that user's password.
(17) An alternative backup that writes the table formats and the data into an SQL file can be dome as follows:
$ sudo mysqldump WeatherStation > <BACKUP_FILENAME>
where <BACKUP_FILENAME> is the fully define filename where the SQL statements will be deposited. It should end with ".sql" so that it is obviously an SQL file.
(1) From the command line start up the database. If you use the root account with sudo, you do not need a password.
$ sudo mariadb
(2) Create the database that is going to be used to hold all of the observations. In this case, it is WeatherStation. This database already exists my system as it contains the readings from the HP2550 Personal Weather Station (PWS). If the database exists, but you want a clean start, use "replace" instead of create and the old database will be dropped before a new one is created.
MariaDB [(none)]> create database WeatherStation comment 'Repository of Weather Sensor Data';
(3) Create a user to manage the database. Note that the password is the plain text version of the password. Note that localhost means that the user has to logon using the computer holding the database.
MariaDB [(none)]> create user 'sysdba'@'localhost' identified by 'secure_password';
(4) Check that this was a success by issuing the following command and confirming that user sysdba from host localhost exists.
MariaDB [(none)]> select user, host from mysql.user;
(5) Grant privileges to the sysdba user to all that user to manage the system without using the root account.
MariaDB [(none)]> grant all on WeatherStation.* to sysdba@localhost;
(6) If you want to allow the sysdba to grant privileges to other users, use the following command:
MariaDB [(none)]> grant grant option on WeatherStation.* to sysdba@localhost;
(7) Confirm that the user sysdba has the privileges you expect by running this command. Note that this password shown is the encrypted password.
MariaDB [(none)]> show grants for sysdba@localhost;
(8) Download the table creation script to make the first database table ws_nodes.
(9) Select the WeatherStation database as the default database to use in following commands:
MariaDB [(none)]> use WeatherStation;
(10) Run the script to create the ws_nodes table that will hold the nodes used by this project.:
MariaDB [WeatherStation]> source create_ws_nodes.sql;
(11) Confirm that the table have been created. It should match the fields and properties in the script file.
MariaDB [WeatherStation]> desc ws_nodes;
(12) Load the ws_nodes table with the example list of nodes. NOTE: The example provided DOES NOT match the nodes on this system. Authorised users may access the actual list of nodes.
MariaDB [WeatherStation]> source load_example_ws_nodes.sql;
(13) Download the ws_node_obs table creation script that will hold information about the project node; and run it.
MariaDB [WeatherStation]> source create_ws_node_obs.sql;
(14) Confirm that the table have been created. It should match the fields and properties in the script file.
MariaDB [WeatherStation]> desc ws_node_obs;
(15) These tables will be used later to record the CPU temperatures of the nodes on the local network using the Qt-based program cputempmonitlor that can be compiled with source code changes for the X86-64 Linux nodes and the Raspberry Pi nodes.
(16) The Weatherstation database files can be backed using the the following commands:
$ sudo mariabackup --backup --target-dir=<TARGET_DIRECTORY> --user=<USERNAME> --password=<PASSWORD>
where <TARGET_DIRECTORY> is the empty directory to place the backup, <USERNAME> is the name of the user with enough database privileges; and <PASSWORD> is that user's password.
(17) An alternative backup that writes the table formats and the data into an SQL file can be dome as follows:
$ sudo mysqldump WeatherStation > <BACKUP_FILENAME>
where <BACKUP_FILENAME> is the fully define filename where the SQL statements will be deposited. It should end with ".sql" so that it is obviously an SQL file.
