CHAPITRE 3
Administrer une base de
données
Ce que vous allez apprendre dans ce chapitre :
• Maîtriser les différentes fonctions liées à l’administration des
BDD
• Vous initier avec les commandes de gestion des comptes et
de privilèges de base
CHAPITRE 3
Administrer une base de données
1. Backup/Restore
2. Importation
3. Exportation
4. Commandes de création des comptes
utilisateurs
5. Commandes de gestion des privilèges
de base
Administrer une base de données : Backup/Restore
Backup
❑ L'outil mysqldump permet de sauvegarder une ou plusieurs bases de données. Il génère un fichier texte
contenant les instructions SQL qui peuvent recréer les bases de données.
❑ mysqldump est situé dans le répertoire root/bin du répertoire d'installation de MySQL.
PARTIE
3
Administrer une base de données : Backup/Restore
Backup d’une ou plusieurs bases de données :
• La commande pour faire un backup avec mysqldump :
mysqldump --user=<username>
--password=<password>
--result-file=<Lien_Fichier_Backup>
--databases <Liste_des_databases>
• Dans cette syntaxe on doit définir :
• Username et password: le nom et le mot de passe de l’utilisateur qui est connecté sur MySQL
• Lien du fichier du backup
• Le ou les noms des bases de données qu’on veut sauvegarder.
• Pour faire un backup de plusieurs bases de données à la fois on suit la syntaxe suivante : --databases Nom_Base1, Nom_Base2, …
PARTIE
• Si on veut faire un backup de toutes les bases de données d’une instance MySql, on remplace l’option : --databases
<Liste_des_databases>
• Par : --all-databases
4
Administrer une base de données : Backup/Restore
Backup d’une ou plusieurs bases de données
Exemples : Backup de la base de données « dbtest »
PARTIE
5
Administrer une base de données : Backup/Restore
Backup d’une ou plusieurs bases de données
Exemples : Backup de la base de données « dbtest »
Le contenu du fichier dbtest_backup.sql après exécution :
PARTIE
6
Administrer une base de données : Backup/Restore
Backup d’une ou plusieurs bases de données
• Afin de faire un backup de tables spécifiques d’une base de données, on exécute :
mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> <Nom_Base>
<table1> <table2> <table3>..
Exemple :
• Réaliser le backup des tables « Produits » et « Sales » de la base de données « dbtest »
mysqldump --user=root --password=Mypassword4@
PARTIE --result-file=c:\backup\backup_tables.sql dbtest
produits sales
7
Administrer une base de données : Backup/Restore
Backup de la structure ou les données d’une bases de données
• L’outil mysqldump permet aussi de sauvegarder juste la structure ou juste les données d’une base de données
en utilisant respectivement les options : --no-data et –-
no-create-info mysqldump --user=<username>
--password=<password>
--result-file=<Lien_Fichier_Backup>
• Structure seulement :
--no-data
--databases <Liste_des_databases>
mysqldump --user=<username>
--password=<password>
PARTIE
• Données seulement : --result-file=<Lien_Fichier_Backup>
--no-create-info
--databases <Liste_des_databases>
8
CHAPITRE 3
Administrer une base de données
1. Backup/Restore
2. Importation
3. Exportation
4. Commandes de création des comptes
utilisateurs
5. Commandes de gestion des privilèges
de base
Administrer une base de données : Importation
• Afin d’importer des données sous forme de fichier sql sur MySQL à partir de la ligne de commande, on
peut utiliser la commande SOURCE comme dans le cas du Restore.
Exemple :
mysql>source c:\backup\fichier_backup.sql
• Il est recommandé d'utiliser la commande SOURCE pour restaurer une base de données car elle
renvoie des informations très détaillées sur le processus, notamment
des avertissements et des erreurs.
PARTIE
10
Administrer une base de données : Importation
• L’utilitaire Import data dans Workbench permet aussi de réaliser cette tâche en suivant ces étapes :
1. Ouvrez MySQL Workbench
2. Dans la liste des MySQL Connexions, choisissez votre base de données
3. Cliquer sur Data Import à partir de l’élément Server dans le menu de navigation
PARTIE
11
Administrer une base de données : Importation
4. Dans le volet « Data Import from Disk », Section « Import Options », choisissez « Import from Self-Contained
File », et sélectionner le fichier SQL qui contient les données à importer.
PARTIE
12
Administrer une base de données : Importation
5. Choisissez le schéma cible par défaut (Default Target Schema) ou les données seront importées.
Vous pouvez également créer une nouvelle base de données en choisissant New (Nouveau)
6. Passez sur le volet : Import Progress. Choisissez Start Import (Démarrer l'importation) pour lancer l’import
PARTIE
13
Administrer une base de données : Importation
7. Votre importation peut prendre quelques minutes ou plus en fonction de la taille du fichier .SQL.
Une fois l’importation terminée, vous devez voir un message semblable à ce qui suit :
PARTIE
14
Administrer une base de données : Importation
Importer des données vers une table :
• MySQL Workbench fournit un outil pour importer des données dans une table. Il permet de modifier les données avant de
les charger.
• Voici les étapes à suivre pour importer des données dans une table :
1. Ouvrez la table dans laquelle vous voulez importer des données
2. Cliquer sur l’icône « Import records from an external file »
PARTIE
15
Administrer une base de données : Importation
Importer des données vers une table :
3. Naviguer vers le fichier qui contient les données.
PARTIE
16
Administrer une base de données : Importation
Importer des données vers une table :
4. Vous pouvez choisir d’importer les données vers une table qui existe déjà, ou en créer une
nouvelle.
PARTIE
17
Administrer une base de données : Importation
Importer des données vers une table :
5. Ensuite vous pouvez vérifier que le type du ficher choisis a été bien détecté et réaliser le mapping des
colonnes du fichier avec celle de la table.
PARTIE
18
Administrer une base de données : Importation
Importer des données vers une table :
6. Cliquer sur Next pour réaliser le Import.
PARTIE
19
CHAPITRE 3
Administrer une base de données
1. Backup/Restore
2. Importation
3. Exportation
4. Commandes de création des comptes
utilisateurs
5. Commandes de gestion des privilèges
de base
Administrer une base de données : Exportation
• L’utilitaire Export data dans Workbench permet d’exporter les données d’une base de données en suivant ces
étapes :
1. Ouvrez MySQL Workbench. Dans la liste des MySQL Connexions, choisissez votre base de données, puis
naviguer dans le menu Server et cliquer sur « Export Data »
PARTIE
21
Administrer une base de données : Exportation
• Sur le volet « Objet Sélection »
2. Choisissez le Schéma qui contient les données à exporter
3. Choisissez les objets à exporter ( Tables, Vues..)
4. La liste déroulante permet de préciser s’il s’agit d’exporter la structure des objets sélectionnés, les données
qu’ils contiennent ou les deux. On peut aussi choisir
d’exporter d’autres objets comme les procédures stockées, les Triggers..
5. Sélectionnez le fichier cible qui va contenir le script des données exportées
PARTIE
22
Administrer une base de données : Exportation
• Sur le volet « Export Progress »
6. Cliquez sur « Start Export » pour commencer le processus
PARTIE
23
Administrer une base de données : Exportation
7. Le système confirme l’export :
24
Administrer une base de données : Exportation
Exporter les données d’une table :
• MySQL Workbench fournit un outil pour exporter les données d’une table. Voici les étapes à suivre :
1. Ouvrez la table de laquelle vous voulez exporter des données
2. Cliquer sur l’icône « Export recordset to an external file »
PARTIE
25
Administrer une base de données : Exportation
Exporter les données d’une table :
3. Choisissez le type et l’emplacement du fichier data à créer, et cliquer sur Ok
PARTIE
26
CHAPITRE 3
Administrer une base de données
1. Backup/Restore
2. Importation
3. Exportation
4. Commandes de création des comptes
utilisateurs
5. Commandes de gestion des privilèges
de base
Administrer une base de données : Commandes de création des comptes utilisateurs
Créer de nouveaux utilisateurs
• On utilise la commande CREATE USER pour créer de nouveaux
utilisateurs dans le serveur de base de données MySql.
• Voici la syntaxe de base de CREATE USER :
CREATE USER [IF NOT EXISTS] nom_compte
IDENTIFIED BY 'mot_de_passe';
• Dans cette expression il faut spécifier :
• nom_compte : Il s’agit du nom du compte a créer et se compose en général de deux partie sous cette forme :
nom_utilisateur@nom_host
• « nom_utilisateur » est le nom de l'utilisateur. Et « nom_host » est le nom de l'hôte à partir duquel l'utilisateur se
connecte au serveur MySQL.
PARTIE La partie nom d'hôte du nom de compte est optionnelle. Si elle est omise, l'utilisateur peut se connecter depuis
n'importe quel hôte.
• Mot_de_passe : Le mot de passe relatif au nouveau compte.
• CREATE USER crée un nouvel utilisateur sans aucun privilège. 28
Administrer une base de données : Commandes de création des comptes utilisateurs
Exemple :
• Sur la ligne de commande MySQL, on liste les utilisateurs existants : mysql> select user from [Link]
Résultat :
mysql> create user Ahmad@localhost identified by 'Monmot2p@ss'
• On peut vérifier sur la table [Link] la création du nouveau
PARTIE
compte :
29
Administrer une base de données : Commandes de création des comptes utilisateurs
Exemple :
• Afin de tester le login, on ouvre une autre session MySql avec le compte « Ahmad » , en utilisant la
commande suivante : Mysql – u Ahmad –p
• Puis on saisit le mot de passe : Monmot2p@ss
• On peut vérifier les bases de données auxquelles Ahmad peut accéder :
PARTIE
30
CHAPITRE 3
Administrer une base de données
1. Backup/Restore
2. Importation
3. Exportation
4. Commandes de création des comptes
utilisateurs
5. Commandes de gestion des privilèges
de base
Administrer une base de données : Commandes de gestion des privilèges de base
Attribution des privilèges (GRANT)
• La commande CREATE USER crée un ou plusieurs comptes d'utilisateurs sans privilèges. Pour qu’un
utilisateur puisse accéder aux objets de base de données, il faut d’abord lui accorder des privilèges .
Ceci se fait à l’aide de la commande GRANT.
• Voici la syntaxe générique de GRANT :
GRANT privilege [,privilege],..
ON privilege_level TO nom_utilisateur;
• Pour donner des privilèges au compte « nom_utilisateur », il faut spécifier le ou les privilèges à
donner ( SELECT, DELETE, UPDATE, ALL…) et sur quel niveau les appliquer.
PARTIE
32
Administrer une base de données : Commandes de gestion des privilèges de base
Attribution des privilèges (GRANT) : Les niveaux de privilèges
Il existe différents niveaux de privilèges dans MySql :
Niveau de Privileges
Global Base de données Proxy
Table Routine
PARTIE
Colonne
33
Administrer une base de données : Commandes de gestion des privilèges de base
Attribution des privilèges (GRANT) : Les niveaux de privilèges
Niveau de Privileges
Global Base de données Proxy
Table Routine
Colonne
1. Niveau global :
• Les privilèges globaux s'appliquent à toutes les bases de données d'un serveur MySQL.
Pour attribuer des privilèges globaux, vous utilisez la syntaxe *.*.
Exemple :
GRANT UPDATE
PARTIE
ON *.*
TO Ahmad@localhost;
34
Administrer une base de données : Commandes de gestion des privilèges de base
Attribution des privilèges (GRANT) : Les niveaux de privilèges
Niveau de Privileges
Global Base de données Proxy
Table Routine
Colonne
2. Niveau Base de données :
• Ces privilèges s'appliquent à tous les objets d'une base de données. Pour attribuer des
privilèges au niveau de la base de données, on utilise la syntaxe : ON
nom_base_de_données.*
GRANT INSERT
Exemple :
ON dbtest.*
PARTIE
TO Ahmad@localhost;
35
Administrer une base de données : Commandes de gestion des privilèges de base
Attribution des privilèges (GRANT): Les niveaux de privilèges
Niveau de Privileges
Global Base de données Proxy
Table Routine
Colonne
3. Niveau Table :
• Les privilèges de table s'appliquent à toutes les colonnes d'une table. Pour attribuer des privilèges
au niveau de la table, on utilise la syntaxe ON nom_base_de_données.nom_table.
Exemple :
PARTIE
GRANT INSERT,DELETE
ON [Link]
TO Ahmad@localhost;
36
Administrer une base de données : Commandes de gestion des privilèges de base
Attribution des privilèges (GRANT): Les niveaux de privilèges
Niveau de Privileges
Global Base de données Proxy
Table Routine
4. Niveau Colonne : Colonne
• Les privilèges de colonne s'appliquent à des colonnes uniques dans une table. Vous
devez spécifier la ou les colonnes pour chaque privilège.
GRANT
Exemple :
SELECT (Num_Produit,Description,
Date_ajout), UPDATE (Prix)
PARTIE
ON [Link]
TO Ahmad@localhost;
37
Administrer une base de données : Commandes de gestion des privilèges de base
Attribution des privilèges (GRANT) : Les niveaux de privilèges
Niveau de Privileges
Global Base de données Proxy
Table Routine
Colonne
5. Niveau Routine :
• Les privilèges de routine s'appliquent aux procédures et fonctions stockées.
Exemple :
PARTIE
GRANT EXECUTE
ON PROCEDURE CalculPrix
TO Ahmad@localhost;
38
Administrer une base de données : Commandes de gestion des privilèges de base
Attribution des privilèges (GRANT) : Les niveaux de privilèges
Niveau de Privileges
Global Base de données Proxy
Table Routine
6. Niveau Proxy : Colonne
• Les privilèges d'utilisateur proxy permettent à un utilisateur externe d'être un proxy pour un autre, c'est-
à-dire, d'avoir les privilèges du deuxième utilisateur. En d'autres termes, l'utilisateur externe est un
"utilisateur proxy" (un utilisateur qui peut usurper l'identité ou devenir un autre utilisateur) et le
deuxième utilisateur est un "utilisateur mandaté" (un utilisateur dont l'identité peut être reprise par un
utilisateur proxy).
Exemple : GRANT PROXY
PARTIE
ON root
TO Ahmad@localhost; 39
Administrer une base de données : Commandes de gestion des privilèges de base
Attribution des privilèges (GRANT): Les niveaux de privilèges
Le tableau suivant illustre les privilèges autorisés les plus utilisés pour les instructions GRANT et REVOKE La liste exhaustives
des privilèges MySql est consultable sur le lien suivant : [Link]
Niveau
Global B. Données Table Colonne Routine Proxy
Privilège Description
ALL [PRIVILEGES] Accorde tous les privilèges au niveau spécifié sauf
GRANT OPTION
ALTER Autorise l'utilisation de ALTER TABLE X X X
ALTER ROUTINE Autorise Alter et Drop des procédures et fonctions X X X
stockées
CREATE Autorise la création des bases de données et tables X X X
CREATE ROUTINE Autorise la création des procédures et fonctions X X
stockées
CREATE TEMPORARY Autorise la création des tables temporaires: CREATE X X
TABLES TEMPORARY TABLE
Autorise l'utilisation de: CREATE USER, DROP
CREATE USER USER, RENAME USER et X
PARTIE REVOKE ALL PRIVILEGES
CREATE VIEW Autorise la création et modification des vues. X X X
DELETE Autorise l'utilisation de DELETE. X X X
DROP Autorise la suppression des bases de données et des X X X
tables.
40
Administrer une base de données : Commandes de gestion des privilèges de base
Attribution des privilèges (GRANT): Les niveaux de privilèges
Niveau
Global B. Données Table Colonne Routine Proxy
Privilège Description
DROP Autorise la suppression des bases de données et X X X
des tables.
EXECUTE Autorise l'exécution des routines. X X X
GRANT OPTION Autorise l'utilisateur a accorder ou révoquer des X X X X X
privilèges d'autres comptes
INDEX Autorise la création et suppression des indexes X X X
INSERT Autorise l'utilisation de INSERT X X X X
PROCESS Autorise l'exécution de SHOW PROCESSLIST. X
PROXY Autorise l'utilisation de PROXY
REFERENCES Autorise la création des clés étrangères X X X X
SELECT Autorise l'utilisation SELECT X X X X
SHUTDOWN
PARTIE Autorise l'utilisation de la commande X
mysqladmin shutdown
UPDATE Autorise l'utilisation de UPDATE X X X X
41
Administrer une base de données : Commandes de gestion des privilèges de base
Révocation des privilèges (REVOKE)
• Afin de supprimer un ou plusieurs privilèges donnés à des utilisateurs, on utilise la commande REVOKE suivant
cette syntaxe :
REVOKE
Privilege1 [,privilege2]..
ON [type_objet] privilege_level
FROM utilisateur1 [, utilisateur2] ..;
• Il faut spécifier :
PARTIE • Une liste de privilèges séparés par des virgules qu’on veut révoquer d'un compte d'utilisateur après le mot-clé
REVOKE ;
• Le type d'objet et le niveau de privilège après le mot-clé ON ;
• Un ou plusieurs comptes d'utilisateur dont vous souhaitez révoquer les privilèges dans la clause FROM.
42
Administrer une base de données : Commandes de gestion des privilèges de base
Révocation des privilèges (REVOKE)
Exemples :
REVOKE
ALL , GRANT OPTION
FROM Ahmad@localhost;
REVOKE SELECT,UPDATE,DELETE
ON dbtest.*
FROM Ahmad@localhost, Fati@localhost;
• L'instruction REVOKE prend effet selon le niveau de privilège :
PARTIE
• Niveau global : Les modifications prennent effet lorsque le compte utilisateur se connecte au serveur MySQL dans les sessions
suivantes. Les modifications ne sont pas appliquées à tous les utilisateurs actuellement connectés.
• Niveau base de données : Les modifications prennent effet après la prochaine instruction USE.
• Niveaux table et colonne : Les modifications prennent effet sur toutes les requêtes suivantes. 43