Préparez la base de données MySQL ou MariaDB

Configurer le serveur de base de données

Une fois l’ensemble des services installé LAMP, WAMP, MAMP o XAMPP Comme expliqué dans le premier article de la série ou simplement après avoir installé sur la machine qui fera office de serveur la somme des applications qui implémentent les services correspondants, il est nécessaire d'ajuster certains aspects de la configuration du serveur de base de données. MySQL ainsi que la création des bases de données et des tables qui seront utilisées sur le serveur Web pour stocker les données des appareils Internet des objets.

Si un serveur public est utilisé, le fournisseur aura sûrement prévu une sorte de panneau pour servir d'intermédiaire entre l'utilisateur et la configuration "réelle" de la base de données afin de renforcer la sécurité du fonctionnement. Si tel est le cas, vous devrez vous référer aux instructions proposées par ce fournisseur. Dans le cas d'un serveur réel ou virtuel hébergé dans les installations d'un tiers, une circonstance comparable peut survenir lorsque le fournisseur le gère ; Si un serveur non géré a été contracté (par le fournisseur, géré par l'utilisateur), les informations suivantes s'appliqueront.

Puisque l'important est de connaître les instructions qui doivent être données au gestionnaire de la base de données et qu'il est entendu qu'elle est utilisée comme moyen de support des dispositifs pour l'Internet des objets, qui ferait l'objet du projet, il peut être de quoi utiliser la console puisque le travail sur le serveur de base de données lui-même sera occasionnel. Si la charge de travail de gestion commence à augmenter, cela peut valoir la peine, car il est plus pratique d'utiliser un gestionnaire sur le serveur lui-même, comme le populaire phpMyAdmin (à partir d'un navigateur Web) ou localement, en accédant bien sûr au serveur de base de données, avec un administrateur tel que MySQL Workbench à partir duquel effectuer dans un niveau avancé GUI les opérations qui vont être expliquées et bien d'autres qui sortent du cadre de cet article et qui méritent toute une série.

En plus des opérations plus ou moins automatiques qui peuvent être effectuées avec MySQL Workbench sur le serveur, les bases de données ou les tables, il est également possible d'effectuer des tâches "manuellement" en écrivant et en exécutant des scripts complets ou des commandes indépendantes dans SQL. Comme avec l'outil MySQL Workbench, SQL nécessite un chapitre séparé et qu'il sera essentiel de connaître en profondeur lorsque la partie du travail Web IoT qui est réalisée implique une attention plus sérieuse à la backend que ce qui est expliqué ici.

Dans tous les cas, et bien sûr pour résoudre la configuration nécessaire de la base de données IoT en cours de traitement, la console peut être utilisée.

Configurer root, l'administrateur principal de MySQL

Si le serveur de base de données est sur sa propre machine, la première chose à faire est de configurer les autorisations de l'administrateur du service pour pouvoir créer, en utilisant ces privilèges, d'autres utilisateurs (qui à leur tour peuvent en disposer) et, le cas échéant, les bases de données et les tables. dans lequel stocker les informations dont le système Internet des objets a besoin.

Il est important de prêter attention au processus d'installation de l'ensemble des services ou du serveur MySQL, selon le processus suivi, puisque, dans de nombreux cas, l'installateur se chargera lui-même de réaliser cette étape et d'autres et il ne sera pas nécessaire de les répéter sauf lorsque vous souhaitez modifier la configuration.

Pour effectuer ces tâches, vous travaillez généralement avec la console. MySQL en tant qu'administrateur. Pour accéder à ce mode, utilisez la commande mysql -u root -p o sudo mysql -u root en cas de première utilisation et lors de l'installation, l'utilisateur n'a pas été configuré root

Dans l'Ordre mysql -u root -p indiqué avec l'option -u l'identifiant (root) et avec l'option -p (mot de passe) que la clé doit être demandée lors de l'accès à la console MySQL. Pour revenir à la console système, vous pouvez utiliser la commande quit pour sortir

Comme le montre la capture d'écran ci-dessus, avant de quitter la console MySQL, pour définir les privilèges de l'utilisateur root la commande a été utilisée :
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '1234' WITH GRANT OPTION;
dans laquelle:

• tous les privilèges sont accordés GRANT ALL PRIVILEGES
• dans toutes les tables de toutes les bases de données ON *.*
• à l'utilisateur root accès depuis la machine qui exécute le serveur MySQL TO 'root'@'localhost'
• en utilisant 1234 comme mot de passe IDENTIFIED BY '1234'
• avec la permission d'autoriser d'autres utilisateurs WITH GRANT OPTION

Afin de gérer la base de données à distance, des privilèges doivent être accordés à l'utilisateur correspondant depuis d'autres emplacements. Dans l'exemple précédent, il était permis root accessible depuis la machine sur laquelle le serveur de base de données est exécuté (localhost) avec 'root'@'localhost', si utilisé 'root'@'%' L’accès sera autorisé depuis n’importe quelle machine.

Si vous disposez de privilèges d'administrateur sur le système, vous pouvez rétablir le mot de passe utilisateur root de MySQL. Pour ce faire, il est nécessaire d'arrêter le service (mysql o mysqld, selon la distribution), démarrez-le sans clé, définissez la nouvelle clé root et redémarrez-le. En accédant sans mot de passe, il est également possible d'accorder les privilèges décrits ci-dessus.

• arrêter le service sudo /etc/init.d/mysql stop
• lanzar MySQL sans clé et déverrouiller la console sudo mysqld_safe --skip-grant-tables &
• accéder en tant que «root» mysql -u root
• changer le mot de passe :
  • accéder à la base de données «mysql»: USE mysql;
  • affecter à "root» la clé « 1234 » : UPDATE user SET authentication_string=PASSWORD("1234") WHERE user="root";
  • appliquer les nouveaux privilèges : FLUSH PRIVILEGES;
  • quitter la console mysql et revenez au système : quit

• redémarrer le service sudo /etc/init.d/mysql restart

Configurer l'accès à la base de données distante

Pour des raisons de sécurité, la configuration initiale du gestionnaire de base de données MySQL ne permet pas l'accès à distance, pour le modifier il faut éditer le document de configuration, normalement my.cnf, que l'on trouve généralement sur l'itinéraire /etc/mysql.

Si utilisé MariaDB, qui est désormais la version "plus libre", de MySQL, sûrement my.cnf sera un lien vers /etc/alternatives/my.cnf qui, à son tour, chargera la configuration, en utilisant !includedir, à partir des documents d'itinéraire /etc/mysql/mariadb.conf.d, probablement d'après le document 50-server.cnf

La ressource que le serveur de base de données utilise pour ne pas répondre aux requêtes distantes consiste à « écouter » uniquement les requêtes effectuées à partir d'un certain emplacement. Adresse IP qui est exprimée comme la valeur du paramètre bind-address (qui remplace le classique skip-networking) Lorsque l'adresse est 127.0.0.1 (localhost) seules les demandes de la machine locale sont traitées. Si commenté avec # ou si la valeur est 0.0.0.0, nous desservons n'importe quelle adresse. Pour permettre un accès externe, le plus sûr est d'indiquer le Adresse IP (qui peut être public ou privé) de l'ordinateur à partir duquel on accède, généralement quelque chose comme bind-address = 192.168.1.74. Dans votre cas, cet équipement peut faire office de tunnel (redirection avec SSH) ou comme passerelle (avec le tables IP principales) pour éviter d'exposer le serveur en dehors d'une zone contrôlée du réseau.

Dans la capture d'écran ci-dessus, vous pouvez voir qu'il a été modifié my.cnf avec la configuration MySQL en utilisant la commande sudo nano /etc/mysql/my.cnf. Dans le cas de travailler avec MariaDB Avec la configuration habituelle, la commande serait quelque chose comme sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf. Comme mentionné ci-dessus, une fois qu'il y a un accès avec des privilèges administratifs, un GUI comme MySQL Workbench pour la gestion régulière de la base de données.

Créer les bases de données

Une fois le serveur de base de données configuré, les bases de données et les tables dans lesquelles stocker les données IoT peuvent être créées. Pour ce faire, vous pouvez utiliser les commandes SQL depuis la console ou depuis le GUI d'un gérant.

L'ordre CREATE DATABASE temperaturas; crée la base de données « températures ». Pour éviter les erreurs, le formulaire peut être utilisé CREATE DATABASE IF NOT EXISTS base_datos_temperaturas; ce qui créerait la base de données « température_database » uniquement si elle n’existe pas actuellement.

Créer les tableaux

Comme dans différentes bases de données, il peut y avoir des tables portant le même nom, vous pouvez faire référence à l'une d'elles en faisant précéder son nom de celui de la base de données et en séparant les deux par un point, quelque chose comme base_datos.tabla Cette notation peut également être étendue aux noms des champs base.tabla.campo

Si le nom de la base de données est omis lors d'une opération sur une table (dans ce cas, lors de sa création), on considère que la dernière sélectionnée est référencée. Pour en sélectionner expressément un spécifique, vous pouvez utiliser la commande USE et indiquez votre nom USE base_datos_temperaturas;

Lors de la création de tables dans des bases de données, dans la même opération, on ajoute généralement les informations des champs qui les composent (bien qu'il soit également possible de les modifier ultérieurement). Tout comme cela se fait avec la base de données, la table ne peut être créée que si elle n'existe pas en ajoutant la clause IF NOT EXISTS ou au contraire, veillez à supprimer la table avant de la créer uniquement si elle existe en utilisant DROP TABLE IF EXISTS valor_temperaturas;

Pour autoriser les noms de tables ou de bases de données qui pourraient entrer en conflit avec des commandes ou des procédures, l'accent grave, parfois appelé virgule inversée, peut être utilisé pour entourer le nom et éviter les conflits ou les erreurs dans les commandes. SQL.

Définir les champs

Les champs ajoutés lors de la création du tableau sont indiqués en les mettant entre parenthèses et en indiquant le type de données qu'ils stockent. Les types de données généralement utilisés dans une base de données pour stocker les informations provenant des appareils connectés à l'Internet des objets sont des nombres entiers (INT o BIGINT) décimales (DECIMAL, DOUBLE o FLOAT) temps (DATE, TIME, DATETIME o TIMESTAMP) et du texte (principalement VARCHAR bien qu'il y en ait d'autres comme TEXT mais ils ne sont pas utilisés pour l'IoT) Après le type de données, et entre parenthèses, la longueur des données et, le cas échéant, le format ou la précision sont ajoutés.

En ajoutant AUTO_INCREMENT La définition d'un champ indique au gestionnaire de la base de données que, si rien d'autre n'est indiqué, lors de la création d'un nouvel enregistrement, la valeur du dernier enregistrement plus l'incrément correspondant doit être automatiquement stocké (qui est normalement 1). Il est généralement inclus dans le champs qui sont utilisés comme index numériques des enregistrements.

Le code NOT NULL indique qu'un champ dans un enregistrement ne peut pas être vide, il doit stocker une valeur.

Lors de l'utilisation DEFAULT Lors de la création d'un champ d'une table, il est indiqué MySQL une valeur par défaut pour le champ à condition que lors de l'ajout d'un nouvel enregistrement ou de sa modification, une autre valeur ne soit pas expressément indiquée. Une valeur par défaut fréquemment utilisée, dans ce cas pour surveiller les valeurs IoT, est CURRENT_TIMESTAMP qui vous permet d'enregistrer automatiquement l'heure actuelle dans un champ lors de la création d'un nouvel enregistrement, afin que les données soient automatiquement identifiées dans le temps.

Pour optimiser les performances de la base de données, il est pratique d'inclure à la fin de la définition des champs de la table celui qui sert d'index avec la clause PRIMARY KEY (ID) ID étant le champ choisi comme index.

A la fin de la définition d'une table vous pouvez indiquer le moteur de base de données avec lequel elle est gérée comme valeur du paramètre ENGINE avec le format ENGINE=InnoDB; siendo InnoDB le moteur choisi dans ce cas.

Créer des bases de données, des tables et configurer des champs dans une session SQL

Le code de l'exemple suivant regroupe les commandes, les étapes à suivre, pour créer une base de données avec une table minimum.

Créer des copies de sauvegarde des bases de données

Une fois le serveur configuré et la base de données et ses tables créées, tout serait prêt pour stocker les informations obtenues par les nœuds IoT. Bien que cette série d'articles n'inclue pas la maintenance, il existe un problème administratif critique qu'il est important de résoudre : les sauvegardes. De la part de la plupart des gestionnaires, y compris phpMyAdmin y MySQL Workbench, des copies de sauvegarde peuvent être réalisées et restaurées mais il est courant de les inclure dans un script qui les automatise (probablement avec la configuration et de nombreux autres documents), il est donc plus pratique d'utiliser l'outil MySQLDump, un client qui fait généralement partie d'une installation conventionnelle de MySQL, ou une alternative comme Dumper de données MySQL Que comprend la commande ? mydumper faire la copie et myloader pour le restaurer

MySQLDump

Le format de commande mysqldump C'est très simple, il vous suffit d'indiquer le nom d'utilisateur user, Le mot de passe password et la base de données que vous souhaitez copier ou --all-databases pour sauvegarder toutes les bases de données comme dans l'exemple suivant :

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

L'exemple précédent génère le document copia.sql qui contient les commandes SQL (c'est-à-dire du texte) nécessaire pour reconstruire toutes les bases de données. Lorsqu'elle est enregistrée au format texte, la copie prend beaucoup plus de temps que nécessaire, c'est pourquoi elle est également généralement compressée pour économiser de l'espace sur l'appareil sur lequel elle est stockée.

La sauvegarde générée avec l'exemple de commande pourrait être restaurée si les informations étaient perdues en raison d'une erreur avec la commande ci-dessous qui exécute les opérations SQL du document copia.sql

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

Dumper de données MySQL

Même si les opérations précédentes seraient suffisantes, il est très courant d'utiliser Dumper de données MySQL, déjà presque un standard pour ses performances bien qu'il ne fasse pas partie des outils de base inclus avec MySQL

Voici les options de mydumper les plus utilisés qui, logiquement, devraient être écrits dans le cadre de la même commande, séparés par des espaces, bien que dans ce texte ils aient été séparés en lignes pour faciliter la lecture :

--host polaridad.es « polaridad.es » est le nom du serveur de base de données
--port 3306 "3306" est le numéro de port sur lequel le serveur écoute (par défaut c'est 3306)
--user pelaez "pelaez" est le nom de l'utilisateur avec lequel on accède à la base de données
--password 1234 "1234" est le mot de passe de l'utilisateur avec lequel on accède à la base de données
--database temperaturas « températures » est le nom de la base de données en cours de sauvegarde
--no-locks ne pas verrouiller la base de données lors de la copie (par défaut elle se verrouille)
--build-empty-files copiez également les tables vides
--compress-input compresser l'entrée
--compress compresser la sortie (la sauvegarde résultante)

Pour restaurer la sauvegarde obtenue avec mydumper S'utilise myloader qui est principalement utilisé avec les options suivantes, qui doivent être incluses dans le même ordre séparées par des espaces :

--host polaridad.es « polaridad.es » est le nom du serveur de base de données
--port 3306 "3306" est le numéro de port sur lequel le serveur écoute (par défaut c'est 3306)
--user pelaez "pelaez" est le nom de l'utilisateur avec lequel on accède à la base de données
--password 1234 "1234" est le mot de passe de l'utilisateur avec lequel on accède à la base de données
--directory=copia_iot «iot_copy» est le dossier dans lequel se trouve la copie générée avec mydumper
--overwrite-tables écraser les tables même si elles existent déjà
--verbose 3 "3" est le degré d'information du processus (plus élevé, plus de messages)
-C utiliser la compression sur le flux de données

Le prochain (et dernier) article de cette série explique comment utiliser le langage de programmation PHP pour stocker les informations obtenues par les appareils connectés à l'Internet des objets dans des bases de données dont on a parlé dans ce texte.