Tutorial Administration MySQL Windows/Linux

---

MySQL est la base de données venue du monde du libre la plus répandue et la plus utilisée.
Dans ce tutorial, vous verrez comment fonctionne MySQL et comment administrer une base de données MySQL

Généralement, les tutorials sur MySQL vous proposent l'installation et l'administration via phpMyAdmin ou phpMyEdit, dans ce tutorial vous verrez plutot l'administration via les outils standards MySQL tels que MySQL Administrateur et MySQL Query Browser.

Principe et fonctionnement de la base de données : un peu de théorie

Principe

Une base de données permet de stocker une grande quantité de manière organisée.
Une base de données se doit de pouvoir mettre à  disposition n'importe quelle de ces données à  partir de requêtes.

Une base de données est composée :

• de tables avec diverses noms correspondant aux données qui y sont stockées. Il existe plusieurs moteurs de tables différents sous MySQL (MyISAM, InnoDB, Merge etc..). Vous pouvez consulter la liste des moteurs à  partir de ce lien : http://dev.mysql.com/doc/refman/5.0/fr/storage-engines.html - Note il est tout à  fait possible de mixer les moteurs de table au sein d'une même base de données.
• Chacune de ces tables sont composées de champs de noms différents avec des propriétés différentes (voir types de champs http://dev.mysql.com/doc/refman/5.0/fr/column-types.html). 
• Par exemple, certains champs n'acceptent que des lettres, d'autres que des chiffres ou d'autres des données binaires. En règle général, la taille des champs est fixée.
• Chacun champs contiennent des données.

Voici un exemple d'une table amis :

ID_AMIS	NOM	PRENOM	ADRESSE	CODE_POSTAL	VILLE
1	TARTEMPION	Robert	15 Avenue de la Victoire	41000	Blois
2	MARTIN	Jean	12 place du 18 Juin 1945	75011	Paris
3	LEAUGEAIS	Claudine	50 rue des Avacias	13000	Marseille

Cette table possèdent plusieurs champs (ID_AMIS, NOM, PRENOM etc...) qui contiennent diverses informations dites enregistrements.
Le champs ID_AMIS est dit clef primaire. La valeur d'une clef primaire ne peut être en double - On ne peut avoir plusieurs ID_AMIS avec la valeur 1 par exemple. Il faut donc bien faire attention au choix de l'ID. Par exemple, on aurait pu prendre le champs NOM comme clef primaire. Cependant, cela aurait posé problème si plusieurs personnes ont le même nom de famille.

Cette clef permet de référencer la ligne dans la table.

L'ID permet de lier des tables entre elles.
Ainsi si nous avons une table amis_emails :

ID_AMIS	ADRESSE_MAIL
1	Robert.Tartempion@wanadoo.fr
2	Jmartin@gmail.com
3	ClaudineLeaugeois@hotmail.com

Le champs ID_AMIS est dit une clef étrangère car il pointe vers la clef ID_AMIS. Cela permet de rattacher chaque adresse email à  son propriétaire.
Cela est interressant si une table doit partager des informations à  plusieurs autres tables.

Contraintes

Les contraintes permettent de s'assurer de la validité des données.
Les contraintes permettent de prévenir de l'insertion de données incorrectes. Par exemple, une date invalide. L'insertion de la valeur Null dans un champs n'acceptant pas cette valeur. Enfin, dans le cas de l'utilisation de clefs étrangères, le référencement de données non existantes.

Connexions et interrogations de la base de données

Les connexions à  une base de données font l'objet d'une authentification. En règle général pour se connecter à  une base de données, vous devez communiquer :

• L'adresse IP ou le nom du serveur hébergeant la base de données
• Le nom de la base de données à  laquelle vous désirez vous connecter
• L'utilisateur de la base de données ayant le droit de se connecter à  la base de données
• Le mot de passe de l'utilisateur ayant le droit de se connecter à  la base de données.

Pour se connecter à  une base de données, vous devez utiliser des pilotes (drivers). Les pilotes se chargent de la connexion et du "dialogue" entre vous et la base de données.
Il existe deux types de Drivers :

• ODBC pour le monde MIcrosoft. Dans le cas de MySQL, il convient d'installer les pilotes de MySQL car ceux-ci ne sont pas livrés en natinf sous Windows. Vous pouvez alors avoir besoin de créer une source ODBC (qui contient les informations de connexions) à  partir du panneau de configuration / Outils d'administration / Source de données ODBC.
• JDBC dans le monde Java.

En règle général, les informations de connexions (citées plus haut) sont regroupés dans une chaà®ne de connexion. La syntaxe de la chaà®ne de connexion diffère selon le type de drivers (ODBC ou JDBC) et parfois de la version de ces derniers.

Quelques exemples :
ODBC : DRIVER={mySQL};server=CheminMonServeur;Option=16834;Database=baseDeDonnees;
JDBC : jdbc:mysql://serveurMysQL;port/nomdelabasededonnées
ex: jdbc:mysql://localhost:3306/Mabasededonnes

Si vous avez un site WEB, vous pouvez aussi utiliser MySQL avec PHP. Pour cela, il faut activer l'extension MySQL de PHP, cela se fait dans le fichier php.ini en ajoutant (ou décommentant) la ligne : extension=mysql.so

Les interrogations se font à  travers des requêtes dites SQL. Par exemple, pour récupérer des informations d'une table, nous avons besoin d'effectuer une requête SQL avec un SELECT.
Par exemple, si nous désirons récupérer tous les enregistrements de la table amis, la syntaxe est :
select * from amis;
* signifique nous souhaitons récupérer tous les champs de la table.
Si nous voulions récupérer que le nom et prénom, la requête serait alors :
select nom,prenom from amis;

Si nous recherchons un nom en particulier, nous devons utiliser la clause WHERE, par exemple pour rechercher l'enregistrement avec le nom Martin :
Select * from amis where NOM='Martin';

Le langage est composé d'une multitude de commandes afin de pouvoir effectuer tous les opérations que vous désirez sur la base de données :

Installation MySQL

Windows

Nous ne verrons pas dans les détails l'installation de MySQL.
Sous Windows, celle-ci est très simple puisque c'est une installation de type installshield avec des boutons suivants.. il suffit alors de se laisser guider en répondant aux diverses questions.
L'administration peut se faire entièrement à  partir des outils MySQL.
MySQL fonctionne avec un service Windows. Si vous désirez arrêter le serveur, il suffit de stopper le service MySQL.

GNU/Linux

Sous GNU/Linux, il suffit d'installer le package serveur (l'installation du package client est recommandé aussi).
Sous Debian, apt-get install mysql-server-4.0 ou mysql-server-4.1 ou mysql-server-5.0 ( selon la version qui vous interresse ).

