Linux:Administration MySQL

De Djjnet.

Sommaire

 [masquer] 

Informations sur MySQL

Généralités

  • Cheminement du traitement d'une requête :
    • L'analyseur transforme la requête SQL dans un format interne ("plus binaire").
    • L'optimiseur calcule la meilleure manière d'exécuter la requête. 
Thread de connexion -> Cache de requêtes -> Analyseur -> Optimiseur -> Moteur de stockage
  • Gestion de la mémoire : 
Client1 -> Mémoire locale-|
Client2 -> Mémoire locale-|-> Mémoire globale - > Moteur de stockage
Client3 -> Mémoire locale-|

La mémoire locale correspond aux tampons de traitement des requêtes (read_buffer, sort_buffer, tmp_table). Cette mémoire est valable par session.

La mémoire globale correspond aux tampons disponibles à tous les threads comme les caches de requêtes, d'indexs ou de tables

Les moteurs de stockages

Pour le stockage des données, MySQL utilise un système de moteurs. Cela permet d'avoir des caractéristiques différentes en fonction des besoins. Cette partie présente rapidement les particularités de certains moteurs.

Les moteurs les plus utilisés sont MyISAM et InnoDB.

Les moteurs de stockages utilisés peuvent être différents pour chaque table d'une base.

Pour voir la liste des moteurs disponibles : 

SHOW ENGINES;

MyISAM

Il s'agit du moteur par défaut. Ses particularités sont :

  • rapidité en lecture
  • recherche fulltexte
  • verrouillage au niveau des tables (pas de lectures sans verrouillage)

MyISAM utilise un fichier de données (.MYD) et un fichier d'index (.MYI) par table.

Les lignes de données peuvent être stockées en trois formats :

  • Statique (par défaut sauf pour les types VARCHAR, BLOB et TEXT) : rapide et moins sensible aux crashs
  • Dynamique : prend moins de passe sur le disque mais moins rapide et plus sensible aux crashs étant donné les entêtes nécessaires
  • Compressé : la table utilise très peu d'espace mais est alors en lecture seule. Les performances en lectures sont peu impactées.

InnoDB

Ses particularités sont :

  • moteur transactionel conforme à l'ACID (Atomique, Cohérent, Isolé, Durable)
  • impossibilité de sauvegarder physiquement une seule base distincte
  • gestion des clés étrangères (FOREIN KEY) et l'intégrité référentielle
  • les données et les indexs sont stockés dans des fichiers d'espace de table (tablespace)
  • verrouillage au niveau des lignes
  • une table a toujours une clef primaire, si celle-ci n'est pas précisée, elle est créée automatiquement.

La clé primaire définie où se situe l'enregistrement. Il faut qu'elle soient la plus petite possible car elle est utilisée pour référencer l'enregistrement.

Autres

  • MEMORY : permet de stocker en mémoire les données pour des besoins de rapidité. Non transactionnel. Ne gère pas le type BLOB ou TEXT.

Il est possible d'utiliser --init-file pour les construire au démarrage. max_heap_table_size définie la taille maximal d'une table en mémoire.

  • FEDERATED (depuis 5.0) : permet d'accéder à des tables de serveurs MySQL distants

Administration de MySQL

Connexion au serveur

Dans cette documentation, lorsque nous utilisons [x] : x est une option.

  • Se connecter au serveur MySQL : 
mysql -h host -u user -p
  • Se déconnecter : 
quit

Attention dans le cas d'une connexion locale, par exemple avec 

mysql -u root -p

Dans ce cas, mysql utilise le premier socket unix qu'il trouve, donc même si vous précisez que vous souhaitez utiliser le port 3307, il faut mettre le nom d'host ou alors vous serez connecté au premier serveur mysql de port 3306 : 

mysql -u root -p -h localhost -P 3307

Gérer une base de données

  • Voir les bases de données disponibles : 
SHOW DATABASES;
  • Choisir une base de données : 
USE nom_bdd (sans ; )
  • Créer une base de données : 
CREATE DATABASE nom_bdd;

Si le nom de votre base a des - utiliser : 

CREATE DATABASE `nom-bdd`;
  • Supprimer une base et ses tables associées : 
DROP DATABASE nom_bdd;

Gérer les droits d'accès à une base de données

  • En root pour donner les droits à un utilisateur à une base de donnée : 
GRANT ALL ON nom_base.* TO 'utilisateur'@'%' IDENTIFIED BY 'mon_pass';

Dans utilisateur@'%', '%' est utilisé pour l'accès depuis n'importe quelle machine mais pas locale. Remplacer '%' par '192.168.1.12' limitera l'accès à la machine d'adresse 192.168.1.12

Le mot de passe est indiqué en clair mais sera crypté, ne pas utiliser la fonction PASSWORD()

Ces droits sont uniquement pour toutes les tables d'une base de donnée précisée par "nom_base".

Pour autoriser la connexion locale : 

GRANT ALL ON nom_base.* TO 'utilisateur'@'localhost' IDENTIFIED BY 'mon_pass';

Pour restreindre l'accès à une table : 

GRANT ALL ON nom_base.nom_table TO 'utilisateur'@'localhost' IDENTIFIED BY 'mon_pass';

Pour limiter le type d'accès, par exemple, uniquement des droits SELECT à une table : 

GRANT SELECT ON nom_base.nom_table TO 'utilisateur'@'localhost' IDENTIFIED BY 'mon_pass';

Ne pas donner tous les droits à toutes les bases de données à un utilisateur autre que le root.

Pour supprimer les droits à un utilisateur : 

REVOKE ALL ON nom_base.* FROM 'utilisateur'@'localhost'
REVOKE UPDATE ON nom_base.nom_table FROM 'utilisateur'@'%'

Changer le mot de passe d'un utilisateur 

SET PASSWORD FOR 'utilisateur'@'localhost' = PASSWORD('mon_nouveau_pass');

ou 

GRANT USAGE ON *.* TO 'utilisateur'@'localhost'  IDENTIFIED BY 'mon_nouveau_pass';

Pour permettre l'authentification depuis d'ancien client mysql : 

SET PASSWORD FOR 'utilisateur'@'%' = OLD_PASSWORD('pass');

Gérer les tables

  • Voir les tables disponibles pour une base sélectionnée : 
SHOW TABLES;
  • Créer une table : 
CREATE TABLE nom_table (NomCol1 TYPE [NOT] NULL [AUTO_INCREMENT] [PRIMARY KEY],...);
  • Créer une table avec pour colonnes NomCol1, NomCol2... de TYPE : 
CHAR(x) colonne de caractères, x = nb max de caractères
INTEGER entier positif, négatif ou nul
DECIMAL(x,y) décimaux, x=nb de chiffres, y=après la virgule
DATE date spécifique au SGBD
LOGICAL TRUE ou FALSE
NOT NULL Ne peut pas avoir de valeur nulle
  • Précisons en plus le moteur de stockage à utiliser : 
CREATE TABLE nom_table (nomCol int) ENGINE = InnoDB;

Si rien n'est précisé, le moteur est MyISAM.

  • Modifier le moteur de stockage courant d'une table : 
ALTER TABLE nom_table ENGINE = InnoDB;

Attention, cette action peut être longues selon la taille des tables car la table est recopiée dans une nouvelle.

  • Passer le format MyISAM en compressé : 
LOCK TABLES nom_table WRITE;
FLUSH TABLES;

Puis dans le dossier, en ligne de commande : 

myisampack nom_table
myisamchk -rq nom_table

Exécution de requêtes SQL

  • Lancer des requêtes à partir d'un fichier : 
mysql -h host -u user -p < fichier
  • Lancer une requêtes en ligne de commande : 
mysql -u root -p -e "SHOW FULL PROCESSLIST"

autre méthode : 

echo "SHOW FULL PROCESSLIST" | mysql -u root -p

Gestion des processus

  • Lister les processus : 
SHOW FULL PROCESSLIST;

