Fiche mysql / mariadb : bases, utilisateurs, droits et sauvegardes

Catégorie : bases de données
Étiquettes : mysql, mariadb

1. Introduction

Dans un environnement BTS CIEL ou SIO, tu vas très souvent déployer des applications qui reposent sur une base de données (WordPress, GLPI, outils internes, etc.).
MariaDB et MySQL sont des SGBD (Systèmes de Gestion de Base de Données) largement utilisés sous Linux.

Dans cette fiche, tu vas apprendre à :

créer une base de données
créer un utilisateur dédié
gérer les droits (principe du moindre privilège)
sécuriser l’accès root
comprendre les mots de passe (hachage, chiffrement)
sauvegarder et automatiser les sauvegardes

Tu vas construire une configuration propre et sécurisée, comme en production.

2. Sommaire

Installation de MariaDB
Synthèse rapide
Fichiers importants
Configuration complète
Gestion des bases et utilisateurs
Sécurité des mots de passe
Sauvegardes et automatisation
Commandes utiles et dépannage
Logs et analyse
Bonnes pratiques
Pour aller plus loin
Liens utiles
TP pratiques

3. Synthèse Markdown pour ta fiche bristol

# MariaDB - Fiche rapide

## Installation
apt install mariadb-server

## Sécurisation
mysql_secure_installation

## Connexion
mysql -u root -p

## Créer une base
CREATE DATABASE db_app;

## Créer utilisateur
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'MotDePasseFort!';

## Donner droits
GRANT SELECT, INSERT, UPDATE, DELETE ON db_app.* TO 'appuser'@'localhost';

## Appliquer
FLUSH PRIVILEGES;

## Sauvegarde
mysqldump -u root -p db_app > backup.sql

## Restauration
mysql -u root -p db_app < backup.sql

## Automatisation
crontab -e
0 2 * * * mysqldump -u root -p'MDP' db_app > /backup/db.sql

4. Fichiers importants

Voici les principaux fichiers utilisés :

/etc/mysql/
├── my.cnf
├── mariadb.conf.d/
│   ├── 50-server.cnf
│   └── 60-galera.cnf
/var/lib/mysql/           # données des bases
/var/log/mysql/error.log  # logs
/etc/cron.d/              # tâches planifiées

5. Configuration complète

# Installation
sudo apt update
sudo apt install mariadb-server

# Démarrage et activation
sudo systemctl enable mariadb
sudo systemctl start mariadb

# Sécurisation initiale
sudo mysql_secure_installation

# Connexion
mysql -u root -p

# Création base
CREATE DATABASE db_app CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# Création utilisateur
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'MotDePasseFort!';

# Attribution droits minimum
GRANT SELECT, INSERT, UPDATE, DELETE ON db_app.* TO 'appuser'@'localhost';

# Application
FLUSH PRIVILEGES;

# Sauvegarde
mysqldump -u root -p db_app > /backup/db_app.sql

# Cron (édition)
crontab -e

# Exemple tâche quotidienne
0 2 * * * mysqldump -u root -p'MDP' db_app > /backup/db_app_$(date +\%F).sql

6. Gestion des bases et utilisateurs

Créer une base

CREATE DATABASE db_app;

Pourquoi ?
Tu isoles les données d’une application.

Créer un utilisateur

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'MotDePasseFort!';

localhost → accès uniquement local (sécurisé)
'%' → accès distant (à éviter sans firewall)

Donner des droits

GRANT SELECT, INSERT, UPDATE, DELETE ON db_app.* TO 'appuser'@'localhost';

Tu appliques ici le principe du moindre privilège :

SELECT → lecture
INSERT → ajout
UPDATE → modification
DELETE → suppression

Évite absolument :

GRANT ALL PRIVILEGES ON *.* TO 'user';

Modifier un utilisateur

ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'NouveauMDP!';

Supprimer un utilisateur

DROP USER 'appuser'@'localhost';

7. Sécurité des mots de passe

Important : MySQL ne chiffre pas, il hash

Quand tu fais :

CREATE USER 'user' IDENTIFIED BY 'password';

Le mot de passe est :

haché automatiquement (plugin mysql_native_password ou caching_sha2_password)
stocké dans la table mysql.user

Vérifier le type d’authentification

SELECT user, host, plugin FROM mysql.user;

Différence clé

Chiffrement → réversible
Hachage → NON réversible (sécurité)

Forcer un mot de passe fort

Dans /etc/mysql/mariadb.conf.d/50-server.cnf :

[mysqld]
plugin-load-add=simple_password_check

Ou installer :

sudo apt install libpam-pwquality

Bon mot de passe

12+ caractères
majuscule + minuscule
chiffres + symboles
pas de dictionnaire

8. Sauvegardes et automatisation

Sauvegarde manuelle

mysqldump -u root -p db_app > backup.sql

Options :

-u → utilisateur
-p → mot de passe
--all-databases → toutes les bases

Sauvegarde complète

mysqldump -u root -p --all-databases > full_backup.sql

Automatisation avec cron

crontab -e

Exemple :

0 2 * * * mysqldump -u root -p'MDP' db_app > /backup/db_app.sql

Explication :

0 2 * * * → tous les jours à 2h
redirection > → fichier

Sécurisation des sauvegardes

chmod 600 /backup/*

9. Commandes utiles et dépannage

Vérifier service

systemctl status mariadb

Se connecter sans mot de passe root (Unix socket)

sudo mysql

Lister bases

SHOW DATABASES;

Voir les droits

SHOW GRANTS FOR 'appuser'@'localhost';

Port utilisé

3306 (par défaut)

Firewall UFW

Accès local uniquement

sudo ufw deny 3306

Accès depuis serveur web

sudo ufw allow from 192.168.1.10 to any port 3306

10. Logs et analyse

Fichier principal

/var/log/mysql/error.log

Activer logs de requêtes