- MariaDB/MySQL en prod : tuning et replication

MariaDB ou MySQL en 2026 : choix de production

MariaDB et MySQL partagent une histoire commune : MariaDB est un fork ne en 2009 apres le rachat de MySQL par Oracle. Pendant plusieurs annees, les deux moteurs sont restes quasiment interchangeables. Aujourd'hui, ils ont diverge sur certains points sans rompre la compatibilite SQL ni le protocole client. Un meme connecteur PHP, Python ou Node.js parle aux deux sans modification de code applicatif.

Differences fondamentales

Installation

# MariaDB sur Debian/Ubuntu (depuis les depots officiels)
sudo apt update
sudo apt install mariadb-server mariadb-client -y

# Verifie l'etat du service
sudo systemctl status mariadb

# Active le demarrage automatique
sudo systemctl enable mariadb

# Affiche la version
mariadb --version

# MySQL alternatif (depots Oracle pour la derniere version)
# Ajout du depot officiel MySQL
wget https://dev.mysql.com/get/mysql-apt-config_0.8.30-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.30-1_all.deb
sudo apt update
sudo apt install mysql-server -y

Securisation initiale du serveur

Apres l'installation, le serveur est dans un etat permissif : compte root sans mot de passe ou avec mot de passe par defaut, base de donnees test accessible, comptes anonymes. La premiere etape obligatoire consiste a executer le script de securisation et a configurer les acces reseau.

Le script mysql_secure_installation

# Lance le script interactif de securisation
sudo mysql_secure_installation

# Le script pose les questions suivantes :
# 1. Definir un mot de passe root        : OUI (mot de passe fort)
# 2. Supprimer les comptes anonymes      : OUI
# 3. Desactiver root remote login        : OUI
# 4. Supprimer la base test              : OUI
# 5. Recharger les privileges            : OUI

# Apres le script, tentez une connexion root
sudo mysql -u root -p

Creer un compte applicatif avec privileges minimaux

Le compte root ne doit jamais etre utilise par une application. Creez un compte dedie avec les privileges strictement necessaires.

-- Connectez-vous en root
-- Creation de la base de donnees
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Creation d'un compte applicatif (depuis localhost uniquement)
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'MotDePasseFortIci123!';

-- Octroi des privileges minimaux sur la base de l'application
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'myapp_user'@'localhost';

-- Pour un compte de migration (DDL temporaire)
CREATE USER 'myapp_migrator'@'localhost' IDENTIFIED BY 'AutreMotDePasse456!';
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp_migrator'@'localhost';

-- Pour un compte read-only (analytics, reporting)
CREATE USER 'myapp_readonly'@'%' IDENTIFIED BY 'ReadOnlyPass789!';
GRANT SELECT ON myapp.* TO 'myapp_readonly'@'%';

-- Recharge les privileges
FLUSH PRIVILEGES;

-- Verifie les comptes existants et leurs privileges
SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 'myapp_user'@'localhost';

Activer SSL/TLS pour les connexions reseau

Si une application doit se connecter depuis un autre serveur, exigez le chiffrement TLS. Ne laissez jamais MySQL/MariaDB ecouter sur 0.0.0.0 sans TLS.

# Verifie si TLS est deja active
sudo mysql -e "SHOW VARIABLES LIKE '%ssl%';"

# Sur les versions recentes, MariaDB et MySQL generent automatiquement
# des certificats auto-signes au premier demarrage dans /var/lib/mysql/

# Force un compte a utiliser SSL
ALTER USER 'myapp_user'@'%' REQUIRE SSL;

# Pour exiger un certificat client signe par votre CA
CREATE USER 'app_secure'@'%' IDENTIFIED BY 'pass'
    REQUIRE X509;
GRANT SELECT ON myapp.* TO 'app_secure'@'%';

Configurer bind-address

# Edite le fichier de configuration
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

# Cherche la directive bind-address dans la section [mysqld]
# Par defaut sur Debian : bind-address = 127.0.0.1 (correct pour app locale)

# Pour exposer sur le LAN uniquement (replication, app sur autre serveur)
bind-address = 10.0.0.5

# JAMAIS bind-address = 0.0.0.0 sur Internet sans firewall + SSL

# Recharge le service apres modification
sudo systemctl restart mariadb

Tuning de la configuration my.cnf

