🐬 MariaDB – Installation, SQL & Administration

Les Moteurs de Stockage (Storage Engines)

La grande force de MariaDB/MySQL est son architecture "pluggable". Le moteur de stockage définit *comment* les données sont stockées, indexées et verrouillées.

Vous pouvez choisir le moteur par table.

-- Choisir un moteur à la création
CREATE TABLE ma_table (
  id INT PRIMARY KEY
) ENGINE=InnoDB;

Processus et Fichiers Clés

• Processus : Le serveur MariaDB s'exécute en tant que daemon (service) unique appelé mysqld.
• Fichier de Configuration :
  • Linux: /etc/mysql/my.cnf ou /etc/my.cnf.d/server.cnf
  • Windows: C:\Program Files\MariaDB [Version]\data\my.ini
• Socket UNIX : (Linux) Pour les connexions locales rapides sans passer par TCP/IP.
  • /var/run/mysqld/mysqld.sock
• Répertoire de Données : (datadir)
  • Linux: /var/lib/mysql/
  • Windows: C:\Program Files\MariaDB [Version]\data\
  • C'est là que les BDD (dossiers) et les tables (fichiers .ibd) sont stockés.
• Logs : (/var/log/mysql/)
  • error.log : Le plus important pour le debug (erreurs de démarrage, crashs).
  • slow-query.log : (Optionnel) Logue les requêtes lentes.
  • binlog : (Optionnel) Log binaire pour la réplication.

RHEL / CentOS 8+ / Fedora

Utilisation du script de configuration de dépôt.

# 1. Installer les prérequis
sudo yum install -y curl

# 2. Télécharger et exécuter le script de configuration
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

# 3. Installer MariaDB (dnf ou yum)
sudo dnf install -y MariaDB-server MariaDB-client MariaDB-backup

Vérification et Post-Installation

Une fois installé, le service mysql (ou mariadb) devrait démarrer.

# Activer le service au démarrage
sudo systemctl enable --now mariadb

# Vérifier le statut
sudo systemctl status mariadb

● mariadb.service - MariaDB 10.11.x database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; ...)
     Active: active (running) since Sat 2025-11-01 20:00:00 CET; 1h ago
...

# Vérifier la version
mysql --version
# mysql  Ver 15.1 Distrib 10.11.6-MariaDB, for debian-linux-gnu (x86_64)

Étape suivante OBLIGATOIRE

