Base de connaissances CCM
Bases de données - MySQL

Vous êtes administrateur d'une base de données MySQL et vous souhaitez analyser en temps réel le fonctionnement de MySQL afin de diagnostiquer les éventuels problèmes, suivre la charge de fonctionnement du serveur ?

PhpMyAdmin permet de voir les variables du serveur MySQL, d'en connaître les paramètres et de dresser la liste des processus, toutefois il ne s'agit pas d'une analyse en temps réel.

Il existe un outil libre permettant de suivre en temps réel le fonctionnement de MySQL : Mtop.

Pour utiliser mtop, il suffit d'avoir un compte sur la machine sur laquelle le serveur MySQL fonctionne, de le télécharger à cette adresse

Puis de l'installer en lançant les commandes suivantes (après avoir pris le soin de décompresser l'archive) :

    perl Makefile.PL 
    make 
    make install 

Lire la suite

My SQL - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) »

Sous phpMyAdmin vous obtenez l'erreur suivante :

 MySQL a répondu:Documentation 
#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' 

Ceci est dû à l'ajout des "collations" sous MySQL (en français interclassement) et en particulier que le jeu de caractère pour le classement de la base de données n'est pas le même que celui ouvert par phpMyAdmin..

Pour remédier il suffit de sélectionner en page d'accueil de phpMyAdmin le même jeu d'interclassement que celui utilisé par la table (généralement latin1_swedish, contenant les caractères accentués)

Lire la suite

My SQL - Réinitialiser le mot de passe root »

Que ce soit lors de la première installation ou après la perte du mot de passe principal de MySQL, il est nécessaire de pouvoir modifier le mot de passe administrateur (root) de MySQL.

Vous avez perdu le mot de passe root de MySQL ?

Pour pouvoir modifier le mot de passe root de MySQL, il faut pouvoir s'y connecter, Or, si vous n'avez pas le mot de passe root actuel, vous vous retrouvez alors dans une situation kafkaïenne. Si vous connaissez le mot de passe actuel de MySQL et que vous souhaitez juste changer le mot de passe root, vous pouvez sauter cette étape !

Heureusement, il est possible de se connecter à MySQL en échappant à la phase d'authentification (ce qui est fortement recommandé sur un serveur en production).

• Pour ce faire il convient d'arrêter le serveur MySQL :

  #/etc/init.d/mysql stop

• Redémarrer MySQL en passant outre l'identification et en désactivant l'écoute du réseau (afin d'éviter d'être piraté à ce moment donné où MySQL est vulnérable) :

  #mysqld --skip-grant-tables --skip-networking &

Réinitialiser le mot de passe

Une fois l'étape précédente réalisée ou si vous souhaitez modifier le mot de passe root (MySQL est installé par défaut sans mot de passe root), connectez-vous à la base de données système (mysql) de MySQL :

# mysql mysql -u root

Puis, saisissez la commande suivante pour mettre à jour le mot de passe root (en remplaçant évidemment nouveaumotdepasse par le votre) :

UPDATE user SET password=PASSWORD('nouveaumotdepasse') WHERE user="root";

• Si vous n'êtes pas passé par la première étape, il suffit de prendre en compte les changement en tapant la commande suivante :

  FLUSH PRIVILEGES;

• Si par contre vous avez démarré MYSQL à la main en mode dégradé, il suffit de redémarrer le serveur :

  #/etc/init.d/mysql restart