+--------+----------+---------------------+----------+-------------+-------+---------------+-----------------------+
| Id     | User     | Host                | db       | Command     | Time  | State         | Info                  |
+--------+----------+---------------------+----------+-------------+-------+---------------+-----------------------+
| 129937 | root     | localhost           | NULL     | Query       |     0 | NULL          | SHOW FULL PROCESSLIST |

La colonne State peut permettre de voir si une requête est toujours en exécution et depuis combien de temps avec la colonne Time.

  • Arrêter un processus : 
KILL 129937;

129937 est obtenu de la colonne Id.

  • Tuer pleins de processus :

Par exemple, dans le cas d'un grand nombre de requête abaissant énormément les performances d'un serveur, il peut être nécessaire de toutes les arrêter.

Exemple d'information obtenue avec SHOW FULL PROCESSLIST : 

5323191	my-bluenity	airfrance5.lan:50616	my-bluenity	Query	3819	Copying to tmp table	SELECT id_bp, titre_bp, date_bp, COUNT(id_bp_consult) AS nb_consult FROM bon_plan LEFT JOIN bon_plan_consultation ON id_bp_consult=id_bp WHERE etat_bp='1' and date_bp < '2008-12-21-17-54-41' GROUP BY id_bp ORDER BY nb_consult ASC ,date_bp DESC

Elle est en fonctionnement depuis longtemps : 3819 secondes et utilise des tables temporaires "Copying to tmp table". C'est la raison de la baisse de performance.

Récupérons la liste de Id réalisant un "Copy" pour construire les kill : 

mysql -p -e "SHOW FULL PROCESSLIST" |grep "Copy" |awk '{print $1}' |sed -e 's/^/kill /'|sed -e 's/$/;/' >/tmp/tokill

Exécutons les kill : 

mysql -p < /tmp/tokill

MySQL et la connexion distante

Par défaut après installation de MySQL en package, celui-ci n'écoute qu'en localhost (127.0.0.1)

  • MySQL écoutera sur toutes les interfaces en commentant le paramètre bind-adress 
#bind-address           = 127.0.0.1
  • Anciennement, il était utilisé le paramètre --skip-networking dans le script de lancement de mysqld ou le même paramètre dans le fichier my.cnf

Fichiers de journalisation

Par défaut, seul les erreurs sont mises dans les fichiers de logs. Il est nécessaire d'activer les autres logs par le fichier my.cnf ou en tant que paramètre au daemon mysqld. Préférons mettre ces options dans le fichier my.cnf afin de les retrouver plus facilement.

Depuis la version 5.1 de MySQL, il est possible de mettre les logs en base de données avec l'option log_output = TABLE,FILE

Logs des requêtes

L'option suivante permet de mettre toutes les requêtes dans le fichier de log indiqué. Attention, cela peut demander beaucoup de ressources en cas de forte utilisation du serveur. Il est bon de ne l'activer uniquement pour débug et de façon ponctuelle. 

log = /var/log/mysql/mysql.log

La rotation est à réaliser avec logrotate (sous Linux).

Logs des requêtes lentes et/ou sans indexs

Il est possible de mettre dans un fichier de log les requêtes longues dépassant un nombre de secondes donné. Il est nécessaire d’activer le paramètre log_slow_queries et de modifier la valeur de long_query_time à 3 pour 3 secondes. Cette action peut-être réalisée pendant quelques jours ou une semaine pendant une phase d’optimisation avec les développeurs. 

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 3
log-queries-not-using-indexes

Attention log-queries-not-using-indexes n'est pas disponible dans les versions de MySQL inférieures à 5.0

Logs binaires

Présentation