Par défaut sur Debian, MySQL n'écoute pas sur le réseau, si le serveur est accessible depuis internet, il est recommandé de laisser tel quel. Vous pouvez aussi par sécurité filtrer le port 3306 sur l'interface internet.
Néanmoins, si vous désirez que MySQL écoute sur le réseau :
Editez le fichier /etc/mysql/my.cnf et décommentez la ligne skip-networking et un petit /etc/init.d/mysql restart pour que cela soit pris en compte.

Après l'installation du package, il convient de modifier le mot de passe root, soit à  partir de la commande :
mysqladmin -u root password nouveaupass

L'utilisateur root est l'utilisateur administrateur, ce dernier à  tous les pouvoirs sur MySQL.
Il peut créer/supprimer les bases de données ou les utilisateurs.

MySQL fonctionne en daemon, pour arrêter le serveur :
/etc/init.d/mysqld stop
pour le démarrer :
/etc/init.d/mysqld start

Installer les outils d'administration de MySQL

Installer MySQL GUI Tools Downloads

MySQL GUI Tools Downloads est une suite d'utilitaires à  savoir :

• MySQL Administrator qui permet d'administrer un serveur MySQL
• MySQL Query Browser qui permet d'accéder/modifer les données et effectuer des requêtes sur une base de données
• MySQL Migration Toolkit qui permet d'effectuer des migrations de base de données de types différentes, par exemple exporter une base Access vers une base MySQL.
• MySQL Workbench est un outils de modélisation de base de données