ATTENTION : la mise à jour de votre mot de passe peut entrainer des problèmes de connexion entre phpmyadmin & mysql. Ce document intitulé « My SQL - Réinitialiser le mot de passe root » issu de CommentCaMarche.net (CCM) (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.

Si vous avez deux tables possédant une clé commune (dans le cas ci-dessous il s'agit de la clé primaire "id"), il est possible de mettre à jour un champ d'une des deux tables (update_table) grâce aux données de la seconde (reference_table) à l'aide d'une jointure.

Le code SQL ressemble à celui-ci :

UPDATE update_table, reference_table SET update_table.field=reference_table.fieldref WHERE update_table.id=reference_table.id

Lire la suite

My SQL - Duplicate entry '127' for key 1 »

Vous tentez de faire une insertion dans une table de votre base de données MySQL et vous obtenez l'erreur MySQL suivante:

Duplicate entry '127' for key 1

Ceci provient très probablement du fait que la clé primaire de votre table est de type TINYINT
Or la taille allouée à un TINYINT n'est pas plus grande qu'un octet, et ce, quelque soit le nombre que vous mettez entre parenthèses pour le type TINYINT. Donc les nombres supportés par les entiers de type TINYINT ne peuvent pas dépasser 127 ou 255, selon que ce TINYINT soit signé ou non-signé.
L'auto incrémentation de MySQL ne pouvant pas aller au delà de 127 (ou 255) pour ces petits entiers, votre base répond donc avec cette erreur.

Si vous devez choisir un type numérique pour une clé primaire, choisissez de préférence le type INT qui est capable d'accueillir des entiers dont la capacité maximale est de l'ordre de 2 à 4 milliards.

Suppléments et sources

• Voir les capacités des différents types entiers de mysql (en)
• Discussion sur le forum avec un membre ayant rencontré ce problème.

Lire la suite

MySQL - SELECT ... YESTERDAY (hier) »

Il vous suffit d'utiliser la syntaxe suivante :

SELECT * FROM maTable WHERE champ_date = DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 1 DAY)

De la même façon pour sélectionner les enregistrements d'avant-hier :

SELECT * FROM maTable WHERE champ_date = DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 2 DAY)

Lire la suite

MySQL en console sur Windows : désactiver le bip d'erreur »

Lorsque vous utilisez MySQL en console sur Windows, chaque erreur provoque un bip.

Pour le désactiver, il vous suffit simplement de rajouter le paramètre -b dans votre chaîne de connexion :

mysql -b -h serveur -u utilisateur -p

Vos voisins vous remercieront...

Lire la suite

mysqld.exe a rencontré une erreur et doit fermer »

Il peut arriver suite à une mise à jour logicielle ou matérielle (ou sans raison apparente ?!) que le service mysqld.exe crashe à chaque lancement. Une solution pour y remédier est :

• Fermer l'application qui éxécute mysqld (par ex : wampserver, ...)
• Démarrer > Exécuter
• Lancer la commande : services.msc
• Cliquer droit sur l'entrée "mysqld" > Propriétés
• Onglet Connexion > Cocher "Autoriser le service à interagir avec le bureau"
• Appliquer les changements

Désormais, vous devriez pouvoir relancer le service.

Lire la suite

My SQL - Installation sous Linux Gentoo »

L'installation de Mysql sous la distribution Gentoo est très facile et se fait en quelques étapes :

• Lancer la commande

emerge dev-db/mysql

Cette commande permet le téléchargement des packages nécessaire à l'installation de mysql et l'installation de ces derniers.

• Lancer la commande

emerge --config =dev-db/mysql-[version]

Cette version permet de lancer la configuration du serveur mysql installé et l'initialisation du mot de passe root.

• Le démarrage ou l'arrêt du serveur peut se faire via la commande :

/etc/init.d/mysql start/stop

Par default l'installation place le serveur mysql au démarrage. Pour l'enlever lancer la commande

rc-update -del mysql

Pour le mettre au niveau du démarrage lancer

rc-update -add mysql    

Lire la suite

My SQL - Sauvegarde et restauration de base de données »

L'article suivant va présenter la manière la plus facile pour sauvegarder et restaurer les bases de données Mysql.

Sauvegarde

Pour sauvegarder une base de données existante il est conseillé de créer un dump.

• Pour dumper toutes les bases de données il faut lancer la commande :

mysqldump --user=****** --password=****** -A > /Chemin/Vers/fichier_dump.SQL

• Pour dumper plusieurs bases de données il faut lancer la commande :

mysqldump --user=****** --password=******  db_1 db_2 db_n> /Chemin/Vers/fichier_dump.SQL

• Pour dumper toutes les tables d'une base de données il faut lancer la commande :

mysqldump --user=****** --password=****** db > /Chemin/Vers/fichier_dump.SQL

• Pour dumper certaines tables d'une base de données il faut lancer la commande :

mysqldump --user=****** --password=****** db --tables tab1 tab2 > /Chemin/Vers/fichier_dump.SQL

