Preparare il database MySQL o MariaDB

Configurare il server del database

Una volta installato il set di servizi LAMP, WAMP, MAMP o XAMPP Come spiegato nel primo articolo della serie o semplicemente dopo aver installato sulla macchina che fungerà da server la somma delle applicazioni che implementano i servizi corrispondenti, è necessario aggiustare alcuni aspetti della configurazione del server database MySQL oltre a creare i database e le tabelle che verranno utilizzati sul server web per archiviare i dati dai dispositivi Internet of Things.

Se si utilizza un server pubblico, il provider avrà sicuramente previsto una sorta di pannello per mediare tra l'utente e la configurazione "reale" del database in modo da rafforzare la sicurezza del funzionamento. In tal caso, dovrai fare riferimento alle istruzioni offerte da questo fornitore. Nel caso di un server reale o virtuale ospitato presso strutture di terzi, una circostanza analoga può verificarsi quando il fornitore lo gestisce; Se è stato contrattato un server non gestito (dal provider, gestito dall'utente), si applicheranno le seguenti informazioni.

Poiché l’importante è conoscere le istruzioni che devono essere date al gestore del database ed è inteso che esso viene utilizzato come mezzo di supporto ai dispositivi per l’Internet of Things, che sarebbe l’oggetto del progetto, potrebbe essere è sufficiente utilizzare la console poiché il lavoro sul server del database stesso sarà occasionale. Se il carico di lavoro di gestione comincia ad aumentare, può valere la pena, perché è più conveniente, utilizzare un gestore sul server stesso, come il popolare phpMyAdmin (da un browser web) o localmente, pur ovviamente accedendo al server del database, con un amministratore come MySQL Workbench da cui esibirsi in fase avanzata GUI le operazioni che verranno spiegate e tante altre che esulano da questo articolo e che meriterebbero tutta una serie.

Oltre alle operazioni più o meno automatiche che si possono fare MySQL Workbench su server, database o tabelle è possibile eseguire operazioni anche "manualmente" scrivendo ed eseguendo script completi o comandi indipendenti in SQL. Lo stesso che con lo strumento MySQL Workbench, SQL richiede un capitolo a parte e che sarà fondamentale conoscere in modo approfondito quando la parte del lavoro web IoT svolta comporta un'attenzione più seria alla backend di quanto spiegato qui.

In ogni caso, e ovviamente per risolvere la necessaria configurazione del database IoT in elaborazione, è possibile utilizzare la console.

Configura root, l'amministratore principale di MySQL

Se il server database si trova su una propria macchina, la prima cosa da fare è configurare i permessi di amministratore del servizio per poter creare, utilizzando tali privilegi, altri utenti (che a loro volta potrebbero averli) e, se applicabile, i database e le tabelle in cui archiviare le informazioni di cui il sistema Internet of Things ha bisogno.

È importante occuparsi del processo di installazione dell'insieme di servizi o del server MySQL, a seconda del processo seguito, poiché, in molti casi, l'installatore stesso sarà incaricato di eseguire questo passaggio e altri e non sarà necessario ripeterli se non quando si desidera modificare la configurazione.

Per eseguire queste attività, di solito si lavora con la console. MySQL come amministratore. Per accedere a questa modalità, utilizzare il comando mysql -u root -p o sudo mysql -u root se utilizzato per la prima volta e durante l'installazione l'utente non è stato configurato root

Nell'Ordine mysql -u root -p indicato con l'opzione -u il nome utente (root) e con l'opzione -p (password) che la chiave deve essere richiesta all'accesso alla console MySQL. Per tornare alla console di sistema è possibile utilizzare il comando quit uscire

Come mostrato nello screenshot qui sopra, prima di lasciare la console MySQL, per impostare i privilegi dell'utente root è stato utilizzato il comando:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '1234' WITH GRANT OPTION;
in quale:

• tutti i privilegi sono concessi GRANT ALL PRIVILEGES
• in tutte le tabelle di tutti i database ON *.*
• all'utente root accedendo dalla macchina su cui è in esecuzione il server MySQL TO 'root'@'localhost'
• utilizzando 1234 come password IDENTIFIED BY '1234'
• con il permesso di autorizzare altri utenti WITH GRANT OPTION

Per gestire il database in remoto, i privilegi devono essere concessi all'utente corrispondente da altre posizioni. Nell'esempio precedente era consentito root accessibile dalla macchina su cui è in esecuzione il server del database (localhost) con 'root'@'localhost', se utilizzato 'root'@'%' L'accesso sarà consentito da qualsiasi macchina.

Se si dispone dei privilegi di amministratore sul sistema, è possibile ristabilire la password dell'utente root de MySQL. Per fare ciò è necessario interrompere il servizio (mysql o mysqld, a seconda della distribuzione), avviarlo senza chiave, impostare la nuova chiave root e riavviarlo. Accedendo senza password è possibile concedere anche i privilegi sopra descritti.