L'installation par défaut n'est **PAS** sécurisée (l'utilisateur root n'a pas de mot de passe ou utilise l'auth unix_socket).

Lancez immédiatement le script de sécurisation (voir section 2.3) :

sudo mysql_secure_installation

2. GRANT (Accorder des privilèges)

Un utilisateur nouvellement créé n'a **aucun** droit (même pas SELECT). Vous devez lui accorder des privilèges sur des BDD spécifiques.

-- Syntaxe
GRANT [PRIVILÈGES] ON [BASE].[TABLE] TO [UTILISATEUR];

-- Exemple 1: Donner tous les droits à 'app_web' sur la BDD 'ideo_lab_crm'
GRANT ALL PRIVILEGES ON ideo_lab_crm.* TO 'app_web'@'localhost';

-- Exemple 2: Donner des droits limités (Lecture/Écriture)
GRANT SELECT, INSERT, UPDATE, DELETE ON ideo_lab_prod.* TO 'app_prod'@'localhost';

-- Exemple 3: Donner un droit de lecture seule sur UNE seule table
GRANT SELECT ON ideo_lab_prod.clients TO 'analyste'@'%';

-- Exemple 4: Donner des droits admin globaux (type 'root')
GRANT ALL PRIVILEGES ON *.* TO 'dev_admin'@'%' WITH GRANT OPTION;
-- (WITH GRANT OPTION lui permet de donner des droits à d'autres)

-- Appliquer les changements (important sur les anciennes versions)
FLUSH PRIVILEGES;

3. Gérer les droits existants

-- Voir les droits d'un utilisateur
SHOW GRANTS FOR 'app_web'@'localhost';

+------------------------------------------------------------------+
| Grants for app_web@localhost                                     |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app_web'@'localhost' ...                  |
| GRANT ALL PRIVILEGES ON `ideo_lab_crm`.* TO 'app_web'@'localhost' |
+------------------------------------------------------------------+

REVOKE (Retirer des privilèges)

-- Retirer le droit de suppression
REVOKE DELETE ON ideo_lab_prod.* FROM 'app_prod'@'localhost';

-- Retirer tous les droits
REVOKE ALL PRIVILEGES ON ideo_lab_prod.* FROM 'app_prod'@'localhost';

DROP USER (Supprimer un utilisateur)

DROP USER 'app_web'@'localhost';

Types Texte (String)

VARCHAR vs TEXT : VARCHAR est stocké "in-line" (plus rapide) si la taille est raisonnable. TEXT est stocké "off-page". Préférez VARCHAR si vous connaissez la limite (ex: < 5000 chars).

Types Date & Heure

-- Utiliser les fonctions natives
INSERT INTO commandes (date_commande) VALUES (NOW());
-- (NOW() insère un DATETIME)

-- Utiliser CURRENT_TIMESTAMP par défaut
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    contenu TEXT,
    cree_le DATETIME DEFAULT CURRENT_TIMESTAMP,
    modifie_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Autres Types

WHERE (Quelles lignes ?)

Le filtre WHERE est la partie la plus importante pour optimiser une requête.

-- Égalité (utilise un index si 'id' est Clé Primaire)
SELECT * FROM utilisateurs WHERE id = 5;

-- Texte (sensible à la casse par défaut selon le Collate)
SELECT * FROM utilisateurs WHERE nom = 'Dupont';

-- Opérateurs
SELECT * FROM utilisateurs WHERE actif = TRUE;
SELECT * FROM commandes WHERE montant > 100;
SELECT * FROM commandes WHERE statut != 'livre';

-- AND / OR (Utiliser des parenthèses)
SELECT * FROM utilisateurs
WHERE (nom = 'Dupont' AND actif = TRUE)
   OR email = 'admin@mail.com';

-- LIKE (Recherche partielle)
-- % = N'importe quel caractère (0 ou plus)
SELECT * FROM utilisateurs WHERE email LIKE '%@mail.com'; -- Finit par
SELECT * FROM utilisateurs WHERE email LIKE 'jean%'; -- Commence par
SELECT * FROM utilisateurs WHERE email LIKE '%dupont%'; -- Contient

-- IN (Liste de valeurs)
SELECT * FROM utilisateurs WHERE id IN (1, 5, 10);

-- BETWEEN (Plage)
SELECT * FROM commandes WHERE date_creation BETWEEN '2025-01-01' AND '2025-01-31';

-- IS NULL (Vérifier si c'est vide)
SELECT * FROM utilisateurs WHERE prenom IS NULL;

ORDER BY (Trier les résultats)

Trie le résultat final. Peut être lent sur de grands datasets sans index.

-- Trier par date (plus récent d'abord)
SELECT * FROM utilisateurs
ORDER BY date_creation DESC;

-- Trier par nom (alphabétique)
SELECT * FROM utilisateurs
ORDER BY nom ASC; -- ASC est le défaut

-- Tri multiple
SELECT * FROM utilisateurs
ORDER BY nom ASC, prenom ASC;

LIMIT & OFFSET (Pagination)

Essentiel pour la pagination (ex: afficher 20 résultats par page).

-- Obtenir les 10 utilisateurs les plus récents
SELECT * FROM utilisateurs
ORDER BY date_creation DESC
LIMIT 10;

-- Obtenir la "Page 3" (20 résultats par page)
-- (Page 1: LIMIT 20 OFFSET 0)
-- (Page 2: LIMIT 20 OFFSET 20)
SELECT * FROM utilisateurs
ORDER BY id
LIMIT 20 OFFSET 40; -- (20 * (Page 3 - 1))

1. INNER JOIN (Intersection)

Ne retourne que les lignes qui ont une correspondance dans les **deux** tables.

[utilisateurs]      [commandes]
 (1) Alice ------+---- (102) 75.00
 (2) Bob   ------+---- (101) 50.00
                 `---- (103) 25.00
 (3) Charlie --- (Aucune commande)
                 
 (Utilisateur 4 n'existe pas) --- (104) 100.00

SELECT
    u.nom,
    c.id_cmd,
    c.montant
FROM utilisateurs AS u
INNER JOIN commandes AS c ON u.id = c.utilisateur_id;

Résultat :

nom   | id_cmd | montant
------|--------|---------
Bob   | 101    | 50.00
Alice | 102    | 75.00
Bob   | 103    | 25.00

Charlie (3) n'apparaît pas (pas de commande). La commande 104 n'apparaît pas (pas d'utilisateur).

2. LEFT JOIN (Garde toute la table de gauche)

Retourne **toutes** les lignes de la table de gauche (utilisateurs), et les correspondances de la table de droite (commandes). S'il n'y a pas de correspondance, les colonnes de droite sont NULL.

SELECT
    u.nom,
    c.id_cmd,
    c.montant
FROM utilisateurs AS u
LEFT JOIN commandes AS c ON u.id = c.utilisateur_id;

Résultat :

nom     | id_cmd | montant
--------|--------|---------
Alice   | 102    | 75.00
Bob     | 101    | 50.00
Bob     | 103    | 25.00
Charlie | NULL   | NULL

Charlie (3) apparaît, avec NULL pour ses commandes. C'est parfait pour trouver "les utilisateurs n'ayant jamais commandé" (WHERE c.id_cmd IS NULL).

Types d'Index (Contraintes)

Vérifier si un Index est utilisé (EXPLAIN)

EXPLAIN est l'outil le plus important pour l'optimisation. Il vous montre le "plan d'exécution" (comment MariaDB va exécuter votre requête).

EXPLAIN SELECT * FROM utilisateurs WHERE email = 'jean.dupont@mail.com';

Résultat (Bon) :

id | select_type | table        | type | possible_keys | key   | key_len | ref   | rows | Extra
---|-------------|--------------|------|---------------|-------|---------|-------|------|-------
1  | SIMPLE      | utilisateurs | ref  | email         | email | 258     | const | 1    | Using index

type: ref et rows: 1. MariaDB a utilisé l'index "email" et a trouvé 1 ligne. Très rapide.

EXPLAIN SELECT * FROM utilisateurs WHERE nom = 'Durand';
-- (En supposant qu'il n'y a PAS d'index sur 'nom')

Résultat (Mauvais) :

id | select_type | table        | type  | possible_keys | key  | key_len | ref   | rows   | Extra
---|-------------|--------------|-------|---------------|------|---------|-------|--------|----------------
1  | SIMPLE      | utilisateurs | ALL   | NULL          | NULL | NULL    | NULL  | 150000 | Using where

type: ALL (Full Table Scan) et rows: 150000. MariaDB a dû lire toute la table. Très lent.
Solution : CREATE INDEX idx_nom ON utilisateurs (nom);

Configuration (my.cnf / my.ini)

Le comportement du serveur mysqld est contrôlé par ce fichier. (Linux: /etc/mysql/my.cnf ou /etc/my.cnf.d/server.cnf).

Toute modification nécessite un redémarrage du service (sudo systemctl restart mariadb).

[mysqld]
# Le 'datadir' (où sont les données)
datadir=/var/lib/mysql

# Networking
bind-address = 127.0.0.1
# (Mettre '0.0.0.0' pour écouter sur toutes les interfaces)
port = 3306
max_connections = 151 # (Augmenter si besoin)

# InnoDB (Le plus important)
# La RAM allouée au cache InnoDB (cache des données et index)
# Mettre 50-70% de la RAM totale du serveur
innodb_buffer_pool_size = 4G

# Logs
log_error = /var/log/mysql/error.log
# (Décommenter pour le debug de performance)
# slow_query_log_file = /var/log/mysql/slow.log
# slow_query_log = 1
# long_query_time = 2

# (Pour la réplication)
# server-id = 1
# log-bin = /var/log/mysql/mariadb-bin

Cas d'usage de la réplication

• Scalabilité en lecture (Read Scaling) : L'application écrit sur le Master, mais lit depuis 3 Slaves (répartit la charge de SELECT).
• Analyse / BI (Reporting) : Les requêtes analytiques lourdes (GROUP BY, JOIN complexes) sont exécutées sur un Slave dédié, sans impacter la production (le Master).
• Sauvegarde (Backup) : On peut faire le mysqldump sur le Slave, sans verrouiller ni stresser le Master.
• Haute Disponibilité (Failover) : Si le Master tombe, on peut "promouvoir" manuellement (ou via un outil) le Slave pour qu'il devienne le nouveau Master. (Note: la réplication standard est *asynchrone*, une perte de données de quelques secondes est possible).

Étapes de configuration (Résumé)

1. Sur le MASTER (Serveur A)

# a. Modifier my.cnf
[mysqld]
server-id = 1
log-bin = /var/log/mysql/mariadb-bin
binlog_format = ROW

# b. Redémarrer MariaDB
sudo systemctl restart mariadb

# c. Créer un utilisateur pour la réplication
CREATE USER 'repl_user'@'[IP_DU_SLAVE]' IDENTIFIED BY 'pass_repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'[IP_DU_SLAVE]';
FLUSH PRIVILEGES;

# d. Noter la position du log
SHOW MASTER STATUS;
-- (Notez le 'File' et 'Position')

2. Sur le SLAVE (Serveur B)

# a. Modifier my.cnf
[mysqld]
server-id = 2 # (DOIT être différent du Master)
relay-log = /var/log/mysql/relay-bin
read_only = 1 # (Bonne pratique)

# b. Redémarrer MariaDB

# c. Configurer le Slave
CHANGE MASTER TO
  MASTER_HOST='[IP_DU_MASTER]',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='pass_repl',
  MASTER_LOG_FILE='[File de SHOW MASTER STATUS]',
  MASTER_LOG_POS=[Position de SHOW MASTER STATUS];

# d. Démarrer la réplication
START SLAVE;

# e. Vérifier
SHOW SLAVE STATUS\G

Cherchez Slave_IO_Running: Yes et Slave_SQL_Running: Yes.

DDL (Data Definition Language) - Structure

-- Créer une table
CREATE TABLE nom_table (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    age INT DEFAULT 18,
    categorie_id INT,
    FOREIGN KEY (categorie_id) REFERENCES categories(id)
) ENGINE=InnoDB;

-- Modifier une table
ALTER TABLE nom_table
    ADD COLUMN date_ajout DATETIME,
    MODIFY COLUMN nom VARCHAR(150),
    DROP COLUMN age;

-- Ajouter un index
CREATE INDEX idx_nom ON nom_table (nom);

-- Vider une table (rapide)
TRUNCATE TABLE nom_table;

-- Supprimer une table
DROP TABLE nom_table;

DML (Data Manipulation Language) - Données

-- Insérer
INSERT INTO nom_table (col1, col2) VALUES ('val1', 'val2');
INSERT INTO nom_table (col1, col2) VALUES ('a', 1), ('b', 2);

-- Lire
SELECT * FROM nom_table;
SELECT col1, col2 FROM nom_table;
SELECT * FROM nom_table WHERE col1 = 'val1';
SELECT * FROM nom_table WHERE col2 > 10 AND col1 LIKE 'A%';
SELECT * FROM nom_table WHERE col3 IN ('A', 'B', 'C');
SELECT * FROM nom_table WHERE col4 IS NOT NULL;
SELECT * FROM nom_table ORDER BY col1 DESC LIMIT 10 OFFSET 20;

-- Mettre à jour (TOUJOURS avec WHERE)
UPDATE nom_table SET col1 = 'new_val' WHERE id = 5;

-- Supprimer (TOUJOURS avec WHERE)
DELETE FROM nom_table WHERE id = 5;

-- Jointures
SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.t1_id;

SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.t1_id
WHERE t2.t1_id IS NULL; -- (Lignes de t1 sans t2)

-- Agrégats
SELECT col1, COUNT(id), SUM(montant)
FROM nom_table
WHERE col2 = 'val'
GROUP BY col1
HAVING COUNT(id) > 2;

DCL (Data Control Language) - Utilisateurs

-- Créer un utilisateur
CREATE USER 'user'@'host' IDENTIFIED BY 'password';

-- Donner des droits
GRANT ALL PRIVILEGES ON db_name.* TO 'user'@'host';
GRANT SELECT, INSERT ON db_name.table_name TO 'user'@'host';
GRANT USAGE ON *.* TO 'user'@'host'; -- (Droit de connexion seul)

-- Appliquer les droits
FLUSH PRIVILEGES;

-- Voir les droits
SHOW GRANTS FOR 'user'@'host';

-- Retirer des droits
REVOKE DELETE ON db_name.* FROM 'user'@'host';

-- Supprimer un utilisateur
DROP USER 'user'@'host';