Pour chacune des commandes ci-dessous il faut spécifier un utilisateur (user) et le mot de passe (password) ayant les droits administrateur sur la base de données.

Restauration

Pour restaurer un dump il suffit de lancer la commande :

mysql --user=****** --password=****** db_nom < /Chemin/Vers/fichier_dump.SQL

Lire la suite

MySQL - Rediriger la sortie standard en console »

Vous connaissez le client MySQL en ligne de commande, aka "mysql", mais saviez-vous qu'il était possible de rediriger la sortie des commandes vers n'importe quel programme ? Pour cela, il vous suffit d'utiliser la commande "pager", suivie du nom du programme vers lequel rediriger la sortie.

Affecter la sortie à un programme

Exemple (sûrement le plus utilisé) :

mysql>pager more  
PAGER set to 'more'  
mysql>

La prochaine fois que le résultat d'une commande excédera une page, il suffira de presser la barre d'espace pour passer de pages en pages.

Remettre le pager par défaut

Pour revenir au pager par défaut, utilisez la commande "nopager" :

mysql> nopager  
PAGER set to stdout  
mysql>

Exemples d'utilisation

La plupart des exemples sont pour Linux/Unix, désolé !

Afficher la sortie dans un éditeur

Pour pouvoir éditer la sortie avec vi :

mysql>pager vi - 

Extraire les lignes contenant un mot

Pour n'afficher que les tables contenant "pierre" dans leur nom, sans sensibilité à la casse :

mysql>pager grep -i pierre  
mysql>show tables;

Je suis sûr que vous avez pleins d'autres idées d'utilisation ! N'hésitez pas à utiliser les commentaires ci-dessous pour donner d'autres exemples :)

Lire la suite

MySQL - en console : sortie en fiches plutôt qu'en liste »

Le client MySQL en ligne de commandes est décidément puissant ...

Vous avez peut-être déjà été confrontés à la difficulté de lecture de résultats contenant de nombreuses colonnes ...
Exemple :

mysql> SHOW SLAVE STATUS; 
+----------------------------------+---------------+-------------+-------------+---------------+------ 
------------+---------------------+------------------+---------------+-----------------------+--------- 
---------+-------------------+-----------------+---------------------------------------------+---------- 
----------+------------------------------------------------------------------------------------------------ 
------------------------------------------------------------------------------------------------------------ 
------------------------------------------------------------------------------------------------------------ 
-----------------------------------+-------------------------+-----------------------------+------------+ 
------------+--------------+---------------------+-----------------+-----------------+----------------+ 
---------------+--------------------+--------------------+--------------------+-----------------+------- 
------------ +----------------+-----------------------+ 
| Slave_IO_State                   | Master_Host   | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File   | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB                         | Replicate_Do_Table | Replicate_Ignore_Table                                                                                                                                                                                                                                                                                                                                    | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | 
+----------------------------------+---------------+-------------+-------------+---------------+------ 
------------+---------------------+------------------+---------------+-----------------------+--------- 
---------+-------------------+-----------------+---------------------------------------------+---------- 
----------+------------------------------------------------------------------------------------------------ 
------------------------------------------------------------------------------------------------------------ 
------------------------------------------------------------------------------------------------------------ 
-----------------------------------+-------------------------+-----------------------------+------------+ 
------------+--------------+---------------------+-----------------+-----------------+----------------+ 
---------------+--------------------+--------------------+--------------------+-----------------+------- 
------------ +----------------+-----------------------+ 
| Waiting for master to send event | XXX.XXX.XXX.XXX | XXXX        |        3306 |            60 | mysql-bin.001632 |           156629364 | relay-bin.000825 |     156629501 | mysql-bin.001632      | Yes              | Yes               |                 |          |                    |              |                         |                             |          0 |            |            0 |           156629364 |       156629501 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |  
... 

Dans ce cas, il vous suffit de remplacer le ; en fin de commande par \G pour obtenir une sortie en "fiches" :

