TP Administration de MYSQL

TP 1  Généralités

• Quelle est la version de Mysql installée ? root passe la commande :
  mysqld -V

• Le serveur MySql est-il actuellement en fonctionnement ? :

  ps ax | grep mysql
  --> root a lancé le processus initial /usr/bin/mysqld_safe
  --> puis a transmis à syslog, la gestion des logs
  --> ensuite x processus serveur mysqld ont été lancés, sous la propriété de l'utilisateur mysql
     (un utilisateur standard, par souci de sécurité)
  

• Le contrôle du serveur, son démarrage, son arrêt sont assurés, comme pour tous les services, par un script /etc/init.d/mysql
  qui attend un paramètre start, stop ou reload

• Examen du fichier de configuration
Sur Debian, il se trouve à /etc/mysql/my.cnf
Attention, par défaut, le serveur Mysql ne travaille qu'en local, ne répond qu'aux requêtes locales, envoyés par des scripts PHP (comme Phpmyadmin) gérés par un serveur Apache hébergé par la même machine.
Que donne une commande nmap ip ?
Commentez avec un # la ligne bind-address =127.0.0.1. Redémarrez Mysql par /etc/init.d/mysql restart
Examinez de nouveau les ports ouverts, en écoute du réseau. Conclusion.
• Examen des logs, dans le fichier /var/log/syslog
  Par exemple le démarrage du serveur a généré une ligne comme :
  date heure hote mysqld_safe: starting mysqld daemon with databases from /var/lib/mysql/

• Mais où sont placées les bases de données ?
  
  • Dès le début de son fonctionnement le serveur a installé la base mysql. IL s'agit de la base de données interne du serveur, elle est destinée à gérer les utilisateurs, l'authentification et leurs droits d'accès (appelés privilèges).
  • Constatez que chaque base de données occupe un sous-répertoire de /var/lib/mysql qui porte le nom de la base.
  • Et que dans un tel répertoire, chaque table de la base correspond à 3 fichiers qui portent le nom de cette table (voir annexe)
  • Un autre moyen de connaitre les bases s'obtient avec la commande mysqlshow

• Documentation
  En ligne et en français : http://dev.mysql.com/doc/mysql/fr/
  Voir en particulier le tutoriel introductif au langage SQL

TP 2  Gestion locale en mode commande

1. Connexion cliente
   La commande mysql exécute un utilitaire client, qui permet de dialoguer avec le serveur MYSQL
   Ce mode d'accès effectué en ligne de commande, est appelé le mode console.
   Lorsque l'on lance cette commande, aucun login, ni mot de passe n'est demandé. On peut ainsi se connecter en fait comme administrateur de mysql, et sans mot de passe !

   mysql
   Welcome to the MySQL monitor. Commands ends with ; or \g
   Your Mysql connection id is ...
   tape help; ou \h for help
   mysql>
   

   On peut aussi se connecter à distance à travers une session ssh
   Connectez-vous à un serveur voisin par connexion ssh, puis commande mysql

2. Requêtes au serveur
   Le serveur Mysql ne comprend que le langage SQL. Il faut s'adresser à lui exclusivement par commandes SQL !
   Or justement, le client mysql est un interpréteur de commandes SQL !
   Remarque : par défaut la casse n'est pas prise en compte; et par convention il est d'usage courant d'écrire les mots-clés SQL en majuscules.
   Petit test (attention les ; de fin de commande sont obligatoires en syntaxe SQL)

   mysql
   mysql> SHOW databases;
   mysql> USE test;
   mysql> SHOW tables;
   mysql> USE mysql;
   mysql> SHOW tables;
   mysql> SHOW columns FROM user;
   mysql> SELECT  host, user, password FROM user;
   mysql>quit
   

3. Stockage des tables
Comparer les tables de la base mysql, avec les fichiers contenus dans le répertoire /var/lib/mysql/mysql.
Conclusion : quels sont les fichiers nécessaires à la description de chaque table d'une base ?
4. Sécuriser le compte administrateur
   • Mettre un mot de passe pour l'administrateur root@localhost (homonyme du superviseur Linux, mais tout-à-fait distinct)

     mysqladmin -u root password fctice
     

     Le mot de passe de l'administrateur "root" de mysql est maintenant fctice.
   • la connexion anonyme est alors interdite

     mysql
      --> access denied for user root@localhost
     

   • Désormais, il faut préciser login et mot de passe interactivement

     mysql -u root -p
     Enter password :  fctice (il n'apparait pas à l'écran)
     Welcome to the MySQL monitor
     
     mysql>USE mysql
     mysql>SELECT  host, user, password FROM user;
     mysql>quit
     

   • Remarques :
     - Observez maintenant le mot de passe crypté présent dans le champ password de la table user
     - On aurait pu attribuer ce mot de passe à root, par l'intermédiaire de phpmyadmin (voir en annexe)