Téléchargez et le programme d'installation de MySQL GUI Tools  à  l'adresse suivante : http://dev.mysql.com/downloads/gui-tools/5.0.html
Après avoir démarré, la fenêtre de bienvenue s'ouvre, cliquez sur le bouton Next pour passer à  l'étape suivante.


Acceptez les termes du contrat en cochant l'option « I Accept the terms in the license agreement Â» puis cliquez sur Next pour passer à  l'étape suivante :


Vous devez ensuite indiquer le dossier dans lequel MySQLAdministrator sera installé, par défaut, il vous est proposé le chemin C:\Program Files\MySQL\MySQL Administrator 1.1\ si vous désirez modifier ce chemin, cliquez sur le bouton « Change Â»
Cliquez sur le bouton Next pour passer à  l'étape suivante :



Sélectionnez « Complete Â» puis cliquez sur le bouton Next pour passer à  l'étape suivante:



Une fenêtre récapitulant les informations s'ouvre alors, cliquez sur le bouton « Install Â» pour démarrer l'installation




La copie de fichiers s'effectue :


Lorsque la copie est terminée, une fenêtre vous indiquant que l'installation est terminée s'ouvre. Cliquez sur le bouton « Finish Â» pour fermer le programme d'installation :


A l'issue de l'installation, vous pouvez démarrer MySQLAdministrator à  partir du menu Démarrer / Programmes / MySQL / MySQLAdministrator.

Premier démarrage de MySQLAdministrator

Lors du premier démarrage de MySQLAdministrator, vous devez configurer la connexion à  la base MySQL.
Après avoir démarré MySQLAdministrator, vous obtenez une fenêtre d'identification similaire à  celle-ci :


Cliquez sur le bouton [...] en haut à  droite, cela ouvre la fenêtre suivante :

• Cliquez sur le bouton « Add new Connection Â» en bas à  gauche afin de créer une nouvelle connexion
• Sélectionnez dans la liste en haut « New Connection Â» qui vient de s'ajouter à  la liste. La partie à  droite « Connection Parameters Â» permet de renseigner les informations de connexion à  la base MySQL
• Dans le champs Connection, saisissez le nom de connexion qui apparaà®tra dans les listes de connexion de MySQLAdministrator
• Dans le Champs Username, saisissez l'utilisateur qui permet de se connecter à  la base de données.
• Dans le champs Password, saisissez le mot de passe de cet utilisateur
• Dans le champs Hostname, saisissez l'IP ou le nom de l'ordinateur qui héberge la base de données MySQL
• Pour enregistrer toutes ces informations de connexions, cliquez en bas sur le bouton « Apply Â» puis sur le bouton « Close Â» pour revenir à  la fenêtre précédente.
• La connexion que nous venons de configurer est alors accessible dans le menu déroulant « Stored Connection Â».
• Saissez le mot de passe de l'utilisateur puis cliquez sur le bouton « OK Â» pour se connecter à  la base MySQL.

Une fois connecté,  vous obtenez dans la partie de gauche les onglets  :

• Server information permet d'obtenir des informations sur le serveur (version de MySQL etc..)
• Service Control permet d'arrêter le serveur MySQL ou ajouter des paramètres de lancement etc..
• Startup Variables permet de modifier les variables et donc la configuration du serveur, comme la mémoire utilisée, le nombre de connexion maximum etc..
• User Administration permet de gérer les utilisateurs
• Server Connections vous permet de visualiser les connexions totales en cours ou par utilisateurs
• Health permet de visualiser l'occupation CPU et mémoire
• Server Logs permet de visualiser les logs
• Replication Status permet de gérer la réplication du serveur si vous avez des clusters
• Backup & Restore permettent d'effectuer des sauvegardes et restarations de bases
• Catalogs vous permet de gérer les bases de données.

