Ressources Informatiques

Installer et utiliser un serveur MariaDB

A présent, le serveur MariaDB est installé automatiquement à la place du serveur MySQL. C'est un fork de MySQL créé en 2009.

Documentation : MariaDB Documentation

Installer

Utiliser

Accès depuis un hôte distant

Installer

adminX@serveurX:~$ sudo apt update
adminX@serveurX:~$ sudo apt install mariadb-server mariadb-client

Valider l'installation

Vérifier que le processus est démarré

adminX@serveurX:~$ ps -e | grep mariadbd
  10303 ?        00:00:00 mariadbd

Vérifier que le port utilisé par `MariaDB` (`3306` par défaut) est ouvert

Commande ss : another utility to investigate sockets
Options :

-l, --listening : Display only listening sockets.
-n, --numeric : Do not try to resolve service names.
-t, --tcp : Display TCP sockets.

adminX@serveurX:~$ ss -lnt sport = :3306
State      Recv-Q Send-Q      Local Address:Port      Peer Address:Port
LISTEN     0      80              127.0.0.1:3306                 *:*

On constate que, par défaut, le serveur MariaDB est accessible uniquement depuis l'adresse locale 127.0.0.1. Pour autoriser l'accès depuis d'autres PC lisez le chapitre Accès depuis un hôte distant.

Vérifier l'état du serveur

adminX@serveurX:~$ systemctl status mariadb
● mariadb.service - MariaDB ... database server
...
   Active: active (running) ...
...

Utiliser

Se connecter au serveur

adminX@serveurX:~$ sudo mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
...
MariaDB [(none)]>

Se déconnecter

MariaDB [(none)]> quit
Bye
adminX@serveurX:~$

Charger un script

Nous allons charger le script baseMeubleFournisseur.sql. Ce script crée la base de données meubleFournisseur qui modélise de manière très simplifiée la base de données d'un magasin de meubles. Il sert de support à l'article Requêtes SQL où elle y est décrite. Vous pouvez tranférer un script sur votre serveur MariaDB en procédant comme indiqué dans l'article : Raspberry Pi : Transférer des fichiers

Code du script utilisé pour l'exemple :

adminX@serveurX:~$ cat baseMeubleFournisseur.sql
DROP DATABASE IF EXISTS meubleFournisseur;
CREATE DATABASE meubleFournisseur;
USE meubleFournisseur;
CREATE TABLE fournisseur (
  fou_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  fou_nom VARCHAR(20) UNIQUE NOT NULL,
  fou_ville VARCHAR(60) NOT NULL
) ENGINE = INNODB;
CREATE TABLE meuble (
  meu_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  meu_designation VARCHAR(80) NOT NULL,
  meu_prix DECIMAL(6,2) NOT NULL DEFAULT '9999.99',
  meu_fouId SMALLINT UNSIGNED NOT NULL,
  FOREIGN KEY (meu_fouId) REFERENCES fournisseur(fou_id)
) ENGINE = INNODB;
INSERT INTO  fournisseur (fou_nom, fou_ville)
VALUES  ('A Meubles','Arpajon'),
        ('E Meubles','Etampes'),
        ('D Meubles','Dourdan');
INSERT INTO meuble  (meu_id, meu_designation, meu_prix,  meu_fouId)
VALUES ('123','chaise verte', '40.10', '1'),
('456','chaise bleue', '40.90', '1'),
('4401','chaise rouge',' 50.10', '2'),
('5003','table verte', '500', '3');

Chargement du script

adminX@serveurX:~$ sudo mysql -u root < baseMeubleFournisseur.sql

Visualiser les bases de données

Après s'être connecté :

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| meubleFournisseur  |
| mysql              |
| performance_schema |
+--------------------+

Visualiser les tables de votre base

MariaDB [(none)]> use meubleFournisseur
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [meubleFournisseur]>  show tables;
+-----------------------------+
| Tables_in_meubleFournisseur |
+-----------------------------+
| fournisseur                 |
| meuble                      |
+-----------------------------+

Gérer les utilisateurs

Documentation : MariaDB : Account Management SQL Commands

Il faut se connecter à la base à l'aide de l'utilisateur root de MariaDB comme effectué plus haut.

Créer un utilisateur

Nous allons créer l'utilisateur adminBaseMeuble1 qui pourra se connecter localement au serveur MariaDB avec le mot de passe bonjour.

MariaDB [(none)]> CREATE USER IF NOT EXISTS 'adminBaseMeuble1'@'localhost' IDENTIFIED BY 'bonjour';

Remarque : si vous souhaitez supprimer un utilisateur créé par erreur, il faut utiliser la commande DROP. Exemple pour l'utilisateur ci-dessus :

MariaDB [(none)]> DROP USER IF EXISTS 'adminBaseMeuble1'@'localhost';

Affecter des droits à un utilisateur

Nous allons donner à l'utilisateur adminBaseMeuble1 les droits SELECT, INSERT, UPDATE et DELETE sur les tables de la base meubleFournisseur.

MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE ON meubleFournisseur.* TO 'adminBaseMeuble1'@'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;

Visualiser les utilisateurs

La table user de la base mysql contient les utilisateurs du système.

MariaDB [(none)]> SELECT `Host`,`User`,`Password` FROM `mysql`.`user`;
 SELECT `Host`,`User`,`Password` FROM `mysql`.`user`;
+-----------+------------------+-------------------------------------------+
| Host      | User             | Password                                  |
+-----------+------------------+-------------------------------------------+
| localhost | mariadb.sys      |                                           |
| localhost | root             | invalid                                   |
| localhost | mysql            | invalid                                   |
| localhost | adminBaseMeuble1 | *BD885142404C127AC29793EE3FBB1FDB01D2B25F |
+-----------+------------------+-------------------------------------------+