Les logs binaires contiennent toutes les requêtes réalisées sur le serveur qui provoquent une modification au niveau des données (Il n'y a donc pas les SELECT).

Ceux-ci sont stockés dans un format binaire afin de rendre plus efficace l'écriture de ceux-ci.

Ces logs peuvent servir à deux choses :

  • pour la mise en place de la réplication
  • pour la sauvegarde incrémentielle

Il est nécessaire d'utiliser la commande mysqlbinlog pour les relire.

Formats

Depuis la version 5.1 il est possible de spécifier le format des logs binaires :

  • STATEMENT : journalisation des requêtes, les requêtes du type RAND() ne sont alors pas journalisables.
  • ROW : journalisation de la modification des lignes, tout est journalisable mais les fichiers de logs sont plus importants
  • MIXED : mixe de ROW et STATEMENT

Il est possible de modifier le type avec --binlog-format= en argument du daemon.

Attention, la version par défaut n'est pas la même selon les versions : STATEMENT à partir de 5.1.29. A vérifier à partir de la page http://dev.mysql.com/doc/refman/5.1/en/binary-log-formats.html

Gestion
  • Pour activer les logs binaire, il faut activer la variable log_bin : 
log_bin = /var/log/mysql/mysql-bin.log

Il est nécessaire de redémarrer mysql pour le prendre en compte.

  • La variable expire_logs_days indique un nombre de jours maximal pour les logs binaire afin de réaliser une purge automatique lors de la rotation de ceux-ci.
  • Les logs sont archivés par une rotation lorsque
    • la commande FLUSH LOGS est lancée
    • au redémarrage de MySQL
    • lorsque max_bin_log_size est atteint (généralement 100Mo)

Commandes :

  • SHOW MASTER LOGS : permet de voir les logs actuellement actifs et leur taille, cette commande est équivalente à SHOW BINARY LOGS
  • RESET MASTER : supprime tous les logs
  • SHOW BINLOG EVENTS : montre les commandes présentes dans les logs binaires

Voir aussi la partie sauvegarde/restauration et la partie réplication pour d'autres informations sur ces logs.

Suppression des logs binaires

Pour les vieilles versions de MySQL (4 et 4.1), sinon préférez l'utilisation des variables expire_logs_days et max_binlog_size.

  • Sous Debian
    • Dans le dossier /etc/cron.daily/, créer mysql-server (avec exécution :-): 
#!/bin/bash
#
# This script only rotates the binary logs. The normal logs are rotated
# via /etc/logrotate.d/mysql-server.
#
# The number of binary logs that should be kept can be configured in
#       /etc/mysql/debian-log-rotate.conf
#
set -e
set -u
###########################################################################
M="mysql --defaults-file=/etc/mysql/debian.cnf"
MA="mysqladmin --defaults-file=/etc/mysql/debian.cnf"
tmp=`tempfile`;
my_exit () {
 rm $tmp
 exit $1
}
test -x /usr/bin/mysqladmin || my_exit 0
# Read config and see if we should rotate at all.
. /etc/mysql/debian-log-rotate.conf
if [ "$KEEP_BINARY_LOGS" -eq 0 ]; then
 my_exit 0
fi
# Test if the server is up and running.
if ! $MA --silent ping >/dev/null; then
 my_exit 0
fi
# Retrieving list of file names. Can fail if no binary logs are in use.
if ! echo 'SHOW MASTER LOGS;' | $M --skip-column-names 2>&1 | cut -f1 >$tmp; then
 if grep -q 'You are not using binary logging' $tmp; then
   my_exit 0
 else
   echo "Unknown problem retrieving MySQL master log filenames in $0."
   cat $tmp
   my_exit 1
 fi
fi
# Test if we have enough log files to rotate and do so if.
if [ `wc -l < $tmp` -gt $KEEP_BINARY_LOGS ]; then
 filename=`tail -n $KEEP_BINARY_LOGS $tmp | head -n 1`
 echo "PURGE MASTER LOGS TO '$filename';" | $M
fi
my_exit 0
    • Dans le dossier /etc/mysql, créer debian-log-rotate.conf pour indiquer combien de fichiers logs à garder : 
# This is the config file for
#       /etc/logrotate.d/mysql-server (for normal logs)
#       /etc/cron.daily/mysql-server-4.1 (for binary logs)
# It should be kept in shell script syntax and contain only variables.
#
# Both log file types are rotated daily, whenever "FLUSH LOGS;" is
# issued and when the server (re)starts so do not choose too low numbers.
# Configuring /etc/logrotate.d/mysql-server does not yet work.
# The number of binary log files that are kept. They are rotated daily
# and on "FLUSH LOGS;". A value of 0 disables rotating and is set as default
# for backward compatibility.
KEEP_BINARY_LOGS=10

Sauvegardes et restauration de données

Sauvegarde physique

Elle consiste généralement à la sauvegarde des fichiers. Ce type de sauvegarde dépend du moteur de stockage utilisé. Elle est plus rapide et prend moins d'espace disque mais n'est pas toujours réalisable (modification pendant la lecture par exemple).

  • MyISAM

Il est possible d'utiliser le script mysqlhotcopy. Attention, celui-ci ne sauvegarde pas les déclencheurs.

Pour une copie des fichiers, il est soit nécessaire de stopper MySQL soit de verrouiller la ou les tables. Exemple : Sous Mysql : 

LOCK TABLE nom_table READ;
FLUSH TABLES;

Sous bash : 

cp *.frm backup/
cp *.TRG backup/
cp *.TRN backup/
cp *.MYI backup/
cp *.MYD backup/

Sous MySQL : 

UNLOCK TABLES;
  • InnoDB

Il faut arrêter le serveur MySQL puis copier les fichiers de configuration, tablespaces, journaux puis relancer MySQL.

Sinon il faut utiliser l'outil payant InnoDB Hot Backup.

Sauvegarde logique d'une base de données par dump SQL

Commande de base : 

mysqldump -u user -p nom_bdd > nom_bdd.sql

Options utiles : 

mysqldump -u user -p --master-data=2 --triggers --routines nom_bdd > nom_bdd.sql
  • --master-data=2 permet de savoir où reprendre dans les logs binaire pour compléter une restauration avec ceux-ci.
  • --routines et --triggers uniquement pour MySQL > 5.0 pour sauvegarder les fonctions/procédures stockées et les déclencheurs

Pour obtenir des sauvegardes cohérentes (pas de mise à jour entre la sauvegarde de chaque table), il faut ajouter :

  • --single-transaction : uniquement si InnoDB est le seul moteur de stockage utilisé
  • --lock-all-tables : pour MyISAM, lock toutes les tables de toutes les bases donc il n'y a plus de mise à jour pendant la sauvegarde de toute la base, ce qui peut être long selon les bases.
  • --order-by-primary : option utile pour faire un import d'une base MyISAM dans une base en InnoDB, cela permet de gagner du temps pour la création des indexs.

Restauration d'une sauvegarde logique

mysql -h host -u user -p < sauvegarde.sql

Il peut être nécessaire (même préférable) de préciser la base de données associée à la restauration : 

mysql -h host -u user -p nombase < sauvegarde.sql

Sauvegarde et restauration incrémentielle

Restauration depuis les logs binaires

Pour restaurer depuis les logs binaires, il faut utiliser l'utilitaire mysqlbinlog qui va lire ceux-ci et nous fournir des commandes SQL sur la sortie standard.

Les commandes sont du type : 

mysqlbinlog --database=world -D --stop-position=863 /var/log/mysql/mysql-bin.000002 |mysql world

Ainsi on restaure les requêtes SQL du fichier binaire mysql-bin.000002 jusqu'à la position 863 de la base world. Les requêtes sont envoyée à MySQL par le PIPE.

Il est possible de traiter plusieurs fichiers de logs binaire en indiquant le premier et l'option --to-last-log : 

mysqlbinlog --database=world -D --start-position=942 --stop-position=1459 --to-last-log logbin/mysql-bin.000002 |mysql world

Il ne faut pas restaurer à l'aveugle les logs binaires. Il convient d'abord d'envoyer les logs dans un fichier texte : 

mysqlbinlog --database=world -D --stop-position=863 /var/log/mysql/mysql-bin.000002 > /tmp/log.sql

Et de regarder celui-ci afin de voir où commencer et arrêter la restauration. En effet, s'il est nécessaire de restaurer jusqu'au moment où il y a eu un DROP TABLE par exemple, il est nécessaire de s'arrêter juste avant avec l'option --stop-position

Aussi, il faut bien mettre l'option -D qui permettent de désactiver les logs binaires pendant la restauration sinon les instructions seront en double dans les logs binaires.

Attention, lors de la restauration de logs binaire suite à une restauration d'un dump SQL complet (sauvegarde logique). La restauration du dump SQL va se retrouver dans les logs binaires, donc il faut faire attention à ne pas rejouer celle-ci lors de l'utilisation des logs binaires.

Il est possible de désactiver les logs binaires temporairement en mettant la variable SQL_LOG_BIN à 0.

Sauvegarde de toutes les bases automatiquement dans un script 

#!/bin/bash
#Besoin de la commande bc
root_password=`cat /usr/local/scripts/param/root.password`
save_dir=/var/backup/mysql
madate=`date +%Y%m%d`
mkdir -p $save_dir
mysql_version=$(/usr/bin/mysql --defaults-extra-file=/etc/mysql/debian.cnf -Bse "SELECT substring_index(version(),'.',2);")
if [ $(echo "${mysql_version} >= 5.0"|bc) -eq 1 ]; then
       dump_options="--master-data=2 --triggers --routines";
else
       dump_options="--master-data=2";
fi
/usr/bin/find $save_dir -type f -mtime +7 -exec rm -f {} ';' > /dev/null
for bdd in `/usr/bin/mysql -s --user=root --password=$root_password --execute="SHOW DATABASES;"`
do
               /usr/bin/mysqldump --user=root --password=$root_password --add-drop-table ${dump_options} $bdd  > $save_dir/${bdd}-${madate}.sql
done
cd $save_dir
gzip *.sql
  • Version Debian 
#!/bin/bash
#Besoin de la commande bc
save_dir=/var/backup/mysql
madate=`date +%Y%m%d`
mkdir -p $save_dir
mysql_version=$(/usr/bin/mysql --defaults-extra-file=/etc/mysql/debian.cnf -Bse "SELECT substring_index(version(),'.',2);")
if [ $(echo "${mysql_version} >= 5.0"|bc) -eq 1 ]; then
       dump_options="--master-data=2 --triggers --routines";
else
       dump_options="--master-data=2";
fi
/usr/bin/find $save_dir -type f -mtime +7 -exec rm -f {} ';' > /dev/null
for bdd in `/usr/bin/mysql --defaults-extra-file=/etc/mysql/debian.cnf -s --execute="SHOW DATABASES;"`
do
              /usr/bin/mysqldump --defaults-extra-file=/etc/mysql/debian.cnf --add-drop-table ${dump_options} $bdd  >  $save_dir/${bdd}-${madate}.sql
done
cd $save_dir
gzip *.sql

Optimisation de la configuration MySQL

Présentation

Plusieurs variables de configuration sont disponibles afin d'optimiser MySQL.

Les variables sont consultables avec la commande : 

SHOW VARIABLES;

Vous pouvez rechercher des variables précises ainsi : 

SHOW VARIABLES LIKE 'key_%';

La plupart des variables sont modifiables en ligne sans redémarrer (cf colonne "Dynamic" du tableau : http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html )

Modifier une valeur : 

SET GLOBAL query_cache_size = 64 * 1024 * 1024;

Ne pas oublier de modifier ensuite le fichier de configuration en conséquence (généralement /etc/mysql/my.cnf) pour la prise en compte après redémarrage de MySQL.

MySQL met à disposition plusieurs variables d'état afin de voir l'activité de celui-ci et d'établir une meilleur configuration : 

SHOW STATUS;

Il faut bien avoir en tête que chaque configuration est propre à chaque installation et donc fonction de l'utilisation réalisée sur celle-ci. Il est nécessaire de réaliser certaines actions afin d'obtenir la meilleur configuration possible.

Optimisation

Cette partie va expliquer comment modifier les principales variables MySQL permettant d'améliorer les performances.

Il est nécessaire d'adapter en fonction de plusieurs choses :

  • le serveur MySQL est sur un serveur dédié à cette fonction ou non ?
  • quelle quantité de RAM je dispose

Il faut aussi prendre en compte le type de variables disponibles, les globales, celles de sessions et celles spécifiques à un moteur de stockage.

Globales
max_connections

Elle indique le maximum de connexions simultanées autorisées.

La variable d'état Max_used_connections permet d'ajuster max_connections. Si cette valeur doit être positionnée à une valeur très haute, bien demander de vérifier au niveau du développement si les connexions sont bien fermées en fin de traitement.

thread_cache

Le cache de thread est utile lorsque beaucoup de connexions sont créées par secondes. Il faut regarder le rapport des variables d'état Threads_created/uptime dans le temps. Le graphe munin sur les threads représente le nombre de connexions (donc threads) actives au moment de la mesure (variable d'état Threads_connected). Plus il y a d'oscillations avec de fortes valeurs, plus il faudra de cache.