5. Supprimer l'utilisateur anonyme
   Sur certains installations, un compte anonyme est créé. Il est vivement recommandé de le supprimer ... pas vraiment pour une question de sécurité car ses privilèges sont très limité, mais pour éviter des confusions liées à la procédure d'authentification. Pour le supprimer, root se connecte directement à la base mysql (le "mysql" à la fin de la commande, en fait effectue une commande USE mysql).
   Puis root passe une requete SQL de suppression

   mysql -u root -p mysql
   mysql> DELETE FROM user WHERE user='';
   mysql> DELETE FROM user WHERE password='';
   mysql> SELECT host, user, password FROM user;
   

TP 3  Accès distant

1. Essai de connexion distante
Le client mysql peut tenter une connexion distante, à partir d'une station voisine (on donne l'adresse IP du serveur ou un nom de machine résolvable)


mysql -h ip -u root -p
Password ..
Refus !! lisez bien le motif du refus, il explique pourquoi ..

2. Créer des comptes distants
   En fait un compte utilisateur est toujours attaché à une machine, à partir de laquelle la connexion est sollicitée.
   Et le compte root maintenant protégé par mot de passe, est un compte local attaché à l'hôte localhost.
   Il s'agit de créer un compte root accessible d'un serveur voisin dont vous connaissez l'ip (pour un serveur en production cela est très peu recommandé !). Pour cela relancer le client mysql localement (ou à travers une connexion ssh)

   mysql -u root -p
   Password ..
   mysql>GRANT all privileges ON *.* TO 'root'@'ip' IDENTIFIED BY 'fctice'
   

3. Retester
Sur la station voisine, redemander la connexion avec le nouveau compte root, et l'utiliser pour créer une nouvelle base, nommée essais, puis la supprimer (vous constatez qu'aucune confirmation n'est demandée !)

mysql -h ip -u root  -p
Password ..
mysql>CREATE DATABASE essais ;

4. Création d'un compte restreint accessible de partout
En adaptant la commande grant précédente, il s'agit de créer un compte stage (mot de passe stage) accessible de n'importe où (y compris localement), mais limité à la commande SQL d'affichage select.

mysql -u root -p
Password ..
mysql>GRANT all privileges ON *.* TO 'root'@'ip' IDENTIFIED BY 'fctice'

Tester ensuite une connexion cliente quelconque. Est-ce possible ?
Se connecter localement avec phpmyadmin, puis passer des commandes d'affichage, d'insertion et de suppression sur une table (par exemple db) de la base système mysql. Que vérifiez-vous ?

TP 4  Installation, sauvegarde et restauration d'une base

Créer une base

Se connecter comme root et créer une nouvelle base cdi
- create database cdi, directement dans la fenêtre SQL de phpmyadmin
- ou dans le formulaire de la page d'accueil "Créer une base de données"
Constater qu'un nouveau sous-rép. cdi vide a été créé dans /var/www/mysql.

Importation de la structure et des données

La structure de la base cdi ainsi que la totalité de ses enregistrements ont été préalablement sauvegardés sous forme de fichier cdi.sql. Au préalable, lire et comprendre le contenu du fichier cdi.sql

En mode console
mysql -u root -p cdi < /chemin/cdi.sql

Sous Phpmyadmin
sélectionner la base cdi
"Emplacement du fichier texte"
chercher le fichier cdi.sql sur le système de fichier local
Exécuter
Bien comparer ce fichier à la structure et aux contenus des tables.

Sauvegarde d'une base au format SQL

Le mode console est indispensable pour programmer une sauvegarde automatique avec crontab

En mode console
mysqldump - u root -p cdi > /chemin/cdi1.sql
--> donner le mot de passe

Sous Phpmyadmin
Comme root, sélectionner la base cdi
"Afficher le schéma de la base de données"
Structure et données
Transmettre
Exécuter
choix du répertoire, nom du fichier : cdi1.sql
Comparer avec le fichier d'origine cdi.sql