• interrompere il servizio sudo /etc/init.d/mysql stop
• varo MySQL senza chiave e sbloccare la console sudo mysqld_safe --skip-grant-tables &
• accedere come «root» mysql -u root
• cambia la password:
  • accedi al database «mysql" USE mysql;
  • assegnato a "root» la chiave «1234»: UPDATE user SET authentication_string=PASSWORD("1234") WHERE user="root";
  • applicare i nuovi privilegi: FLUSH PRIVILEGES;
  • consolle di uscita mysql e tornare al sistema: quit

• riavviare il servizio sudo /etc/init.d/mysql restart

Configurare l'accesso al database remoto

Per motivi di sicurezza, la configurazione iniziale del gestore database MySQL non consente l'accesso remoto, per modificarlo è necessario modificare il documento di configurazione, normalmente my.cnf, che di solito si trova lungo il percorso /etc/mysql.

Se utilizzato MariaDB, che ora è la versione "più libera", di MySQL, certamente my.cnf sarà un collegamento a /etc/alternatives/my.cnf che, a sua volta, caricherà la configurazione, utilizzando !includedir, dai documenti di percorso /etc/mysql/mariadb.conf.d, probabilmente dal documento 50-server.cnf

La risorsa utilizzata dal server del database per non rispondere alle richieste remote consiste nell'"ascoltare" solo le richieste effettuate da una determinata posizione. indirizzo IP che è espresso come il valore del parametro bind-address (che sostituisce il classico skip-networking) Quando l'indirizzo è 127.0.0.1 (localhost) vengono soddisfatte solo le richieste provenienti dalla macchina locale. Se commentato con # o se il valore è 0.0.0.0, serviamo qualsiasi indirizzo. Per consentire l'accesso esterno la cosa più sicura è indicare il indirizzo IP (che può essere pubblico o privato) del computer da cui si accede, solitamente qualcosa di simile bind-address = 192.168.1.74. Nel tuo caso, questa attrezzatura può fungere da a tunnel (reindirizzamento con SSH) o come passerella (con il tabelle IP principali) per evitare di esporre il server al di fuori di un'area controllata della rete.

Nello screenshot qui sopra puoi vedere che è stato modificato my.cnf con la configurazione MySQL utilizzando l'ordine sudo nano /etc/mysql/my.cnf. Nel caso di lavorare con MariaDB Con la consueta configurazione, l'ordine sarebbe qualcosa del genere sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf. Come accennato in precedenza, una volta effettuato l'accesso con privilegi amministrativi, a GUI come MySQL Workbench per la gestione regolare del database.

Creare i database

Una volta configurato il server database, è possibile creare i database e le tabelle in cui archiviare i dati IoT. Per fare ciò, puoi usare i comandi SQL dalla console o dal GUI di un dirigente.

L'ordine CREATE DATABASE temperaturas; crea il database “temperature”, per evitare errori è possibile utilizzare il form CREATE DATABASE IF NOT EXISTS base_datos_temperaturas; che creerebbe il database “temperature_database” solo se attualmente non esiste.

Crea le tabelle

Poiché in diversi database potrebbero esserci tabelle con lo stesso nome, è possibile fare riferimento a una di esse facendo precedere il suo nome con quello del database e separando entrambe con un punto, qualcosa come base_datos.tabla Questa notazione può essere estesa anche ai nomi dei campi base.tabla.campo

Se si omette il nome del database quando si opera su una tabella (in questo caso, al momento della creazione) si considera referenziato l'ultimo selezionato. Per selezionarne espressamente uno specifico è possibile utilizzare il comando USE e indica il tuo nome USE base_datos_temperaturas;

Quando si creano tabelle nei database, nella stessa operazione, solitamente vengono aggiunte le informazioni dei campi che le compongono (anche se è possibile modificarle anche successivamente).Come avviene con il database, la tabella può essere creata solo se non esiste aggiungendo la clausola IF NOT EXISTS o al contrario, assicurati di eliminare la tabella prima di crearla solo se esiste utilizzando DROP TABLE IF EXISTS valor_temperaturas;

Per consentire nomi di tabelle o database che potrebbero entrare in conflitto con comandi o procedure, è possibile utilizzare l'accento grave, talvolta chiamato virgola rovesciata, per racchiudere il nome ed evitare conflitti o errori nei comandi. SQL.

Definire i campi

I campi che vengono aggiunti durante la creazione della tabella sono indicati racchiudendoli tra parentesi e indicando il tipo di dati che memorizzano. I tipi di dati tipicamente utilizzati in un database per archiviare informazioni dai dispositivi connessi all'Internet delle cose sono numeri interi (INT o BIGINT) decimali (DECIMAL, DOUBLE o FLOAT) di tempo (DATE, TIME, DATETIME o TIMESTAMP) e testo (principalmente VARCHAR anche se ce ne sono altri simili TEXT ma non vengono utilizzati per l'IoT) Dopo il tipo di dati e tra parentesi vengono aggiunti la lunghezza dei dati e, se applicabile, il formato o la precisione.

Aggiungendo AUTO_INCREMENT La definizione di un campo indica al gestore del database che, se non viene indicato altro, durante la creazione di un nuovo record deve essere automaticamente memorizzato il valore dell'ultimo più il relativo incremento (che normalmente è 1). campi che vengono utilizzati come indici numerici dei record.