table_cache

Il s'agit du cache de définitions des tables. Plus il y a de tables, plus cette valeur doit être augmentée. Regardez la variable d'état Opened_tables, plus elle est grande, plus table_cache doit être augmenté.

De plus, plus il y a de connexion, plus il doit y avoir de cache de table car il y a une ouverture par connexion. L'idéal est de connaître le nombre de table maximum utilisé dans les jointures mais c'est rarement possible. Essayons de mettre au minimum : 

table_cache > 2 x max_connections

table_cache est aussi dépendant de open_files_limit qui doit lui être supérieur.

open_files_limit

Il est préférable d'avoir : 

open_files_limit > 2 x table_cache.
query_cache_size et query_cache_limit

Il s'agit des deux variables qui jouent beaucoup sur les performances des requêtes SELECT.

Attention, les SELECT ayant par exemple le recours à une fonction dépendante du temps ou d'un nombre aléatoire (NOW() ou RAND() par exemple), ne sont jamais mises en cache ...

query_cache_size représente la taille totale du cache. Il est important de l'augmenter en fonction de la mémoire du système. Des valeurs comme 256M, 512M ou 1G sont tout à fait courante pour des systèmes avec 1Go ou 2Go de RAM.

query_cache_limit définit la taille limite d'une requête pouvant aller en cache. Il faut trouver un juste milieu :

    • si cette valeur est trop petite, des requêtes ne seront pas acceptée
    • si elle est trop élevé, le cache sera trop vite rempli puis vidé pour laisser de la place à une autre requête

Le graphe munin sur les requêtes indiquent avec "cache_hits" les requêtes qui ont été lues dans le cache.

Une requête est enlevée du cache dès qu'un update, un delete ou un insert modifie le résultat de cette requête.

Les variables de status Qc* aident à établir le comportement du cache et les modifications à réaliser : 

show status like "Qc_%";

Vous souhaitez vider le cache : 

RESET QUERY CACHE
tmp_table_size et max_heap_table_size

Le ratio des variables d'état Created_tmp_disk_tables/Created_tmp_tables indique si beaucoup de tables temporaires sont créées sur disque.

Created_tmp_disk_tables doit tendre vers 0.

max_heap_table_size indique la taille maximale des tables en mémoire et tmp_table_size la taille maximale des tables temporaires en mémoire. Le plus simple est d'avoir : 

max_heap_table_size = tmp_table_size
Session

Ces variables de sessions permettent d'améliorer les performances de requête, attention à ne pas mettre une valeur trop importante car vous pourrez allouer en mémoire jusqu'à SOMME(variables de sessions) x max_connections.

Leurs valeurs sont initiées à la valeur global lors de la création d'un nouveau thread. Pour les modifier :

  • SET GLOBAL variable = 1 * 1024 * 1024;

Cela ne modifiera pas la valeur de la variable pour votre session mais uniquement pour les suivantes.

Pour changer la valeur de la session courante :

  • SET SESSION variable = 1 * 1024 * 1024;

Les variables les plus importantes sont :

  • read_buffer_size (par défaut à 128k)

La taille du buffer est alloué au thread lorsqu'il réalise un scan séquentiel.

  • read_rnd_buffer_size (par défaut à 256k)

La taille du buffer est alloué au thread pour les accès disques des requêtes avec tri (ORDER BY).

  • join_buffer_size (par défaut à 128k)

La taille du buffer qui est utilisée pour les jointures ne pouvant utiliser d'indexes. Il faut privilégier les indexes plutôt que l'augmentation de ce buffer.

  • sort_buffer_size (par défaut à 2M)

La taille du buffer est alloué au thread pour les requêtes avec tri (ORDER BY et GROUP BY).

MyISAM
key_buffer_size

Il s'agit du cache des index pour le moteur de stockage MyISAM. Il n'est pas global à MySQL. Pour le dimensionner, il suffit de connaître la taille des index.

Pour MySQL 5.0 et > : 

SELECT SUM(T.`INDEX_LENGTH`) FROM `TABLES` T WHERE T.`ENGINE`='MyISAM'

Sinon pour avoir une valeur proche, faire la somme des fichiers d'indexs (taille en kilo) : 

find /var/lib/mysql -name '*.MYI' -exec du '{}' \; 2>&1 | awk '{ s += $1 } END { printf("%.0f\n", s )}'

Il y a deux façon de voir, soit on met légèrement plus (10% par exemple) pour limiter l'utilisation de la RAM mais on risque de devoir le refaire si les données augmentent. Soit on met beaucoup plus si la mémoire est disponible.

Il est de toute façon avoir une valeur inférieur à 0,01 pour le rapport : 

Key_reads/Key_read_requests

Attention Bug N’affectez pas plus de 4Go sur la version 5.0.51a-24+lenny1 de Lenny 64bit, le serveur se met à crasher. Message d'erreur rencontré : 

Jan 11 16:52:46 server_name mysqld[8853]: 100111 16:52:46 - mysqld got signal 11;
Jan 11 16:52:46 server_name mysqld[8853]: This could be because you hit a bug. It is also possible that this binary
Jan 11 16:52:46 server_name mysqld[8853]: or one of the libraries it was linked against is corrupt, improperly built,
Jan 11 16:52:46 server_name mysqld[8853]: or misconfigured. This error can also be caused by malfunctioning hardware.
Jan 11 16:52:46 server_name mysqld[8853]: We will try our best to scrape up some info that will hopefully help diagnose
Jan 11 16:52:46 server_name mysqld[8853]: the problem, but since we have already crashed, something is definitely wrong
Jan 11 16:52:46 server_name mysqld[8853]: and this may fail.
Jan 11 16:52:46 server_name mysqld[8853]:
Jan 11 16:52:46 server_name mysqld[8853]: key_buffer_size=5368709120
Jan 11 16:52:46 server_name mysqld[8853]: read_buffer_size=3141632
Jan 11 16:52:46 server_name mysqld[8853]: max_used_connections=115
Jan 11 16:52:46 server_name mysqld[8853]: max_connections=800
Jan 11 16:52:46 server_name mysqld[8853]: threads_connected=23
Jan 11 16:52:46 server_name mysqld[8853]: It is possible that mysqld could use up to
Jan 11 16:52:46 server_name mysqld[8853]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 9335673 K
Jan 11 16:52:46 server_name mysqld[8853]: bytes of memory
Jan 11 16:52:46 server_name mysqld[8853]: Hope that's ok; if not, decrease some variables in the equation.
Jan 11 16:52:46 server_name mysqld[8853]:
Jan 11 16:52:46 server_name mysqld[8853]: thd=0x775bc10
Jan 11 16:52:46 server_name mysqld[8853]: Attempting backtrace. You can use the following information to find out
Jan 11 16:52:46 server_name mysqld[8853]: where mysqld died. If you see no messages after this, something went
Jan 11 16:52:46 server_name mysqld[8853]: terribly wrong...
Jan 11 16:52:46 server_name mysqld[8853]: Cannot determine thread, fp=0x775bc10, backtrace may not be correct.
Jan 11 16:52:46 server_name mysqld[8853]: Bogus stack limit or frame pointer, fp=0x775bc10, stack_bottom=0x41620000,  thread_stack=131072, aborting backtrace.
Jan 11 16:52:46 server_name mysqld[8853]: Trying to get some variables.
Jan 11 16:52:46 server_name mysqld[8853]: Some pointers may be invalid and cause the dump to abort...
Jan 11 16:52:46 server_name mysqld[8853]: thd->query at 0x776ca70 = SHOW INNODB STATUS
Jan 11 16:52:46 server_name mysqld[8853]: thd->thread_id=40
Jan 11 16:52:46 server_name mysqld[8853]: The manual page at http://www.mysql.com/doc/en/Crashing.html contains
Jan 11 16:52:46 server_name mysqld[8853]: information that should help you find out what is causing the crash.
Jan 11 16:52:46 server_name mysqld_safe[10187]: Number of processes running now: 0
Jan 11 16:52:46 server_name mysqld_safe[10189]: restarted