Création d'une table à partir d'une exportation d'une table msaccess ou msexcel

1. Créer la table glossaire dans la base test
   

   Avec Phpmyadmin, sélectionner la base test
   "Créer une nouvelle table sur la base test" :
   Nom : glossaire Champs : 3
   Exécuter
   
   A l'aide du formulaire proposé, définir sa structure
         Champ         Type          Null       primaire
       ----------   -------------   ------      --------
        sigle         varchar(12)    not null      x
        descriptif    varchar(50)    not null
        type          char(1)        not null
   
   Sauvegarder -->  La nouvelle table glossaire est alors créée dans la base test ( ou dans votre base webmestre)
   

2. Remarquer que la requête SQL envoyée par phpmyadmin pour créer la table est :

    CREATE TABLE glossaire (
     sigle VARCHAR(12) NOT NULL,
     descriptif VARCHAR(50) default NOT NULL,
     type VARCHAR(1) default NOT NULL,
     PRIMARY KEY (sigle)
     );
   

3. Gestion de la table
   Phpmyadmin permet maintenant de gérer la table
   Observer les rubriques : Supprimer (la table), Insérer (des enregistrements) et la possibilité de modifier ou suppression de champs
   
4. Examiner le fichier fourni glossaire.txt
   Il a été obtenu en exportant une table access au format délimité, avec le séparateur ; sans délimitateur de texte.
5. Dans Phpmyadmin, cliquer sur insérer des données provenant d'un fichier de texte dans la table. Renseigner le formulaire:

   Emplacement du fichier texte : glossaire.txt
   Champs terminée par ;
   Supprimer les autres choix
   Exécuter
   

   Observer les 283 enregistrements importés et supprimer le premier.

TP 5 Créer de comptes, accorder des "privilèges"

Création des comptes d'accès

Il s'agit de créer 2 comptes pour la base cdi, à l'aide de phpmyadmin :
public (sans mot de passe) et admin/admin
Ces utilisateurs ne doivent posséder aucun droit d'administration globaux sur le serveur lui-même, mais uniquement des droits d'accès grand public (public) et d'administration (admin) sur la base cdi

Création des comptes d'accès à cdi

Dans la base Mysql, table user, lien insérer un nouvel enregistrement

Base mysql; table user; action "insérer"
 - host  localhost     localhost
 - user  public        admin
 - mdp    .....        admin  -- avec fonction=PASSWORD
Laisser tous les droits (globaux) à No
Ne pas oublir de relancer le serveur

Se connecter sous le compte admin/admin en mode console ou avec phpmyadmin; admin peut-il alors accéder à la gestion des utilisateurs ? Conclusion.

Commandes SQL

Ce sont les commandes SQL grant et revoke qui permettent d'attribuer et de retirer des droits. Exemple :
Accorder à admin le privilège d'être gestionnaire de la base cdi, avec droit d'accès à distance (@% = de partout), avec certains privilèves (ALL PRIVILEGES pour les attribuer tous) sur toutes (*) les tables de cdi

mysql> GRANT select,insert,update,delete
ON  cdi.*
TO 'admin'@'%'
IDENTIFIED BY "admin";

Attribution des privilèges pour la base cdi

Conformément aux informations suivantes, à l'aide de phpmyadmin (ou si on a le courage avec grant !)

   Comptes      |              droits à attribuer
----------------|-------------------------------------------------------------------
 admin/admin    | - droits select,insert,update,delete, au niveau de la base CDI
----------------|-------------------------------------------------------------------
 public/...     | - accès à la base cdi, sans aucun privilège global
                | - droit SELECT  (exclusivement) sur les tables inscrits, documents
                | - droit INSERT  sur la table demande
----------------|-------------------------------------------------------------------

Relancer le serveur et bien vérifier l'application de ces droits, comme admin et comme public.
Remarque : il est aussi facile d'utiliser la commande grant (si on la maitrise). Ecrire les commandes grant pour créer le compte pub/pub, avec les memes droits que public.

TP 6  Rapide introduction au langage SQL

ANNEXES

Accès sécurisé à Mysql par phpmyadmin

• Attention ! Par défaut l'accès à la gestion des bases par phpMyAdmin n'est pas protégé. Donc tout utilisateur peut lancer l'URL vers phpMyAdmin, et à travers cette interface agir comme root
  Il faut donc placer rapidement dans le répertoire un processus d'authentification par mot de passe.