Il codice NOT NULL indica che un campo in un record non può essere vuoto, deve memorizzare un valore.

Quando si usa DEFAULT Quando si crea un campo di una tabella, viene indicato MySQL un valore predefinito per il campo purché in fase di aggiunta di un nuovo record o di modifica dello stesso non sia espressamente indicato un altro valore. Un valore predefinito utilizzato di frequente, in questo caso per monitorare i valori IoT, è CURRENT_TIMESTAMP che consente di registrare automaticamente l'ora corrente in un campo quando si crea un nuovo record, in modo che i dati vengano automaticamente identificati nel tempo.

Per ottimizzare le prestazioni del database, è conveniente includere alla fine della definizione dei campi della tabella uno che venga utilizzato come indice con la clausola PRIMARY KEY (ID) ID è il campo scelto come indice.

Al termine della definizione di una tabella è possibile indicare come valore del parametro il motore del database con cui viene gestita ENGINE con il formato ENGINE=InnoDB; siendo InnoDB il motore scelto in questo caso.

Crea database, tabelle e configura campi in una sessione SQL

Il codice nell'esempio seguente riunisce gli ordini, i passaggi che dovrebbero essere eseguiti per creare un database con una tabella minima.

Creare copie di backup dei database

Una volta configurato il server e creato il database e le sue tabelle, tutto sarebbe pronto per archiviare le informazioni ottenute dai nodi IoT. Sebbene l'argomento centrale di questa serie di articoli non includa la manutenzione, esiste un problema amministrativo critico che è importante affrontare: i backup. Dalla maggior parte dei manager, incluso phpMyAdmin y MySQL Workbench, è possibile creare e ripristinare copie di backup ma è comune includerle in uno script che le automatizza (probabilmente insieme alla configurazione e a molti altri documenti) quindi è più comodo utilizzare lo strumento MySQLDump, un client che di solito fa parte di un'installazione convenzionale di MySQL, o qualche alternativa come Dumper di dati MySQL Cosa include l'ordine? mydumper per fare la copia e myloader per ripristinarlo

MySQLDump

Il formato dell'ordine mysqldump È molto semplice, devi solo indicare il nome utente user, Parola d'ordine password e il database che desideri copiare o --all-databases per eseguire il backup di tutti i database come nel seguente esempio:

mysqldump --opt --user=pelaez --password=1234 --all-databases > copia.sql

L'esempio precedente genera il documento copia.sql che contiene gli ordini SQL (cioè testo) necessario per ricostruire tutti i database. Se salvata in formato testo, la copia occupa molto più del necessario, motivo per cui solitamente viene anche compressa per risparmiare spazio sul dispositivo su cui è archiviata.

Il backup generato con il comando di esempio potrebbe essere ripristinato nel caso in cui le informazioni andassero perse a causa di un errore con il comando seguente che esegue le operazioni SQL del documento copia.sql

mysql --user=pelaez --password=1234 < copia.sql

Dumper di dati MySQL

Anche se le operazioni precedenti sarebbero sufficienti, il suo utilizzo è molto comune Dumper di dati MySQL, già quasi uno standard per le sue prestazioni nonostante non faccia parte degli strumenti base inclusi MySQL

Di seguito sono riportate le opzioni di mydumper più utilizzati che, logicamente, dovrebbero essere scritti come parte dello stesso comando, separati da spazi, anche se in questo testo sono stati separati in righe per facilitarne la lettura:

--host polaridad.es “polaridad.es” è il nome del server del database
--port 3306 "3306" è il numero di porta su cui il server è in ascolto (per impostazione predefinita è 3306)
--user pelaez "pelaez" è il nome dell'utente con cui si accede al database
--password 1234 "1234" è la password dell'utente con cui si accede al database
--database temperaturas “temperature” è il nome del database di cui viene eseguito il backup
--no-locks non bloccare il database durante la copia (per impostazione predefinita si blocca)
--build-empty-files copia anche le tabelle vuote
--compress-input comprimere l'input
--compress comprimere l'output (il backup risultante)

Per ripristinare il backup ottenuto con mydumper è usato myloader che viene utilizzato principalmente con le seguenti opzioni, che devono essere incluse nello stesso ordine separate da spazi:

--host polaridad.es “polaridad.es” è il nome del server del database
--port 3306 "3306" è il numero di porta su cui il server è in ascolto (per impostazione predefinita è 3306)
--user pelaez "pelaez" è il nome dell'utente con cui si accede al database
--password 1234 "1234" è la password dell'utente con cui si accede al database
--directory=copia_iot «iot_copy» è la cartella in cui si trova la copia generata con mydumper
--overwrite-tables sovrascrivere le tabelle anche se già esistono
--verbose 3 "3" è il grado di informazione del processo (più alto, più messaggi)
-C utilizzare la compressione sul flusso di dati

Il prossimo (e ultimo) articolo di questa serie spiega come utilizzare il linguaggio di programmazione PHP per archiviare in database le informazioni ottenute dai dispositivi connessi all'Internet delle cose di cui si è parlato in questo testo.