mysql> SHOW SLAVE STATUS\G 

 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: XXX.XXX.XXX.XXX 
                Master_User: XXXX 
                Master_Port: 3306 
              Connect_Retry: 60 
            Master_Log_File: mysql-bin.001632 
        Read_Master_Log_Pos: 156842311 
             Relay_Log_File: relay-bin.000825 
              Relay_Log_Pos: 156842448 
      Relay_Master_Log_File: mysql-bin.001632 
           Slave_IO_Running: Yes 
          Slave_SQL_Running: Yes 
            Replicate_Do_DB:  
        Replicate_Ignore_DB:  
         Replicate_Do_Table:  
     Replicate_Ignore_Table:  
    Replicate_Wild_Do_Table:  
Replicate_Wild_Ignore_Table:  
                 Last_Errno: 0 
                 Last_Error:  
               Skip_Counter: 0 
        Exec_Master_Log_Pos: 156842311 
            Relay_Log_Space: 156842448 
            Until_Condition: None 
             Until_Log_File:  
              Until_Log_Pos: 0 
         Master_SSL_Allowed: No 
         Master_SSL_CA_File:  
         Master_SSL_CA_Path:  
            Master_SSL_Cert:  
          Master_SSL_Cipher:  
             Master_SSL_Key:  
      Seconds_Behind_Master: 0 
1 row in set (0.00 sec) 

Lire la suite

My SQL - Eviter d'être bloqué en cas d'erreur "TOO MANY CONN..." »

Par défaut, Mysql autorise 100 connexions (150 depuis la version 5.1) au serveur ...
Cela signifie que le 101ème utilisateur recevra le message d'erreur "too many connections" ...

Si cela vous arrive, quelqu'en soit la raison (site web trop chargé, code qui "boucle", ...), comment vous en sortir ?

Il suffit de savoir que Mysql réserve une 101ème connexion aux utilisateurs disposant du privilège "SUPER" ... Du coup, en vous connectant (par exemple) en root, vous devriez pouvoir accéder au serveur pour :

• examiner ce qui se passe (SHOW FULL PROCESSLIST)
• supprimer des connexions (KILL)
• dans le pire des cas arrêter le serveur (SHUTDOWN)

ATTENTION : comme dit plus haut, la 101ème connexion est réservée aux utilisateurs SUPER ... ce qui signifie que SI (honte à vous ! :) ) l'utilisateur configuré dans votre code PHP par exemple a lui-même ce privilège, tout ceci ne fonctionnera pas ...

Raison de plus pour ne pas configurer un site web avec un utilisateur étendu ...

Si néanmoins cela vous arrive, il ne vous reste plus qu'à détruire le process principal (kill -9), il vaudra mieux faire un check tables ensuite ...

Plus d'informations

Lire la suite

MySQL - Savoir qui est connecté et qui fait quoi »

Pour connaître qui est connecté et ce qu'il fait au niveau d'une base MySQL il suffit de lancer la commande :

mysql> show processlist;  

La liste des process et des utilisateur s'affiche. Pour terminer un processus, il suffit de le tuer en utilisant comme paramètre son id avec la commande SQL (XYZ Id du processus):

mysql>kill XYZ 

ou la commande shell :

mysqladmin kill XYZ 

Lire la suite

MySQL - Supprimer des doublons dans une table »

Pour supprimer des doublons au niveau d'une table donnée définie comme suit :

CREATE TABLE IF NOT EXISTS TabTest (    
           cle_prim integer(4) NOT NULL auto_increment, 
           x integer, 
           y integer, 
           z integer, 
           Constraint pk_Tab_test PRIMARY KEY  (cle_prim)                            
); 

Il faut commencer par fixer les champs relatifs au doublons (dans notre cas les champs x et y):

mysql> select * from TabTest; 
+----------+------+------+------+ 
| cle_prim | x    | y    | z    | 
+----------+------+------+------+ 
|        1 |    1 |    2 |    3 | 
|        2 |    1 |    2 |    3 | 
|        3 |    1 |    5 |    4 | 
|        4 |    1 |    6 |    4 | 
+----------+------+------+------+ 
4 rows in set (0.00 sec) 

Pour supprimer les doublons au niveau des champs x et y lancer la commande :

ALTER IGNORE TABLE  TabTest ADD UNIQUE INDEX(x,y); 

Lire la suite

MySQL - Afficher les informations sur une table »