La configuration par defaut est conservatrice : adaptee a un poste de developpement, pas a un serveur de production. Les bons reglages dependent de la RAM disponible, du nombre de connexions simultanees attendues et du profil de charge (lecture intensive, ecriture intensive, mixte).

Fichier de configuration principal

# Sur Debian/Ubuntu, le fichier principal est :
# /etc/mysql/mariadb.conf.d/50-server.cnf  (MariaDB)
# /etc/mysql/mysql.conf.d/mysqld.cnf       (MySQL)

# Cree un fichier de tuning prioritaire
sudo nano /etc/mysql/mariadb.conf.d/99-tuning.cnf

Configuration de production complete

# ============================================================
# /etc/mysql/mariadb.conf.d/99-tuning.cnf — Tuning production
# Hypothese : serveur dedie avec 16 Go RAM
# ============================================================

[mysqld]

# -------- Connexions --------

# Nombre maximal de connexions simultanees
# Trop eleve = sur-allocation RAM ; trop bas = erreurs "Too many connections"
# Calcul : RAM disponible / RAM par connexion (~10 Mo)
max_connections = 200

# Timeout d'une connexion inactive (en secondes)
# Trop long = connexions orphelines retiennent de la RAM
wait_timeout = 600
interactive_timeout = 600

# Timeout pour les connexions n'ayant pas encore termine le handshake
connect_timeout = 10

# -------- InnoDB (moteur principal) --------

# Buffer pool : cache principal des pages d'index et donnees
# Regle d'or : 70 % de la RAM si serveur dedie, 50 % sinon
innodb_buffer_pool_size = 11G

# Decoupe le buffer pool en plusieurs instances (concurrence)
# Recommandation : 1 instance par Go de buffer (max 64)
innodb_buffer_pool_instances = 8

# Taille des fichiers de log InnoDB (transactions en attente)
# Plus grand = transactions plus efficaces, recovery plus long
innodb_log_file_size = 1G

# Buffer des transactions en cours (avant flush sur log)
innodb_log_buffer_size = 64M

# Mode de flush du log InnoDB
# 1 = ACID complet (par defaut, le plus sur)
# 2 = legerement plus rapide, perd ~1 sec de transactions en cas de crash OS
innodb_flush_log_at_trx_commit = 1

# Methode de flush systeme de fichiers (Linux)
# O_DIRECT evite le double buffering OS+DB (plus rapide sur disque dedie)
innodb_flush_method = O_DIRECT

# Concurrency : limite le nombre de threads InnoDB simultanes
# 0 = pas de limite (par defaut, recommande sur serveurs modernes)
innodb_thread_concurrency = 0

# Active le checksum des pages (detection corruption)
innodb_checksum_algorithm = crc32

# -------- Tables et index --------

# Cache des definitions de tables ouvertes
table_open_cache = 4000

# Cache des definitions structurelles
table_definition_cache = 2000

# -------- Query Cache (MariaDB seulement, retire dans MySQL 8) --------

# Le query cache est globalement nuisible en concurrence eleve
# Desactivez-le sauf cas tres specifique (lecture quasi-unique)
query_cache_type = 0
query_cache_size = 0

# -------- Threads --------

# Cache de threads pour les nouvelles connexions
thread_cache_size = 50

# Active thread pool (MariaDB) pour gerer les pics de connexions
thread_handling = pool-of-threads
thread_pool_size = 8

# -------- Tri et tampon par session --------

# Buffer pour les operations ORDER BY et GROUP BY
# Attention : alloue par session, multiplie par max_connections
sort_buffer_size = 4M

# Buffer pour les jointures sans index
join_buffer_size = 4M

# Buffer de lecture sequentielle
read_buffer_size = 2M

# -------- Slow query log --------

# Active le log des requetes lentes (vital pour l'optimisation)
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log

# Seuil en secondes au-dela duquel une requete est consideree lente
long_query_time = 1

# Log aussi les requetes sans index utilise
log_queries_not_using_indexes = 1

# -------- Binary log (necessaire pour replication et PITR) --------

# Active le binary log
log_bin = /var/log/mysql/mysql-bin.log

# Format du binary log
# ROW : enregistre les changements de lignes (recommande, sur)
# MIXED : selon le type de requete
# STATEMENT : les requetes telles quelles (deprecate)
binlog_format = ROW

