« Avec Docker, la gestion de MySQL en production sur un VPS devient plus flexible et sécurisée. » . Ce guide exhaustif vous accompagne dans la gestion complète de votre base de données MySQL de l’application monapplication via le terminal. Conçu pour les développeurs, administrateurs de bases de données et équipes DevOps, il couvre tous les aspects de l’administration MySQL en environnement containerisé Docker.
Technologies couvertes
- Base de données : MySQL 8.0, SQL avancé, Procédures stockées
- Infrastructure : Docker containers, Volume management
- Framework : Laravel 11 Eloquent, Migrations, Seeders
- Outils : MySQL CLI, mysqldump, mysqlcheck, pt-tools
- Monitoring : Performance Schema, Slow Query Log, EXPLAIN
Convention de codage
- 🇬🇧 English : Commentaires techniques en anglais
- 🇫🇷 Français : Traductions et explications en français
commandes: Formatage des commandes shell et SQL- Gras : Points importants et concepts clés
-
Citations : Objectifs et notes importantes
Navigation rapide
- Utilisez
Ctrl+Fpour rechercher des requêtes spécifiques
Table des matières :
- 1 Index des Commandes Essentielles
- 2 Connexion à la Base de Données
- 3 Structure de la Base monapplication
- 4 Requêtes Essentielles
- 5 Gestion des Utilisateurs
- 6 Sauvegardes et Restauration
- 7 Monitoring et Performance
- 8 Maintenance de la Base
- 9 Sécurité des Données
- 10 Optimisation Avancée
- 11 Scripts et Procédures
Index des Commandes Essentielles
| 🎯Action | 📝Commande | |
|---|---|---|
| Se connecter | docker exec -it db mysql -u root -p |
|
| Lister les tables | SHOW TABLES; |
|
| Voir les utilisateurs | SELECT user,host FROM mysql.user; |
|
| Sauvegarde complète | mysqldump monapplication > backup.sql |
|
| Analyser performance | SHOW PROCESSLIST; |
|
| Optimiser table | OPTIMIZE TABLE table_name; |
|
| Vérifier taille DB | SELECT table_schema, SUM(data_length)... |
|
| Requêtes lentes | SHOW VARIABLES LIKE 'slow_query%'; |
Connexion à la Base de Données
🎯 Objectif : Maîtriser les différentes méthodes de connexion à votre instance MySQL containerisée pour un accès sécurisé et efficace.
Cette section présente toutes les méthodes de connexion à votre base de données MySQL monapplication. Que ce soit pour des opérations de maintenance, de debug ou d’administration, vous apprendrez à établir des connexions fiables et sécurisées via Docker.
Connexion via Docker Container
# 🇬🇧 Connect to MySQL container / 🇫🇷 Se connecter au conteneur MySQL
docker exec -it db mysql -u root -p
# 🇬🇧 Direct connection with password / 🇫🇷 Connexion directe avec mot de passe
docker exec -it db mysql -u root -p[PASSWORD] monapplication
# 🇬🇧 Connect as specific user / 🇫🇷 Se connecter avec un utilisateur spécifique
docker exec -it db mysql -u monapplication -p monapplication
# 🇬🇧 Connect and execute command / 🇫🇷 Se connecter et exécuter une commande
docker exec db mysql -u root -p[PASSWORD] -e "SHOW DATABASES;"
Connexion depuis l’Extérieur du Container
# 🇬🇧 Connect from host if port is exposed / 🇫🇷 Connexion depuis l'hôte si port exposé
mysql -h localhost -P 3306 -u monapplication -p monapplication
# 🇬🇧 Remote connection (if configured) / 🇫🇷 Connexion distante (si configurée)
mysql -h your-vps-ip -P 3306 -u monapplication -p monapplication
# 🇬🇧 Connection with SSL / 🇫🇷 Connexion avec SSL
mysql -h localhost -P 3306 -u monapplication -p monapplication --ssl-mode=REQUIRED
Vérification de la Connexion
-- 🇬🇧 Check connection status / 🇫🇷 Vérifier l'état de la connexion
SELECT CONNECTION_ID(), USER(), DATABASE(), VERSION();
-- 🇬🇧 Show current time and timezone / 🇫🇷 Afficher l'heure et fuseau horaire
SELECT NOW(), @@time_zone, @@system_time_zone;
-- 🇬🇧 Display server variables / 🇫🇷 Afficher les variables du serveur
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
Configuration de Session
-- 🇬🇧 Set safe updates / 🇫🇷 Activer les mises à jour sécurisées
SET sql_safe_updates = 1;
-- 🇬🇧 Set timezone / 🇫🇷 Définir le fuseau horaire
SET time_zone = '+02:00';
-- 🇬🇧 Show warnings / 🇫🇷 Afficher les avertissements
SET sql_mode = 'STRICT_TRANS_TABLES';
Structure de la Base monapplication
🎯 Objectif : Comprendre l’architecture de votre base de données et naviguer efficacement dans les tables et relations de l’application monapplication.
Cette section détaille la structure complète de votre base de données monapplication basée sur Laravel 11. Vous découvrirez l’organisation des tables, leurs relations et les conventions utilisées pour optimiser vos requêtes et comprendre le modèle de données.
1. Vue d’Ensemble de la Base
-- 🇬🇧 List all databases / 🇫🇷 Lister toutes les bases de données
SHOW DATABASES;
-- 🇬🇧 Use monapplication database / 🇫🇷 Utiliser la base monapplication
USE monapplication;
-- 🇬🇧 Show all tables / 🇫🇷 Afficher toutes les tables
SHOW TABLES;
-- 🇬🇧 Show table count / 🇫🇷 Compter le nombre de tables
SELECT COUNT(*) AS table_count
FROM information_schema.tables
WHERE table_schema = 'monapplication';
2. Tables Principales de monapplication
-- 🇬🇧 Core application tables / 🇫🇷 Tables principales de l'application
DESCRIBE users; -- Utilisateurs de l'application
DESCRIBE roles; -- Rôles et permissions
DESCRIBE exercises; -- Exercices de natation
DESCRIBE workouts; -- Séances d'entraînement
DESCRIBE plans; -- Plans d'entraînement
DESCRIBE swim_sets; -- Sets de natation
DESCRIBE uploads; -- Fichiers uploadés
DESCRIBE pages; -- Contenu des pages
DESCRIBE my_lists; -- Listes personnalisées utilisateurs
-- 🇬🇧 Junction/Pivot tables / 🇫🇷 Tables de liaison
DESCRIBE workout_exercises; -- Liaison workouts <-> exercises
DESCRIBE workout_swim_sets; -- Liaison workouts <-> swim_sets
DESCRIBE plan_workouts; -- Liaison plans <-> workouts
DESCRIBE my_list_items; -- Items des listes personnalisées
3. Analyse des Relations
-- 🇬🇧 Show foreign key constraints / 🇫🇷 Afficher les contraintes de clés étrangères
SELECT
CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'monapplication'
AND REFERENCED_TABLE_NAME IS NOT NULL;
-- 🇬🇧 Show table sizes / 🇫🇷 Afficher la taille des tables
SELECT
table_name AS 'Table',
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
table_rows AS 'Rows'
FROM information_schema.TABLES
WHERE table_schema = 'monapplication'
ORDER BY (data_length + index_length) DESC;
4. Structure Détaillée des Tables Clés
-- 🇬🇧 Users table structure / 🇫🇷 Structure de la table utilisateurs
SHOW CREATE TABLE users;
-- 🇬🇧 Workouts table with indexes / 🇫🇷 Table workouts avec index
SHOW INDEXES FROM workouts;
-- 🇬🇧 Check table engine and charset / 🇫🇷 Vérifier moteur et encodage
SELECT
table_name,
engine,
table_collation,
create_time
FROM information_schema.tables
WHERE table_schema = 'monapplication';
Requêtes Essentielles
Objectif : Maîtriser les requêtes SQL fondamentales pour extraire, analyser et manipuler efficacement les données de votre application monapplication.
Cette section rassemble les requêtes SQL les plus utiles pour la gestion quotidienne de votre application. Des statistiques utilisateurs aux analyses de performance, ces requêtes vous permettront de comprendre et optimiser l’utilisation de votre plateforme.
1. Statistiques Générales
-- 🇬🇧 Count total users / 🇫🇷 Compter le nombre total d'utilisateurs
SELECT COUNT(*) AS total_users FROM users;
-- 🇬🇧 Count active users (last 30 days) / 🇫🇷 Utilisateurs actifs (30 derniers jours)
SELECT COUNT(*) AS active_users
FROM users
WHERE updated_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);
-- 🇬🇧 Users by role / 🇫🇷 Utilisateurs par rôle
SELECT r.name AS role_name, COUNT(u.id) AS user_count
FROM roles r
LEFT JOIN users u ON r.id = u.role_id
GROUP BY r.id, r.name
ORDER BY user_count DESC;
-- 🇬🇧 Recent registrations / 🇫🇷 Inscriptions récentes
SELECT
DATE(created_at) AS registration_date,
COUNT(*) AS new_users
FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY DATE(created_at)
ORDER BY registration_date DESC;
2. Analyses des Entraînements
-- 🇬🇧 Most popular exercises / 🇫🇷 Exercices les plus populaires
SELECT
e.name AS exercise_name,
e.category,
COUNT(we.exercise_id) AS usage_count
FROM exercises e
LEFT JOIN workout_exercises we ON e.id = we.exercise_id
GROUP BY e.id, e.name, e.category
ORDER BY usage_count DESC
LIMIT 10;
-- 🇬🇧 Workouts statistics / 🇫🇷 Statistiques des entraînements
SELECT
u.first_name,
u.last_name,
COUNT(w.id) AS total_workouts,
MAX(w.created_at) AS last_workout
FROM users u
LEFT JOIN workouts w ON u.id = w.user_id
GROUP BY u.id, u.first_name, u.last_name
HAVING total_workouts > 0
ORDER BY total_workouts DESC;
-- 🇬🇧 Monthly workout trends / 🇫🇷 Tendances mensuelles des entraînements
SELECT
YEAR(created_at) AS year,
MONTH(created_at) AS month,
COUNT(*) AS workout_count
FROM workouts
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY YEAR(created_at), MONTH(created_at)
ORDER BY year DESC, month DESC;
3. Gestion du Contenu
-- 🇬🇧 Upload statistics / 🇫🇷 Statistiques des uploads
SELECT
file_type,
COUNT(*) AS file_count,
ROUND(SUM(file_size) / 1024 / 1024, 2) AS total_size_mb
FROM uploads
GROUP BY file_type
ORDER BY total_size_mb DESC;
-- 🇬🇧 Recent uploads / 🇫🇷 Uploads récents
SELECT
u.first_name,
u.last_name,
up.file_name,
up.file_type,
up.created_at
FROM uploads up
JOIN users u ON up.user_id = u.id
ORDER BY up.created_at DESC
LIMIT 20;
-- 🇬🇧 Page content analysis / 🇫🇷 Analyse du contenu des pages
SELECT
title,
slug,
status,
LENGTH(content) AS content_length,
updated_at
FROM pages
ORDER BY updated_at DESC;
4. Requêtes de Maintenance
-- 🇬🇧 Find orphaned records / 🇫🇷 Trouver les enregistrements orphelins
SELECT w.id, w.title
FROM workouts w
LEFT JOIN users u ON w.user_id = u.id
WHERE u.id IS NULL;
-- 🇬🇧 Duplicate detection / 🇫🇷 Détection des doublons
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING count > 1;
-- 🇬🇧 Data integrity check / 🇫🇷 Vérification de l'intégrité des données
SELECT
'workout_exercises' AS table_name,
COUNT(*) AS orphaned_count
FROM workout_exercises we
LEFT JOIN workouts w ON we.workout_id = w.id
WHERE w.id IS NULL
UNION ALL
SELECT
'workout_swim_sets' AS table_name,
COUNT(*) AS orphaned_count
FROM workout_swim_sets wss
LEFT JOIN workouts w ON wss.workout_id = w.id
WHERE w.id IS NULL;
Gestion des Utilisateurs
Objectif : Administrer efficacement les comptes utilisateurs, gérer les permissions et maintenir la sécurité des accès à votre base de données MySQL.
Cette section couvre la gestion complète des utilisateurs MySQL et des utilisateurs de l’application monapplication. Vous apprendrez à créer, modifier et supprimer des comptes, gérer les privilèges et auditer les accès pour maintenir une sécurité optimale.
1. Gestion des Utilisateurs MySQL
-- 🇬🇧 Show all MySQL users / 🇫🇷 Afficher tous les utilisateurs MySQL
SELECT user, host, authentication_string, password_expired
FROM mysql.user
ORDER BY user;
-- 🇬🇧 Create new MySQL user / 🇫🇷 Créer un nouvel utilisateur MySQL
CREATE USER 'monapplication_readonly'@'%' IDENTIFIED BY 'secure_password';
-- 🇬🇧 Grant specific privileges / 🇫🇷 Accorder des privilèges spécifiques
GRANT SELECT ON monapplication.* TO 'monapplication_readonly'@'%';
GRANT SELECT, INSERT, UPDATE ON monapplication.users TO 'monapplication_app'@'%';
-- 🇬🇧 Show user privileges / 🇫🇷 Afficher les privilèges d'un utilisateur
SHOW GRANTS FOR 'monapplication'@'%';
-- 🇬🇧 Revoke privileges / 🇫🇷 Révoquer des privilèges
REVOKE INSERT ON monapplication.* FROM 'monapplication_readonly'@'%';
-- 🇬🇧 Drop user / 🇫🇷 Supprimer un utilisateur
DROP USER 'monapplication_readonly'@'%';
-- 🇬🇧 Flush privileges / 🇫🇷 Recharger les privilèges
FLUSH PRIVILEGES;
2. Audit des Connexions
-- 🇬🇧 Show current connections / 🇫🇷 Afficher les connexions actuelles
SHOW PROCESSLIST;
-- 🇬🇧 Connection statistics / 🇫🇷 Statistiques de connexion
SELECT
user,
host,
COUNT(*) AS connection_count
FROM information_schema.processlist
GROUP BY user, host
ORDER BY connection_count DESC;
-- 🇬🇧 Long running queries / 🇫🇷 Requêtes en cours d'exécution longue
SELECT
id,
user,
host,
db,
command,
time,
state,
LEFT(info, 50) AS query_start
FROM information_schema.processlist
WHERE time > 30
ORDER BY time DESC;
3. Gestion des Utilisateurs Application
-- 🇬🇧 User management queries / 🇫🇷 Requêtes de gestion des utilisateurs application
-- List all users with roles
SELECT
u.id,
u.first_name,
u.last_name,
u.email,
r.name AS role,
u.email_verified_at,
u.created_at
FROM users u
LEFT JOIN roles r ON u.role_id = r.id
ORDER BY u.created_at DESC;
-- 🇬🇧 Find inactive users / 🇫🇷 Trouver les utilisateurs inactifs
SELECT
id,
email,
first_name,
last_name,
DATEDIFF(NOW(), updated_at) AS days_inactive
FROM users
WHERE updated_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
ORDER BY days_inactive DESC;
-- 🇬🇧 User activity summary / 🇫🇷 Résumé d'activité utilisateur
SELECT
u.email,
COUNT(w.id) AS workouts_count,
COUNT(ml.id) AS lists_count,
MAX(w.created_at) AS last_workout,
u.updated_at AS last_login
FROM users u
LEFT JOIN workouts w ON u.id = w.user_id
LEFT JOIN my_lists ml ON u.id = ml.user_id
GROUP BY u.id, u.email, u.updated_at
ORDER BY workouts_count DESC;
4. Sécurité et Permissions
-- 🇬🇧 Password policy check / 🇫🇷 Vérification de la politique de mots de passe
SELECT
user,
host,
password_expired,
password_last_changed,
password_lifetime
FROM mysql.user
WHERE user NOT IN ('mysql.session', 'mysql.sys', 'root');
-- 🇬🇧 Failed login attempts (if general log enabled) / 🇫🇷 Tentatives de connexion échouées
SHOW VARIABLES LIKE 'general_log%';
-- 🇬🇧 Create backup user with minimal privileges / 🇫🇷 Créer utilisateur sauvegarde avec privilèges minimaux
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'backup_secure_password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON monapplication.* TO 'backup_user'@'localhost';
GRANT RELOAD, PROCESS ON *.* TO 'backup_user'@'localhost';
Sauvegardes et Restauration
Objectif : Mettre en place des stratégies de sauvegarde robustes et maîtriser les procédures de restauration pour garantir la protection et la récupération de vos données critiques.
La protection des données est essentielle pour la continuité de votre service. Cette section détaille les meilleures pratiques pour créer, gérer et restaurer des sauvegardes MySQL, avec des scripts automatisés et des procédures de récupération d’urgence.
1. Sauvegardes Complètes
# 🇬🇧 Full database backup / 🇫🇷 Sauvegarde complète de la base
docker exec db mysqldump -u root -p[PASSWORD] monapplication > monapplication_full_$(date +%Y%m%d_%H%M%S).sql
# 🇬🇧 Compressed backup / 🇫🇷 Sauvegarde compressée
docker exec db mysqldump -u root -p[PASSWORD] monapplication | gzip > monapplication_full_$(date +%Y%m%d_%H%M%S).sql.gz
# 🇬🇧 Backup with structure only / 🇫🇷 Sauvegarde structure uniquement
docker exec db mysqldump -u root -p[PASSWORD] --no-data monapplication > monapplication_structure_$(date +%Y%m%d).sql
# 🇬🇧 Backup with data only / 🇫🇷 Sauvegarde données uniquement
docker exec db mysqldump -u root -p[PASSWORD] --no-create-info monapplication > monapplication_data_$(date +%Y%m%d).sql
# 🇬🇧 Backup specific tables / 🇫🇷 Sauvegarde de tables spécifiques
docker exec db mysqldump -u root -p[PASSWORD] monapplication users workouts exercises > monapplication_critical_$(date +%Y%m%d).sql
2. Sauvegardes Différentielles
# 🇬🇧 Enable binary logging for incremental backups / 🇫🇷 Activer les logs binaires
# Add to MySQL configuration: log-bin=mysql-bin
# 🇬🇧 Backup with binary logs / 🇫🇷 Sauvegarde avec logs binaires
docker exec db mysqldump -u root -p[PASSWORD] --master-data=2 --single-transaction monapplication > monapplication_incremental_$(date +%Y%m%d_%H%M%S).sql
# 🇬🇧 Show binary log status / 🇫🇷 Afficher l'état des logs binaires
docker exec db mysql -u root -p[PASSWORD] -e "SHOW MASTER STATUS;"
# 🇬🇧 Flush binary logs / 🇫🇷 Vider les logs binaires
docker exec db mysql -u root -p[PASSWORD] -e "FLUSH LOGS;"
3. Restauration de Base de Données
# 🇬🇧 Full database restore / 🇫🇷 Restauration complète
docker exec -i db mysql -u root -p[PASSWORD] monapplication < monapplication_full_20241220_143000.sql
# 🇬🇧 Restore compressed backup / 🇫🇷 Restaurer sauvegarde compressée
zcat monapplication_full_20241220_143000.sql.gz | docker exec -i db mysql -u root -p[PASSWORD] monapplication
# 🇬🇧 Create new database for restore / 🇫🇷 Créer nouvelle base pour restauration
docker exec db mysql -u root -p[PASSWORD] -e "CREATE DATABASE monapplication_restore;"
docker exec -i db mysql -u root -p[PASSWORD] monapplication_restore < backup_file.sql
# 🇬🇧 Restore specific table / 🇫🇷 Restaurer une table spécifique
grep -E "^(CREATE|INSERT).*users" backup_file.sql | docker exec -i db mysql -u root -p[PASSWORD] monapplication
4. Scripts de Sauvegarde Automatisée
#!/bin/bash
# 🇬🇧 Automated backup script / 🇫🇷 Script de sauvegarde automatisée
# Configuration
DB_NAME="monapplication"
DB_USER="root"
DB_PASS="your_password"
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=7
# Create backup directory
mkdir -p $BACKUP_DIR
# Generate timestamp
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
# Full backup
echo "🔄 Starting full backup..."
docker exec db mysqldump -u $DB_USER -p$DB_PASS $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_full_${TIMESTAMP}.sql.gz
# Structure only backup (daily)
echo "🏗️ Creating structure backup..."
docker exec db mysqldump -u $DB_USER -p$DB_PASS --no-data $DB_NAME > $BACKUP_DIR/${DB_NAME}_structure_$(date +%Y%m%d).sql
# Verify backup
if [ $? -eq 0 ]; then
echo "✅ Backup completed successfully"
else
echo "❌ Backup failed"
exit 1
fi
# Cleanup old backups
echo "🧹 Cleaning old backups..."
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
find $BACKUP_DIR -name "*.sql" -mtime +$RETENTION_DAYS -delete
# Backup verification
echo "📊 Backup verification:"
ls -lah $BACKUP_DIR/*$TIMESTAMP*
echo "✅ Backup process completed at $(date)"
5. Test de Restauration
#!/bin/bash
# 🇬🇧 Backup restoration test script / 🇫🇷 Script de test de restauration
TEST_DB="monapplication_test_restore"
BACKUP_FILE="$1"
if [ -z "$BACKUP_FILE" ]; then
echo "Usage: ./test_restore.sh backup_file.sql.gz"
exit 1
fi
echo "🧪 Testing restore of $BACKUP_FILE"
# Create test database
docker exec db mysql -u root -p$DB_PASS -e "DROP DATABASE IF EXISTS $TEST_DB;"
docker exec db mysql -u root -p$DB_PASS -e "CREATE DATABASE $TEST_DB;"
# Restore backup
if [[ $BACKUP_FILE == *.gz ]]; then
zcat $BACKUP_FILE | docker exec -i db mysql -u root -p$DB_PASS $TEST_DB
else
docker exec -i db mysql -u root -p$DB_PASS $TEST_DB < $BACKUP_FILE
fi
# Verify restore
TABLES_COUNT=$(docker exec db mysql -u root -p$DB_PASS -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$TEST_DB';" -s -N)
USERS_COUNT=$(docker exec db mysql -u root -p$DB_PASS -e "SELECT COUNT(*) FROM $TEST_DB.users;" -s -N)
echo "📊 Restore verification:"
echo " - Tables count: $TABLES_COUNT"
echo " - Users count: $USERS_COUNT"
# Cleanup test database
docker exec db mysql -u root -p$DB_PASS -e "DROP DATABASE $TEST_DB;"
echo "✅ Restore test completed"
Monitoring et Performance
Objectif : Surveiller les performances de votre base de données et identifier proactivement les goulots d’étranglement pour maintenir des temps de réponse optimaux.
Le monitoring proactif est essentiel pour une base de données performante. Cette section vous fournit les outils et requêtes nécessaires pour surveiller l’utilisation des ressources, analyser les performances et détecter les problèmes avant qu’ils n’impactent vos utilisateurs.
1. Surveillance des Performances
-- 🇬🇧 Show current processlist / 🇫🇷 Afficher les processus en cours
SHOW FULL PROCESSLIST;
-- 🇬🇧 Show server status / 🇫🇷 Afficher l'état du serveur
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Connection%';
SHOW STATUS LIKE 'Questions';
SHOW STATUS LIKE 'Uptime';
-- 🇬🇧 Memory usage / 🇫🇷 Utilisation de la mémoire
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- 🇬🇧 Query cache statistics / 🇫🇷 Statistiques du cache de requêtes
SHOW STATUS LIKE 'Qcache%';
-- 🇬🇧 Table locks / 🇫🇷 Verrous de tables
SHOW STATUS LIKE 'Table_locks%';
2. Analyse des Requêtes Lentes
-- 🇬🇧 Enable slow query log / 🇫🇷 Activer le log des requêtes lentes
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';
-- 🇬🇧 Check slow query log settings / 🇫🇷 Vérifier les paramètres du log
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 🇬🇧 Most expensive queries (Performance Schema) / 🇫🇷 Requêtes les plus coûteuses
SELECT
SUBSTRING(sql_text, 1, 100) AS query_sample,
count_star AS exec_count,
avg_timer_wait/1000000000 AS avg_time_sec,
sum_timer_wait/1000000000 AS total_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;
3. Analyse de l’Utilisation des Tables
-- 🇬🇧 Table size and row count / 🇫🇷 Taille des tables et nombre de lignes
SELECT
table_name,
table_rows,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
ROUND((data_length / 1024 / 1024), 2) AS data_mb,
ROUND((index_length / 1024 / 1024), 2) AS index_mb,
create_time,
update_time
FROM information_schema.tables
WHERE table_schema = 'monapplication'
ORDER BY (data_length + index_length) DESC;
-- 🇬🇧 Index usage statistics / 🇫🇷 Statistiques d'utilisation des index
SELECT
t.table_name,
s.index_name,
s.column_name,
s.seq_in_index,
s.cardinality
FROM information_schema.statistics s
JOIN information_schema.tables t ON s.table_name = t.table_name
WHERE s.table_schema = 'monapplication'
ORDER BY t.table_name, s.index_name, s.seq_in_index;
-- 🇬🇧 Unused indexes detection / 🇫🇷 Détection des index inutilisés
SELECT
object_schema,
object_name,
index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'monapplication'
AND index_name IS NOT NULL
AND count_star = 0
ORDER BY object_name, index_name;
4. Monitoring des Ressources Système
-- 🇬🇧 InnoDB status / 🇫🇷 État d'InnoDB
SHOW ENGINE INNODB STATUS;
-- 🇬🇧 Buffer pool usage / 🇫🇷 Utilisation du buffer pool
SELECT
ROUND(
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data') * 100 /
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'), 2
) AS buffer_pool_utilization_percent;
-- 🇬🇧 Connection usage / 🇫🇷 Utilisation des connexions
SELECT
VARIABLE_VALUE AS current_connections
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Threads_connected';
SELECT
VARIABLE_VALUE AS max_connections
FROM information_schema.GLOBAL_VARIABLES
WHERE VARIABLE_NAME = 'max_connections';
5. Scripts de Monitoring Automatisé
#!/bin/bash
# 🇬🇧 MySQL monitoring script / 🇫🇷 Script de monitoring MySQL
DB_USER="root"
DB_PASS="your_password"
LOG_FILE="/var/log/mysql_monitor.log"
echo "=== MySQL Monitoring Report - $(date) ===" >> $LOG_FILE
# 🇬🇧 Check if MySQL is running / 🇫🇷 Vérifier si MySQL fonctionne
if docker exec db mysql -u $DB_USER -p$DB_PASS -e "SELECT 1;" &>/dev/null; then
echo "✅ MySQL is running" >> $LOG_FILE
else
echo "❌ MySQL is down!" >> $LOG_FILE
exit 1
fi
# 🇬🇧 Connection count / 🇫🇷 Nombre de connexions
CONNECTIONS=$(docker exec db mysql -u $DB_USER -p$DB_PASS -e "SHOW STATUS LIKE 'Threads_connected';" -s -N | awk '{print $2}')
echo "🔗 Current connections: $CONNECTIONS" >> $LOG_FILE
# 🇬🇧 Slow queries count / 🇫🇷 Nombre de requêtes lentes
SLOW_QUERIES=$(docker exec db mysql -u $DB_USER -p$DB_PASS -e "SHOW STATUS LIKE 'Slow_queries';" -s -N | awk '{print $2}')
echo "🐌 Slow queries: $SLOW_QUERIES" >> $LOG_FILE
# 🇬🇧 Database size / 🇫🇷 Taille de la base
DB_SIZE=$(docker exec db mysql -u $DB_USER -p$DB_PASS -e "
SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS db_size_mb
FROM information_schema.tables
WHERE table_schema = 'monapplication';" -s -N)
echo "💾 Database size: ${DB_SIZE}MB" >> $LOG_FILE
# 🇬🇧 Table with most rows / 🇫🇷 Table avec le plus de lignes
LARGEST_TABLE=$(docker exec db mysql -u $DB_USER -p$DB_PASS -e "
SELECT CONCAT(table_name, ' (', table_rows, ' rows)')
FROM information_schema.tables
WHERE table_schema = 'monapplication'
ORDER BY table_rows DESC
LIMIT 1;" -s -N)
echo "📊 Largest table: $LARGEST_TABLE" >> $LOG_FILE
echo "=== End Report ===" >> $LOG_FILE
echo "" >> $LOG_FILE
Maintenance de la Base
Objectif : Effectuer les tâches de maintenance préventive pour optimiser les performances, corriger la fragmentation et maintenir l’intégrité de votre base de données.
La maintenance régulière est cruciale pour préserver les performances et la fiabilité de votre base de données. Cette section détaille les procédures d’optimisation, de vérification d’intégrité et de nettoyage pour maintenir votre système MySQL en parfait état de fonctionnement.
1. Optimisation des Tables
-- 🇬🇧 Optimize all tables / 🇫🇷 Optimiser toutes les tables
SELECT CONCAT('OPTIMIZE TABLE ', table_name, ';') AS optimize_command
FROM information_schema.tables
WHERE table_schema = 'monapplication'
AND engine = 'InnoDB';
-- 🇬🇧 Optimize specific tables / 🇫🇷 Optimiser des tables spécifiques
OPTIMIZE TABLE users, workouts, exercises;
-- 🇬🇧 Analyze tables for better query planning / 🇫🇷 Analyser les tables pour optimiser les requêtes
ANALYZE TABLE users, workouts, exercises, plans;
-- 🇬🇧 Check table fragmentation / 🇫🇷 Vérifier la fragmentation des tables
SELECT
table_name,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
ROUND(data_free / 1024 / 1024, 2) AS free_mb,
ROUND((data_free / (data_length + index_length)) * 100, 2) AS fragmentation_percent
FROM information_schema.tables
WHERE table_schema = 'monapplication'
AND data_free > 0
ORDER BY fragmentation_percent DESC;
2. Vérification de l’Intégrité
-- 🇬🇧 Check table integrity / 🇫🇷 Vérifier l'intégrité des tables
CHECK TABLE users, workouts, exercises EXTENDED;
-- 🇬🇧 Repair tables if needed / 🇫🇷 Réparer les tables si nécessaire
REPAIR TABLE users, workouts, exercises;
-- 🇬🇧 Check foreign key constraints / 🇫🇷 Vérifier les contraintes de clés étrangères
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'monapplication'
AND REFERENCED_TABLE_NAME IS NOT NULL;
-- 🇬🇧 Validate referential integrity / 🇫🇷 Valider l'intégrité référentielle
SELECT 'workouts -> users' AS check_name, COUNT(*) AS orphaned_records
FROM workouts w
LEFT JOIN users u ON w.user_id = u.id
WHERE u.id IS NULL
UNION ALL
SELECT 'workout_exercises -> workouts', COUNT(*)
FROM workout_exercises we
LEFT JOIN workouts w ON we.workout_id = w.id
WHERE w.id IS NULL
UNION ALL
SELECT 'workout_exercises -> exercises', COUNT(*)
FROM workout_exercises we
LEFT JOIN exercises e ON we.exercise_id = e.id
WHERE e.id IS NULL;
3. Nettoyage et Purge
-- 🇬🇧 Clean up old sessions / 🇫🇷 Nettoyer les anciennes sessions
DELETE FROM personal_access_tokens
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- 🇬🇧 Remove old cache entries / 🇫🇷 Supprimer les anciennes entrées de cache
DELETE FROM cache
WHERE expiration < UNIX_TIMESTAMP();
-- 🇬🇧 Clean up orphaned uploads / 🇫🇷 Nettoyer les uploads orphelins
SELECT u.*
FROM uploads u
LEFT JOIN users usr ON u.user_id = usr.id
WHERE usr.id IS NULL;
-- 🇬🇧 Archive old data / 🇫🇷 Archiver les anciennes données
CREATE TABLE workouts_archive LIKE workouts;
INSERT INTO workouts_archive
SELECT * FROM workouts
WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);
-- 🇬🇧 Remove archived data from main table / 🇫🇷 Supprimer les données archivées
-- DELETE FROM workouts WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);
4. Maintenance des Index
-- 🇬🇧 Check for missing indexes / 🇫🇷 Vérifier les index manquants
SELECT
table_name,
column_name,
'Consider adding index' AS recommendation
FROM information_schema.columns
WHERE table_schema = 'monapplication'
AND column_name IN ('user_id', 'created_at', 'updated_at', 'status')
AND (table_name, column_name) NOT IN (
SELECT table_name, column_name
FROM information_schema.statistics
WHERE table_schema = 'monapplication'
);
-- 🇬🇧 Show index cardinality / 🇫🇷 Afficher la cardinalité des index
SELECT
table_name,
index_name,
column_name,
cardinality,
CASE
WHEN cardinality = 0 THEN 'Low selectivity'
WHEN cardinality < 10 THEN 'Consider removal'
ELSE 'Good selectivity'
END AS recommendation
FROM information_schema.statistics
WHERE table_schema = 'monapplication'
ORDER BY table_name, index_name;
-- 🇬🇧 Find duplicate indexes / 🇫🇷 Trouver les index dupliqués
SELECT
table_name,
GROUP_CONCAT(index_name) AS duplicate_indexes,
column_name
FROM information_schema.statistics
WHERE table_schema = 'monapplication'
GROUP BY table_name, column_name
HAVING COUNT(*) > 1;
5. Script de Maintenance Automatisée
#!/bin/bash
# 🇬🇧 Automated maintenance script / 🇫🇷 Script de maintenance automatisée
DB_USER="root"
DB_PASS="your_password"
DB_NAME="monapplication"
LOG_FILE="/var/log/mysql_maintenance.log"
echo "=== MySQL Maintenance Started - $(date) ===" >> $LOG_FILE
# 🇬🇧 Function to execute SQL and log / 🇫🇷 Fonction pour exécuter SQL et logger
execute_sql() {
local sql="$1"
local description="$2"
echo "🔄 $description..." >> $LOG_FILE
if docker exec db mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "$sql" >> $LOG_FILE 2>&1; then
echo "✅ $description completed" >> $LOG_FILE
else
echo "❌ $description failed" >> $LOG_FILE
fi
}
# 🇬🇧 Analyze tables / 🇫🇷 Analyser les tables
execute_sql "ANALYZE TABLE users, workouts, exercises, plans, swim_sets;" "Analyzing tables"
# 🇬🇧 Optimize fragmented tables / 🇫🇷 Optimiser les tables fragmentées
FRAGMENTED_TABLES=$(docker exec db mysql -u $DB_USER -p$DB_PASS -e "
SELECT GROUP_CONCAT(table_name)
FROM information_schema.tables
WHERE table_schema = '$DB_NAME'
AND data_free > 1024 * 1024
AND (data_free / (data_length + index_length)) > 0.1;" -s -N)
if [ ! -z "$FRAGMENTED_TABLES" ]; then
execute_sql "OPTIMIZE TABLE $FRAGMENTED_TABLES;" "Optimizing fragmented tables"
fi
# 🇬🇧 Clean up old sessions / 🇫🇷 Nettoyer les anciennes sessions
execute_sql "DELETE FROM personal_access_tokens WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);" "Cleaning old sessions"
# 🇬🇧 Clean up cache / 🇫🇷 Nettoyer le cache
execute_sql "DELETE FROM cache WHERE expiration < UNIX_TIMESTAMP();" "Cleaning cache"
# 🇬🇧 Update table statistics / 🇫🇷 Mettre à jour les statistiques
execute_sql "FLUSH TABLES;" "Flushing tables"
echo "=== MySQL Maintenance Completed - $(date) ===" >> $LOG_FILE
echo "" >> $LOG_FILE
# 🇬🇧 Send summary / 🇫🇷 Envoyer le résumé
tail -n 20 $LOG_FILE | mail -s "MySQL Maintenance Report" admin@monapplication.com
Sécurité des Données
Objectif : Implémenter des mesures de sécurité robustes pour protéger vos données sensibles contre les accès non autorisés et les vulnérabilités potentielles.
La sécurité de votre base de données est primordiale pour protéger les informations de vos utilisateurs et maintenir la conformité réglementaire. Cette section couvre les bonnes pratiques de sécurisation, le chiffrement des données et l’audit des accès.
1. Configuration de Sécurité de Base
-- 🇬🇧 Check security settings / 🇫🇷 Vérifier les paramètres de sécurité
SHOW VARIABLES LIKE 'validate_password%';
SHOW VARIABLES LIKE 'sql_mode';
SHOW VARIABLES LIKE 'local_infile';
-- 🇬🇧 Disable dangerous features / 🇫🇷 Désactiver les fonctionnalités dangereuses
SET GLOBAL local_infile = 0;
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
-- 🇬🇧 Check SSL configuration / 🇫🇷 Vérifier la configuration SSL
SHOW VARIABLES LIKE 'have_ssl';
SHOW STATUS LIKE 'Ssl_cipher';
-- 🇬🇧 List users without passwords / 🇫🇷 Lister les utilisateurs sans mot de passe
SELECT user, host
FROM mysql.user
WHERE authentication_string = ''
OR authentication_string IS NULL;
2. Audit et Surveillance
-- 🇬🇧 Enable general log for audit / 🇫🇷 Activer le log général pour audit
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
-- 🇬🇧 Check failed login attempts / 🇫🇷 Vérifier les tentatives de connexion échouées
SELECT
user,
host,
password_expired,
account_locked
FROM mysql.user
WHERE account_locked = 'Y';
-- 🇬🇧 Monitor privilege escalation / 🇫🇷 Surveiller l'escalade de privilèges
SELECT
user,
host,
Super_priv,
Process_priv,
File_priv,
Grant_priv
FROM mysql.user
WHERE Super_priv = 'Y'
OR Process_priv = 'Y'
OR File_priv = 'Y'
OR Grant_priv = 'Y';
-- 🇬🇧 Audit table access / 🇫🇷 Auditer l'accès aux tables
SELECT
object_schema,
object_name,
count_read,
count_write,
count_delete
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = 'monapplication'
ORDER BY (count_read + count_write + count_delete) DESC;
3. Protection des Données Sensibles
-- 🇬🇧 Identify sensitive data / 🇫🇷 Identifier les données sensibles
SELECT
table_name,
column_name,
data_type
FROM information_schema.columns
WHERE table_schema = 'monapplication'
AND (
column_name LIKE '%email%' OR
column_name LIKE '%password%' OR
column_name LIKE '%phone%' OR
column_name LIKE '%address%' OR
column_name LIKE '%card%'
);
-- 🇬🇧 Check for unencrypted passwords / 🇫🇷 Vérifier les mots de passe non chiffrés
SELECT
id,
email,
LENGTH(password) AS password_length,
CASE
WHEN password LIKE '$2y$%' THEN 'Hashed (bcrypt)'
WHEN password LIKE '$argon%' THEN 'Hashed (argon2)'
ELSE 'Potentially unsafe'
END AS password_status
FROM users
LIMIT 10;
-- 🇬🇧 Anonymize data for testing / 🇫🇷 Anonymiser les données pour les tests
/*
UPDATE users
SET
email = CONCAT('user', id, '@example.com'),
first_name = 'Test',
last_name = CONCAT('User', id),
phone = NULL
WHERE id > 1; -- Keep admin user
*/
4. Contrôle d’Accès Avancé
-- 🇬🇧 Create role-based access / 🇫🇷 Créer un accès basé sur les rôles
-- Read-only role for reporting
CREATE USER 'monapplication_readonly'@'%' IDENTIFIED BY 'strong_password_123!';
GRANT SELECT ON monapplication.users TO 'monapplication_readonly'@'%';
GRANT SELECT ON monapplication.workouts TO 'monapplication_readonly'@'%';
GRANT SELECT ON monapplication.exercises TO 'monapplication_readonly'@'%';
-- Analytics role with limited access
CREATE USER 'monapplication_analytics'@'%' IDENTIFIED BY 'analytics_password_456!';
GRANT SELECT ON monapplication.* TO 'monapplication_analytics'@'%';
-- Backup role
CREATE USER 'monapplication_backup'@'localhost' IDENTIFIED BY 'backup_password_789!';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON monapplication.* TO 'monapplication_backup'@'localhost';
-- 🇬🇧 Restrict by IP / 🇫🇷 Restreindre par IP
CREATE USER 'admin_user'@'192.168.1.%' IDENTIFIED BY 'admin_password';
GRANT ALL PRIVILEGES ON monapplication.* TO 'admin_user'@'192.168.1.%';
FLUSH PRIVILEGES;
5. Script de Sécurité Automatisé
#!/bin/bash
# 🇬🇧 Security audit script / 🇫🇷 Script d'audit de sécurité
DB_USER="root"
DB_PASS="your_password"
SECURITY_LOG="/var/log/mysql_security_audit.log"
echo "=== MySQL Security Audit - $(date) ===" >> $SECURITY_LOG
# 🇬🇧 Check for users without passwords / 🇫🇷 Vérifier les utilisateurs sans mot de passe
EMPTY_PASSWORDS=$(docker exec db mysql -u $DB_USER -p$DB_PASS -e "
SELECT COUNT(*) FROM mysql.user
WHERE authentication_string = '' OR authentication_string IS NULL;" -s -N)
if [ $EMPTY_PASSWORDS -gt 0 ]; then
echo "⚠️ WARNING: $EMPTY_PASSWORDS users without passwords found" >> $SECURITY_LOG
else
echo "✅ All users have passwords" >> $SECURITY_LOG
fi
# 🇬🇧 Check for users with excessive privileges / 🇫🇷 Vérifier les utilisateurs avec privilèges excessifs
SUPER_USERS=$(docker exec db mysql -u $DB_USER -p$DB_PASS -e "
SELECT COUNT(*) FROM mysql.user
WHERE Super_priv = 'Y' AND user != 'root';" -s -N)
echo "🔑 Non-root super users: $SUPER_USERS" >> $SECURITY_LOG
# 🇬🇧 Check SSL status / 🇫🇷 Vérifier l'état SSL
SSL_STATUS=$(docker exec db mysql -u $DB_USER -p$DB_PASS -e "
SHOW VARIABLES LIKE 'have_ssl';" -s -N | awk '{print $2}')
if [ "$SSL_STATUS" = "YES" ]; then
echo "✅ SSL is enabled" >> $SECURITY_LOG
else
echo "⚠️ WARNING: SSL is not enabled" >> $SECURITY_LOG
fi
# 🇬🇧 Check for recent login failures / 🇫🇷 Vérifier les échecs de connexion récents
FAILED_LOGINS=$(grep -c "Access denied" /var/log/mysql/error.log 2>/dev/null || echo "0")
echo "🚫 Recent failed logins: $FAILED_LOGINS" >> $SECURITY_LOG
# 🇬🇧 Check for suspicious activity / 🇫🇷 Vérifier l'activité suspecte
LONG_QUERIES=$(docker exec db mysql -u $DB_USER -p$DB_PASS -e "
SELECT COUNT(*) FROM information_schema.processlist
WHERE time > 300;" -s -N)
if [ $LONG_QUERIES -gt 0 ]; then
echo "⚠️ WARNING: $LONG_QUERIES long-running queries detected" >> $SECURITY_LOG
fi
# 🇬🇧 Check database size growth / 🇫🇷 Vérifier la croissance de la base
DB_SIZE=$(docker exec db mysql -u $DB_USER -p$DB_PASS -e "
SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2)
FROM information_schema.tables
WHERE table_schema = 'monapplication';" -s -N)
echo "💾 Current database size: ${DB_SIZE}MB" >> $SECURITY_LOG
echo "=== Security Audit Completed ===" >> $SECURITY_LOG
echo "" >> $SECURITY_LOG
# 🇬🇧 Alert if critical issues found / 🇫🇷 Alerter si problèmes critiques trouvés
if [ $EMPTY_PASSWORDS -gt 0 ] || [ "$SSL_STATUS" != "YES" ]; then
echo "🚨 CRITICAL: Security issues detected!" | mail -s "MySQL Security Alert" admin@monapplication.com
fi
Optimisation Avancée
Objectif : Implémenter des techniques d’optimisation avancées pour maximiser les performances de votre base de données et supporter une charge utilisateur croissante.
L’optimisation avancée permet de tirer le maximum de votre infrastructure MySQL. Cette section couvre les techniques d’optimisation de requêtes, la configuration fine du serveur et les stratégies de scaling pour maintenir des performances optimales même sous forte charge.
1. Analyse et Optimisation des Requêtes
-- 🇬🇧 Analyze query execution plans / 🇫🇷 Analyser les plans d'exécution
EXPLAIN FORMAT=JSON
SELECT u.*, COUNT(w.id) as workout_count
FROM users u
LEFT JOIN workouts w ON u.id = w.user_id
WHERE u.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY u.id
ORDER BY workout_count DESC;
-- 🇬🇧 Find queries without indexes / 🇫🇷 Trouver les requêtes sans index
SELECT
query_sample_text,
count_star,
avg_timer_wait/1000000000 AS avg_time_sec,
no_index_used_count,
no_good_index_used_count
FROM performance_schema.events_statements_summary_by_digest
WHERE no_index_used_count > 0
OR no_good_index_used_count > 0
ORDER BY count_star DESC
LIMIT 10;
-- 🇬🇧 Identify inefficient joins / 🇫🇷 Identifier les jointures inefficaces
SELECT
object_schema,
object_name,
count_read,
avg_timer_read/1000000000 AS avg_read_time,
count_write,
avg_timer_write/1000000000 AS avg_write_time
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = 'monapplication'
ORDER BY avg_timer_read DESC;
2. Optimisation des Index
-- 🇬🇧 Create composite indexes for common queries / 🇫🇷 Créer des index composites
CREATE INDEX idx_workouts_user_date ON workouts(user_id, created_at);
CREATE INDEX idx_exercises_category_difficulty ON exercises(category, difficulty_level);
CREATE INDEX idx_users_email_verified ON users(email, email_verified_at);
-- 🇬🇧 Create covering indexes / 🇫🇷 Créer des index couvrants
CREATE INDEX idx_workouts_stats ON workouts(user_id, created_at, title, duration);
-- 🇬🇧 Analyze index effectiveness / 🇫🇷 Analyser l'efficacité des index
SELECT
object_schema,
object_name,
index_name,
count_read,
count_write,
count_fetch,
count_insert,
count_update,
count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'monapplication'
AND count_star > 0
ORDER BY count_read DESC;
-- 🇬🇧 Monitor index cardinality / 🇫🇷 Surveiller la cardinalité des index
SELECT
table_name,
index_name,
column_name,
cardinality,
ROUND(cardinality / (SELECT table_rows FROM information_schema.tables WHERE table_name = s.table_name AND table_schema = s.table_schema) * 100, 2) AS selectivity_percent
FROM information_schema.statistics s
WHERE table_schema = 'monapplication'
AND cardinality > 0
ORDER BY selectivity_percent DESC;
3. Configuration Avancée du Serveur
-- 🇬🇧 Optimize InnoDB settings / 🇫🇷 Optimiser les paramètres InnoDB
SET GLOBAL innodb_buffer_pool_size = 1073741824; -- 1GB
SET GLOBAL innodb_log_file_size = 268435456; -- 256MB
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL innodb_flush_method = 'O_DIRECT';
-- 🇬🇧 Query cache optimization / 🇫🇷 Optimisation du cache de requêtes
SET GLOBAL query_cache_size = 67108864; -- 64MB
SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_limit = 1048576; -- 1MB
-- 🇬🇧 Connection optimization / 🇫🇷 Optimisation des connexions
SET GLOBAL max_connections = 200;
SET GLOBAL thread_cache_size = 50;
SET GLOBAL table_open_cache = 4000;
-- 🇬🇧 Show current configuration / 🇫🇷 Afficher la configuration actuelle
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'max_connections';
4. Partitioning pour Grandes Tables
-- 🇬🇧 Create partitioned table for workouts / 🇫🇷 Créer une table partitionnée pour workouts
CREATE TABLE workouts_partitioned (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
title VARCHAR(255) NOT NULL,
description TEXT,
duration INT,
created_at TIMESTAMP NULL DEFAULT NULL,
updated_at TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (id, created_at),
KEY idx_user_id (user_id),
KEY idx_created_at (created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- 🇬🇧 Show partition information / 🇫🇷 Afficher les informations de partition
SELECT
table_name,
partition_name,
partition_ordinal_position,
partition_method,
partition_expression,
table_rows
FROM information_schema.partitions
WHERE table_schema = 'monapplication'
AND partition_name IS NOT NULL;
-- 🇬🇧 Partition pruning example / 🇫🇷 Exemple d'élagage de partition
EXPLAIN PARTITIONS
SELECT * FROM workouts_partitioned
WHERE created_at >= '2024-01-01'
AND created_at < '2025-01-01';
5. Techniques de Caching
-- 🇬🇧 Create materialized views for analytics / 🇫🇷 Créer des vues matérialisées pour analytics
CREATE TABLE user_workout_stats AS
SELECT
u.id as user_id,
u.email,
COUNT(w.id) as total_workouts,
AVG(w.duration) as avg_duration,
MAX(w.created_at) as last_workout,
SUM(CASE WHEN w.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) as workouts_last_30_days
FROM users u
LEFT JOIN workouts w ON u.id = w.user_id
GROUP BY u.id, u.email;
-- 🇬🇧 Create index on materialized view / 🇫🇷 Créer un index sur la vue matérialisée
CREATE INDEX idx_user_workout_stats_user_id ON user_workout_stats(user_id);
CREATE INDEX idx_user_workout_stats_total ON user_workout_stats(total_workouts);
-- 🇬🇧 Refresh materialized view / 🇫🇷 Actualiser la vue matérialisée
-- This would be done via a scheduled job
DROP TABLE user_workout_stats;
CREATE TABLE user_workout_stats AS SELECT ...; -- Same query as above
6. Script d’Optimisation Automatisé
#!/bin/bash
# 🇬🇧 Performance optimization script / 🇫🇷 Script d'optimisation des performances
DB_USER="root"
DB_PASS="your_password"
DB_NAME="monapplication"
OPTIMIZATION_LOG="/var/log/mysql_optimization.log"
echo "=== MySQL Optimization Started - $(date) ===" >> $OPTIMIZATION_LOG
# 🇬🇧 Function to execute optimization queries / 🇫🇷 Fonction pour exécuter les requêtes d'optimisation
optimize_query() {
local query="$1"
local description="$2"
echo "🔧 $description..." >> $OPTIMIZATION_LOG
RESULT=$(docker exec db mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "$query" 2>&1)
if [ $? -eq 0 ]; then
echo "✅ $description completed" >> $OPTIMIZATION_LOG
echo "$RESULT" >> $OPTIMIZATION_LOG
else
echo "❌ $description failed: $RESULT" >> $OPTIMIZATION_LOG
fi
}
# 🇬🇧 Analyze tables / 🇫🇷 Analyser les tables
optimize_query "ANALYZE TABLE users, workouts, exercises, plans;" "Analyzing critical tables"
# 🇬🇧 Check for missing indexes / 🇫🇷 Vérifier les index manquants
MISSING_INDEXES=$(docker exec db mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "
SELECT CONCAT('CREATE INDEX idx_', table_name, '_', column_name, ' ON ', table_name, '(', column_name, ');')
FROM information_schema.columns
WHERE table_schema = '$DB_NAME'
AND column_name IN ('user_id', 'created_at', 'status')
AND (table_name, column_name) NOT IN (
SELECT table_name, column_name
FROM information_schema.statistics
WHERE table_schema = '$DB_NAME'
AND index_name != 'PRIMARY'
);" -s -N)
if [ ! -z "$MISSING_INDEXES" ]; then
echo "🔍 Potential missing indexes:" >> $OPTIMIZATION_LOG
echo "$MISSING_INDEXES" >> $OPTIMIZATION_LOG
fi
# 🇬🇧 Check slow queries / 🇫🇷 Vérifier les requêtes lentes
SLOW_QUERIES=$(docker exec db mysql -u $DB_USER -p$DB_PASS -e "
SELECT COUNT(*) FROM performance_schema.events_statements_summary_by_digest
WHERE avg_timer_wait > 1000000000;" -s -N)
echo "🐌 Slow queries detected: $SLOW_QUERIES" >> $OPTIMIZATION_LOG
# 🇬🇧 Buffer pool utilization / 🇫🇷 Utilisation du buffer pool
BUFFER_UTILIZATION=$(docker exec db mysql -u $DB_USER -p$DB_PASS -e "
SELECT ROUND(
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data') * 100 /
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'), 2
);" -s -N)
echo "💾 Buffer pool utilization: ${BUFFER_UTILIZATION}%" >> $OPTIMIZATION_LOG
echo "=== MySQL Optimization Completed - $(date) ===" >> $OPTIMIZATION_LOG
echo "" >> $OPTIMIZATION_LOG
Scripts et Procédures
Objectif : Automatiser les tâches répétitives avec une bibliothèque de scripts et procédures stockées prêts à l’emploi pour une gestion efficace et standardisée.
Cette section rassemble des scripts d’automatisation et procédures stockées pour simplifier la gestion quotidienne de votre base de données. Ces outils vous permettront d’automatiser les tâches courantes et de maintenir une cohérence dans vos opérations.
1. Procédures Stockées Utiles
-- 🇬🇧 Create procedure to clean old data / 🇫🇷 Créer procédure pour nettoyer anciennes données
DELIMITER //
CREATE PROCEDURE CleanOldData(IN days_old INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE table_count INT DEFAULT 0;
-- Clean old personal access tokens
DELETE FROM personal_access_tokens
WHERE created_at < DATE_SUB(NOW(), INTERVAL days_old DAY);
SET table_count = ROW_COUNT();
-- Clean old cache entries
DELETE FROM cache
WHERE expiration < UNIX_TIMESTAMP();
-- Clean old failed jobs
DELETE FROM failed_jobs
WHERE failed_at < DATE_SUB(NOW(), INTERVAL days_old DAY);
SELECT CONCAT('Cleaned ', table_count, ' old records') AS result;
END //
DELIMITER ;
-- 🇬🇧 Create procedure for user statistics / 🇫🇷 Créer procédure pour statistiques utilisateur
DELIMITER //
CREATE PROCEDURE GetUserStatistics(IN user_id_param BIGINT)
BEGIN
SELECT
u.id,
u.first_name,
u.last_name,
u.email,
COUNT(DISTINCT w.id) AS total_workouts,
COUNT(DISTINCT p.id) AS total_plans,
COUNT(DISTINCT ml.id) AS total_lists,
AVG(w.duration) AS avg_workout_duration,
MAX(w.created_at) AS last_workout_date,
DATEDIFF(NOW(), u.created_at) AS member_days
FROM users u
LEFT JOIN workouts w ON u.id = w.user_id
LEFT JOIN plans p ON u.id = p.user_id
LEFT JOIN my_lists ml ON u.id = ml.user_id
WHERE u.id = user_id_param
GROUP BY u.id;
END //
DELIMITER ;
-- 🇬🇧 Usage examples / 🇫🇷 Exemples d'utilisation
CALL CleanOldData(30);
CALL GetUserStatistics(1);
2. Fonctions Utilitaires
-- 🇬🇧 Create function to calculate workout difficulty / 🇫🇷 Créer fonction pour calculer difficulté entraînement
DELIMITER //
CREATE FUNCTION CalculateWorkoutDifficulty(workout_id_param BIGINT)
RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE difficulty_score DECIMAL(3,2);
DECLARE difficulty_level VARCHAR(20);
SELECT AVG(e.difficulty_level) INTO difficulty_score
FROM workout_exercises we
JOIN exercises e ON we.exercise_id = e.id
WHERE we.workout_id = workout_id_param;
IF difficulty_score IS NULL THEN
SET difficulty_level = 'Unknown';
ELSEIF difficulty_score <= 2 THEN
SET difficulty_level = 'Beginner';
ELSEIF difficulty_score <= 4 THEN
SET difficulty_level = 'Intermediate';
ELSE
SET difficulty_level = 'Advanced';
END IF;
RETURN difficulty_level;
END //
DELIMITER ;
-- 🇬🇧 Create function to format duration / 🇫🇷 Créer fonction pour formater durée
DELIMITER //
CREATE FUNCTION FormatDuration(duration_minutes INT)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE hours INT;
DECLARE minutes INT;
DECLARE result VARCHAR(20);
SET hours = FLOOR(duration_minutes / 60);
SET minutes = duration_minutes % 60;
IF hours > 0 THEN
SET result = CONCAT(hours, 'h ', minutes, 'm');
ELSE
SET result = CONCAT(minutes, 'm');
END IF;
RETURN result;
END //
DELIMITER ;
-- 🇬🇧 Usage examples / 🇫🇷 Exemples d'utilisation
SELECT
w.title,
w.duration,
FormatDuration(w.duration) AS formatted_duration,
CalculateWorkoutDifficulty(w.id) AS difficulty
FROM workouts w
LIMIT 5;
3. Scripts de Reporting
#!/bin/bash
# 🇬🇧 Generate daily report / 🇫🇷 Générer rapport quotidien
DB_USER="root"
DB_PASS="your_password"
DB_NAME="monapplication"
REPORT_DATE=$(date +%Y-%m-%d)
REPORT_FILE="/reports/daily_report_$REPORT_DATE.html"
# 🇬🇧 Create HTML report / 🇫🇷 Créer rapport HTML
cat > $REPORT_FILE << EOF
<!DOCTYPE html>
<html>
<head>
<title>monapplication Daily Report - $REPORT_DATE</title>
<style>
body { font-family: Arial, sans-serif; margin: 20px; }
table { border-collapse: collapse; width: 100%; margin: 20px 0; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
th { background-color: #f2f2f2; }
.metric { font-size: 24px; font-weight: bold; color: #2196F3; }
</style>
</head>
<body>
<h1>🏊♂️ monapplication Daily Report</h1>
<p><strong>Date:</strong> $REPORT_DATE</p>
<h2>📊 Key Metrics</h2>
EOF
# 🇬🇧 Get daily statistics / 🇫🇷 Obtenir statistiques quotidiennes
NEW_USERS=$(docker exec db mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "
SELECT COUNT(*) FROM users WHERE DATE(created_at) = CURDATE();" -s -N)
NEW_WORKOUTS=$(docker exec db mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "
SELECT COUNT(*) FROM workouts WHERE DATE(created_at) = CURDATE();" -s -N)
TOTAL_USERS=$(docker exec db mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "
SELECT COUNT(*) FROM users;" -s -N)
# 🇬🇧 Add metrics to report / 🇫🇷 Ajouter métriques au rapport
cat >> $REPORT_FILE << EOF
<table>
<tr><td>New Users Today</td><td class="metric">$NEW_USERS</td></tr>
<tr><td>New Workouts Today</td><td class="metric">$NEW_WORKOUTS</td></tr>
<tr><td>Total Users</td><td class="metric">$TOTAL_USERS</td></tr>
</table>
<h2>👥 Top Active Users</h2>
<table>
<tr><th>User</th><th>Workouts This Week</th><th>Last Activity</th></tr>
EOF
# 🇬🇧 Get top users / 🇫🇷 Obtenir utilisateurs les plus actifs
docker exec db mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "
SELECT
CONCAT(u.first_name, ' ', u.last_name) AS user_name,
COUNT(w.id) AS workouts_this_week,
MAX(w.created_at) AS last_activity
FROM users u
LEFT JOIN workouts w ON u.id = w.user_id
AND w.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY u.id, u.first_name, u.last_name
HAVING workouts_this_week > 0
ORDER BY workouts_this_week DESC
LIMIT 10;" -H >> $REPORT_FILE
cat >> $REPORT_FILE << EOF
</table>
<h2>🏋️♀️ Popular Exercises</h2>
<table>
<tr><th>Exercise</th><th>Category</th><th>Times Used</th></tr>
EOF
# 🇬🇧 Get popular exercises / 🇫🇷 Obtenir exercices populaires
docker exec db mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "
SELECT
e.name,
e.category,
COUNT(we.exercise_id) AS usage_count
FROM exercises e
LEFT JOIN workout_exercises we ON e.id = we.exercise_id
GROUP BY e.id, e.name, e.category
ORDER BY usage_count DESC
LIMIT 10;" -H >> $REPORT_FILE
cat >> $REPORT_FILE << EOF
</table>
<p><em>Report generated on $(date)</em></p>
</body>
</html>
EOF
echo "📊 Report generated: $REPORT_FILE"
# 🇬🇧 Send report by email / 🇫🇷 Envoyer rapport par email
# mail -s "monapplication Daily Report - $REPORT_DATE" -a "Content-Type: text/html" admin@monapplication.com < $REPORT_FILE
4. Script de Migration de Données
#!/bin/bash
# 🇬🇧 Data migration script / 🇫🇷 Script de migration de données
SOURCE_DB="monapplication_old"
TARGET_DB="monapplication"
DB_USER="root"
DB_PASS="your_password"
MIGRATION_LOG="/var/log/data_migration.log"
echo "=== Data Migration Started - $(date) ===" >> $MIGRATION_LOG
# 🇬🇧 Function to migrate table data / 🇫🇷 Fonction pour migrer données de table
migrate_table() {
local table_name="$1"
local where_clause="$2"
echo "🔄 Migrating table: $table_name" >> $MIGRATION_LOG
# Export data
docker exec db mysqldump -u $DB_USER -p$DB_PASS $SOURCE_DB $table_name --where="$where_clause" --no-create-info > /tmp/${table_name}_migration.sql
if [ $? -eq 0 ]; then
# Import data
docker exec -i db mysql -u $DB_USER -p$DB_PASS $TARGET_DB < /tmp/${table_name}_migration.sql
if [ $? -eq 0 ]; then
echo "✅ $table_name migration completed" >> $MIGRATION_LOG
else
echo "❌ $table_name import failed" >> $MIGRATION_LOG
fi
else
echo "❌ $table_name export failed" >> $MIGRATION_LOG
fi
# Cleanup
rm -f /tmp/${table_name}_migration.sql
}
# 🇬🇧 Migrate recent data only / 🇫🇷 Migrer seulement les données récentes
migrate_table "users" "created_at >= '2024-01-01'"
migrate_table "workouts" "created_at >= '2024-01-01'"
migrate_table "exercises" "1=1" # All exercises
# 🇬🇧 Verify migration / 🇫🇷 Vérifier la migration
SOURCE_COUNT=$(docker exec db mysql -u $DB_USER -p$DB_PASS $SOURCE_DB -e "SELECT COUNT(*) FROM users WHERE created_at >= '2024-01-01';" -s -N)
TARGET_COUNT=$(docker exec db mysql -u $DB_USER -p$DB_PASS $TARGET_DB -e "SELECT COUNT(*) FROM users WHERE created_at >= '2024-01-01';" -s -N)
echo "📊 Migration verification:" >> $MIGRATION_LOG
echo " Source users: $SOURCE_COUNT" >> $MIGRATION_LOG
echo " Target users: $TARGET_COUNT" >> $MIGRATION_LOG
if [ "$SOURCE_COUNT" -eq "$TARGET_COUNT" ]; then
echo "✅ Migration verification passed" >> $MIGRATION_LOG
else
echo "❌ Migration verification failed" >> $MIGRATION_LOG
fi
echo "=== Data Migration Completed - $(date) ===" >> $MIGRATION_LOG
5. Script de Health Check Avancé
#!/bin/bash
# 🇬🇧 Advanced health check script / 🇫🇷 Script de vérification santé avancé
DB_USER="root"
DB_PASS="your_password"
DB_NAME="monapplication"
HEALTH_LOG="/var/log/mysql_health.log"
ALERT_THRESHOLD_CONNECTIONS=80
ALERT_THRESHOLD_SLOW_QUERIES=10
echo "=== MySQL Health Check - $(date) ===" >> $HEALTH_LOG
# 🇬🇧 Function to check metric / 🇫🇷 Fonction pour vérifier métrique
check_metric() {
local metric_name="$1"
local query="$2"
local threshold="$3"
local operator="$4" # gt, lt, eq
local value=$(docker exec db mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "$query" -s -N)
echo "📊 $metric_name: $value" >> $HEALTH_LOG
case $operator in
"gt")
if [ "$value" -gt "$threshold" ]; then
echo "⚠️ WARNING: $metric_name exceeds threshold ($value > $threshold)" >> $HEALTH_LOG
return 1
fi
;;
"lt")
if [ "$value" -lt "$threshold" ]; then
echo "⚠️ WARNING: $metric_name below threshold ($value < $threshold)" >> $HEALTH_LOG
return 1
fi
;;
esac
echo "✅ $metric_name OK" >> $HEALTH_LOG
return 0
}
# 🇬🇧 Check various metrics / 🇫🇷 Vérifier diverses métriques
ALERTS=0
# Connection usage
check_metric "Active Connections" "SHOW STATUS LIKE 'Threads_connected'" $ALERT_THRESHOLD_CONNECTIONS "gt"
[ $? -ne 0 ] && ((ALERTS++))
# Slow queries
check_metric "Slow Queries" "SHOW STATUS LIKE 'Slow_queries'" $ALERT_THRESHOLD_SLOW_QUERIES "gt"
[ $? -ne 0 ] && ((ALERTS++))
# Buffer pool hit ratio
BUFFER_HIT_RATIO=$(docker exec db mysql -u $DB_USER -p$DB_PASS -e "
SELECT ROUND(
(1 - (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')) * 100, 2
);" -s -N)
echo "📊 Buffer Pool Hit Ratio: ${BUFFER_HIT_RATIO}%" >> $HEALTH_LOG
if [ $(echo "$BUFFER_HIT_RATIO < 95" | bc) -eq 1 ]; then
echo "⚠️ WARNING: Low buffer pool hit ratio" >> $HEALTH_LOG
((ALERTS++))
fi
# 🇬🇧 Final health status / 🇫🇷 État de santé final
if [ $ALERTS -eq 0 ]; then
echo "✅ Overall health: GOOD" >> $HEALTH_LOG
else
echo "⚠️ Overall health: $ALERTS alerts detected" >> $HEALTH_LOG
# Send alert notification
tail -n 20 $HEALTH_LOG | mail -s "MySQL Health Alert - $ALERTS issues" admin@monapplication.com
fi
echo "=== Health Check Completed ===" >> $HEALTH_LOG
echo "" >> $HEALTH_LOG
Ce guide complet vous fournit tous les outils nécessaires pour administrer efficacement votre base de données MySQL de l’application monapplication en production. De la connexion de base aux optimisations avancées, vous disposez maintenant d’une expertise complète pour maintenir des performances optimales et une sécurité robuste.