Visualiser les droits d'un utilisateur

MariaDB [(none)]> SHOW GRANTS FOR 'adminBaseMeuble1'@'localhost';
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for adminBaseMeuble1@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `adminBaseMeuble1`@`localhost` IDENTIFIED BY PASSWORD '*BD885142404C127AC29793EE3FBB1FDB01D2B25F' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `meubleFournisseur`.* TO `adminBaseMeuble1`@`localhost`                         |
+-------------------------------------------------------------------------------------------------------------------------+*

Se connecter à l'aide d'un utilisateur créé précédemment

adminX@serveurX:~$ mysql -u adminBaseMeuble1 -p meubleFournisseur
Enter password:
MariaDB [meubleFournisseur]>

Effectuer une requête SQL

MariaDB Documentation : SQL Statements

Les requêtes SQL sont décrites dans l'article Requêtes SQL

Accès depuis un hôte distant

Ouvrir le port pour tous les réseaux `IP`

Il est nécessaire de modifier la directive bind-address dans le fichier /etc/mysql/mariadb.conf.d/50-server.cnf en l'affectant à la valeur 0.0.0.0 afin que notre serveur soit à l'écoute des requêtes des hôtes de tous les réseaux.

Sauvegarder la configuration initiale

adminX@serveurX:~$ sudo cp /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/50-server.cnf.sav

Effectuer la configuration

Effectuez la modification à l'aide de l'éditeur de votre choix : nano, ...

Modifier `/etc/mysql/mariadb.conf.d/50-server.cnf`

adminX@serveurX:~$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Résultat de la modification :

adminX@serveurX:~$ diff /etc/mysql/mariadb.conf.d/50-server.cnf.sav /etc/mysql/mariadb.conf.d/50-server.cnf
29c29
< bind-address          = 127.0.0.1
---
> bind-address          = 0.0.0.0

Redémarrer le serveur MariaDB

adminX@serveurX:~$ sudo systemctl restart mariadb
adminX@serveurX:~$ systemctl status mariadb
● mariadb.service - MariaDB database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: en
   Active: active (running)

Visualiser les ports ouverts

adminX@serveurX:~$ ss -lnt sport = :3306
State      Recv-Q Send-Q      Local Address:Port     Peer Address:Port
LISTEN     0      80                      *:3306                *:*

Gérer les utilisateurs

Se connecter à la base à l'aide de l'utilisateur `root` de MySQL.

adminX@serveurX:~$ sudo mysql -u root

Créer un utilisateur

Nous allons créer l'utilisateur adminBaseMeuble2 qui pourra se connecter au serveur MariaDB avec le mot de passe bonjour depuis tous les hôtes du réseau 192.168.1.0/24.

MariaDB [(none)]> CREATE USER 'adminBaseMeuble2'@'192.168.56.%' IDENTIFIED BY 'bonjour';

Affecter des droits à un utilisateur

Nous allons donner à l'utilisateur adminBaseMeuble2 les droits SELECT, INSERT, UPDATE et DELETE sur les tables de la base meubleFournisseur ; il possèdera ces droits s'il se connecte depuis n'importe quel hôte du réseau 192.168.1.0/24.

MariaDB [(none)]>GRANT SELECT, INSERT, UPDATE, DELETE ON meubleFournisseur.* TO 'adminBaseMeuble2'@'192.168.56.%';
MariaDB [(none)]> FLUSH PRIVILEGES;

Se connecter à l'aide d'un utilisateur créé précédemment

Notre serveur MariaDB à pour adresse IP 192.168.56.11

root@debian:~$ mysql -h 192.168.56.11 -u adminBaseMeuble2 -p meubleFournisseur
Enter password: 
...
MariaDB [meubleFournisseur]>

Configurer l'accès sécurisé à MariaDB

Section réservé aux étudiants de seconde année en BTS CIEL du lycée michelet à Arpajon.

Installer et utiliser un serveur MariaDB

Installer

Installer

Valider l'installation

Vérifier que le processus est démarré

Vérifier que le port utilisé par MariaDB (3306 par défaut) est ouvert

Vérifier l'état du serveur

Utiliser

Se connecter au serveur

Se déconnecter

Charger un script

Code du script utilisé pour l'exemple :

Chargement du script

Visualiser les bases de données

Visualiser les tables de votre base

Gérer les utilisateurs

Créer un utilisateur

Affecter des droits à un utilisateur

Visualiser les utilisateurs

Visualiser les droits d'un utilisateur

Se connecter à l'aide d'un utilisateur créé précédemment

Effectuer une requête SQL

Accès depuis un hôte distant

Ouvrir le port pour tous les réseaux IP

Sauvegarder la configuration initiale

Effectuer la configuration

Modifier /etc/mysql/mariadb.conf.d/50-server.cnf

Redémarrer le serveur MariaDB

Visualiser les ports ouverts

Gérer les utilisateurs

Se connecter à la base à l'aide de l'utilisateur root de MySQL.

Créer un utilisateur

Affecter des droits à un utilisateur

Se connecter à l'aide d'un utilisateur créé précédemment

Configurer l'accès sécurisé à MariaDB

Vérifier que le port utilisé par `MariaDB` (`3306` par défaut) est ouvert

Ouvrir le port pour tous les réseaux `IP`

Modifier `/etc/mysql/mariadb.conf.d/50-server.cnf`

Se connecter à la base à l'aide de l'utilisateur `root` de MySQL.