Dans tous les cas, en 32Bit, il ne faut pas dépasser 4Go.

InnoDB
innodb_buffer_pool_size

Ce cache est le paramètre principale pour innodb. Il représente le cache de données et d'indexs. Il faut affecter une grande valeur à cette variable si InnoDB est utilisé afin de diminuer les I/O disques.

Pour évaluer la taille nécessaire, il est possible pour MySQL 5.0 et > de faire la somme de : 

SELECT SUM(T.`INDEX_LENGTH`) FROM `INFORMATION_SCHEMA`.`TABLES` T WHERE T.`ENGINE`='innodb';

et 

SELECT SUM(T.`DATA_LENGTH`) FROM `INFORMATION_SCHEMA`.`TABLES` T WHERE T.`ENGINE`='innodb';

Sur un système dédié à MySQL et si seul InnoDB est utilisé, il est raisonnable de mettre 80% de la RAM disponible pour ce paramètre. Si MyISAM est aussi utilisé, il est nécessaire de ne pas dépasser 50% de la RAM disponible.

Il est possible d'avoir des informations sur l'utilisation de ce buffer avec la commande : 

SHOW ENGINE INNODB STATUS \G
innodb_log_buffer_size

Il s'agit du cache des logs de transactions InnoDB. 1Mo par défaut, celui-ci peut être raisonnablement augmenté à 8Mo lorsque InnoDB est beaucoup utilisé.

innodb_log_file_size

Utilisez un fichier de log de taille 25 % du buffer mémoire (innodb_buffer_pool_size).

/!\ Attention lors de la modification de cette variable, il faut :

  • Arrêter MySQL
  • Supprimer /var/lib/mysql/ib_logfile0 et /var/lib/mysql/ib_logfile1
  • Modifier innodb_log_file_size dans my.cnf
  • Démarrer MySQL
innodb_autoextend_increment

Dans le cas d’une augmentation régulière et importante des données dans les tables innodb, il est possible d’augmenter la valeur d’auto extension du tablespace innodb. Cela a pour but de limiter la fragmentation du fichier sur le disque. Par exemple 50Mo au lieu de 8Mo.

innodb_thread_concurrency

Il n’est pas utile d’augmenter le paramètre innodb_thread_concurrency à une valeur supérieur à 8 car il n’y aura pas d’amélioration dans les traitements.

Optimisation par création d'indexs avec EXPLAIN et les logs

EXPLAIN est une commande MySQL qui explique comment va être exécutée la requête SQL. C'est l'optimiseur qui nous donne ces indications.

Il indique l'ordre dans lequel les tables sont parcourues. Quels sont les indexes disponibles et ceux qui sont utilisés. Une information très importante est le nombre de lignes lues. Il doit être le plus petit possible.

Vous allez récupérer de trois manières les requêtes à analyser :

  • dans les logs de requêtes lentes et/ou sans indexs
  • avec la commande SHOW FULL PROCESSLIST
  • par le développeur

Exemple d'utilisation d'EXPLAIN.

Nous avons une requête obtenue suite à une surcharge sur un serveur et donc la commande SHOW FULL PROCESSLIST : 

5323191	my-base	server5.lan:50616	my-base	Query	3819	Copying to tmp table	SELECT id_bp, titre_bp, date_bp, COUNT(id_bp_consult) AS nb_consult FROM bon_plan LEFT JOIN bon_plan_consultation ON id_bp_consult=id_bp WHERE etat_bp='1' and date_bp < '2008-12-21-17-54-41' GROUP BY id_bp ORDER BY nb_consult ASC ,date_bp DESC

Regardons le résultat de la commande EXPLAIN avant l'ajout d'indexs : 

EXPLAIN SELECT id_bp, titre_bp, date_bp, COUNT(id_bp_consult) AS nb_consult FROM bon_plan LEFT JOIN bon_plan_consultation ON id_bp_consult=id_bp WHERE etat_bp='1' and date_bp < '2008-12-21-17-54-41' GROUP BY id_bp ORDER BY nb_consult ASC ,date_bp DESC \G

Vous remarquerez l'utilisation de \G et non pas ; afin d'avoir un affichage plus facilement lisible. 

id: 1
select_type: SIMPLE
table: bon_plan
type: ref
possible_keys: archive_bp,etat_bp,etat_bp_2
key: etat_bp_2
key_len: 1
ref: const
rows: 736
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: bon_plan_consultation
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 29967
Extra:

Les informations importantes sont aux lignes :

  • table : pour savoir sur quelle table nous allons ajouter un index
  • type : comment la table est parcourue afin d'obtenir les données
  • possible_keys : indique les indexs utilisable
  • key : la clef utilisée
  • rows : le nombre de lignes retournées
  • Extra : va indiquer l'utilisation d'une clause where, la création d'une table temporaire ou encore le tri des données (order by)

Si type vaut ALL, cela veut dire que toute la table a été parcourue. D'ailleurs, dans ce cas, key vaut NULL. Il est clair qu'il faut poser un index.

Comment choisir l'index.

Nous savons que c'est la table bon_plan_consultation. Celle-ci est utilisée dans la requête au niveau du JOIN : 

JOIN bon_plan_consultation ON id_bp_consult=id_bp

Regardons comment a été créée la table et si elle dispose d'indexs : 

SHOW CREATE TABLE bon_plan_consultation;

CREATE TABLE `bon_plan_consultation` (
 `id_consult` int(11) NOT NULL auto_increment,
 `id_inscrit_consult` int(11) NOT NULL,
 `id_bp_consult` int(11) NOT NULL,
 `date_consult` varchar(19) NOT NULL,
 PRIMARY KEY  (`id_consult`),
) ENGINE=MyISAM AUTO_INCREMENT=41904 DEFAULT CHARSET=latin1

Il n'y a pas d'index sur la colonne id_bp_consult utilisée dans le join. Ajoutons le : 

CREATE INDEX idx_id_bp_consult ON bon_plan_consultation(id_bp_consult);

Après la création de l'index, vérifions le résultat, en relançant l'EXPLAIN. Résultat pour la table bon_plan_consultation : 

id: 1
select_type: SIMPLE
table: bon_plan_consultation
type: ref
possible_keys: idx_id_bp_consult
key: idx_id_bp_consult
key_len: 4
ref: my-bluenity.bon_plan.id_bp
rows: 38
Extra: Using index

On voit que MySQL utilise bien l'index créé et qu'il n'y a plus 38 lignes utilisées au lieu de presque 30000. Le problème de performance est tout de suite résolu.

type vaut maintenant ref, ce qui est très bon au niveau des performances.

Créer un indexe de taille optimisée

Il n'est pas bon de créer des indexes de taille trop importante, les performances peuvent ne pas être améliorées si l'indexe est sur une chaîne trop grande.

La syntaxe de création d'un indexe est : 

CREATE INDEX nom_indexe ON table(colonne);

Pour préciser sur quelle taille de la colonne créer l'indexe : 

CREATE INDEX nom_indexe ON table(colonne(taille));
  • Comment choisir la taille :

Nous allons faire une requête pour savoir combien de pourcentage de la table entière nous avons lorsque l'on restreint aux résultats différents pour une taille plus petite d'une colonne : 

SELECT COUNT(DISTINCT LEFT (Colonne,taille)) / COUNT(*) FROM table;