Les bases de données

En cliquant donc sur l'onglet Catalogs vous pouvez accéder aux bases de données du serveur. Dans l'exemple ci-dessous, c'est la base de données phpbb2.
Vous pouvez alors visualiser les tables sous forme de liste dans la partie droite de la fenêtre.

Les boutons en bas :

• Create Table permet de créer une nouvelle table
• Edit Table permet d'éditer la structure de la table
• Maintenance permet de réparer/optimiser ou vérifier que la table n'est pas corrompue
• Refresh rafraà®chit l'affichage

En effectuant un clic droit sur l'onglet Catalogs puis New vous pouvez créer une nouvelle base de données.

Exporter et importer une base MySQL

L'export (dump

Pour exporter une base MySQL, suivez les manipulations suivantes :

• Cliquez sur l'icà´ne dans la liste « Backup Â»
• Dans la nouvelle fenêtre, cliquez sur « New Project Â» afin de créer un projet de sauvegarde. Ce dernier pourra être réutilisé par la suite.
• Dans le champs en haut « Project Name Â», saisissez le nom du projet, par exemple « sauvegarde Â».
• Dans la liste « Schemata Â» apparaà®t les bases de données, sélectionnez la base de données et cliquez sur la flèche > afin de faire basculer la base dans le champs « Backup Content Â»
• Par défaut, une sauvegarde complète de la base est proposée puisque dans la partie « Backup Content Â» toutes les tables sont cochées. Dans le cas, o๠vous désirez exporter des tables spéficiques, il vous suffit de sélectionner celles qui vous intéressent
• Cliquez sur le bouton en bas « Save Project Â» pour sauvegarder le project qui s'ajoutera dans la liste des projets « Backup Projects Â» dans la partie gauche de la fenêtre
• Enfin, démarrez l'exportation de la base en cliquant sur le bouton tout en bas à  droite « Execute Backup now Â»
• Une fenêtre similaire à  celle-ci s'ouvre, sélectionnez le dossier de destination de la sauvegarde puis cliquez sur le bouton « Enregistrer Â» afin de démarrer l'exportation.
• Lorsque l'exportation est terminée, vous obtenez une fenêtre vous indiquant que la sauvegarde s'est terminée correctement et vous indique le fichier de sauvegarde et son emplacement.

Le fichier d'exportation est sous forme de requête SQ,  c'est donc en fichier texte. Il est conseillé de compresser ce fichier avec un outil de type Winzip ou Winrarafin de gagner en place disque.

Note : L'onglet Schedule permet de créer une tà¢ches planifiées afin d'effectuer des sauvegardes régulières.

La restauration (import)

Pour importer une base MySQL, suivez les manipulations suivante :

• Connectez vous à  MySQL à  l'aide de MySQLAdministrator
• Cliquez dans la liste des icà´nes sur « restore Â»
• Cliquez sur le bouton en bas « Open Backup File Â» afin d'ouvrir la fenêtre pour Sélectionner le fichier de sauvegarde à  partir duquel vous souhaitez effectuer l'importation.
• Cliquez sur le bouton en bas à  droite « Start Restorer Â» pour démarrer l'importation

• Notez aussi que dans le fenêtre « Général Â» vous pouvez choisir la base de destination dans Target Schema. Cela peut-être interressant si vous désirez tester la restauration sur autre base que celle de production.
• En cliquant sur l'onglet « Restore Content Â» puis sur le bouton, en bas à  droite « Analyse Backfup File Content Â», vous avez la possibilité d'analyser le fichier de restauration et sélectionnez les tables que vous désirez restaurer dans le cas o๠vous ne voulez pas restaurer la base entière.

Script de sauvegarde de base MySQL

Exemple de script de sauvegarde de base MySQL à  chaud pour Linux/Windows.

Sous GNU/Linux