Pour afficher les informations sur une table TabTest il faut lancer la commande suivante (l'option \G améliore le formatage u résultat) :

mysql> show create table TabTest\G 

 1. row *************************** 
       Table: TabTest 
Create Table: CREATE TABLE 'tabtest' ( 
  'cle_prim' int(4) NOT NULL AUTO_INCREMENT, 
  'x' int(11) DEFAULT NULL, 
  'y' int(11) DEFAULT NULL, 
  'z' int(11) DEFAULT NULL, 
  PRIMARY KEY ('cle_prim'), 
  UNIQUE KEY 'x' ('x','y') 
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 
1 row in set (0.00 sec) 

A noter que cette commande ne marche pas si la base de données est démarrée avec les options --safe-show-database ou --skip-show-database.

Lire la suite

My SQL - Linux - Monitorer le port 3306 »

Pour faire le monitoring du port 3306 sous Linux il suffit d'utiliser la commande :

tcpdump  -i eth0 -nN -vvv -xX  -s 1500  port 3306 

Où s représente la longueur du paquet.

Lire la suite

My SQL - Chargement d'un fichier texte dans une table »

Pour charger une fichier texte défini comme suit :

$ tail /home/user1/test.txt 
   'nom1',1,9 
   'nom2',2,3 
   'nom3',3,54 
   'nom4',4,2 
   'nom5',5,9 

Dans une table définie comme suit :

CREATE TABLE chargertest ( 
                cle_prim int(11) NOT NULL auto_increment, 
                nom varchar(20), 
                x integer, 
                y integer, 
                z timestamp(14), 
                Constraint pk_chargertest PRIMARY KEY  (cle_prim) 
); 

A noter que le champ 'z' n'est pas défini au niveau du fichier texte et que le séparateur utilisé est ','.

mysql> load data infile '/home/user1/test.txt' into table chargertest fields terminated by ',' (nom,x,y);

Pour plus d'informations

Lire la suite

My SQL - Optimisation »

L'optimisation au niveau de MySQL passe par trois composants, à savoir :

• Optimisation du serveur MySQL
• Optimisation de la base de données
• Optimisation des requêtes

Optimisation de la configuration de serveur MySQL

L'optimisation du serveur peut inclure une multitude d'approches et de méthodes, ce que nous essayons de présenter dans ce qui suit est une introduction relative aux approches de base, à savoir :

• Compilation du serveur
• Tuning des paramètres du serveur
• Tuning d'autres paramètres

Pour faire une bonne optimisation, il faut procéder par une méthodologie empirique à savoir faire les modifications une par une et tester à chaque fois la réaction du système pour avoir une idée sur le rendu.
Une mesure de performance avant et après avoir effectué l'optimisation permet de voir si le système a été optimisé ou pas.

Compilation du serveur

Il est préconisé d'utiliser la version code source du serveur MySQL et de la compiler en prenant en considération les différents paramètres du système à savoir le jeu de caractère à utiliser, le micro-processeur sur lequel il va tourner et d'utiliser un compilateur adapté (ex : pgcc pour les micro-processeurs Pentium).

Tuning des paramètres du serveur

Il est possible d'optimiser le fonctionnement de MySQL en changeant les valeurs des paramètres du serveur.
A titre de rappel pour afficher les paramètres il faut utiliser la commande :

show variables; 

Pour voir l'effet des paramètres sur le serveur il faut lancer la commande :

show status; 

Il existe de nombreux outils de monitoring permettant de voir les répercutions des changements effectués sur les paramètres sur le serveur MySQL tél que Mytop équivalent à la commande top de linux.
Le fichier my.cnf contient l'ensemble des paramètres à optimiser.
Dans une première étape, il est possible de commencer avec les paramètres gérant la mémoire. Le principe à suivre est que plus le serveur bénéficie de mémoire plus il est rapide, néanmoins, il faut s'assurer que la mémoire est disponible
MySQL contient un ensemble de buffers et cache internes, qu'il est possible de paramétrer l'espace alloué à chacun à partir des variables du fichier my.cnf. Les deux variables les plus importantes sont key_buffer_size et table_cache car elles sont partagées par tous les threads qui tournent sur le serveur et influence de façon considérable les performances.
Un exemple de variables :

• key_buffer_size: mémoire utilisé pour la sauvegardes de indexes MyISAM.
• table_cache: Nombre de tables ouvrables simultanément.
• read_buffer_size: mémoire utilisée pour la sauvegarde des données issues des full scans de tables.
• sort_buffer: mémoire utilisée pour la sauvegarde des données de tables qui seront triées par un ORDER BY.

Tuning d'autres paramètres

Le serveur MySQL est préconisé pour un fonctionnement optimal sur SOLARIS, néanmoins, il est possible de l'optimiser sur les autres OS pour se rapprocher de son rendement idéal.
L'utilisation de RAID-RAID 0 est préconisée pour l'optimisation des opérations de lecture écriture. Ainsi que l'utilisation des disques SCSI plutôt que les IDE.
L'utilisation de réseaux rapides optimise les temps de réponses et optimise la communication entre client/serveur et maître/esclave pour la réplication.

Optimisation de la base de données

Généralement pour l'optimisation des bases de données nous nous tournons vers les bonnes pratiques et les méthodologies de conception de base de données qui permettent de mettre en place des schémas de bases de données performants et normalisés. Néanmoins il faut pour cela :

• Comprendre ce qui est lent au niveau des bases de données
• Faire le bon choix de méthodologie et faire les bons choix conceptuels
• Utiliser les indexes
• Utiliser OPTIMIZE TABLE

Qu'est ce qui ralenti la base de données

Un certain nombre de facteurs sont généralement la cause de la lenteur des bases de données. Nous citons dans ce qui suit les plus fréquents à savoir :

• Pas assez d'indexes : La première cause de lenteur est l'utilisation de tables sans indexes ou sans indexes sur les colonnes concerné par les recherches. Cela ne veux pas dire que toutes les tables doivent avoir des indexes, mais qu'il faut bien étudier les besoins en indexation.
• Utilisation excessive d'indexes : pour optimiser les consultations et recherches les indexes sont la solution, néanmoins, l'augmentation du nombre d'indexes influence les performances au niveau des mises à jour, en effet, lors de la mise à jour d'une table les opérations d'ajout, modification et suppression se répercutent généralement sur les indexes.
• Utilisation privilèges au niveau des tables et des colonnes des tables : à chaque accès MySQL doit vérifier les droits sur les tables et les colonnes des tables ce qui ralenti considérablement les performances.
• Ne pas faire les bons choix au niveau de la conception de la base de données.

Modélisation de la base de données

Utiliser les bonnes pratiques de modélisation et de conception de base de données ainsi que le choix de la méthodologie appropriée permet de mettre en place des bases de données performantes.
Un certain nombre de considérations sont à prendre tel que :

• Choix approprié des types des champs : toujours essayer de choisir les variables les plus adaptées au besoin (exemple pour le stockage d'un nombre ne dépassant pas les 10 chiffres, il est plus judicieux d'utiliser un type TINYINT). Utiliser les champs les plus petits possible permet de charger en mémoire plus de colonnes.
• Utilisation champs à longueur fixe : l'utilisation de longueurs prédéterminée permet d'optimiser l'accès aux colonnes car leurs positions sont prédéfinies. Ceci implique diminuer l'utilisation de VARCHAR, de TEXT et de BLOB (pour TEXT et BLOB, il est préconisé de casser la normalisation du schéma de la base de données et de sauvegarder ces champs dans des tables tierces).
• Augmenter l'utilisation des contraintes NOT NULL quand ce la est possible pour optimiser l'espace de stockage.
• Choisir le bon type pour les tables : MySQL permet d'avoir au niveau d'un même schéma des tables de types différents.
• Faire un bon indexage des tables.

Utiliser les indexes

Un indexe est une table de recherche qui nous permet de trouver rapidement des lignes dans une table. L'indexe permet de détermine la position de l'enregistrement recherché au niveau d'une table.
Si une table n'a pas d'indexe, lors de la recherche tous les enregistrements seront parcourus.
Les indexes au niveau de MySQL sont stockés sous forme de b-trees (arbres binaires), qui représente une structure de données facile et rapide à parcourir.
L'indexe peut inclure une simple ou plusieurs colonnes, l'index sera appelé lors d'une recherche portant sur les colonnes indexées.
Au niveau de MySQL, l'indexation est automatique au niveau des tables ayant des champs avec les contraintes PRIMARY, KEY, UNIQUE.
L'idée de base à retenir est que si une recherche est fréquente et qu'elle incluse une ou plusieurs colonnes, il faut créer l'indexe correspondant pour optimiser le temps de réponse via la commande CREATE INDEX.

Utilisation de la commande OPTIMIZE TABLE

Equivalente à la défragmentation de disque, la commande OPTIMIZE TABLE permet de fragmenter les tables.

Optimisation des requêtes

MySQL permet d'analyser les requêtes et de connaître le temps et le plan d'exécution. Ces information permettent comprendre ce qui rends les requêtes lentes et d'en optimiser l'exécution.

Détecter les requêtes lentes

Pour détecter les requêtes lentes il est possible :

• soit d'observer des lenteurs lors des exécutions et des temps de réponse anormaux
• soit de faire des benchmark : tester les applications pour voir quelles composantes sont les plus lentes.
• soit vérifier le Slow query log : il est possible d'activer cette option au niveau de MySQL paramétrant la variable --log-slow-queries

Une fois que les requêtes lentes détectées, lancer la commande EXPLAIN permet de comprendre l'exécution et donc connaître ou intervenir pour optimiser.

Lire la suite

My SQL - éviter les doublons dans un SELECT »

Problème

Comment éviter les doublons dans les résultats d'une requête SQL ?

Solution

Il suffit d'utiliser la clause DISTINCT entre SELECT et les champs.

Exemple :

SELECT DISTINCT id,nom,prenom FROM matable

Plus d'informations

• http://dev.mysql.com/doc/refman/5.0/en/select.html

Remerciements

Merci à Bahan sur le forum pour cette astuce. Ce document intitulé « MySQL - éviter les doublons dans un SELECT » issu de Comment Ça Marche Informatique (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.

Pour de multiples raisons, je préfère installer Mysql depuis les binaires compilés par Mysql ,plutôt que d'utiliser les packages (rpm, apt, ...) :

• les packages installent dans les répertoires de leur choix, il est difficile de maîtriser cela
• on est tributaire de la fréquence de mise à jour desdits packages : impossible de tester une nouvelle version simplement
• parfois, on veut pouvoir installer de multiples versions et basculer (switcher) facilement de l'une à l'autre, syndrôme "une mise à jour est disponible, mais si je l'installe comment pourrai-je retourner en arrière en cas de problème ?"

Les alternatives sont :

• compiler soi-même le code source, parfois compliqué, mais surtout à moins d'être un pro de la compilation, on optimisera difficilement mieux que les ingénieurs de Mysql ...
• utiliser les binaires gentiment compilés par Mysql ,objet de l'astuce qui suit.

Vous allez voir, l'ensemble de l'opération prend moins de 15 mn, et vous procure une installation fraîche de Mysql, et surtout sur laquelle vous avez la main de A à Z !

Remarque : les étapes suivantes permettent d'effectuer une installation propre, si vous souhaitez faire une migration, il vous suffit de sauter les étapes de configuration, mise en place des fichiers de données, ...

On suppose que :

• l'installation se fera dans /usr/local
• vous avez les droits suffisants sur le serveur (évidemment !)
• l'utilisateur Unix sera "Mysql" (compte existant)

1. Récupérer les binaires de mysql

ici on suppose un serveur Linux Intel 64bits

> cd /usr/local/  
> wget http://mirrors.ircam.fr/pub/mysql/Downloads/MySQL-5.1/mysql-5.1.44-linux-x86_64-glibc23.tar.gz 

2. Décompression

> tar zxvf /usr/local/mysql-5.1.44-linux-x86_64-glibc23.tar.gz  

Cela crée le répertoire /usr/local/mysql-5.1.44-linux-x86_64-glibc23

3. Création du lien symbolique mysql

Pour créer un lien symbolique mysql, il suffira de refaire cette étape pour changer de version lors d'une mise à jour (upgrade) par exemple.

> ln -s mysql-5.1.44-linux-x86_64-glibc23/ mysql  

4. Attribution des droits

> cd /usr/local/mysql  
> chown -R mysql .  
> chgrp -R mysql .  

5. Mise en place du fichier de configuration

Mettre en place le fichier de configuration dans /etc/my.cnf : de nombreux exemples sont fournis avec les binaires, il suffit d'en choisir un. Ici, on prendra le "mysql-huge" qui est adapté aux machines avec 4Go de RAM

> cp support-files/my-huge.cnf /etc/my.cnf  

6. Editer le fichier /etc/my.cnf

Votre configuration : éditer le fichier /etc/my.cnf, et décider au minimum le répertoire qui contiendra vos données (datadir) : par défaut, celui-ci sera présupposé dans /usr/local/mysql/data/ : par exemple,

[mysqld]  
user            = mysql  
basedir         = /usr/local/mysql  
datadir         = /mon/repertoire/mysql  

7. Mettre en place le script init.d

Le script init.d permettra l'arrêt/relance de Mysql (start, stop, restart, ...)

> cp support-files/mysql.server /etc/init.d/mysql  

Puis par mesure de précaution éditer ce fichier est mettre le bon datadir (datadir=...)

8. Installer les données minimales

> ./scripts/mysql_install_db --datadir=(le meme que ci-dessus) --user=mysql  

Cela créera le répertoire, installera les tables système, ...

9. Démarrer mysql

> /etc/init.d/mysql start  

10. Mettre un mot de passe root

Best practice : immédiatement attribuer au user root un mot de passe !! (celui-ci est vide par defaut)

> ./bin/mysqladmin -u root password "ce-que-vous-voulez"  

Et voila ... sachez que le fichier INSTALL-BINARY vous explique (mais en anglais) tout cela, avec des variations, mais l'objectif est le même ...

11. Définir le PATH

les outils (mysql client, mysqldump, ...) sont situés dans le répertoire /usr/local/mysql/bin : pour pouvoir les appeler depuis n'importe où, vous pouvez au choix

• ajouter ce répertoire à votre PATH (fichier ~/.bashrc)
• créer des liens symboliques dans le répertoire /usr/bin/

Enfin, si vous effectuez une migration de Mysql 5.0 vers 5.1, le script "bin/mysql_upgrade -p" vous permettra de mettre à jour les tables systèmes (INFORMATION_SCHEMA, ...)

Bon courage

Lire la suite

MySQL - Les moteurs de stockage »

La base de données MySQL présente une particularité par rapport aux bases de données présentent dans le marché. Cette spécificité réside dans la possibilité d'associer à chaque table un moteur de stockage assurant des fonctionnalités telle que les transactions, l'indexation, les algorithmes.

Par défaut, le moteur de recherche disponible est MyISAM. Ce moteur ne risque pas d'être supprimer par le DBA. Par contre, le DBA pourra ajouter un moteur de stockage à l'aide de commande « Install plugin StockageName ». la commande « Uninstall Plugin » permet de supprimer le même moteur.

MyISAM est basé sur ISAM mais il ne supporte pas les transactions. La commande suivante permet de spécifier exlicitement la création d'une table MyIsam. Par défaut en mySQL, le type par défaut des tables est MyIsam

CREATE TABLE t (i INT) ENGINE = MYISAM;

L'InnoDB est l'un des principaux moteurs de stockage et est le moteur le plus utilisé. Il est le leader des moteurs de stockages transactionnel. InnoDB intègre plusieurs fonctionnalités comme la réplication, les procédures stockées, les triggers et les vues.
La commande suivante permet de créer une table au format InnoDB :

CREATE TABLE TableName (Field1 Int, Field2 Int) TYPE = InnoDB;

Si vous avez besoin de stocker temporairement des données, le moteur Memory peut être utilisé. Les données d'une table Memory sont stockées dans la mémoire. En cas de clash ou redémarrage du serveur, les données seront perdues.
La commande suivante permet de créer une table au format Memory :

CREATE TABLE TableName (Field1 Int, Field2 Int) ENGINE = MEMORY;

Dans le cas où votre besoin est de gérer plusieurs tables historiques identiques ou bien de répartir une grande table, en lecture seul dans différents endroits du disque. L'utilisation d'une table Merge est fortement conseillée.


Si vous avez besoin d'optimiser le stockage des informations, il faudra utiliser le moteur Archive. Attention, avec ce moteur, il est impossible de mettre à jour la table en ligne. Si vous êtes intéressé par ce moteur, lors de la configuration de la compilation de MySQL, l'option suivante permet d'activer le moteur

--with-archive-storage-engine