Nous essayons avec plusieurs valeurs pour trouver un pourcentage mais une taille petite. Par exemple sur la base d'exemple World fournie sur le site de Mysql : 

SELECT COUNT(DISTINCT LEFT (Name,7)) / COUNT(*) FROM City;

-> 0,9478 

SELECT COUNT(DISTINCT LEFT (Name,35)) / COUNT(*) FROM City;

-> 0,98

Il y a à peine 4% de différence pour que l'indexe trouve le résultat mais la taille de l'indexe est beaucoup plus petite !

Optimisation du type de vos données

Comment choisir entre le type CHAR et le type VARCHAR. CHAR a l'avantage d'être moins sensible aux crash et corruptions de données. VARCHAR permet d'avoir un espace disque moindre.

MySQL permet de vous aider à choisir en vous indiquant des informations sur la taille de vos données : 

SELECT colonne from TABLE PROCEDURE ANALYSE()\G

Maintenance des bases/tables

Optimisation physique des tables

Pour améliorer les performances, il peut être bon d'utiliser la commande OPTIMIZE : 

OPTIMIZE TABLE nom_table;
  • Pour MyISAM, elle réalise une défragmentation
  • Pour InnoDB, elle recrée la table

Attention, cette commande peut être longue à traiter et coûteux en performance en fonction de la taille de la table.

Afin de connaître les tables à optimiser : 

SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY';

Automatiser l'optimisation : 

#!/bin/bash

for table in $(/usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf -s --execute="SELECT CONCAT(TABLE_SCHEMA,'.', TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY';")
do
       /usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf --execute="OPTIMIZE TABLE $table;" > /dev/null
done

Réparation de bases de données

Outils myisamchk, vérifier l'état des tables : 

myisamchk *.MYI

Réparation :

  • Juste réparation de l'index : 
myisamchk -r -q nomtable
  • Si insuffisant : 
myisamchk -r nomtable
  • Si la réparation a échouée : 
myisamchk --safe-recover nomtable

Une autre solution peut être la commande MySQl : REPAIR TABLE : En étant connecté à mysql : 

REPAIR TABLE nomtable;

Exemple de script pour réparer toutes les tables d'une base passé en argument : 

#!/bin/bash
root_password=PASSWORD
if [ "$1" == "" ]; then
       echo $0 db
       exit 1;
fi
db=$1
for table in `/usr/bin/mysql -s --user=root --password=$root_password $db --execute="SHOW TABLES;"`
do
               /usr/bin/mysql --user=root --password=$root_password $db --execute="REPAIR TABLE $table;"
done

En cas de gros problème :

  • Déplacer les fichiers de données de la table dans un autre dossier
  • Dans mysql, recréer une table vide ainsi : 
SET AUTOCOMMIT=1;
TRUNCATE TABLE nomtable;
quit
  • Copier les anciens fichiers de données puis recréer l'index : 
myisamchk -r -q nomtable

Requêtes utiles avec la base d'information information_schema

Les tables avec beaucoup de lignes

Exemple avec les 10 tables les plus importantes : 

mysql --defaults-file=/etc/mysql/debian.cnf --silent --skip-column-names -D information_schema -e "SELECT T.TABLE_SCHEMA, T.TABLE_NAME, T.TABLE_ROWS FROM information_schema.TABLES T ORDER BY T.TABLE_ROWS DESC LIMIT 10;"

ou les tables avec plus de 100000 lignes : 

mysql --defaults-file=/etc/mysql/debian.cnf --silent --skip-column-names -D information_schema -e "SELECT T.TABLE_NAME, T.TABLE_ROWS FROM information_schema.TABLES T WHERE T.TABLE_ROWS > 100000 ORDER BY T.TABLE_ROWS;"

SHOW ou comment voir dans les entrailles de MySQL :-)

  • Informations sur InnoDB : 
SHOW INNODB STATUS\G
  • Liste de processus : 
SHOW FULL PROCESSLIST;
  • Liste des tables ouvertes : 
SHOW OPEN TABLES;
  • Informations sur les tables d'une base : 
SHOW TABLE STATUS FROM dbname;

Petits scripts pratiques

Tuer toutes les requêtes en exécution depuis plus d'un certain nombre de secondes

#!/bin/bash
SEUIL_ALERTE_CRITICAL=800
MYSQL_OUT=/tmp/mysql_kill
echo -e "Etes-vous sur de vouloir tuer les processus MySQL O/N :\c"
read ans
if [ "$ans" != "o" -a "$ans" != "O" ]
then
       exit 1
fi
mysql --defaults-file=/etc/mysql/debian.cnf --silent --skip-column-names -e "SHOW FULL PROCESSLIST" |grep -vE "Sleep|PROCESSLIST"> ${MYSQL_OUT}

for query in $(cat ${MYSQL_OUT}|awk '{ print $1"-"$6 }');
do
       if [ $(echo ${query}|cut -d- -f2) -gt ${SEUIL_ALERTE_CRITICAL} ];then
               mysql --defaults-file=/etc/mysql/debian.cnf --silent --skip-column-names -e "kill $(echo ${query}|cut -d- -f1)"
       fi
done
echo "Fin des kill"

Erreurs

Mot de passe root oublié

Arrêter MySQL : 

/etc/init.d/mysql stop

Lancer : 

mysqld_safe --skip-grant-tables --skip-networking &

Puis changer le mot de passe : 

mysqladmin -u root password 'nouveau mot de passe'

Si cela ne fonctionne pas, se connecter à MySQL, bdd mysql puis lancer : 

UPDATE user SET password=PASSWORD('mot de passe') WHERE User="root" AND Host="localhost";

Erreur d'authentification à la connexion au serveur MySQL

L'erreur est : 

Client does not support authentication protocol requested
by server; consider upgrading MySQL client

Il s'agit d'un problème de version entre le serveur (récent >=4.1) et un client plus ancien. Il faut soit mettre à jour le client soit modifier le mot de passe ainsi : 

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

ou 

UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd') WHERE Host = 'some_host' AND User = 'some_user';
FLUSH PRIVILEGES;

Pour dire à MySQL d'utiliser les anciens types de mots de passe, il faut activer la variable suivante dans my.cnf : 

old_passwords   = 1


ERROR 1373 (HY000) at line 1: Target log not found in binlog index

Modifier le fichier /etc/cron.daily/mysql-server : 

filename=`tail -n $KEEP_BINARY_LOGS $tmp | head -n 1`

devient 

filename=`tail -n $KEEP_BINARY_LOGS $tmp | head -n 1 | awk '{print $1}'`

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

Lors de manipulation de gros volume de données sur les tables InnoDB, si vous rencontrez l'erreur 1206, il faut alors augmenter dans my.cnf innodb_buffer_pool_size. Puis relancez mysql.

Host 'XXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

Cette erreur arrive lorsqu'il y a eu trop d'erreurs de connections vers le serveur mysql.

Pour rétablir les connexions, lancer sur le serveur mysql : 

mysqladmin flush-hosts -p
Enter password:

Afin d'éviter ce problème, modifiez la valeur par défaut de max_connect_error (10) en éditant my.cnf : 

max_connect_errors = 500

Vous pouvez aussi l'appliquer sans relancer MySQL avec : 

set global max_connect_errors = 500;

Si vous n'arrivez pas à modifier la valeur ou lancer le flush-hosts, en cas d'urgence, vous pouvez relancer mysql (/etc/init.d/mysql restart) pour reseter le compteur interne mysql et permettre les nouvelles connections.

Multiples instances MySQL sous debian

Les scripts et fichiers de configuration d'origines sont dans /etc/mysql et celui de démarrage est /etc/init.d/mysql
Chaque nouvelle instance de mysql tourne sur un autre port que celui par défaut (3306).
Le script de démarrage et le dossier de configuration seront alors renommés mysql-numeroport

Dans l'exemple, la nouvelle instance fonctionnera avec le port 3307.

Script de démarrage

cp /etc/init.d/mysql /etc/init.d/mysql-3307

Editer le nouveau script :
Modifier :

CONF=/etc/mysql/my.cnf
MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"