#!/bin/sh
# Script de sauvegarde des bases mysql
# Ce script fait un dump de la base MySQL et tar.gzip le fichier dumpe
#
# Syntaxe: save_mysql.sh Nomdelamachine Nom_de_la_base_de_données user mot_de_passe
# 18/03/04 -- par tbd
#
 
# --- Definition des variables
date=`date +'%H:%M:%S %d/%m/%Y'`
file=`date -I`
repTrav=/OuLeFichierDumpeVa
repSave=/OuLeFichierTarVa
Host=$1
baseSave="$2"
user="$3"
password="$4"
 
echo Sauvegarde journaliere $2
 
# --- Deplacement des fichier
rm $repSave/$baseSave.tar.gz.4
mv $repSave/$baseSave.tar.gz.3 $repSave/$baseSave.tar.4
mv $repSave/$baseSave.tar.gz.2 $repSave/$baseSave.tar.3
mv $repSave/$baseSave.tar.gz.1 $repSave/$baseSave.tar.2
mv $repSave/$baseSave.tar.gz   $repSave/$baseSave.tar.1
 
# --- Sauvegarde
mysqldump --opt -h $1 -u $user --password=$password $baseSave > $repTrav/$baseSave-$file.sql
cd $repTrav
tar -czf $repSave/$baseSave.tar.gz $baseSave-$file.sql
chmod 600 $repSave/$baseSave.tar.gz
rm $repTrav/$baseSave-$file.sql
 
# --- On fait un petit rapport
echo $date -- Sauvegarde journaliere de $2 >> $repSave/rapport.log

Sous Windows

Vous devez créer un .cmd qui lancera ce script que vous pouvez par exemple mettre en tà¢ches planifiées. Ce fichier cmd doit contenir les paramètres suivants :
@Echo off
Chemin_du_fichier_cmd/nom_du_cmd host_du_serveur_mysql database user motdepasse

Script de sauvegarde :
@Echo off
setlocal
 
rem Ce cmd a pour but de sauvegarder des base mysql en effectuant un dump de la database.
rem la syntaxe est : mysql_dump.cmd ip_serveur_mysql database user password
 
rem Changez les chemins de mysqldump et la destination des sauvegardes
set chemin_mysql=c:\MySQL
set destination=D:\savemysql
 
 
rem ** NE PAS TOUCHER A PARTIR d'ICI **
rem ----------------------------------------------------
echo demarrage de la sauvegarde pour la base %2 sur %1
 
rem On décalle toutes les sauvegardes pour creer %2.sql
del /F /Q %destination%\%2_05.sql
move %destination%\%2_04.sql %destination%\%2_05.sql
move %destination%\%2_03.sql %destination%\%2_04.sql
move %destination%\%2_02.sql %destination%\%2_03.sql
move %destination%\%2.sql %destination%\%2_02.sql
 
rem On lance la sauvegarde avec les paramètres
%chemin_mysql%\bin\mysqldump -h %1 --user=%3 --password=%4 -B %2 > %destination%\%2.sql
rem ----------------------------------------------------

MySQL en ligne de commandes

Pour se connecter à  mysql :
- mysql -u user base -p

Pour se connecter à  une base de données :
connect nomdatabase;

Pour visualiser les tables :
show tables;

Pour visualiser la structure d'une table :
show columns from tables;
puis connect database; pour se connecter à  la base.

Donner tous les droits à  un user sur une base :
grant all privileges on Database.tables to login@'ip' identified by 'password' with grant option;
grant all privileges on Database.* to login@'%' identified by 'password' with grant option;

Supprimer les droits à  un user sur une base :
revoke all privieges on database from user;

exporter une base de données :
mysqldump -u user -p -B database -h ipduserveur > fichier.sql
exporter toutes les bases de données :
mysqldump -u root -p -A -h ipduserveur > fichier.sql
exporter une table d'une base de données :
mysqldump -u user -p -B database --tables tables -h ipduserveur > fichier.sql

importer une base qui a été exportée avec mysqldump
mysql -u root -p -B database < backup-file.sql
ou
mysql -e "source /patch-to-backup/backup-file.sql" database

Retour à  la page d'accueil