• Examiner la base mysql, la structure de la table user et ses enregistrements root.
  Il s'agit d'une situation initiale particulière. Attention, en l'absence de mot de passe sur root, il y a une totale insécurité !
  Pour protéger l'accès en root par un mot de passe (fctice par exemple)
  - sur la ligne de root sur localhost, activer le lien Modifier
  - sur le champ Password, sélectionner la fonction PASSWORD, et mettre la valeur fctice
  exécuter, et observer maintenant la présence du mot de passe crypté pour root dans la table user
• Si on relance le serveur, maintenant Phpmyadmin ne fonctionne plus ! "accès refusé pour l'utilisateur root@localhost"
  En effet le serveur Mysql réclame maintenant un mot de passe ..mais comment le lui donner ?
  Pour cela, il faut effectuer une petite modification du paramètrage dans le fichier de configuration config.inc.php.
  (explications détaillées dans le fichier Documentation.html accessible sur la page d'accueil)
  Choisir l'authentification par le serveur http. A l'utilisation, une boite d'authentification s'ouvrira pour saisir login et mot de passe de l'utilisateur voulant accéder au serveur mysql :

  $cfgServers[$i]['auth_type'] = 'http'
  $cfgServers[$i]['user'] = 'root'
  $cfgServers[$i]['password'] = ''
  

Organisation d'une base

• Pour créer la table carnet dans la base essais, il faut se connecter au serveur, et passer la commande console de description de la structure de la table :

  mysql> USE essais
  mysql> CREATE  TABLE carnet (num int, nom varchar(30), email varchar(30) );
  

• On constate que 3 fichiers portant le nom de cette table carnet sont créés dans le répertoire de la base /var/lib/mysql/essais
  Il en sera de même pour nouvelle table incluse dans cette base.

  carnet.MYD	contient les données (vide à la création)
  carnet.MYI	contient la descripton des index
  carnet.frm	décrit la structure de la table

Gestion des utilisateurs et système de droits

Droits d'accès des utilisateurs aux bases

• à créer et à supprimer des utilisateurs (login, mot de passe)
• à créer et supprimer les bases de données
• à fixer les privilèges des utilisateurs sur ces bases et leurs composants.

Description des droits

• La table mysql.user contient la liste des comptes pouvant accéder au serveur et détermine les droits GLOBAUX au niveau serveur sur l'ENSEMBLE des bases. Elle est consultée lors de CHAQUE demande de connexion, que ce soit par l'utilitaire mysql, ou tout autre mode d'accès, par exemple avec la fonction PHP mysql_connect(). Voici la structure de cette table :

  Attribut	Valeur	Signification
  Host		La machine de laquelle on tente la connexion (host=localhost par défaut)
  User		nom de connexion de l'utilisateur
  Password		mot de passe mysql de l'utilisateur
  Select_priv	Y/N	droit de sélection
  Insert_priv	Y/N	droit d'insertion
  Update_priv	Y/N	droit de modification
  Delete_priv	Y/N	droit de suppression
  Index_priv	Y/N	Indexation
  Alter_priv	Y/N	droit de modification (table, champ)
  Create_priv	Y/N	droit de création
  Drop_priv	Y/N	Suppression
  Grant_priv	Y/N	Permission
  Reload_priv	Y/N	Relancer mysql
  Shutdown_priv	Y/N	Arreter mysql
  Process_priv	Y/N	Processus
  File_priv	Y/N	Lecture et écriture de fichiers (import/export)

• La table mysql.db contient les privilèges d'un utilisateur (autre que root) sur chaque base de données créée (donc pas la table mysql). Elle est consultée à chaque commande SQL de cet utilisateur portant sur les tables de cette base, pour examiner s'il a le droit le "privilège" de pouvoir utiliser cette commande. Voici la structure cette table :

  Attribut	Valeur	Signification
  Host		machine de laquelle on tente la connexion
  Db		Base de données
  User		Utilisateur
  Select_priv	Y/N	droit select, de passer des commandes sql SELECT
  Insert_priv	Y/N	droit insert, de passer des commandes INSERT
  Update_priv	Y/N	droit update, de mise à jour des enregistrements d'une table
  Delete_priv	Y/N	droit delete, d'effacement des enregistrements d'une table
  Index_priv	Y/N	droit d'utiliser INDEX pour créer ou supprimer des index
  Alter_priv	Y/N	droit alter, pour utiliser ALTER TABLE pour modifier la structure d'une table (ou la renommer)
  Create_priv	Y/N	droit create, d'utiliser CREATE pour créer de nouvelles bases ou tables
  Drop_priv	Y/N	droit drop, d'utiliser DROP pour supprimer une base existante (ou une table)
  Grant_priv	Y/N	droit grant, attribue à d'autres utilisateurs Mysql les permissions que l'on possède

  Les tables mysql.tables_priv et mysql.columns_priv déterminent des droits encore plus précis des utilisateurs respectivement sur une table d'une base et sur un champ (colonne) d'une table

Utilitaires d'administration

Comment connaitre l'état du serveur ? utilitaire mysqladmin

• L'utiitaire mysqladmin est spécialisé dans la gestion interne du serveur (mais aussi des bases et des utilisateurs)
  Ainsi, pour arrêter le serveur : mysqladmin shutdown
• Pour connaitre la charge de travail du serveur et des statistiques, option status :
  

   mysqladmin -p status
  Enter password :
  Uptime: 4153 Treads: 1 Questions 76 ......
  

  Uptime donne le nombre de secondes écoulées depuis le lancement du serveur mysqld, Threads le nombre de processus fils, c'est-à-dire à peu près le nombre de connexions ouvertes par des clients
• Pour connaitre le détail de chaque connexion cliente, option processlist

   mysqladmin -p processlist
  Enter password :
  Id  User  Host db Command Time State Info
  

Comment sauvegarder les bases ? utilitaire mysqldump

• A la place de commandes de sauvegardes classiques comme tar, il est bien préférable d'utiliser un outil spécialisé mysqldump, qui génére le source écrit en commandes SQL. La restauration consiste alors à exécuter ce fichier de commandes.
• Cet utilitaire posséde de nombreuses options : voir man mysqldump
• Pour sauvegarder le contenu de la base essais dans un fichier essais.sql, situé dans /home/mysql.svg
  (l'option -p pour faire demander le mot de passe )

   mkdir /home/mysql.svg
   mysqldump -p essais >  /home/mysql.svg/essais.sql
   less essais.sql
  

• Pour restaurer la base essais, si nécessaire procéder d'abord à sa (re)création, puis importer le fichier sauvegardé précédemment essais.sql, en entrée de la commande mysql. La ligne suivante va alors produire l'exécution des commandes SQL qu'il contient, et donc reconstituer les structures des tables et y réinsérer tous leurs enregistrements
  

  mysql -p essais <  /home/mysql.svg/essais.sql
  

• Ici, le gestionnaire admin ne sauvegarde que les structures des tables (option -d), et place le résultat dans son rép.personnel.

  mysqldump -u admin -p -d essais >  ~/essais.sql
  less /home/admin/essais.sql
  

• On peut sauvegarder séparément la structure et les données de chaque table de la base essais, dans un répertoire /home/mysql.svg Pour chaque table de la base essais, 2 fichiers sont générés. Ainsi carnet.sql et carnet.txt contiennent respectivement les commandes de création de la table carnet, et ses enregistrements.

  mysqldump -p essais -T /home/mysql.svg
  cd /home/mysql.svg
  ll
  

Comment vérifier et réparer les bases ? utilitaire myisamchk

• L'utilitaire myisamchk est surtout utilisé pour retrouver l'intégrité d'une table endommagée. Il doit être lancé pendant un arrêt du serveur, et il faut lui indiquer le chemin vers la table :

   myisamchk -d  /var/lib/mysql/cdi/eleves
  ISAM files: eleves
  Data records :  150       Deteted blocks:   0
  .........
  

• L'option -r défragmente le fichier si nécessaire (en cas de nombreux Deteted blocks)
  

   myisamchk -r cdi.eleves

Perte du mot de passe mysql

1. arrêter le Serveur Mysql
/etc/init.d/mysql stop
2. On relance en inhibant de l'authentification
/usr/sbin/mysqld --skip-grant-tables &
3. Mettre le mot de passe en rentrant comme root dans phpmyadmin
4. ou en ligne de commande

mysql
mysql> use mysql
mysql> update user set password=PASSWORD("nouveau_mot_de_passe") where user='root';
mysql> quit

5. Relancer mysql
/etc/init.d/mysql restart
si problème, lancer d'abord killall mysqld