Ainsi : 

CONF=/etc/mysql-3307/my.cnf
MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql-3307/debian.cnf"

ERR_LOGGER="logger -p daemon.err -t /etc/init.d/mysql -i"

Ainsi : 

ERR_LOGGER="logger -p daemon.err -t /etc/init.d/mysql-3307 -i"

export HOME=/etc/mysql/

Ainsi : 

export HOME=/etc/mysql-3307/

/usr/sbin/mysqld  --defaults-file=/etc/mysql/my.cnf --print-defaults

Ainsi : 

/usr/sbin/mysqld  --defaults-file=/etc/mysql-3307/my.cnf --print-defaults
 if [ ! -r /etc/mysql/my.cnf ]; then
   log_warning_msg "$0: WARNING: /etc/mysql/my.cnf cannot be read. See README.Debian.gz"
   echo                "WARNING: /etc/mysql/my.cnf cannot be read. See README.Debian.gz" | $ERR_LOGGER
 fi

Ainsi : 

 if [ ! -r /etc/mysql-3307/my.cnf ]; then
   log_warning_msg "$0: WARNING: /etc/mysql-3307/my.cnf cannot be read. See README.Debian.gz"
   echo                "WARNING: /etc/mysql-3307/my.cnf cannot be read. See README.Debian.gz" | $ERR_LOGGER
 fi

/usr/bin/mysqld_safe --defaults-file=/etc/mysql/my.cnf > /dev/null 2>&1 &

Ainsi : 

/usr/bin/mysqld_safe --defaults-file=/etc/mysql-3307/my.cnf > /dev/null 2>&1 &

output=$(/etc/mysql/debian-start)

Ainsi : 

output=$(/etc/mysql-3307/debian-start)

if [ -f /etc/mysql/debian-log-rotate.conf ]; then
           echo "/etc/mysql/debian-log-rotate.conf is obsolete, see /usr/share/doc/mysql-server-5.0/NEWS.Debian.gz" | $ERR_LOGGER -p daemon.info
fi

Ainsi : 

if [ -f /etc/mysql-3307/debian-log-rotate.conf ]; then
           echo "/etc/mysql-3307/debian-log-rotate.conf is obsolete, see /usr/share/doc/mysql-server-5.0/NEWS.Debian.gz" | $ERR_LOGGER -p daemon.info
fi

Ajouter au démarrage : 

update-rc.d mysql-3307 defaults


Outils d'administration externe à MySQL

  • Les scripts présents sur les sites http://www.day32.com/MySQL/ et http://blog.mysqltuner.com/ permettent d'orienter sur les modifications d'optimisation à réaliser. Attention, ces scripts vous aident à modifier les paramètres mais il ne faut pas les appliquer sans en comprendre la signification. Chaque serveur MySQL est utilisé différemment, il faut modifier en fonction de votre expérience sur celui-ci.

Voici une copie des scripts :

Media:Tuning-primer.sh et Media:Mysqltuner.zip

Passons à MySQL 5.1

Au niveau de la configuration :

  • La variable table_cache devient table_open_cache
  • La variable log_slow_queries est dépréciée et : 
slow_query_log  = 1
slow_query_log_file = /var/log/mysql/mysql-slow-5.1.log
log_queries_not_using_indexes
  • La variable skip-bdb n'est plus autorisée car ce moteur n'est plus supporté.
  • Attention, dans les fichiers de configuration MySQL 5.0 de debian, thread_stack = 128K. Cette valeur est trop petite en 5.1 et provoque des erreurs, il suffit de mettre en commentaire pour avoir la valeur par défaut de 192K. 
ERROR 1436 (HY000) at line 20: Thread stack overrun: 4996 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld -O thread_stack=#' to specify a bigger stack.

Lors d'une installation hors package, dans /usr/local par les binaires de mysql.com, vous devez spécifier le chemin d'installation ainsi :

  • Pour mysql_safe : 
/usr/local/mysql-5.1.54/bin/mysqld_safe --defaults-file=/etc/mysql/my-5.1.conf --ledir=/usr/local/mysql-5.1.54/bin --basedir=/usr/local/mysql-5.1.54
  • Pour mysql_upgrade si vous avez mis un autre nom de socket : 
/usr/local/mysql-5.1.54/bin/mysql_upgrade -S /var/run/mysqld/mysqld-5.1.sock -u debian-sys-maint -p
  • Pour relancer mysql_fix_privilege_tables si besoin même après un mysql_upgrade : 
/usr/local/mysql-5.1.54/bin/mysql_fix_privilege_tables --basedir=/usr/local/mysql-5.1.54 --socket=/var/run/mysqld/mysqld-5.1.sock --user=debian-sys-maint --password=PASSWORD

Passons à MySQL 5.5

Au niveau de la configuration :

  • language devient lc-messages-dir et il ne faut plus préciser la langue à la fin du chemin 
110215 19:05:27 [ERROR] An old style --language value with language specific part detected: /usr/local/mysql-sessions/share/english/
110215 19:05:27 [ERROR] Use --lc-messages-dir without language specific part instead.

On a donc : 

lc-messages-dir = /usr/local/mysql/share


Si vous avez l'erreur : 

mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

Il manque la librairie libaio1 : 

apt-get install libaio1

Vous pouvez le désactiver avec : 

innodb_use_native_aio=0

performance_schema

performance_schema est une base "virtuelle" apparue avec MySQL 5.5.3.

Pour l'activer, ajouter --performance_schema en argument au lancement de mysqld ou ajouter à my.cnf dans la section mysqld ainsi : 

[mysqld]
...
performance_schema

Visualiser si performance_schema est actif : 

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

Fonctionnalités

Le partitionnement

Depuis la version 5.1, il est possible de réaliser du partitionnement horizontal. Cela correspond à séparer une table en groupe d'enregistrements.

Il y a quatre méthodes de partitionnement :

  • RANGE : intervalles de valeurs
  • LIST : séries discrètes de valeurs
  • HASH ou KEY : répartition uniforme par hachage

Le repartitionnement est possible à chaud (attention bien sûr aux accès disques provoqués sur de grandes tables).

Le partitionnement doit être fait sur les clés primaires ou les indexes uniques.

Pour gagner en performance, il faut utiliser dans les requêtes les indexes indiqué pour le partitionnement.

  • Créer un partitionnement : 