# Identifiant unique du serveur (different sur chaque noeud de replication)
server_id = 1

# Duree de conservation des binary logs (7 jours)
binlog_expire_logs_seconds = 604800

# Compresse les binary logs (gain disque, leger CPU)
log_bin_compress = ON

# -------- Encodage --------

# UTF-8 4 bytes (supporte les emoji et tout l'Unicode)
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

# -------- Securite --------

# Desactive les fichiers locaux (LOAD DATA LOCAL INFILE)
local_infile = 0

# Desactive les fonctions externes non necessaires
secure_file_priv = /var/lib/mysql-files/

Appliquer et verifier

# Verifie la syntaxe de la configuration
sudo mysqld --validate-config

# Redemarre le service pour appliquer
sudo systemctl restart mariadb

# Verifie que les nouvelles valeurs sont appliquees
sudo mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
sudo mysql -e "SHOW VARIABLES LIKE 'max_connections';"

# Verifie que le buffer pool est utilise correctement
sudo mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool%';"

# Hit ratio : doit etre > 99% en regime stationnaire
# Innodb_buffer_pool_read_requests : lectures totales
# Innodb_buffer_pool_reads : lectures qui ont du aller au disque

Sauvegardes coherentes et restauration

Une base de donnees sans sauvegarde est une bombe a retardement. La question n'est pas "si" mais "quand" un incident se produira : suppression accidentelle, corruption disque, ransomware, erreur de migration. Une sauvegarde efficace combine plusieurs methodes pour repondre a differents scenarios de restauration.

Methode 1 : mysqldump (logique)

mysqldump exporte toutes les donnees sous forme de scripts SQL. Lent sur les grosses bases mais portable, lisible, simple a restaurer.

# Backup logique d'une base avec coherence transactionnelle
# --single-transaction : isole le dump dans une transaction (pas de lock pour InnoDB)
# --quick : streame ligne par ligne sans tout charger en RAM
# --routines : inclut les procedures stockees et fonctions
# --triggers : inclut les triggers
# --events : inclut les events
# --master-data=2 : note la position binlog (utile pour PITR et replication)
# --gtid : inclut les GTID si replication GTID active

mysqldump -u root -p \
    --single-transaction \
    --quick \
    --routines \
    --triggers \
    --events \
    --master-data=2 \
    --gtid \
    myapp > /backup/myapp-$(date +%Y%m%d).sql

# Backup compresse a la volee (recommande pour les gros volumes)
mysqldump -u root -p --single-transaction myapp \
    | gzip > /backup/myapp-$(date +%Y%m%d).sql.gz

# Backup chiffre avec gpg (pour stockage hors site)
mysqldump -u root -p --single-transaction myapp \
    | gzip \
    | gpg --encrypt --recipient admin@exemple.com \
    > /backup/myapp-$(date +%Y%m%d).sql.gz.gpg

# Backup de TOUTES les bases en un fichier
mysqldump -u root -p --all-databases --single-transaction \
    > /backup/full-$(date +%Y%m%d).sql

Methode 2 : mariabackup / Percona XtraBackup (physique)

Sauvegarde physique des fichiers de donnees pendant que la base tourne. Beaucoup plus rapide que mysqldump pour les bases > 50 Go. Supporte les backups incrementaux.

# Installation de mariabackup (livre avec mariadb-backup)
sudo apt install mariadb-backup -y

# Backup complet
sudo mariabackup --backup \
    --target-dir=/backup/mariabackup-$(date +%Y%m%d) \
    --user=root \
    --password=VotreMotDePasse

# Etape de "prepare" : finalise les fichiers de sauvegarde pour restauration
sudo mariabackup --prepare \
    --target-dir=/backup/mariabackup-$(date +%Y%m%d)

# Backup incremental (depuis le dernier complet)
sudo mariabackup --backup \
    --target-dir=/backup/incremental-$(date +%Y%m%d-%H%M) \
    --incremental-basedir=/backup/mariabackup-20260507 \
    --user=root \
    --password=VotreMotDePasse

Restauration

# Restauration depuis mysqldump
mysql -u root -p myapp < /backup/myapp-20260507.sql

# Restauration depuis fichier compresse
gunzip < /backup/myapp-20260507.sql.gz | mysql -u root -p myapp

