Cours de Génie Logiciel
Thème :
Junior
1 vues ·3/13 leçonsCOURS D’ADMINISTRATION DES BASES DE DONNÉES MYSQL
2 Crédits - 30 heures (CM, TD, TP)
CHAPITRE 1 : INTRODUCTION
1.1 Objectifs du cours
L’administration des bases de données est une compétence essentielle dans le monde informatique moderne. Ce cours vise à :
- Maîtriser l’installation et la configuration de MySQL
- Comprendre l’architecture et les composants du SGBD
- Gérer la sécurité et les performances
- Assurer la maintenance et la sauvegarde des données
1.2 Qu’est-ce qu’un DBA ?
Un administrateur de base de données (DBA) est responsable de :
- L’installation et la configuration du SGBD
- La gestion des utilisateurs et de la sécurité
- L’optimisation des performances
- La sauvegarde et la récupération des données
- La surveillance et la maintenance
CHAPITRE 2 : PRÉSENTATION DE MYSQL
2.1 Histoire et évolution
MySQL est un système de gestion de base de données relationnelle (SGBDR) :
- Créé en 1995 par MySQL AB
- Racheté par Sun Microsystems en 2008
- Actuellement propriété d’Oracle Corporation
- Fork MariaDB créé en 2009
2.2 Produits MySQL
2.2.1 Éditions disponibles
- MySQL Community Edition : Version open source gratuite
- MySQL Enterprise Edition : Version commerciale avec support
- MySQL Cluster : Solution haute disponibilité
2.2.2 Services MySQL
- MySQL Enterprise Monitor
- MySQL Enterprise Backup
- MySQL Enterprise Security
- MySQL Enterprise Audit
2.3 Avantages de MySQL
- Open source et gratuit
- Multi-plateforme
- Performance élevée
- Support des transactions ACID
- Large communauté
TP 1 : Installation de MySQL
# Installation sur Ubuntu/Debian
sudo apt update
sudo apt install mysql-server
# Installation sur CentOS/RHEL
sudo yum install mysql-server
# Vérification de l'installation
mysql --version
CHAPITRE 3 : ARCHITECTURE DE MYSQL
3.1 Architecture en couches
┌──────────────────────────────────┐
│ Applications Clientes │
├──────────────────────────────────┤
│ Couche de Connexion │
├──────────────────────────────────┤
│ Couche SQL │
│ (Parser, Optimizer, Executor) │
├──────────────────────────────────┤
│ Moteurs de Stockage │
│ (InnoDB, MyISAM, Memory...) │
├──────────────────────────────────┤
│ Système de Fichiers │
└──────────────────────────────────┘
3.2 Composants principaux
3.2.1 Gestionnaire de connexions
- Gestion des connexions clients
- Authentification
- Gestion des threads
3.2.2 Analyseur SQL (Parser)
- Analyse syntaxique des requêtes
- Création de l’arbre syntaxique
3.2.3 Optimiseur de requêtes
- Choix du plan d’exécution optimal
- Utilisation des index
- Optimisation des jointures
3.2.4 Cache de requêtes
- Stockage des résultats de requêtes
- Amélioration des performances
CHAPITRE 4 : SERVEUR MYSQL
4.1 Démarrage et arrêt du serveur
Linux/Unix:
# Démarrage
sudo systemctl start mysql
# ou
sudo service mysql start
# Arrêt
sudo systemctl stop mysql
# Redémarrage
sudo systemctl restart mysql
# Statut
sudo systemctl status mysql
4.2 Fichiers importants du serveur
# Fichier de configuration principal
/etc/mysql/my.cnf
# Répertoire de données
/var/lib/mysql/
# Fichiers de log
/var/log/mysql/error.log
/var/log/mysql/mysql.log
# Socket Unix
/var/run/mysqld/mysqld.sock
4.3 Processus mysqld
Le démon mysqld est le processus principal du serveur MySQL :
- Écoute les connexions réseau
- Gère les requêtes clients
- Maintient les caches et buffers
TD 1 : Surveillance du serveur
-- Vérifier les processus actifs
SHOW PROCESSLIST;
-- Afficher le statut du serveur
SHOW STATUS;
-- Variables du serveur
SHOW VARIABLES;
Vérifier les processus actifs
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 16510 | Waiting on empty queue | NULL |
| 18 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
2 rows in set, 1 warning (0,00 sec)
mysql>
Afficher le statut du serveur
SHOW STATUS;
|
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_table_lock_stat_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Performance_schema_users_lost | 0 |
| Prepared_stmt_count | 0 |
| Queries | 818 |
| Questions | 2 |
| Replica_open_temp_tables | 0 |
| Resource_group_supported | ON |
| Rsa_public_key -----BEGIN PUBLIC KEY-----
############################################################
####################################################"##########
###########################################################"""""""
gQIDAQAB
-----END PUBLIC KEY-----
|
| Secondary_engine_execution_count | 0 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 0 |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 18446744073709551615 |
| Ssl_ctx_verify_mode | 5 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_server_not_after | May 3 11:25:59 2035 GMT |
| Ssl_server_not_before | May 5 11:25:59 2025 GMT |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | SERVER |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 128 |
| Ssl_session_cache_timeout | 300 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 6 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 0 |
| Table_open_cache_misses | 0 |
| Table_open_cache_overflows | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Telemetry_traces_supported | ON |
| Threads_cached | 2 |
| Threads_connected | 1 |
| Threads_created | 3 |
| Threads_running | 2 |
| Tls_library_version | OpenSSL 3.0.13 30 Jan 2024 |
| Uptime | 16986 |
| Uptime_since_flush_status | 16986 |
+-------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
499 rows in set (0,00 sec)
mysql>
Variables du serveur
SHOW
|
| rbr_exec_mode | STRICT |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| regexp_stack_limit | 8000000 |
| regexp_time_limit | 32 |
| relay_log | devinsto-relay-bin |
| relay_log_basename | /var/lib/mysql/devinsto-relay-bin |
| relay_log_index | /var/lib/mysql/devinsto-relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | TABLE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| replica_allow_batching | ON |
| replica_checkpoint_group | 512 |
| replica_checkpoint_period | 300 |
| replica_compressed_protocol | OFF |
| replica_exec_mode | STRICT |
| replica_load_tmpdir | /tmp |
| replica_max_allowed_packet | 1073741824 |
| replica_net_timeout | 60 |
| replica_parallel_type | LOGICAL_CLOCK |
| replica_parallel_workers | 4 |
| replica_pending_jobs_size_max | 134217728 |
| replica_preserve_commit_order | ON |
| replica_skip_errors | OFF |
| replica_sql_verify_checksum | ON |
| replica_transaction_retries | 10 |
| replica_type_conversions | |
| replication_optimize_for_static_plugin_config | OFF |
| replication_sender_observe_commit_only | OFF |
| report_host | |
| report_password | |
| report_port | 3306 |
| report_user | |
| require_row_format | OFF |
| require_secure_transport | OFF |
| resultset_metadata | FULL |
| rpl_read_size | 8192 |
| rpl_stop_replica_timeout | 31536000 |
| rpl_stop_slave_timeout | 31536000 |
| schema_definition_cache | 256 |
| secondary_engine_cost_threshold | 100000.000000 |
| secure_file_priv | /var/lib/mysql-files/ |
| select_into_buffer_size | 131072 |
| select_into_disk_sync | OFF |
| select_into_disk_sync_delay | 0 |
| server_id | 1 |
| server_id_bits | 32 |
| server_uuid | b650c085-29a3-11f0-89fb-a44cc831f389 |
| session_track_gtids | OFF |
| session_track_schema | ON |
| session_track_state_change | OFF |
| session_track_system_variables | time_zone,autocommit,character_set_client,character_set_results,character_set_connection |
| session_track_transaction_info | OFF |
| sha256_password_auto_generate_rsa_keys | ON |
| sha256_password_private_key_path | private_key.pem |
| sha256_password_proxy_users | OFF |
| sha256_password_public_key_path | public_key.pem |
| show_create_table_skip_secondary_engine | OFF |
| show_create_table_verbosity | OFF |
| show_gipk_in_create_table_and_information_schema | ON |
| show_old_temporals | OFF |
| skip_external_locking | ON |
| skip_name_resolve | OFF |
| skip_networking | OFF |
| skip_replica_start | OFF |
| skip_show_database | OFF |
| skip_slave_start | OFF |
| slave_allow_batching | ON |
| slave_checkpoint_group | 512 |
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 60 |
| slave_parallel_type | LOGICAL_CLOCK |
| slave_parallel_workers | 4 |
| slave_pending_jobs_size_max | 134217728 |
| slave_preserve_commit_order | ON |
| slave_rows_search_algorithms | INDEX_SCAN,HASH_SCAN |
| slave_skip_errors | OFF |
| slave_sql_verify_checksum | ON |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/devinsto-slow.log |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 262144 |
| source_verify_checksum | OFF |
| sql_auto_is_null | OFF |
| sql_big_selects | ON |
| sql_buffer_result | OFF |
| sql_generate_invisible_primary_key | OFF |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
| sql_notes | ON |
| sql_quote_show_create | ON |
| sql_replica_skip_counter | 0 |
| sql_require_primary_key | OFF |
| sql_safe_updates | OFF |
| sql_select_limit | 18446744073709551615 |
| sql_slave_skip_counter | 0 |
| sql_warnings | OFF |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_fips_mode | OFF |
| ssl_key | server-key.pem |
| ssl_session_cache_mode | ON |
| ssl_session_cache_timeout | 300 |
| statement_id | 734 |
| stored_program_cache | 256 |
| stored_program_definition_cache | 256 |
| super_read_only | OFF |
| sync_binlog | 1 |
| sync_master_info | 10000 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
| sync_source_info | 10000 |
| system_time_zone | WAT |
| table_definition_cache | 2000 |
| table_encryption_privilege_check | OFF |
| table_open_cache | 4000 |
| table_open_cache_instances | 16 |
| tablespace_definition_cache | 256 |
| temptable_max_mmap | 1073741824 |
| temptable_max_ram | 1073741824 |
| temptable_use_mmap | ON |
| terminology_use_previous | NONE |
| thread_cache_size | 9 |
| thread_handling | one-thread-per-connection |
| thread_stack | 1048576 |
| time_zone | SYSTEM |
| timestamp | 1761751974.012575 |
| tls_ciphersuites | |
| tls_version | TLSv1.2,TLSv1.3 |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_allow_batching | OFF |
| transaction_isolation | REPEATABLE-READ |
| transaction_prealloc_size | 4096 |
| transaction_read_only | OFF |
| transaction_write_set_extraction | XXHASH64 |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| use_secondary_engine | ON |
| version | 8.0.43-0ubuntu0.24.04.2 |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_compile_zlib | 1.3 |
| wait_timeout | 28800 |
| warning_count | 0 |
| windowing_use_high_precision | ON |
| xa_detach_on_prepare | ON |
+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
656 rows in set (0,00 sec)
CHAPITRE 5 : CONFIGURATION DU SERVEUR MYSQL
5.1 Fichier de configuration (my.cnf)
Structure du fichier my.cnf :
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
# Configuration de base
port = 3306
bind-address = 127.0.0.1
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
# Configuration de la mémoire
key_buffer_size = 256M
max_allowed_packet = 64M
thread_stack = 192K
thread_cache_size = 8
# Configuration InnoDB
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
# Configuration des logs
general_log = 1
general_log_file = /var/log/mysql/mysql.log
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
5.2 Variables de serveur dynamiques
Variables modifiables en temps réel :
-- Afficher une variable
SHOW VARIABLES LIKE 'max_connections';
-- Modifier une variable (session)
SET SESSION sort_buffer_size = 512000;
-- Modifier une variable (global)
SET GLOBAL max_connections = 200;
-- Rendre la modification permanente
SET PERSIST max_connections = 200;
5.3 Modes SQL du serveur
Configuration des modes SQL :
-- Afficher le mode SQL actuel
SELECT @@sql_mode;
-- Définir le mode SQL
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
-- Modes SQL courants
-- STRICT_TRANS_TABLES : Mode strict pour les tables transactionnelles
-- NO_ZERO_DATE : Interdit '0000-00-00' comme date
-- NO_AUTO_CREATE_USER : Empêche la création automatique d'utilisateurs
-- ONLY_FULL_GROUP_BY : Requiert tous les champs non-agrégés dans GROUP BY
5.4 Fichiers journaux et d’état
5.4.1 Types de journaux
-- Journal des erreurs
SHOW VARIABLES LIKE 'log_error';
-- Journal général
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
-- Journal des requêtes lentes
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2;
5.5 Journalisation binaire
Configuration du binary log :
[mysqld]
# Activation du binary log
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
Gestion du binary log :
-- Afficher les fichiers binaires
SHOW BINARY LOGS;
-- Afficher les événements
SHOW BINLOG EVENTS;
-- Purger les anciens logs
PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
TP 2 : Configuration et optimisation
# 1. Créer un fichier de configuration personnalisé
sudo nano /etc/mysql/mysql.conf.d/custom.cnf
# 2. Ajouter les paramètres d'optimisation
[mysqld]
# Cache et buffers
query_cache_size = 64M
query_cache_type = 1
tmp_table_size = 64M
max_heap_table_size = 64M
# 3. Redémarrer MySQL
sudo systemctl restart mysql
# 4. Vérifier les changements
mysql -u root -p -e "SHOW VARIABLES LIKE 'query_cache%';"
CHAPITRE 6 : CLIENTS MYSQL
6.1 Présentation des clients d’administration
6.1.1 Clients en ligne de commande
- mysql : Client interactif principal
- mysqladmin : Administration du serveur
- mysqldump : Sauvegarde des données
- mysqlimport : Import de données
- mysqlcheck : Vérification et réparation des tables
6.1.2 Clients graphiques
- MySQL Workbench
- phpMyAdmin
- HeidiSQL
- DBeaver
6.2 Appel de programmes client MySQL
Syntaxe générale :
mysql [options] [database]
# Options courantes
-h, --host=name : Nom d'hôte du serveur
-u, --user=name : Nom d'utilisateur
-p, --password : Mot de passe
-P, --port=# : Port de connexion
-D, --database=name : Base de données par défaut
-e, --execute=name : Exécuter une commande
6.3 Utilisation du client MySQL
# Connexion simple
mysql -u root -p
# Connexion avec base de données
mysql -u root -p mydatabase
# Exécution d'une commande
mysql -u root -p -e "SHOW DATABASES;"
# Exécution d'un script
mysql -u root -p < script.sql
# Export des résultats
mysql -u root -p -e "SELECT * FROM users;" > users.txt
Commandes utiles dans le client :
-- Commandes système
\h ou help -- Aide
\c -- Annuler la commande en cours
\q ou exit -- Quitter
\s ou status -- Statut de la connexion
\! command -- Exécuter une commande système
-- Navigation
USE database; -- Changer de base de données
SHOW DATABASES; -- Lister les bases de données
SHOW TABLES; -- Lister les tables
DESCRIBE table; -- Structure d'une table
6.4 Client MySQLAdmin
Utilisation de mysqladmin :
# Vérifier si le serveur est actif
mysqladmin -u root -p ping
# Créer une base de données
mysqladmin -u root -p create testdb
# Supprimer une base de données
mysqladmin -u root -p drop testdb
# Afficher le statut
mysqladmin -u root -p status
# Afficher les variables
mysqladmin -u root -p variables
# Afficher les processus
mysqladmin -u root -p processlist
# Tuer un processus
mysqladmin -u root -p kill [process_id]
# Recharger les privilèges
mysqladmin -u root -p reload
# Arrêter le serveur
mysqladmin -u root -p shutdown
6.5 Connecteurs MySQL
6.5.1 Connecteurs disponibles
- MySQL Connector/J : Java (JDBC)
- MySQL Connector/NET : .NET
- MySQL Connector/Python : Python
- MySQL Connector/C++ : C++
- MySQL Connector/Node.js : Node.js
- MySQL Connector/PHP : PHP (mysqli, PDO)
6.5.2 Exemple de connexion PHP
<?php
// Connexion avec mysqli
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_error) {
die("Erreur de connexion: " . $mysqli->connect_error);
}
// Connexion avec PDO
try {
$pdo = new PDO("mysql:host=localhost;dbname=database", "user", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo "Erreur de connexion: " . $e->getMessage();
}
?>
CHAPITRE 7 : PRÉSENTATION DES TYPES DE DONNÉES
7.1 Types numériques
| Type | Taille | Description |
|---|---|---|
| TINYINT | 1 octet | -128 à 127 |
| SMALLINT | 2 octets | -32768 à 32767 |
| MEDIUMINT | 3 octets | -8388608 à 8388607 |
| INT | 4 octets | -2147483648 à 2147483647 |
| BIGINT | 8 octets | -2^63 à 2^63-1 |
| DECIMAL(M,D) | Variable | Nombres décimaux précis |
| FLOAT | 4 octets | Nombres à virgule flottante |
| DOUBLE | 8 octets | Double précision |
7.2 Types de chaînes
| Type | Description | Taille maximale |
|---|---|---|
| CHAR(n) | Chaîne fixe | 255 caractères |
| VARCHAR(n) | Chaîne variable | 65535 caractères |
| TINYTEXT | Texte court | 255 octets |
| TEXT | Texte moyen | 65535 octets |
| MEDIUMTEXT | Texte long | 16 MB |
| LONGTEXT | Texte très long | 4 GB |
| BINARY(n) | Binaire fixe | 255 octets |
| VARBINARY(n) | Binaire variable | 65535 octets |
| BLOB | Binary Large Object | 65535 octets |
7.3 Types de date et heure
| Type | Format | Plage |
|---|---|---|
| DATE | YYYY-MM-DD | 1000-01-01 à 9999-12-31 |
| TIME | HH:MM:SS | -838:59:59 à 838:59:59 |
| DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 à 9999-12-31 23:59:59 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:01 UTC à 2038-01-19 03:14:07 UTC |
| YEAR | YYYY | 1901 à 2155 |
7.4 Types JSON et spatiaux
-- Type JSON (MySQL 5.7+)
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
attributes JSON
);
INSERT INTO products VALUES
(1, 'Laptop', '{"brand": "Dell", "ram": 16, "cpu": "i7"}');
-- Requête JSON
SELECT name, JSON_EXTRACT(attributes, '$.brand') AS brand
FROM products;
-- Types spatiaux
CREATE TABLE locations (
id INT PRIMARY KEY,
name VARCHAR(100),
position POINT,
area POLYGON
);
TD 2 : Manipulation des types de données
-- Créer une table avec différents types
CREATE TABLE test_types (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age TINYINT UNSIGNED,
salary DECIMAL(10,2),
hire_date DATE,
last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
bio TEXT,
photo BLOB,
is_active BOOLEAN DEFAULT TRUE,
metadata JSON
);
-- Insérer des données
INSERT INTO test_types (name, age, salary, hire_date, bio, metadata)
VALUES
('John Doe', 30, 55000.50, '2020-01-15', 'Développeur senior',
'{"skills": ["PHP", "MySQL", "JavaScript"], "level": "Senior"}');
-- Requêtes avec conversions
SELECT
name,
CAST(salary AS CHAR) AS salary_text,
DATE_FORMAT(hire_date, '%d/%m/%Y') AS hire_date_fr,
JSON_EXTRACT(metadata, '$.skills[0]') AS first_skill
FROM test_types;
CHAPITRE 8 : MÉTADONNÉES
8.1 Le schéma INFORMATION_SCHEMA
INFORMATION_SCHEMA est une base de données virtuelle contenant les métadonnées :
-- Afficher toutes les tables de métadonnées
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
ORDER BY TABLE_NAME;
8.2 Tables importantes
8.2.1 SCHEMATA (Bases de données)
-- Lister toutes les bases de données avec leurs caractéristiques
SELECT
SCHEMA_NAME,
DEFAULT_CHARACTER_SET_NAME,
DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA;
8.2.2 TABLES
-- Informations sur les tables
SELECT
TABLE_SCHEMA,
TABLE_NAME,
TABLE_TYPE,
ENGINE,
TABLE_ROWS,
DATA_LENGTH,
INDEX_LENGTH,
CREATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydb';
8.2.3 COLUMNS
-- Structure des colonnes
SELECT
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
IS_NULLABLE,
COLUMN_DEFAULT,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'mydb'
ORDER BY TABLE_NAME, ORDINAL_POSITION;
8.2.4 STATISTICS (Index)
-- Informations sur les index
SELECT
TABLE_NAME,
INDEX_NAME,
COLUMN_NAME,
SEQ_IN_INDEX,
NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'mydb';
8.3 Performance Schema
-- Activer Performance Schema
SET GLOBAL performance_schema = ON;
-- Tables de performance
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
-- Statistiques des tables
SELECT * FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'mydb';
TP 3 : Exploration des métadonnées
-- Script d'audit de base de données
DELIMITER //
CREATE PROCEDURE database_audit(IN db_name VARCHAR(64))
BEGIN
-- Taille de la base de données
SELECT
CONCAT('Database: ', db_name) AS 'Info',
ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = db_name;
-- Nombre de tables
SELECT COUNT(*) AS 'Number of Tables'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = db_name;
-- Top 5 des plus grandes tables
SELECT
TABLE_NAME,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)',
TABLE_ROWS AS 'Rows'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = db_name
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC
LIMIT 5;
END//
DELIMITER ;
-- Utilisation
CALL database_audit('mydb');
CHAPITRE 9 : MOTEURS DE STOCKAGE
9.1 Présentation des moteurs
MySQL supporte plusieurs moteurs de stockage :
-- Afficher les moteurs disponibles
SHOW ENGINES;
-- Moteur par défaut
SELECT @@default_storage_engine;
9.2 InnoDB (Moteur par défaut)
Caractéristiques :
- Support des transactions ACID
- Verrouillage au niveau ligne
- Clés étrangères
- Récupération automatique après crash
- MVCC (Multi-Version Concurrency Control)
-- Configuration InnoDB
SHOW VARIABLES LIKE 'innodb%';
-- Créer une table InnoDB
CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
-- Paramètres importants
SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2GB
SET GLOBAL innodb_flush_log_at_trx_commit = 1; -- Durabilité maximale
9.3 MyISAM
Caractéristiques :
- Très rapide pour les lectures
- Verrouillage au niveau table
- Pas de support des transactions
- Compression des tables
- Index FULLTEXT natif
-- Créer une table MyISAM
CREATE TABLE logs (
id INT PRIMARY KEY AUTO_INCREMENT,
message TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM;
-- Optimiser une table MyISAM
OPTIMIZE TABLE logs;
-- Réparer une table MyISAM
REPAIR TABLE logs;
9.4 Memory (HEAP)
-- Table en mémoire
CREATE TABLE cache_data (
cache_key VARCHAR(255) PRIMARY KEY,
cache_value TEXT,
expires_at DATETIME
) ENGINE=MEMORY;
9.5 CSV
-- Table CSV pour l'échange de données
CREATE TABLE export_data (
id INT NOT NULL,
name VARCHAR(100) NOT NULL,
value DECIMAL(10,2) NOT NULL
) ENGINE=CSV;
9.6 Archive
-- Table pour l'archivage (compression)
CREATE TABLE archived_logs (
id INT AUTO_INCREMENT,
log_data TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
KEY(id)
) ENGINE=ARCHIVE;
9.7 Conversion entre moteurs
-- Changer le moteur d'une table
ALTER TABLE mytable ENGINE=InnoDB;
-- Script de conversion en masse
SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ENGINE=InnoDB;') AS conversion_sql
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydb'
AND ENGINE = 'MyISAM';
CHAPITRE 10 : PARTITIONNEMENT
10.1 Présentation et avantages du partitionnement
Le partitionnement divise une grande table en plusieurs parties physiques :
- Amélioration des performances : Requêtes plus rapides
- Maintenance facilitée : Opérations sur des partitions individuelles
- Archivage simplifié : Suppression de partitions anciennes
- Parallélisation : Traitement simultané des partitions
10.2 Types de partitionnement
10.2.1 RANGE Partitioning
-- Partitionnement par plage de dates
CREATE TABLE sales (
id INT AUTO_INCREMENT,
sale_date DATE,
amount DECIMAL(10,2),
customer_id INT,
PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
10.2.2 LIST Partitioning
-- Partitionnement par liste de valeurs
CREATE TABLE employees (
id INT AUTO_INCREMENT,
name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10,2),
PRIMARY KEY (id, department)
) PARTITION BY LIST COLUMNS(department) (
PARTITION p_sales VALUES IN ('Sales', 'Marketing'),
PARTITION p_tech VALUES IN ('IT', 'Development'),
PARTITION p_admin VALUES IN ('HR', 'Finance', 'Admin'),
PARTITION p_other VALUES IN ('Other')
);
10.2.3 HASH Partitioning
-- Partitionnement par hachage
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY HASH(id)
PARTITIONS 4;
10.2.4 KEY Partitioning
-- Partitionnement par clé
CREATE TABLE logs (
id INT AUTO_INCREMENT,
user_id INT,
action VARCHAR(100),
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, user_id)
) PARTITION BY KEY(user_id)
PARTITIONS 10;
10.3 Obtention des informations de partition
-- Afficher les partitions d'une table
SELECT
PARTITION_NAME,
PARTITION_EXPRESSION,
PARTITION_DESCRIPTION,
TABLE_ROWS,
DATA_LENGTH,
INDEX_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'mydb'
AND TABLE_NAME = 'sales';
-- Analyser une partition spécifique
EXPLAIN PARTITIONS
SELECT * FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';
-- Vérifier quelle partition contient des données
SELECT * FROM sales PARTITION (p2023) LIMIT 10;
10.4 Modification et suppression de partitions
-- Ajouter une nouvelle partition
ALTER TABLE sales ADD PARTITION (
PARTITION p2024 VALUES LESS THAN (2025)
);
-- Réorganiser les partitions
ALTER TABLE sales REORGANIZE PARTITION p_future INTO (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- Supprimer une partition (et ses données)
ALTER TABLE sales DROP PARTITION p2020;
-- Tronquer une partition (vider les données)
ALTER TABLE sales TRUNCATE PARTITION p2021;
-- Échanger une partition avec une table
CREATE TABLE sales_archive LIKE sales;
ALTER TABLE sales_archive REMOVE PARTITIONING;
ALTER TABLE sales EXCHANGE PARTITION p2020 WITH TABLE sales_archive;
-- Optimiser une partition
ALTER TABLE sales OPTIMIZE PARTITION p2023;
-- Réparer une partition
ALTER TABLE sales REPAIR PARTITION p2023;
TP 4 : Mise en œuvre du partitionnement
-- Créer une table de logs partitionnée par mois
CREATE TABLE access_logs (
id BIGINT AUTO_INCREMENT,
user_id INT,
ip_address VARCHAR(45),
url VARCHAR(500),
access_time DATETIME,
PRIMARY KEY (id, access_time)
) PARTITION BY RANGE (TO_DAYS(access_time)) (
PARTITION p_jan2024 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p_feb2024 VALUES LESS THAN (TO_DAYS('2024-03-01')),
PARTITION p_mar2024 VALUES LESS THAN (TO_DAYS('2024-04-01')),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- Procédure pour maintenance automatique des partitions
DELIMITER //
CREATE PROCEDURE maintain_partitions()
BEGIN
DECLARE next_month DATE;
DECLARE partition_name VARCHAR(64);
SET next_month = DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
SET partition_name = CONCAT('p_', DATE_FORMAT(next_month, '%b%Y'));
SET @sql = CONCAT('ALTER TABLE access_logs REORGANIZE PARTITION p_future INTO (',
'PARTITION ', partition_name, ' VALUES LESS THAN (TO_DAYS(''',
DATE_ADD(LAST_DAY(next_month), INTERVAL 1 DAY), ''')),',
'PARTITION p_future VALUES LESS THAN MAXVALUE)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END//
DELIMITER ;
CHAPITRE 11 : TRANSACTIONS ET VERROUILLAGE
11.1 Concepts ACID
Les transactions garantissent :
- Atomicité : Tout ou rien
- Cohérence : État cohérent de la base
- Isolation : Transactions indépendantes
- Durabilité : Persistance des changements
11.2 Gestion des transactions
-- Démarrer une transaction
START TRANSACTION;
-- ou
BEGIN;
-- Opérations
INSERT INTO accounts (name, balance) VALUES ('John', 1000);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Valider la transaction
COMMIT;
-- Annuler la transaction
ROLLBACK;
-- Points de sauvegarde
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (1, 100);
SAVEPOINT sp1;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), 1, 2);
-- Si erreur
ROLLBACK TO SAVEPOINT sp1;
-- Sinon
COMMIT;
11.3 Niveaux d’isolation
-- Afficher le niveau d'isolation actuel
SELECT @@transaction_isolation;
-- Définir le niveau d'isolation
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- Par défaut
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
| Niveau | Lecture sale | Lecture non répétable | Fantôme |
|---|---|---|---|
| READ UNCOMMITTED | Oui | Oui | Oui |
| READ COMMITTED | Non | Oui | Oui |
| REPEATABLE READ | Non | Non | Oui* |
| SERIALIZABLE | Non | Non | Non |
11.4 Types de verrouillage
11.4.1 Verrous de table
-- Verrouillage explicite des tables
LOCK TABLES accounts WRITE, transactions READ;
-- Opérations...
UNLOCK TABLES;
-- Verrouillage en lecture
LOCK TABLES accounts READ;
-- Seules les lectures sont permises
UNLOCK TABLES;
11.4.2 Verrous de ligne (InnoDB)
-- Verrou partagé (SELECT ... LOCK IN SHARE MODE)
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- Autres transactions peuvent lire mais pas modifier
COMMIT;
-- Verrou exclusif (SELECT ... FOR UPDATE)
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Autres transactions ne peuvent ni lire ni modifier
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;
-- NOWAIT et SKIP LOCKED (MySQL 8.0+)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
SELECT * FROM accounts WHERE status = 'pending' FOR UPDATE SKIP LOCKED;
11.5 Détection des blocages
-- Afficher les verrous actuels
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- Afficher les transactions en attente
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- Afficher les transactions actives
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
-- Monitoring des deadlocks
SHOW ENGINE INNODB STATUS;
-- Configuration du timeout
SET innodb_lock_wait_timeout = 50; -- secondes
TD 3 : Gestion des transactions
-- Simulation de transfert bancaire avec gestion d'erreurs
DELIMITER //
CREATE PROCEDURE transfer_money(
IN from_account INT,
IN to_account INT,
IN amount DECIMAL(10,2)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'Transaction échouée' AS message;
END;
START TRANSACTION;
-- Vérifier le solde
SELECT balance INTO @current_balance
FROM accounts
WHERE id = from_account
FOR UPDATE;
IF @current_balance < amount THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Solde insuffisant';
END IF;
-- Effectuer le transfert
UPDATE accounts
SET balance = balance - amount
WHERE id = from_account;
UPDATE accounts
SET balance = balance + amount
WHERE id = to_account;
-- Enregistrer la transaction
INSERT INTO transactions (from_account, to_account, amount, transaction_date)
VALUES (from_account, to_account, amount, NOW());
COMMIT;
SELECT 'Transaction réussie' AS message;
END//
DELIMITER ;
CHAPITRE 12 : GESTION DE LA SÉCURITÉ ET DES UTILISATEURS
12.1 Risques de sécurité
Principaux risques :
- Injection SQL : Code malveillant dans les requêtes
- Accès non autorisé : Comptes faibles ou par défaut
- Privilèges excessifs : Droits trop étendus
- Données non chiffrées : Transmission et stockage en clair
- Audit insuffisant : Pas de traçabilité des actions
12.2 Mesures de sécurité
12.2.1 Sécurisation de l’installation
# Script de sécurisation initial
sudo mysql_secure_installation
# Actions effectuées :
# - Définir le mot de passe root
# - Supprimer les utilisateurs anonymes
# - Désactiver la connexion root à distance
# - Supprimer la base de test
# - Recharger les privilèges
12.2.2 Configuration sécurisée
[mysqld]
# Désactiver la résolution DNS
skip-name-resolve
# Limiter les connexions réseau
bind-address = 127.0.0.1
# Désactiver LOCAL INFILE
local_infile = 0
# Activer le SSL
require_secure_transport = ON
ssl-ca = /etc/mysql/ca-cert.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem
12.3 Privilèges MySQL
12.3.1 Types de privilèges
Privilèges globaux :
-- Privilèges administratifs
ALL PRIVILEGES -- Tous les privilèges
CREATE USER -- Créer des utilisateurs
RELOAD -- Recharger les tables de privilèges
SHUTDOWN -- Arrêter le serveur
PROCESS -- Voir tous les processus
FILE -- Lire/écrire des fichiers
SUPER -- Super privilèges
REPLICATION SLAVE -- Réplication
REPLICATION CLIENT -- Surveillance de la réplication
Privilèges de base de données :
CREATE -- Créer des bases/tables
DROP -- Supprimer des bases/tables
ALTER -- Modifier la structure
INDEX -- Créer/supprimer des index
REFERENCES -- Créer des clés étrangères
CREATE VIEW -- Créer des vues
SHOW VIEW -- Voir la définition des vues
TRIGGER -- Créer des triggers
Privilèges de table :
SELECT -- Lire les données
INSERT -- Insérer des données
UPDATE -- Modifier les données
DELETE -- Supprimer des données
12.4 Niveaux d’accès
12.4.1 Comptes utilisateur
-- Créer un utilisateur
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'username'@'%' IDENTIFIED BY 'password'; -- Toutes les machines
CREATE USER 'username'@'192.168.1.%' IDENTIFIED BY 'password'; -- Sous-réseau
-- Avec options de sécurité (MySQL 8.0+)
CREATE USER 'username'@'localhost'
IDENTIFIED BY 'password'
REQUIRE SSL
WITH MAX_QUERIES_PER_HOUR 100
MAX_CONNECTIONS_PER_HOUR 10
MAX_USER_CONNECTIONS 2
PASSWORD EXPIRE INTERVAL 90 DAY
ACCOUNT LOCK;
-- Modifier un utilisateur
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';
ALTER USER 'username'@'localhost' ACCOUNT UNLOCK;
ALTER USER 'username'@'localhost' PASSWORD EXPIRE;
-- Renommer un utilisateur
RENAME USER 'old_name'@'localhost' TO 'new_name'@'localhost';
-- Supprimer un utilisateur
DROP USER 'username'@'localhost';
12.4.2 Privilèges sur les bases de données
-- Accorder des privilèges sur une base
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost';
GRANT SELECT, INSERT, UPDATE ON database.* TO 'username'@'localhost';
-- Privilèges sur toutes les bases
GRANT SELECT ON *.* TO 'readonly'@'localhost';
12.4.3 Privilèges sur les tables
-- Privilèges sur une table spécifique
GRANT SELECT, INSERT ON database.table TO 'username'@'localhost';
-- Privilèges avec possibilité de délégation
GRANT SELECT ON database.* TO 'username'@'localhost' WITH GRANT OPTION;
12.4.4 Privilèges sur les colonnes
-- Privilèges sur des colonnes spécifiques
GRANT SELECT (id, name), UPDATE (name) ON database.users TO 'username'@'localhost';
12.4.5 Privilèges sur les routines stockées
-- Privilèges sur les procédures et fonctions
GRANT EXECUTE ON PROCEDURE database.procedure_name TO 'username'@'localhost';
GRANT EXECUTE ON FUNCTION database.function_name TO 'username'@'localhost';
-- Créer et modifier des routines
GRANT CREATE ROUTINE, ALTER ROUTINE ON database.* TO 'developer'@'localhost';
12.5 Gestion des comptes utilisateur
-- Afficher les utilisateurs
SELECT User, Host, authentication_string FROM mysql.user;
-- Afficher les privilèges d'un utilisateur
SHOW GRANTS FOR 'username'@'localhost';
-- Afficher ses propres privilèges
SHOW GRANTS FOR CURRENT_USER();
-- Révoquer des privilèges
REVOKE INSERT, UPDATE ON database.* FROM 'username'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';
-- Recharger les privilèges
FLUSH PRIVILEGES;
12.6 Contrôle de l’accès au client
-- Créer des rôles (MySQL 8.0+)
CREATE ROLE 'app_read', 'app_write', 'app_admin';
-- Attribuer des privilèges aux rôles
GRANT SELECT ON myapp.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON myapp.* TO 'app_write';
GRANT ALL ON myapp.* TO 'app_admin';
-- Attribuer des rôles aux utilisateurs
GRANT 'app_read' TO 'john'@'localhost';
GRANT 'app_read', 'app_write' TO 'jane'@'localhost';
-- Activer les rôles
SET DEFAULT ROLE 'app_read' TO 'john'@'localhost';
12.7 Utilisation de connexions sécurisées
12.7.1 Configuration SSL/TLS
# Générer les certificats
mysql_ssl_rsa_setup
# Vérifier la configuration SSL
mysql> SHOW VARIABLES LIKE '%ssl%';
mysql> \s
12.7.2 Connexion avec SSL
# Connexion client avec SSL
mysql -u username -p --ssl-mode=REQUIRED
# Forcer SSL pour un utilisateur
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
# Avec certificat client
CREATE USER 'cert_user'@'%' IDENTIFIED BY 'password'
REQUIRE X509;
TP 5 : Mise en place de la sécurité
-- Script de création d'environnement sécurisé
DELIMITER //
CREATE PROCEDURE setup_secure_environment()
BEGIN
-- Créer les rôles
CREATE ROLE IF NOT EXISTS 'developer', 'analyst', 'admin';
-- Attribuer les privilèges aux rôles
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX ON myapp.* TO 'developer';
GRANT SELECT ON myapp.* TO 'analyst';
GRANT ALL ON myapp.* TO 'admin' WITH GRANT OPTION;
-- Créer les utilisateurs
CREATE USER IF NOT EXISTS 'dev_user'@'localhost' IDENTIFIED BY 'Dev@2024Pass';
CREATE USER IF NOT EXISTS 'analyst_user'@'localhost' IDENTIFIED BY 'Ana@2024Pass';
CREATE USER IF NOT EXISTS 'admin_user'@'localhost' IDENTIFIED BY 'Adm@2024Pass';
-- Attribuer les rôles
GRANT 'developer' TO 'dev_user'@'localhost';
GRANT 'analyst' TO 'analyst_user'@'localhost';
GRANT 'admin' TO 'admin_user'@'localhost';
-- Définir les rôles par défaut
SET DEFAULT ROLE ALL TO 'dev_user'@'localhost';
SET DEFAULT ROLE ALL TO 'analyst_user'@'localhost';
SET DEFAULT ROLE ALL TO 'admin_user'@'localhost';
-- Appliquer les restrictions
ALTER USER 'dev_user'@'localhost' WITH MAX_QUERIES_PER_HOUR 1000;
ALTER USER 'analyst_user'@'localhost' WITH MAX_QUERIES_PER_HOUR 5000;
SELECT 'Environnement sécurisé créé avec succès' AS message;
END//
DELIMITER ;
-- Exécution
CALL setup_secure_environment();
CHAPITRE 13 : MAINTENANCE DES TABLES
13.1 Opérations de maintenance
13.1.1 ANALYZE TABLE
-- Analyser les statistiques d'une table
ANALYZE TABLE employees;
-- Analyser plusieurs tables
ANALYZE TABLE employees, departments, salaries;
-- Résultat : Met à jour les statistiques pour l'optimiseur
13.1.2 OPTIMIZE TABLE
-- Optimiser une table (défragmentation)
OPTIMIZE TABLE large_table;
-- Optimiser après de nombreuses suppressions
DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
OPTIMIZE TABLE logs;
-- Pour InnoDB avec file-per-table
ALTER TABLE large_table ENGINE=InnoDB;
13.1.3 CHECK TABLE
-- Vérifier l'intégrité d'une table
CHECK TABLE employees;
-- Vérification rapide
CHECK TABLE employees FAST;
-- Vérification moyenne
CHECK TABLE employees MEDIUM;
-- Vérification complète
CHECK TABLE employees EXTENDED;
13.1.4 REPAIR TABLE
-- Réparer une table corrompue (MyISAM)
REPAIR TABLE corrupted_table;
-- Réparation rapide
REPAIR TABLE corrupted_table QUICK;
-- Réparation étendue
REPAIR TABLE corrupted_table EXTENDED;
-- Utilisation de l'outil en ligne de commande
mysqlcheck -r database_name table_name
13.2 Maintenance automatisée
-- Créer un événement de maintenance
DELIMITER //
CREATE EVENT maintenance_weekly
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-01-07 02:00:00'
DO
BEGIN
-- Analyser les tables importantes
ANALYZE TABLE employees, departments, salaries;
-- Nettoyer les vieilles données
DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
-- Optimiser après suppression
OPTIMIZE TABLE logs;
-- Enregistrer l'exécution
INSERT INTO maintenance_log (task, executed_at)
VALUES ('Weekly maintenance', NOW());
END//
DELIMITER ;
-- Activer l'event scheduler
SET GLOBAL event_scheduler = ON;
13.3 Surveillance de l’espace disque
-- Espace utilisé par base de données
SELECT
table_schema AS 'Database',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;
-- Tables les plus volumineuses
SELECT
table_schema AS 'Database',
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 NOT IN ('information_schema', 'mysql', 'performance_schema')
ORDER BY (data_length + index_length) DESC
LIMIT 20;
TD 4 : Script de maintenance
#!/bin/bash
# Script de maintenance MySQL
MYSQL_USER="maintenance"
MYSQL_PASS="password"
MYSQL_HOST="localhost"
DATABASE="mydb"
# Fonction de log
log_message() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> /var/log/mysql_maintenance.log
}
# Analyser les tables
log_message "Début de l'analyse des tables"
mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST $DATABASE -e "
SELECT CONCAT('ANALYZE TABLE ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = '$DATABASE';" | \
grep -v CONCAT | \
mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST $DATABASE
# Optimiser les tables fragmentées
log_message "Optimisation des tables"
mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST $DATABASE -e "
SELECT CONCAT('OPTIMIZE TABLE ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = '$DATABASE'
AND data_free > 100000000;" | \
grep -v CONCAT | \
mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST $DATABASE
log_message "Maintenance terminée"
CHAPITRE 14 : EXPORTATION ET IMPORTATION DE DONNÉES
14.1 Export de données
14.1.1 Utilisation de mysqldump
# Export d'une base complète
mysqldump -u root -p database_name > backup.sql
# Export avec données uniquement
mysqldump -u root -p --no-create-info database_name > data_only.sql
# Export structure uniquement
mysqldump -u root -p --no-data database_name > structure_only.sql
# Export de tables spécifiques
mysqldump -u root -p database_name table1 table2 > tables.sql
# Export avec options avancées
mysqldump -u root -p \
--single-transaction \
--quick \
--lock-tables=false \
--triggers \
--routines \
--events \
database_name > full_backup.sql
# Export en format CSV
mysqldump -u root -p \
--tab=/tmp \
--fields-terminated-by=',' \
--fields-enclosed-by='"' \
--lines-terminated-by='\n' \
database_name
14.1.2 Export avec SELECT INTO OUTFILE
-- Export en CSV
SELECT * FROM employees
INTO OUTFILE '/tmp/employees.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
-- Export avec en-têtes
(SELECT 'id', 'name', 'email', 'salary')
UNION ALL
(SELECT id, name, email, salary FROM employees)
INTO OUTFILE '/tmp/employees_with_headers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
14.2 Import de données
14.2.1 Import avec mysql
# Import d'un dump SQL
mysql -u root -p database_name < backup.sql
# Import avec affichage de progression
pv backup.sql | mysql -u root -p database_name
# Import de fichiers volumineux
mysql -u root -p database_name --max_allowed_packet=1024M < large_backup.sql
14.2.2 Import avec LOAD DATA INFILE
-- Import depuis un fichier CSV
LOAD DATA INFILE '/tmp/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
-- Import avec transformation
LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(@col1, @col2, @col3, @col4)
SET id = @col1,
name = UPPER(@col2),
email = LOWER(@col3),
created_at = STR_TO_DATE(@col4, '%d/%m/%Y');
14.3 Vues
14.3.1 Qu’est-ce qu’une vue ?
Une vue est une table virtuelle basée sur le résultat d’une requête SELECT.
Avantages :
- Simplification des requêtes complexes
- Sécurité (masquer certaines colonnes)
- Abstraction de la structure des tables
14.3.2 Création de vues
-- Vue simple
CREATE VIEW employee_summary AS
SELECT
e.id,
e.name,
d.department_name,
e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id;
-- Vue avec agrégation
CREATE VIEW department_stats AS
SELECT
d.department_name,
COUNT(e.id) AS employee_count,
AVG(e.salary) AS avg_salary,
MAX(e.salary) AS max_salary,
MIN(e.salary) AS min_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id;
-- Vue avec algorithme spécifique
CREATE ALGORITHM = MERGE VIEW active_employees AS
SELECT * FROM employees WHERE status = 'active';
-- Vue avec vérification
CREATE VIEW high_salary_employees AS
SELECT * FROM employees WHERE salary > 50000
WITH CHECK OPTION;
14.3.3 Vues modifiables
-- Une vue est modifiable si :
-- - Pas de clause DISTINCT, GROUP BY, HAVING
-- - Pas de fonctions d'agrégation
-- - Pas de UNION
-- - Référence une seule table
-- Vue modifiable
CREATE VIEW simple_employee_view AS
SELECT id, name, email, salary FROM employees;
-- Insert via la vue
INSERT INTO simple_employee_view (name, email, salary)
VALUES ('John Doe', 'john@example.com', 55000);
-- Update via la vue
UPDATE simple_employee_view
SET salary = salary * 1.1
WHERE id = 1;
-- Delete via la vue
DELETE FROM simple_employee_view WHERE id = 1;
14.3.4 Gestion des vues
-- Afficher les vues
SHOW FULL TABLES WHERE Table_type = 'VIEW';
-- Afficher la définition d'une vue
SHOW CREATE VIEW employee_summary;
-- Modifier une vue
CREATE OR REPLACE VIEW employee_summary AS
SELECT
e.id,
e.name,
e.email,
d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;
-- Modifier avec ALTER
ALTER VIEW employee_summary AS
SELECT id, name FROM employees;
-- Supprimer une vue
DROP VIEW IF EXISTS employee_summary;
-- Informations sur les vues
SELECT * FROM information_schema.views
WHERE table_schema = 'mydb';
TP 6 : Import/Export et vues
-- Créer une procédure d'export personnalisé
DELIMITER //
CREATE PROCEDURE export_monthly_report(IN report_month INT, IN report_year INT)
BEGIN
-- Créer une vue temporaire pour le rapport
SET @view_sql = CONCAT('
CREATE OR REPLACE VIEW monthly_report_', report_year, '_', LPAD(report_month, 2, "0"), ' AS
SELECT
DATE_FORMAT(order_date, "%Y-%m") AS month,
COUNT(DISTINCT customer_id) AS unique_customers,
COUNT(*) AS total_orders,
SUM(total_amount) AS revenue,
AVG(total_amount) AS avg_order_value
FROM orders
WHERE MONTH(order_date) = ', report_month, '
AND YEAR(order_date) = ', report_year
);
PREPARE stmt FROM @view_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Export vers fichier
SET @export_sql = CONCAT('
SELECT * FROM monthly_report_', report_year, '_', LPAD(report_month, 2, "0"), '
INTO OUTFILE "/tmp/monthly_report_', report_year, '_', LPAD(report_month, 2, "0"), '.csv"
FIELDS TERMINATED BY ","
ENCLOSED BY "\""
LINES TERMINATED BY "\\n"'
);
PREPARE stmt FROM @export_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT CONCAT('Report exported to /tmp/monthly_report_',
report_year, '_', LPAD(report_month, 2, '0'), '.csv') AS message;
END//
DELIMITER ;
CHAPITRE 15 : SAUVEGARDE ET RÉCUPÉRATION
15.1 Planification de la récupération
15.1.1 Stratégies de sauvegarde
- Sauvegarde complète : Toutes les données
- Sauvegarde incrémentale : Changements depuis la dernière sauvegarde
- Sauvegarde différentielle : Changements depuis la dernière sauvegarde complète
15.1.2 Politiques de rétention
# Exemple de politique
# - Sauvegardes quotidiennes : 7 jours
# - Sauvegardes hebdomadaires : 4 semaines
# - Sauvegardes mensuelles : 12 mois
# - Sauvegardes annuelles : 5 ans
15.2 Présentation des outils de sauvegarde
| Outil | Type | Avantages | Inconvénients |
|---|---|---|---|
| mysqldump | Logique | Simple, portable | Lent pour grandes BDD |
| mysqlpump | Logique | Parallélisation | MySQL 5.7+ |
| MySQL Enterprise Backup | Physique | Rapide, hot backup | Commercial |
| Percona XtraBackup | Physique | Gratuit, hot backup | InnoDB seulement |
| Mariabackup | Physique | Fork de XtraBackup | MariaDB |
15.3 Sauvegarde brute (physique)
15.3.1 Copie à froid
# Arrêter MySQL
sudo systemctl stop mysql
# Copier les fichiers de données
sudo cp -r /var/lib/mysql /backup/mysql_cold_backup
# Redémarrer MySQL
sudo systemctl start mysql
15.3.2 Percona XtraBackup
# Installation
sudo apt install percona-xtrabackup-80
# Sauvegarde complète
xtrabackup --backup --target-dir=/backup/full
# Sauvegarde incrémentale
xtrabackup --backup \
--target-dir=/backup/inc1 \
--incremental-basedir=/backup/full
# Préparation de la restauration
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --prepare \
--target-dir=/backup/full \
--incremental-dir=/backup/inc1
# Restauration
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
15.4 Sauvegarde logique
15.4.1 mysqldump complet
#!/bin/bash
# Script de sauvegarde complète
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
MYSQL_USER="backup"
MYSQL_PASS="password"
# Créer le répertoire si nécessaire
mkdir -p $BACKUP_DIR
# Sauvegarde de toutes les bases
mysqldump -u$MYSQL_USER -p$MYSQL_PASS \
--all-databases \
--single-transaction \
--routines \
--triggers \
--events \
--add-drop-database \
--compress \
> $BACKUP_DIR/full_backup_$DATE.sql
# Compression
gzip $BACKUP_DIR/full_backup_$DATE.sql
# Suppression des anciennes sauvegardes (> 30 jours)
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete
15.4.2 Sauvegarde par base de données
#!/bin/bash
# Sauvegarde individuelle des bases
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)
# Liste des bases à sauvegarder
DATABASES=$(mysql -e "SHOW DATABASES;" | grep -v Database | grep -v mysql | grep -v information_schema | grep -v performance_schema)
for DB in $DATABASES; do
echo "Backing up $DB..."
mysqldump --single-transaction --routines --triggers --events \
$DB | gzip > $BACKUP_DIR/${DB}_${DATE}.sql.gz
done
15.5 Sauvegarde des fichiers journaux et d’état
# Sauvegarde du binary log
mysqlbinlog /var/log/mysql/mysql-bin.000001 > binlog_backup.sql
# Rotation et sauvegarde des logs
mysql -e "FLUSH LOGS;"
cp /var/log/mysql/mysql-bin.* /backup/binlogs/
# Sauvegarde de la position pour la réplication
mysql -e "SHOW MASTER STATUS\G" > /backup/master_status.txt
15.6 Réplication comme aide à la sauvegarde
15.6.1 Configuration du master
-- Sur le serveur master
-- my.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_do_db = mydb
-- Créer un utilisateur de réplication
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-- Obtenir la position
SHOW MASTER STATUS;
15.6.2 Configuration du slave
-- Sur le serveur slave
-- my.cnf
[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin
-- Configurer la réplication
CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
-- Démarrer la réplication
START SLAVE;
-- Vérifier le statut
SHOW SLAVE STATUS\G
15.7 Comparaison entre les méthodes de sauvegarde
| Critère | mysqldump | XtraBackup | Réplication |
|---|---|---|---|
| Type | Logique | Physique | Temps réel |
| Vitesse sauvegarde | Lente | Rapide | N/A |
| Vitesse restauration | Lente | Rapide | Immédiate |
| Impact performance | Élevé | Faible | Faible |
| Espace disque | Compressé | Non compressé | x2 |
| Granularité | Flexible | Complète | Complète |
| Point-in-time recovery | Oui (avec binlog) | Oui | Oui |
15.8 Récupération de données
15.8.1 Restauration depuis mysqldump
# Restauration simple
mysql < backup.sql
# Restauration d'une base spécifique
mysql database_name < database_backup.sql
# Restauration avec progression
pv backup.sql | mysql
# Restauration point-in-time
# 1. Restaurer la dernière sauvegarde complète
mysql < full_backup.sql
# 2. Appliquer les binary logs jusqu'au moment souhaité
mysqlbinlog --stop-datetime="2024-01-15 10:30:00" \
mysql-bin.000001 mysql-bin.000002 | mysql
15.8.2 Récupération après crash
# Vérification et réparation automatique InnoDB
# InnoDB effectue une récupération automatique au démarrage
# Forcer la récupération InnoDB (my.cnf)
[mysqld]
innodb_force_recovery = 1
# Niveaux : 1-6 (du moins au plus agressif)
# Après récupération, dump et restauration
mysqldump --all-databases > emergency_backup.sql
# Réinitialiser les données
# Restaurer depuis le dump
TP 7 : Plan de sauvegarde complet
#!/bin/bash
# Script de sauvegarde automatisé avec rotation
# Configuration
BACKUP_DIR="/backup/mysql"
LOG_FILE="/var/log/mysql_backup.log"
MYSQL_USER="backup"
MYSQL_PASS="secure_password"
RETENTION_DAYS=30
EMAIL="admin@example.com"
# Fonction de log
log_message() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a $LOG_FILE
}
# Fonction de notification
send_notification() {
subject=$1
message=$2
echo "$message" | mail -s "$subject" $EMAIL
}
# Début du backup
log_message "=== Début de la sauvegarde ==="
# Créer les répertoires
mkdir -p $BACKUP_DIR/{daily,weekly,monthly}
# Déterminer le type de sauvegarde
DAY_OF_WEEK=$(date +%u)
DAY_OF_MONTH=$(date +%d)
if [ $DAY_OF_MONTH -eq 1 ]; then
BACKUP_TYPE="monthly"
elif [ $DAY_OF_WEEK -eq 7 ]; then
BACKUP_TYPE="weekly"
else
BACKUP_TYPE="daily"
fi
BACKUP_FILE="$BACKUP_DIR/$BACKUP_TYPE/backup_$(date +%Y%m%d_%H%M%S).sql.gz"
# Effectuer la sauvegarde
log_message "Type de sauvegarde: $BACKUP_TYPE"
log_message "Fichier de destination: $BACKUP_FILE"
if mysqldump -u$MYSQL_USER -p$MYSQL_PASS \
--all-databases \
--single-transaction \
--routines \
--triggers \
--events \
--add-drop-database | gzip > $BACKUP_FILE; then
log_message "Sauvegarde réussie"
# Vérifier la taille
SIZE=$(du -h $BACKUP_FILE | cut -f1)
log_message "Taille du fichier: $SIZE"
# Test de restauration
log_message "Test de validation..."
if gunzip -c $BACKUP_FILE | mysql -u$MYSQL_USER -p$MYSQL_PASS test_restore; then
log_message "Validation réussie"
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "DROP DATABASE IF EXISTS test_restore;"
else
log_message "ERREUR: Validation échouée"
send_notification "Erreur Backup MySQL" "La validation de la sauvegarde a échoué"
fi
else
log_message "ERREUR: Sauvegarde échouée"
send_notification "Erreur Backup MySQL" "La sauvegarde MySQL a échoué"
exit 1
fi
# Rotation des sauvegardes
log_message "Nettoyage des anciennes sauvegardes..."
# Daily: garder 7 jours
find $BACKUP_DIR/daily -name "*.sql.gz" -mtime +7 -delete
# Weekly: garder 4 semaines
find $BACKUP_DIR/weekly -name "*.sql.gz" -mtime +28 -delete
# Monthly: garder 12 mois
find $BACKUP_DIR/monthly -name "*.sql.gz" -mtime +365 -delete
# Sauvegarder aussi les binary logs
if [ "$BACKUP_TYPE" = "daily" ]; then
log_message "Sauvegarde des binary logs..."
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "FLUSH LOGS;"
cp /var/log/mysql/mysql-bin.* $BACKUP_DIR/binlogs/
find $BACKUP_DIR/binlogs -name "mysql-bin.*" -mtime +7 -delete
fi
log_message "=== Sauvegarde terminée ==="
# Notification de succès
send_notification "Backup MySQL Réussi" "Sauvegarde $BACKUP_TYPE effectuée avec succès. Taille: $SIZE"
EXERCICES PRATIQUES RÉCAPITULATIFS
Exercice 1 : Installation et configuration
- Installer MySQL Server
- Sécuriser l’installation
- Créer un fichier de configuration optimisé
- Configurer les logs (error, general, slow query, binary)
Exercice 2 : Gestion des utilisateurs
- Créer trois rôles : admin, developer, analyst
- Créer des utilisateurs avec différents privilèges
- Implémenter une politique de mots de passe
- Configurer l’accès SSL obligatoire
Exercice 3 : Optimisation et partitionnement
- Créer une table de logs partitionnée par date
- Implémenter la rotation automatique des partitions
- Analyser les performances avec EXPLAIN
- Optimiser les requêtes lentes
Exercice 4 : Sauvegarde et récupération
- Mettre en place un plan de sauvegarde complet
- Tester la restauration point-in-time
- Configurer la réplication master-slave
- Simuler et récupérer d’un crash
Exercice 5 : Monitoring et maintenance
- Créer un dashboard de monitoring
- Implémenter des alertes automatiques
- Planifier la maintenance régulière
- Documenter les procédures d’urgence
CONCLUSION
Ce cours a couvert les aspects essentiels de l’administration MySQL :
- Architecture et configuration du serveur
- Gestion de la sécurité et des utilisateurs
- Optimisation des performances
- Stratégies de sauvegarde et récupération
- Maintenance et monitoring
Les compétences acquises permettent de :
- Installer et configurer MySQL en production
- Assurer la sécurité des données
- Optimiser les performances
- Garantir la disponibilité et la récupération
- Maintenir un environnement MySQL sain
Ressources supplémentaires
- Documentation officielle MySQL
- MySQL Performance Blog
- Planet MySQL
- Forums et communautés MySQL
Certifications recommandées
- MySQL Database Administrator Certified Professional
- Oracle Certified Professional, MySQL Database Administrator
Prêt à passer à la suite ?
Vous avez terminé cette leçon sur COURS D'ADMINISTRATION DES BASES DE DONNÉES MYSQL. Continuez votre apprentissage avec la prochaine leçon ou explorez d'autres formations.