ALTER TABLE City PARTITION BY RANGE(ID)
(
PARTITION p0 VALUES LESS THAN (1000),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (3000),
PARTITION p3 VALUES LESS THAN (4000),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
  • Pour InnoDB, il faut avoir innodb_file_per_table positionné à ON pour que le partitionnement soit efficace, sinon les partitions sont toujours dans le fichier principal ibdata1.
  • Annuler le partitionnement : 
ALTER TABLE City REMOVE PARTITIONING;
  • Supprimer une partition avec ses enregistrements : 
ALTER TABLE City DROP PARTITION p1;

La réplication

Présentation

La réplication MySQL permet d'avoir un serveur ou plusieurs serveurs ayant les même bases et les mêmes données qu'un serveur maître. La réplication MySQL est asynchrone, il n'est pas garanti à un instant t que les données soient les même sur tous les serveurs.

Il y a un serveur maître et un ou plusieurs esclaves qui sont synchronisés par rapport au maître.

Les écritures doivent toutes être réalisées sur le maître. Les écritures réalisées sur les esclaves ne sont pas répliquées.

Deux types d'utilisation à la réplication :

  • répartition de charge. Le maître traite les écritures et plusieurs esclaves traitent les lectures. C'est le code applicatif qui doit gérer cette répartition.
  • pour la sauvegarde. Le serveur maître est trop chargé pour être sauvegardé, un serveur esclave permet de réaliser les dumps sans perturber la production.

Installation

  • Préparation du maître

Dans le fichier my.cnf indiquer un id au serveur (généralement 1 pour le maître) : 

server-id=1

Activer les logs binaire avec log-bin

Ajouter un utilisateur pour la réplication : 

GRANT replication slave on *.* to 'replic'@'%' IDENTIFIED BY 'PASSWORD';
  • Préparation de l'esclave

Dans le fichier my.cnf indiquer un id au serveur (généralement 2 pour le premier esclave, etc... ) : 

server-id=2
  • Préparation du dump sur le maître pour l'importer sur l'esclave.

Il y a un point à bien comprendre, il est nécessaire de connaître la position dans les logs sur le maître afin de s'y synchroniser ensuite sur l'esclave. Pour cela, il y a deux façons de procéder.

Soit on utilise SHOW MASTER STATUS; pour connaître la position. Elle sera à indiquer sur l'esclave. Soit on l'intègre dans le dump, ce qui est plus pratique : 

mysqldump --master-data=1 database -p > fichier.sql
  • Mise en place de la réplication

Avec le fichier master.info qui contient les informations de connexion au maître, il n'est plus nécessaire de mettre dans le my.cnf de l'esclave les paramètres de connexion (master-host, master-user, master-password ou encore master-port=3306)

Se connecter à MySQL et indiquer les informations de connexion : 

CHANGE MASTER TO MASTER_HOST='servermaitre', MASTER_USER='replic', MASTER_PASSWORD='PASSWORD', MASTER_PORT=3306;

Si vous avez choisit d'utiliser SHOW MASTER STATUS au lieu de --master-data=1, utilisez la commande : 

CHANGE MASTER TO MASTER_HOST=`servermaitre`, MASTER_USER='replic', MASTER_PASSWORD='PASSWORD', MASTER_PORT=3306, MASTER_LOG_FILE='Fichierdelog.XXXXX', MASTER_LOG_POS=NNNNN;

Importer le dump réalisé précédemment. Dans le cas de --master-data=1, il indique aussi à mysql où se positionner au niveau des logs du maître. S'il s'agit d'un réimport sur une réplication déjà fonctionnelle, pensez à arrêter la réplication avec SLAVE STOP ;

Attention, de bien faire la CHANGE MASTER TO en premier car lors de l'utilisation de cette commande avec les paramètres MASTER_HOST et/ou MASTER_USER, la configuration est réinitialisé.Donc s'il est fait après l'import, la position dans l'import ne sera pas gardée.

Lancer la réplication : 

SLAVE START;

Vérifier la bonne configuration avec : 

SHOW SLAVE STATUS;

Administration

Informations sur le fonctionnement
  • C'est l'esclave qui se connecte au maître et attend les requêtes du maître. C'est le paramètre master-connect-retry=60 dans le my.cnf de l'esclave qui indique le nombre de secondes entre les tentatives de connexions en cas de coupure.
  • L'esclave lance 2 threads, un qui s'occupe de transférer les logs binaires entre les serveurs et un qui exécute localement les requêtes SQL.
  • Les logs binaires sont conservés par rapport à une taille maximale de fichiers binaires configurée dans my.cnf. Tant qu'il n'y a pas de problème, il ne garde pas plus de logs binaires.

S'il y a un problème, il va conserver le maximum de logs possibles en dépassant la taille configurée afin de pouvoir rejouer les requêtes lorsque l'erreur n'est plus présente et ainsi rattraper son retard.

  • Il est possible de répliquer tout le serveur ou uniquement certaines bases grâce aux paramètres suivants sur le maître dans le my.cnf (exemple pour les relay-backup) : 
binlog_do_db            = fixroutes
binlog_do_db            = postfix
binlog_ignore_db        = mysql
binlog_ignore_db        = information_schema

On voit si ces paramètres sont utilisés sur le maître : 

mysql> SHOW MASTER STATUS;
+------------------+----------+-------------------+--------------------------+
| File             | Position | Binlog_Do_DB      | Binlog_Ignore_DB         |
+------------------+----------+-------------------+--------------------------+
| mysql-bin.000012 |     1629 | fixroutes,postfix | mysql,information_schema |
+------------------+----------+-------------------+--------------------------+
1 row in set (0.00 sec)
  • Deux fichiers sont important à la réplication sur les esclaves. Ils se trouvent dans le dossier de données des bases
    • master.info : contient les informations de connexions au maître et la position dans le journal binaire du maître
    • relay-log.info : indique la position dans les logs binaires pour le maître mais aussi pour les logs binaires relai.

Ils doivent être sauvegardés.

Principales commandes
  • SHOW MASTER STATUS (à lancer sur le maître) 
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.004693 | 101652116 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

Cette commande est principalement utilisée lors de l'installation de la réplication. Elle indique quel est le fichier de log binaire actuellement utilisé et la position dans ce fichier.

  • STOP SLAVE (à lancer sur l'esclave)

Cette commande arrête les threads de réplication, il est possible de spécifier l'un ou l'autre des threads :

    • STOP SLAVE IO_THREAD
    • STOP SLAVE ISQL_THREAD
  • START SLAVE (à lancer sur l'esclave)

Cette commande démarre les threads de réplication, il est possible de spécifier l'un ou l'autre des threads :

    • START SLAVE IO_THREAD
    • START SLAVE ISQL_THREAD
  • SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; (à lancer sur l'esclave)

Cette commande permet lors qu'il y a eu une erreur qui a été résolu (par exemple commande relancé manuellement) de passer l'erreur (Last_Error et Last_Errno). Elle doit être suivie de START SLAVE.

  • SHOW SLAVE STATUS (à lancer sur l'esclave) 
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 | 10.0.0.11   | replicator  |        3306 |            60 | mysql-bin.004694 |             4242456 | magic32-relay-bin.002632 |       4242593 | mysql-bin.004694      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |             4242456 |         4242593 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.43 sec)

Cette commande permet de voir où en est la réplication.

Principales variables d'état de réplication
  • Slave_IO_State indique l'état du thread d'IO, il est généralement dans l'état "Waiting for master to send event"
  • Slave_IO_Running et Slave_SQL_Running indique si les threads sont en fonctionnement, la valeur doit être à Yes. Si la valeur est à No c'est qu'il y a un problème sur un des threads.
  • Last_Errno doit être à 0, si ce n'est pas le cas, c'est qu'il y a une erreur dans la réplication. Dans ce cas, voir ce qu'indique Last_Error
  • Last_Error doit être vide, sinon cette variable indique le problème et ce qu'il faut faire pour le résoudre, généralement une requête SQL à lancer manuellement.
  • Seconds_Behind_Master indique une estimation du nombre de secondes dont l'esclave est en retard par rapport au maître. Ce nombre varie en permanence, l'idéal étant 0. Il n'est pas dramatique d'avoir moins de 30 minutes de retard par rapport au maître.

En cas d'erreur

Exemple de Slave_SQL_Running = No

Etat de SHOW SLAVE STATUS :

  • Slave_SQL_Running = No
  • Last_Errno = 1146
  • Last_Error = Error 'Table 'forumppc_old.inscrit' doesn't exist' on query. Default database: 'forumppc_old'. Query: 'UPDATE forumppc.inscrit,forumppc_old.inscrit SET forumppc.inscrit.signature_forum=forumppc_old.inscrit.signature_forum WHERE forumppc.inscrit.id=forumppc_old.inscrit.id'

Le thread SQL ne fonctionne plus, il n'a pu exécuter une requête. Il faut lancer celle-ci manuellement sur l'esclave : 

use forumppc_old
UPDATE forumppc.inscrit,forumppc_old.inscrit SET forumppc.inscrit.signature_forum=forumppc_old.inscrit.signature_forum WHERE forumppc.inscrit.id=forumppc_old.inscrit.id

Relancer le thread : 

START SLAVE

Vérifier l'état : 

SHOW SLAVE STATUS

Slave_SQL_Running est toujours à No, il faut passer l'erreur : 

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE

SHOW SLAVE STATUS montre maintenant que Slave_SQL_Running est bien à Yes et qu'il n'y a plus d'erreur. L'esclave va maintenant rattraper le retard accumuler, la valeur Seconds_Behind_Master doit normalement diminuer.

Récupérée de « http://www.dj-j.net/w/index.php?title=Linux:Administration_MySQL&oldid=733 »
Outils personnels