# Restauration physique avec mariabackup
sudo systemctl stop mariadb
sudo rm -rf /var/lib/mysql/*
sudo mariabackup --copy-back --target-dir=/backup/mariabackup-20260507
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mariadb

Script automatise avec cron

# /usr/local/sbin/backup-mariadb.sh
#!/bin/bash
# Backup quotidien avec rotation 7 jours

BACKUP_DIR="/backup/mariadb"
DATE=$(date +%Y%m%d)
RETENTION_DAYS=7

# Cree le dossier si inexistant
mkdir -p "$BACKUP_DIR"

# Backup
mysqldump -u backup_user -p"$(cat /etc/mariadb/backup.pass)" \
    --all-databases \
    --single-transaction \
    --routines --triggers --events \
    --master-data=2 \
    | gzip > "$BACKUP_DIR/full-$DATE.sql.gz"

# Verification de la taille (alerte si trop petit)
SIZE=$(stat -c%s "$BACKUP_DIR/full-$DATE.sql.gz")
if [ "$SIZE" -lt 1000000 ]; then
    echo "Backup suspect : taille = $SIZE octets" | mail -s "ALERTE BACKUP" admin@exemple.com
    exit 1
fi

# Rotation : supprime les backups > 7 jours
find "$BACKUP_DIR" -name "full-*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup OK : $BACKUP_DIR/full-$DATE.sql.gz ($SIZE octets)"

# Cron quotidien a 2h du matin
sudo crontab -e

0 2 * * * /usr/local/sbin/backup-mariadb.sh >> /var/log/backup-mariadb.log 2>&1

Replication master-slave avec GTID

La replication MySQL/MariaDB permet d'avoir une copie quasi temps reel des donnees sur un second serveur. Cas d'usage typiques : haute disponibilite (failover en cas de panne du master), montee en charge en lecture (slaves dedies aux SELECT), sauvegardes sans impact sur le master, geo-replication pour la latence.

Les GTID (Global Transaction IDs) modernisent la replication : chaque transaction est identifiee par un identifiant global unique. Les slaves savent precisement ou ils sont sans avoir a connaitre les positions binlog exactes. La promotion d'un slave en master devient triviale.

Configuration cote master

# /etc/mysql/mariadb.conf.d/99-replication.cnf (sur le master)

[mysqld]

# Identifiant unique du serveur (different sur chaque noeud)
server_id = 1

# Active le binary log (necessaire pour la replication)
log_bin = /var/log/mysql/mysql-bin.log

# Format ROW (recommande, le plus sur)
binlog_format = ROW

# GTID activation
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

# Conservation des binlogs (7 jours)
binlog_expire_logs_seconds = 604800

# Synchronisation binlog au commit (ACID)
sync_binlog = 1

# Sur le master, cree le compte de replication
sudo mysql -u root -p

CREATE USER 'repl'@'%' IDENTIFIED BY 'MotDePasseReplFort123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

# Verifie l'etat du master
SHOW MASTER STATUS;

# Sortie attendue
# +-------------------+----------+--------------+-------------------+
# | File              | Position | Binlog_Do_DB | Executed_Gtid_Set |
# +-------------------+----------+--------------+-------------------+
# | mysql-bin.000003  |     1234 |              | 0-1-1234          |
# +-------------------+----------+--------------+-------------------+

Cloner les donnees sur le slave

# Sur le master : dump complet avec coherence transactionnelle
mysqldump -u root -p \
    --all-databases \
    --single-transaction \
    --routines --triggers --events \
    --gtid \
    --master-data=2 \
    > /tmp/master-dump.sql

# Transfere le dump vers le slave
scp /tmp/master-dump.sql user@slave-server:/tmp/

# Sur le slave : importe le dump
mysql -u root -p < /tmp/master-dump.sql

# Le dump contient deja les informations GTID grace a --gtid

Configuration cote slave

# /etc/mysql/mariadb.conf.d/99-replication.cnf (sur le slave)

[mysqld]

# Identifiant DIFFERENT du master
server_id = 2

# Active le binary log meme sur le slave (si on veut chainer ou promouvoir)
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW

# GTID activation
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

# Slave en lecture seule (forcer les ecritures sur le master)
read_only = ON
super_read_only = ON

# Relay logs (logs de replication recus du master)
relay_log = /var/log/mysql/relay-bin
relay_log_recovery = ON

# Sur le slave : configure la connexion au master
sudo mysql -u root -p

CHANGE MASTER TO
    MASTER_HOST='10.0.0.10',
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='MotDePasseReplFort123!',
    MASTER_AUTO_POSITION=1;

-- Demarre la replication
START SLAVE;

-- Verifie l'etat
SHOW SLAVE STATUS\G

-- Lignes critiques a verifier :
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0 (ou faible)
-- Last_Error: (vide)

Test de la replication

-- Sur le master : cree une donnee de test
USE myapp;
INSERT INTO users (name) VALUES ('Test replication 2026-05-07');

-- Sur le slave : verifie qu'elle apparait (delai typique : < 1 seconde)
USE myapp;
SELECT * FROM users WHERE name LIKE 'Test replication%';

Monitoring et analyse de performance

Un serveur de base de donnees demande un monitoring continu : performance des requetes, espace disque, cache hit ratio, replication lag. Plusieurs outils complementent les commandes natives de MySQL/MariaDB.

SHOW PROCESSLIST et statut des sessions

-- Liste des sessions actives
SHOW FULL PROCESSLIST;

-- Filtrer les requetes longues uniquement (> 5 secondes)
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE time > 5
ORDER BY time DESC;

-- Tuer une session bloquee
KILL 12345;

-- Variables de statut interessantes
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages%';

Analyse du slow query log avec mysqldumpslow

# Top 10 des requetes les plus lentes (cumul)
sudo mysqldumpslow -t 10 /var/log/mysql/mariadb-slow.log

# Top 10 trie par nombre d'occurrences
sudo mysqldumpslow -t 10 -s c /var/log/mysql/mariadb-slow.log

# Top 10 trie par duree moyenne
sudo mysqldumpslow -t 10 -s at /var/log/mysql/mariadb-slow.log

Percona Toolkit : analyse avancee

# Installation de Percona Toolkit
sudo apt install percona-toolkit -y

# pt-query-digest : analyse fine du slow log avec percentiles
pt-query-digest /var/log/mysql/mariadb-slow.log

# pt-summary : rapport complet sur l'etat du serveur
pt-mysql-summary --user=root --password=PASS

# pt-online-schema-change : modification de schema sans lock
pt-online-schema-change --alter "ADD COLUMN status VARCHAR(20)" \
    D=myapp,t=users,h=localhost,u=root,p=PASS \
    --execute

mytop : monitoring temps reel

# Installation de mytop (top-like pour MySQL)
sudo apt install mytop -y

# Lance le monitoring
sudo mytop -u root -p

# Touches utiles
# h : aide
# c : voir les commandes par utilisateur
# t : threads
# k : kill une session
# s : changer l'intervalle de rafraichissement

Metriques cles a surveiller

Checklist production et plan de PRA

Securite

• mysql_secure_installation execute, comptes anonymes supprimes.
• Mot de passe root fort, stocke dans un coffre.
• Comptes applicatifs avec privileges minimaux.
• bind-address restrictif (127.0.0.1 ou IP LAN).
• SSL/TLS active si connexions reseau.
• local_infile = 0.

Tuning

• innodb_buffer_pool_size a 70 % RAM (serveur dedie).
• max_connections dimensionne et limite raisonnable.
• Slow query log active avec seuil approprie.
• Binary log active avec retention 7+ jours.
• binlog_format = ROW.

Sauvegardes

• Sauvegarde quotidienne automatisee via cron.
• Au moins deux methodes : mysqldump + mariabackup.
• Retention 7 jours sur place + 30 jours hors site chiffre.
• Test de restauration mensuel sur serveur tiers.
• Surveillance de la taille des sauvegardes (alerte si decroissance suspecte).

Replication

• Slave configure avec GTID actif.
• read_only = ON sur le slave.
• Monitoring du replication lag (alerte si > 30 sec).
• Procedure de promotion documentee et testee.

Plan de PRA (Plan de Reprise d'Activite)

• RPO defini (Recovery Point Objective : perte max acceptable, ex: 1h).
• RTO defini (Recovery Time Objective : duree max d'indisponibilite, ex: 4h).
• Procedure de restauration ecrite et testee.
• Contacts d'astreinte documentes.
• Acces console/IPMI/cloud panel verifie.