« Le système d'exploitation GNU-Linux/La base de données MySQL » : différence entre les versions

Un livre de Wikilivres.
Contenu supprimé Contenu ajouté
Ligne 501 : Ligne 501 :


* '''{{bleu|-o}}''' ou '''{{bleu|--optimize}}''' : permet d'optimiser les tables indiquées.
* '''{{bleu|-o}}''' ou '''{{bleu|--optimize}}''' : permet d'optimiser les tables indiquées.

Exemple : vérifier l'intégrité de toutes les tables de la base de données '''testdb''' :

# mysqlcheck '''testdb'''
testdb.client OK
testdb.client2 OK

Vérifier uniquement la table '''client''' :

# mysqlcheck testdb '''client'''
testdb.client OK






Version du 4 décembre 2009 à 16:21

Introduction

Mysql est un serveur de bases de données relationnelles SQL (OpenSource), fonctionnant sur le port 3306 en mode TCP.

Moteurs de stockage

Par défaut, Mysql utilise le moteur de stockage MyISAM.

Toutefois, Mysql propose d'autres moteurs de stockage :

  • InnoDB : support des transactions (compatible ACID), des clés étrangères et de l'intégrité différentielle
  • MERGE ou MRG_MYISAM : permet de fusionner plusieurs tables de structures identiques
  • BDB : Berkeley DB est un format de stockage très répandu (utilisé par exemple par OpenLDAP) et supporte les transactions (compatible ACID)
  • HEAP ou MEMORY : ces tables sont stockées en mémoire vive (RAM), elles sont très rapides mais ne survivent pas à un redémarrage de Mysql
  • CSV : les données sont stockées dans des fichiers au format CSV (Comma Separated Values)
  • ARCHIVE : stocke des informations en utilisant le minimum de place disque (perte de la notion d'index)
  • BLACKHOLE : les données sont tout simplement envoyées vers /dev/null et donc perdues. Moteur utilisé pour faire des tests

Types de données

Chaque information stockée dans une table est définie par un type décrivant la nature de l'information enregistrée.

Les nombres

  • TINYINT
  • SMALLINT
  • MEDIUMINT
  • INT
  • BIGINT
  • FLOAT
  • DOUBLE
  • DECIMAL

Les chaînes de caractères

  • CHAR
  • VARCHAR
  • BINARY
  • VARBINARY
  • BLOB
  • TEXT
  • ENUM
  • SET

Les dates et heures

  • DATE
  • TIME
  • DATETIME
  • TIMESTAMP
  • YEAR

Installation

Pour installer mysql sous Debian, on tape la commande suivante :

 # apt-get install mysql-server mysql-client

Fichier de configuration

Le fichier de configuration est /etc/mysql/my.cnf.

Dans ce fichier de configuration on trouve plusieurs sections :

 # Section du client Mysql
 [client]
 port            = 3306
 socket          = /var/run/mysqld/mysqld.sock
 
 # Section de mysqld_safe
 [mysqld_safe]
 socket          = /var/run/mysqld/mysqld.sock
 nice            = 0
 
 
 # Section du serveur Mysql
 [mysqld]
 # Utilisateur qui lance le daemon
 user            = mysql
 
 # Fichier qui contient le PID du processus
 pid-file        = /var/run/mysqld/mysqld.pid
 
 # Fichier socket qui permet une communication locale avec Mysqld (plus performant que de passer par le port 3306)
 socket          = /var/run/mysqld/mysqld.sock
 
 # Port sur lequel écoute Mysql
 port            = 3306
 
 # Répertoire de base de Mysql
 basedir         = /usr
 
 # Répertoire contenant les bases de données
 datadir         = /var/lib/mysql
 
 # Répertoire temporaire
 tmpdir          = /tmp
 
 # Permet de personnaliser le langage
 language        = /usr/share/mysql/english
 
 # Evite le blockage externe
 skip-external-locking
 
 # Adresse IP sur lequel écoute Mysql
 bind-address            = 127.0.0.1
 
 # Taille des caches Mysql : permet d'optimiser les performances de Mysql
 key_buffer              = 16M
 max_allowed_packet      = 16M
 thread_stack            = 128K
 thread_cache_size       = 8
 #max_connections        = 100
 #table_cache            = 64
 #thread_concurrency     = 10
 query_cache_limit       = 1M
 
 # Emplacement du fichier de Log
 log            = /var/log/mysql/mysql.log
 
 # On peut logger les requetes lentes
 #log_slow_queries       = /var/log/mysql/mysql-slow.log
 #long_query_time = 2
 #log-queries-not-using-indexes
 
 # Permet d'exécuter Mysql dans une cage chroot
 # chroot = /var/lib/mysql/
 
 # Options pour définir les certificats Mysql
 # ssl-ca=/etc/mysql/cacert.pem
 # ssl-cert=/etc/mysql/server-cert.pem
 # ssl-key=/etc/mysql/server-key.pem
 
 
 # Section pour mysqldump
 [mysqldump]
 quick
 quote-names
 max_allowed_packet      = 16M
 
 
 # Section pour ?
 [mysql]
 #no-auto-rehash # faster start of mysql but no tab completition
 
 
 # Section pour le format ISAM
 [isamchk]
 key_buffer              = 16M


Chaque fois que l'on modifie ce fichier, il faut relancer mysql :

 # /etc/init.d/mysql restart

Les bases de données sont sotckées dans le répertoire /var/lib/mysql/. Il faut donc sauvegarder ce répertoire.

Pour se connecter à Mysql à partir du shell, on utilise le client Mysql :

 # mysql -h <host> -u <login> -p <password>

Commandes SQL d'importation / exportation

LOAD DATA INFILE

La commande SQL LOAD DATA INFILE permet d'importer des données dans une table SQL à partir d'un fichier texte.

Par exemple, nous avons la table suivante :

mysql> DESC contacts;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| prenom | varchar(50) | YES  |     | NULL    |                |
| nom    | varchar(50) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Nous voulons insérer le fichier suivant :

# cat /var/lib/mysql/dbalex/data.txt
Anne;Aconda
Clara;Sheller
Sophie;Garo
Pauline;Machin

Pour importer le fichier dans la table contact, nous tapons la commande suivante :

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE contacts 
       FIELDS TERMINATED BY ";" (prenom,nom);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

Vérification :

mysql> SELECT * FROM contacts;
+----+---------+---------+
| id | prenom  | nom     |
+----+---------+---------+
| 18 | Anne    | Aconda  |
| 19 | Clara   | Sheller |
| 20 | Sophie  | Garo    |
| 21 | Pauline | Machin  |
+----+---------+---------+
4 rows in set (0.02 sec)

Pour connaître l'ensemble des options disponibles de la commande LOAD DATA INFILE, voir la documentation Mysql : Doc Mysql / LOAD DATA INFILE

SELECT INTO OUTFILE

La commande SQL SELECT INTO OUTFILE permet d'exporter des données de la base de données dans un fichier texte.

mysql> SELECT prenom,nom FROM contacts
       INTO OUTFILE '/tmp/result.txt'
       FIELDS TERMINATED BY ';'
       LINES TERMINATED BY '\n';
Query OK, 4 rows affected (0.02 sec)


Dans le cas présent, les champs "prenom" et "nom" ont été sélectionnés à partir de la table "contacts" afin de pouvoir être exportés dans le fichier "result.txt" situé dans le répertoire "/tmp"

Attention, il faut penser à préciser les clauses "FIELDS" et "LINES" destinées à traiter correctement les délimitations des champs et des lignes lors de l'export !


Remarque : C'est l'utilisateur mysql qui crée ce fichier, il doit donc avoir les droits nécessaires :

# ls -l /tmp/result.txt
-rw-rw-rw- 1 mysql mysql 53 2008-11-27 09:26 /tmp/result.txt

Les fichiers de données

Les fichiers contenant les données sont stockés dans /var/lib/mysql/.


/var/lib/mysql/ est donc le répertoire important à sauvegarder.


Chaque base de données dispose de son propre répertoire /var/lib/mysql/<nomdelabase>/.

Le fichier db.opt contient la configuration de la base de données (encodage des caractères ...).


Chaque table est enregistré dans trois fichiers :

  • <nomdetable>.FRM : contient la définition de la table
  • <nomdetable>.MYD : contient les données
  • <nomdetable>.MYI : contient les index

Les fichiers journaux

Les fichiers journaux sont contenus dans le répertoire /var/log/mysql/.

Il y a 3 fichiers importants :

  • mysql.log : contient les authentifications des utilisateurs et les requêtes SQL
  • mysql.err : contient les erreurs. Sous Debian, ce fichier n'existe pas ou est vide car les erreurs Mysql sont envoyées à syslog (Debian improvement).
  • mysql-slow.log : contient les requêtes lentes


Pour activer les fichiers log, il faut décommenter les deux lignes dans /etc/mysql/my.cnf :

log = /var/log/mysql/mysql.log
log_slow_queries = /var/log/mysql/mysql-slow.log

Puis relancer mysql :

/etc/init.d/mysql restart

Les documentations

Package Debian mysql-doc-5.0

Debian propose un package contenant les documentations Mysql. Il faut pour y accéder installer le package mysql-doc-5.0 :

# apt-get install mysql-doc-5.0

La documentation sur mysql est accessible dans le répertoire /usr/share/doc/mysql-doc-5.0.

Dans ce répertoire, on trouve des pages HTML qui contiennent toute la documentation de mysql.


Documentations en ligne

Sur le site de mysql

- Toutes les documentations

- Le manuel de référence en français


Listes de diffusion

Toutes les listes de diffusion


IRC

Canal #mysql sur Freenode

Modifications des privilèges

Il y a trois techniques pour modifier les privilèges :

  • modifier directement les tables user et db de la base de données mysql (ne pas oublier de faire un FLUSH PRIVILEGES; après)
  • utiliser les instructions SQL GRANT et REVOKE

Exemple : enlever tous les droits à l'utilisateur alex sur la base de données dbalex :

mysql> REVOKE ALL PRIVILEGES ON dbalex.* FROM alex;

Exemple : donner tous les droits à l'utilisateur alex sur la base de données dbalex :

mysql> GRANT ALL PRIVILEGES ON dbalex.* to alex;

on peut également spécifier les droits à donner :

mysql> GRANT select,insert,update,delete ON dbalex.* to alex;
  • utiliser la commande shell mysql_setpermission (cf ci-dessous)
  • utiliser un outil graphique de type mysql-admin ou phpmyadmin

Les commandes d'administration

Utile : pour ne pas avoir à spécifier le mot de passe du root chaque fois que l'on invoque une commande mysql*, il suffit de créer un fichier .my.cnf dans son répertoire de travail et qui contient le mot de passe à utiliser. Attention aux droits d'accès à ce fichier !

# echo -e "[client]\npassword=root" > ~/.my.cnf && chmod 600 ~/.my.cnf
# ls -l /root/.my.cnf
-rw------- 1 root root 23 déc  4 15:53 /root/.my.cnf
# cat /root/.my.cnf
[client]
password=root

mysql

La commande mysql est le client Mysql en ligne de commande. Il permet de se connecter à Mysql et de saisir des commandes SQL.

Voici les options les plus courantes :

Options de base (communes à la plupart des commandes mysql) :

  • -h (--host=) : définit l'hôte hébergeant la base de données
  • -D (--database=) : définit la base sur laquelle l'utilisateur va se connecter.
  • -u (--user=) : précise le nom d'utilisateur Mysql sous lequel l'utilisateur se connecte.
  • -P (--port=) : détermine le port à utiliser pour la connexion
  • -p (--password=) : demande la saisie du mot de passe (obligatoire si l'utilisateur a été défini pour se connecter avec un mot de passe)

Exemple :

$ mysql --database=dbalex -u paul -p
PASSWORD:

Dans cet exemple la requête demande de se connecter par mot de passe à la base dbalex en tant que paul.

Autres options intéressantes :

  • -H (--html) : permet de produire des sorties en HTML
  • -X (--xml) : permet de produire des sorties en XML

Pour aller plus loin :

# mysql --help

Donne la liste et un bref descriptif de toutes les options à utiliser avec la commande mysql

mysqldump

La commande mysqldump permet d'exporter des données d'une base de données.

Son fonctionnement est particulièrement intéressant car elle génère les commandes SQL permettant de re-créer la base de données sur un autre serveur.


Pour exporter la base de donnée « myBase », on utilise la commande suivante :

# mysqldump -u root -p myBase > myBase_backup.sql

Ceci fera l'export dans un fichier « myBase_backup.sql ».


Pour importer une base de données sauvegardée via mysqldump, on utilise la commande cliente mysql et une redirection en entrée :

# mysql -u root -p myBase < myBase_backup.sql

L'option --compatible permet de spécifier à mysqldump le format à utiliser pour être compatible avec les bases de données existantes. Exemple :

# mysqldump --compatible=oracle -u root -p myBase > myBase_backup.sql

Cette option peut prendre les valeurs suivantes : ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options

En utilisant mysqldump et ssh, on peut dupliquer une base de données sur une machien distante :

# mysqldump testdb -p<mot de passe local> | ssh pc211 'echo "create database dbalex;" | mysql -p<mot de passe distant> ; cat - | mysql -p<mot de passe distant> dbalex'

mysqlimport

...

# mysqlimport -p --fields-terminated-by="\;"
              --lines-terminated-by="\n"
              --columns=prenom,nom
              dbalex /var/lib/mysql/dbalex/contacts.txt
Enter password:
dbalex.contacts: Records: 4  Deleted: 0  Skipped: 0  Warnings: 4
#

Attention : le nom du fichier (sans extension) doit être le même que la table.

mysqladmin

La commande mysqladmin permet de passer des commandes à Mysql.

$ mysqladmin [OPTIONS] commande [options de la commande]

OPTIONS :

-h host
-u user
-p password 

Commandes :

  • create : crée une base de données
  • drop : supprime une base de données
  • flush-privileges : recharge les tables de droits
  • flush-hosts : met à jour les informations concernant les hôtes
  • flush-logs : met à jour les informations concernant les journaux
  • flush-status : efface les variables status
  • flush-tables : met à jour les informations concernant les tables
  • flush-threads : met à jour les informations concernant les threads
  • password : change le mot de passe
  • old-password : change le mot de passe en utilisant l'ancien algorithme de chiffrement
  • ping : teste si mysql fonctionne
  • reload : recharge la configuration
  • refresh : vide les caches
  • shutdown : arrête mysql
  • status : connaitre des informations sur l'état du serveur
  • extended-status : connaitre des informations détaillées sur l'état du serveur
  • proc ou processlist : connaitre les utilisateurs connectés
  • debug : Passe Mysql en mode debug
  • kill : Permet d'arrêter des threads
  • start-slave : Démarre la réplication sur le serveur de réplication esclave
  • stop-slave : Arrête la réplication sur le serveur de réplication esclave
  • ...

Utile : j'ai perdu le mot de passe du compte root mysql, comment le changer :

1. On arrête Mysql :

# /etc/init.d/mysql stop

2. On relance le daemon mysqld avec l'option --skip-grant-tables

# mysqld --skip-grant-tables

3. Depuis une autre fenêtre, on se connecte à Mysql

# mysql mysql

4. On met à jour la table user :

mysql> update user set password=PASSWORD('root') where user='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

5. On arrête proprement le daemon mysqld :

# killall mysqld

6. On relance Mysql :

# /etc/init.d/mysql start

mysqlcheck

La commande mysqlcheck permet de vérifier l'intégrité d'une base de données. On indique la (les) base(s) de données à vérifier (et optionnellement les tables à vérifier), et la commande donne l'état : OK ou corrompue.


Options courantes :

  • -r ou --repair  : permet de corriger presque tout, sauf les problèmes de doublons pour les clés uniques.
  • -a ou --analyse  : permet d'analyser les tables indiquées.
  • -o ou --optimize  : permet d'optimiser les tables indiquées.

Exemple : vérifier l'intégrité de toutes les tables de la base de données testdb :

# mysqlcheck testdb
testdb.client                                      OK
testdb.client2                                     OK

Vérifier uniquement la table client :

# mysqlcheck testdb client
testdb.client                                      OK


Remarque : mysqlcheck ne fonctionne qu'en utilisant des instructions SQL (CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, et OPTIMIZE TABLE), il a donc besoin que le serveur Mysql soit en train de fonctionner.

myisamchk

La commande myisamchk permet la restauration d'une table ou plusieurs tables endommagées à la suite de crash répétés du démon mysqld.

Attention : myisamchk intervient directement sur les fichiers, il se passe de mysqld contrairement à mysqlcheck !

Quelques précautions d'usage: : Si vous utilisez myisamchk pour réparer ou optimiser les tables, vous devez toujours vous assurer que mysqld n'utilise pas cette table (ce qui s'applique aussi si vous utilisez --skip-external-locking ). Si vous n'éteignez pas le serveur mysqld , vous devez au moins utiliser mysqladmin flush-tables avant de lancer myisamchk.

Attention : Vos tables peuvent être corrompues si le serveur mysqld et myisamchk travaillent dans une même table simultanément.


Vérifier la cohérence d'une table:

On indique à myisamchk les tables à vérifier en désignant les fichiers index MYI :

# myisamchk /chemin/bases/mysql/nom_de_la_bd/nom_de_table.MYI

On peut également spécifier toutes les tables comme ceci :

# myisamchk /chemin/bases/mysql/nom_de_la_bd/*.MYI

Voire carrément vérifier toutes les tables de toutes les bases :

# myisamchk /chemin/bases/mysql/*/*.MYI


La commande affiche un rapport d'analyse, et si tout est OK, ne signale pas d'erreur :

# myisamchk /var/lib/mysql/ampache/album.MYI
 
 Checking MyISAM file: /var/lib/mysql/ampache/album.MYI
 Data records:     542   Deleted blocks:       0
 - check file-size
 - check record delete-chain
 - check key delete-chain
 - check index reference
 - check data record references index: 1
 - check data record references index: 2
 - check data record references index: 3
 - check data record references index: 4
 - check record links

Cette commande trouvera 99.99% de toutes les erreurs. Elle ne peut toutefois détecter les erreurs qui impliquent uniquement le fichier de données (ce qui est très inhabituel).

Si vous voulez uniquement vérifier une table sans que la commande produise un affichage, il faut utiliser l'option -s (ou --silent).

mysql_setpermission

La commande mysql_setpermission permet de définir les permissions des utilisateurs Mysql de manière intéractive.

Ecrit en Perl, ce script a besoin des modules DBI et DBD::mysql pour fonctionner.

# mysql_setpermission -p
Option p is ambiguous (password, port)
Password for user  to connect to MySQL:
######################################################################
## Welcome to the permission setter 1.4 for MySQL.
## made by Luuk de Boer
######################################################################
What would you like to do:
 1. Set password for an existing user.
 2. Create a database + user privilege for that database
    and host combination (user can only do SELECT)
 3. Create/append user privilege for an existing database
    and host combination (user can only do SELECT)
 4. Create/append broader user privileges for an existing
    database and host combination
    (user can do SELECT,INSERT,UPDATE,DELETE)
 5. Create/append quite extended user privileges for an
    existing database and host combination (user can do
    SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,
    LOCK TABLES,CREATE TEMPORARY TABLES)
 6. Create/append full privileges for an existing database
    and host combination (user has FULL privilege)
 7. Remove all privileges for for an existing database and
    host combination.
    (user will have all permission fields set to N)
 0. exit this program

Make your choice [1,2,3,4,5,6,7,0]:


  • 1 : Permet de modifier le mot de passe d'un utilisateur éxistant


  • 2 : Permet d'ajouter/modifier le droit SELECT d'un utilisateur sur une table en créant un utilisateur et une table :
# Make your choice [1,2,3,4,5,6,7,0]: 2


# Which database would you like to add: dbuser //On donne le nom de la base de donnée à créer.
# The new database dbuser will be created

# What username is to be created: user //On donne le nom de l'utilisateur à créer.
# Username = user

# We now need to know from what host(s) the user will connect.
# Keep in mind that % means 'from any host' ...
# The host please: % //On donne l'host
#

L'utilisateur user aura le droit SELECT sur la base de donnée dbuser


  • 3 : Même fonction que la sélection 2, sauf que l'on donne le droit SELECT sur une table existante.


  • 4 : Même fonction que la sélection 2, pour les droits SELECT,INSERT,UPDATE,DELETE.


  • 5 : Même fonction que la sélection 2, pour les droits SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,LOCK TABLES,CREATE TEMPORARY TABLES


  • 6 : Même fonction que la sélection 2, pour tous les droits.


  • 7 : Met les droits par défaut (N) pour un utilisateur existant.


  • 0 : sortir du programme


Les différentes options de mysql_setpermission :

  • --help (ou -h) : permet d'afficher l'aide
  • --host (ou -h) : se connecte au serveur Mysql donné
  • --password (ou -p) : pour qu'il demande le mot de passe à la connexion
  • --port (ou -P) : donne le numéro de port si différent de celui par défaut
  • --user (ou -u) : se connecte au serveur avec le nom de compte donné

mysqlhotcopy

La commande mysqlhotcopy permet de copier une base de données à chaud, c'est à dire sans arrêter le serveur Mysql.

Pour cela, la commande bloque les tables afin qu'il n'y ai pas de modification des tables durant la copie.

Les tables sont ensuite débloquées.

Exemple d'utilisation :

# mysqlhotcopy -p motdepasse dbalex /tmp
Locked 1 tables in 0 seconds.
Flushed tables (`db2`.`contacts`) in 0 seconds.
Copying 4 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 1 tables (4 files) in 0 seconds  (0 seconds overall)

Vérification :

# ls -l /tmp/db2/
total 24
-rw-rw---- 1 mysql mysql 8618 2008-11-27 10:56 contacts.frm
-rw-rw---- 1 mysql mysql   96 2008-11-27 11:43 contacts.MYD
-rw-rw---- 1 mysql mysql 2048 2008-11-27 11:43 contacts.MYI
-rw-rw---- 1 mysql mysql   65 2008-11-27 10:55 db.opt

Autres programmes utiles

mysqlcc

mysqlcc est l'ancien logiciel d'administration de Mysql. Il n'est plus développé actuellement, et n'est pas 100% compatible avec la version 5 de Mysql. Il est désormais remplacé par mysql-admin et mysql-query-browser.

mysql-admin

À faire...link={{{link}}}

rajouter descriptif

mysql-query-browser

À faire...link={{{link}}}

rajouter descriptif et une copie écran

mysql-navigator

À faire...link={{{link}}}

rajouter descriptif et une copie écran


phpmyadmin

Phpmyadmin est un paquet qui permet, grâce à une interface web, d'éditer/créer/supprimer des bases, des tables et leur contenu.



Prérequis :

  • Serveur Apache installé.
  • PHP installé.

Installation du serveur Apache et PHP

#apt-get install apache2 php5

Accepter les options par défaut.

Pour vérifier l'installation d'Apache, entrer l'url http://localhost dans un navigateur. Un message explicite "It works!" devrait s'afficher.

Installation de Phpmyadmin

#apt-get install phpmyadmin

Une fois les paquets téléchargés et installés, taper http://localhost/phpmyadmin/ dans la barre d'adresse d'un navigateur (1) et faire "Enter". Une page s'affichera, invitant à choisir sa langue d'affichage (2), entrer son nom utilisateur (3) et son mot de passe (4). Valider avec le bouton "Exécuter"

Vous obtiendrez cet écran où toutes les bases sont paramétrables.

Tests de performance

MyISAM vs Innodb