HowtoMySQL – Infogerance Evolix – Trac

Howto MySQL

Documentation officielle : http://dev.mysql.com/doc/refman/5.0/en/

Installation

Sous Debian Lenny, la version proposée est la 5.0 :

# aptitude install mysql-server

Note : lors des demandes de mot de passe lors de l’installation, on a parfois constaté des bugs et obtenu un mot de passe invalide au final !

On vérifie ensuite que tout s’est bien passé :

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24487
Server version: 5.0.51a-24+lenny3 (<span class="searchword2">Debian</span>)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql&gt;

Il est ensuite conseillé de créer un administrateur pour la base de données (par exemple « adminmysql », car « root » peut prêter à confusion) :
mysql&gt; GRANT ALL PRIVILEGES ON *.* TO adminmysql@localhost IDENTIFIED BY 'XXX' WITH GRANT OPTION;
mysql&gt; DELETE FROM mysql.user where User='root';

On notera sous Debian la présence d’un utilisateur debian-sys-maint qui sert à certains scripts Debian : il ne doit pas être supprimé ! Au niveau sécurité, le mot de passe est généré à l’installation (stocké dans /etc/mysql/debian.cnf qui doit évidemment n’être lisible que par root) par la commande :
perl -e 'print map{("a".."z","A".."Z",0..9)[int(rand(62))]}(1..16)'

Enfin, on peut utiliser un fichier .my.cnf pour conserver les login/pass.
# cat /root/.my.cnf
[client]
user = adminmysql
password = XXX

Configuration

Les fichiers de configuration se trouvent dans /etc/mysql/ et notamment my.cnf qui centralise toutes les directives.

Sous Debian, les journaux de MySQL (démarrage, erreurs, infos) sont envoyés à syslog Les binlogs (trace de toutes les requêtes executées) sont dans /var/log/mysql/

Par défaut, MySQL écoute en TCP/IP sur 127.0.0.1. Pour activer les connexions réseau à distance, il suffit de modifier la ligne suivante dans le fichier my.cnf :

bind-address            = 0.0.0.0

Selon les ressources de la machine, il faut optimiser les directives dans my.cnf (par défaut, la configuration est adaptée… pour un petit serveur !). Sous Debian, on trouvera quelques exemples dans le répertoire /usr/share/doc/mysql-server-5.0/examples/

Voici les paramètres essentiels :

[mysqld]

###### Connexions
# Maximum de connexions concurrentes (defaut = 100)... provoque un "Too many connections"
max_connections = 250
# Maximum de connexions en attente en cas de max_connections atteint (defaut = 50)
back_log = 100
# Maximum d'erreurs avant de blacklister un hote
max_connect_errors = 10
# Loguer les requetes trop longues
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2

###### Tailles
# Taille reservee au buffer des index MyIsam
# A ajuster selon les resultats
key_buffer              = 512M
# Taille max des paquets envoyés/reçus ... provoque un "Packet too large"
max_allowed_packet      = 64M
# Taille de la memoire reserve pour un thread
thread_stack            = 192K
# A mettre le nombre de CPU x2
thread_cache_size       = 8
# Taille maximum des tables de type MEMORY
max_heap_table_size = 64M

###### Cache
# max_connections x nbre max de tables dans une jointure (defaut = 64)
table_cache            = 1500
# Taille max des requetes cachees (defaut = 1M)
query_cache_limit       = 8M
# Taille reservee pour le cache (defaut = 0)
query_cache_size        = 256M
# Type de requetes a cacher (defaut = 1 = tout peut etre cache)
query_cache_type        = 1

###### <span class="searchword1">InnoDB</span>
# Si <span class="searchword1">InnoDB</span> n'est pas utilise... le <span class="searchword0">desactiver</span>
#skip-<span class="searchword1">innodb</span>
# En general, il est plus optimum d'avoir un fichier par table
<span class="searchword1">innodb</span>_file_per_table
# Taille memoire allouee pour le cache des datas et index
# A ajuster en fonction de sa RAM (si serveur dedie a MySQL, on peut aller jusqu'a 80%)
<span class="searchword1">innodb</span>_buffer_pool_size = 2G
# Taille buffer memoire pour structures internes <span class="searchword1">InnoDB</span>
<span class="searchword1">innodb</span>_additional_mem_pool_size = 16M
# Nombre maximum de threads systeme concurents
<span class="searchword1">innodb</span>_thread_concurrency = 16

Après quelques temps d’utilisation, il est très intéressant de regarder les résultats du script mysqltuner.pl téléchargeable sur http://mysqltuner.pl/
perl mysqltuner.pl 

 &gt;&gt;  MySQLTuner 1.0.1 - Major Hayden &lt;major@mhtx.net&gt;
 &gt;&gt;  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 &gt;&gt;  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-24+lenny3
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +<span class="searchword1">InnoDB</span> -ISAM -NDBCluster 

[--] Data in MyISAM tables: 556M (Tables: 3831)
[--] Data in <span class="searchword1">InnoDB</span> tables: 34M (Tables: 968)
[--] Data in MEMORY tables: 3K (Tables: 9)
[!!] Total fragmented tables: 335

-------- Performance Metrics -------------------------------------------------
[--] Up for: 20h 59m 23s (2M q [29.301 qps], 31K conn, TX: 5B, RX: 300M)
[--] Reads / Writes: 23% / 77%
[--] Total buffers: 58.0M global + 2.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 320.5M (10% of installed RAM)
[OK] Slow queries: 0% (1/2M)
[OK] Highest usage of available connections: 11% (11/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/216.0M
[OK] Key buffer hit rate: 97.0% (31M cached / 942K reads)
[OK] Query cache efficiency: 77.2% (921K cached / 1M selects)
[!!] Query cache prunes per day: 78490
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 59K sorts)
[!!] Joins performed without indexes: 29333
[OK] Temporary tables created on disk: 25% (22K on disk / 88K total)
[OK] Thread cache hit rate: 99% (26 created / 31K connections)
[!!] Table cache hit rate: 0% (64 open / 82K opened)
[OK] Open file limit used: 12% (126/1K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[!!] <span class="searchword1">InnoDB</span> data size / buffer pool: 34.4M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (&gt; 16M)
    join_buffer_size (&gt; 128.0K, or always use indexes with joins)
    table_cache (&gt; 64)
    <span class="searchword1">innodb</span>_buffer_pool_size (&gt;= 34M)

Administration

On crée une base de données et un utilisateur associé :

# mysqladmin create NOM_BASE
# mysql
mysql&gt; GRANT ALL PRIVILEGES ON NOM_BASE.* TO 'USER_BASE'@localhost IDENTIFIED BY 'XXX';

Cette opération revient à insérer des lignes brutes dans les tables mysql.user et mysql.db ainsi :
# mysql
mysql&gt; INSERT INTO user VALUES ('localhost','USER_BASE',password('XXX'),'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0);
mysql&gt; INSERT INTO db VALUES ('localhost','NOM_BASE','USER_BASE','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql&gt; FLUSH PRIVILEGES;

On pourra ainsi régler finement les droits d’un utilisateurs en connaissant la signification de chaque colonne :
mysql&gt; desc user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       | 
| User                  | char(16)                          | NO   | PRI |         |       | 
| Password              | char(41)                          | NO   |     |         |       | 
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       | 
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       | 
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       | 
| File_priv             | enum('N','Y')                     | NO   |     | N       |       | 
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| References_priv       | enum('N','Y')                     | NO   |     | N       |       | 
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       | 
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       | 
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       | 
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       | 
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       | 
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       | 
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       | 
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       | 
| ssl_cipher            | blob                              | NO   |     | NULL    |       | 
| x509_issuer           | blob                              | NO   |     | NULL    |       | 
| x509_subject          | blob                              | NO   |     | NULL    |       | 
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       | 
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       | 
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       | 
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       | 
+-----------------------+-----------------------------------+------+-----+---------+-------+

mysql&gt; desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       | 
| Db                    | char(64)      | NO   | PRI |         |       | 
| User                  | char(16)      | NO   | PRI |         |       | 
| Select_priv           | enum('N','Y') | NO   |     | N       |       | 
| Insert_priv           | enum('N','Y') | NO   |     | N       |       | 
| Update_priv           | enum('N','Y') | NO   |     | N       |       | 
| Delete_priv           | enum('N','Y') | NO   |     | N       |       | 
| Create_priv           | enum('N','Y') | NO   |     | N       |       | 
| Drop_priv             | enum('N','Y') | NO   |     | N       |       | 
| Grant_priv            | enum('N','Y') | NO   |     | N       |       | 
| References_priv       | enum('N','Y') | NO   |     | N       |       | 
| Index_priv            | enum('N','Y') | NO   |     | N       |       | 
| Alter_priv            | enum('N','Y') | NO   |     | N       |       | 
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       | 
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       | 
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       | 
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       | 
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       | 
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       | 
| Execute_priv          | enum('N','Y') | NO   |     | N       |       | 
+-----------------------+---------------+------+-----+---------+-------+

Par exemple, pour permettre à un utilisateur de faire des « SHOW VIEW » :
mysql&gt; UPDATE user SET Show_view_priv='Y' WHERE User='<span class="searchword2">debian</span>-sys-maint';
mysql&gt; FLUSH PRIVILEGES;

On peut aussi gérer des droits sur les tables :
mysql&gt; GRANT Select,Insert,Update ON BASE.TABLE TO 'USER_BASE'@localhost;

Pour réparer ou optimiser toutes les tables (une sorte de fsck pour les tables) :
# mysqlcheck --auto-repair --check --optimize --all-databases

Pour réparer une seule table :
mysql&gt; CHECK TABLE mabase.matable;
mysql&gt; REPAIR TABLE mabase.matable;
mysql&gt; OPTIMIZE TABLE mabase.matable;

Dans le cas des tables MyISAM, une réparation est aussi possible via myisamchk… à faire avec le service MySQL arrêté :
# myisamchk -r -q /var/lib/mysql/BASE/TABLE.MYD

Sauvegarde MySQL

Pour sauvegarder une base de données :

# mysqldump NOM_BASE &gt; NOM_FICHIER

Pour restaurer une base de données :
# mysqadmin create NOM_BASE
# mysql NOM_BASE &lt; NOM_FICHIER

Sauvegarder une base complète :
# mysqldump --opt --all-databases &gt; NOM_FICHIER

Utilisation

Créer une nouvelle base de données nommée NOM_BASE :

mysql&gt; CREATE DATABASE NOM_BASE;

Voir les bases de données créées :
mysql&gt; SHOW DATABASES;

Utiliser la base de données NOM_BASE :
mysql&gt; USE NOM_BASE

Voir les tables créées :
mysql&gt; SHOW TABLES;

Créer une table nommée test avec différents champs :
mysql&gt; CREATE TABLE test (id INT not null AUTO_INCREMENT, prenom VARCHAR
(50) not null , nom VARCHAR (50) not null , ne_le DATE not null ,
ville VARCHAR (90), enfants INT, PRIMARY KEY (id));

Décrire une table :
DESC test;

Ajouter un champ à une table :
mysql&gt; ALTER TABLE test ADD another TEXT;

Supprimer un champ à une table :
mysql&gt; ALTER TABLE test DROP another;

Insertion de données dans une table :
mysql&gt; INSERT INTO test VALUES (1,'jp','papin','2005-06-12','Marseille',2);
INSERT INTO test (id,prenom,nom,ne_le) VALUES (2,'c','waddle','2004-06-17');

Sélectionner tous les champs d’une table :
mysql&gt; SELECT * FROM test;

Effacer des données d’une table :
mysql&gt; DELETE FROM test WHERE nom='waddle';

Effacer TOUTES les données d’une table :
DELETE FROM test;

Supprimer une table :
DROP TABLE test;

Supprimer une base de données :
DROP DATABASE NOM_BASE;

Monitoring

Pour surveiller un service MySQL en production, on pourra faire :

# mysqladmin status
# mysqladmin extended-status
# mysqladmin processlist

Pour avoir une version plus conviviale et dynamique des process en cours, on utilisera l’indispensable outil mytop.
# aptitude install mytop

On édite le fichier /root/.mytop ainsi :
user = <span class="searchword2">debian</span>-sys-maint
pass = MOT_DE_PASSE
db = mysql

Reste plus qu’à lancer la commande mytop

Pour surveiller le moteur InnoDB, on utilisera la commande suivante :

mysql&gt; SHOW <span class="searchword1">INNODB</span> STATUS;

Enfin, reste Munin qui permet d’obtenir de jolis graphes.

Afin d’être alerté en cas de soucis avec des tables à réparer, il est conseillé d’ajouter la configuration suivante au logiciel log2mail :

file = /var/log/syslog
  pattern = "is marked as crashed and should be repaired"
  mailto = ADRESSE-MAIL-ALERTE
  template = /etc/log2mail/template.mysql

Le fichier /etc/log2mail/template.mysql contenant :
From: %f
To: %t
Subject: MySQL problem

Hello!

We have matched your pattern "%m" in "%F" %n times:

%l

Yours,
log2mail.

Note : il faut ajouter l’utilisateur log2mail dans le groupe adm

Réplication MySQL

Préparation

  • Prérequis : disposer de deux serveurs MySQL avec un datadir identique

Dans le cas où le futur master est en production et ne peux être arrété :

mysql&gt; FLUSH TABLES WITH READ LOCK;

# mysqldump --all-databases &gt; mysql.dump

mysql&gt; SHOW MASTER STATUS;  -- notez les valeurs de File et Position
mysql&gt; UNLOCK TABLES;

  • Autoriser les connections MySQL distantes
  • Activer les logs binaires sur chaque serveur : log-bin = /var/log/mysql/mysql-bin.log
  • Positionner un server-id différent sur chaque serveur
  • Créer un utilisateur dédié pour la réplication sur chaque serveur avec le droit REPLICATION SLAVE : grant replication slave on *.* to repl@'%' identified by 'XXX';

Activation

  • Exécuter SHOW MASTER STATUS sur le premier serveur (qui sera le serveur master en mode master-slave) et noter les informations
  • Sur le serveur B (le slave en mode master-slave), exécuter :

CHANGE MASTER TO
   MASTER_HOST='$MASTER_IP',
   MASTER_USER='repl',
   MASTER_PASSWORD='XXX',
   MASTER_LOG_FILE='mysql-bin.NNNNNN',
   MASTER_LOG_POS=NNN;

  • Pour exclure une base de la réplication, dans /etc/mysql/my.cnf :
    binlog-ignore-db = mysql
    
  • Puis démarrer la réplication sur le serveur B avec la commande : START SLAVE
  • Enfin, exécuter SHOW SLAVE STATUS pour vérifier le bon fonctionnement

Etapes supplémentaires pour une réplication master-master

  • Positionner la directive auto-increment-increment = 10 sur chaque serveur
  • Positionner la directive auto-increment-offset avec une valeur numérique différente sur chaque serveur

Exemple : auto-increment-offset = 1 sur le serveur A, auto-increment-offset = 2 sur le serveur B

  • Effectuer l’étape Activation dans le sens A->B et B->A

Résolution des erreurs lors de la réplication

On vérifie les erreurs avec les commandes SHOW SLAVE STATUS et SHOW MASTER STATUS. En cas d’erreur, il faut « simplement » résoudre l’erreur, puis relancer la réplication avec la commande START SLAVE. Voici quelques erreurs possibles

  • Incorrect key file for table ‘./base/table.MYI’; try to repair it : il faut réparer la table concernée
  • Duplicate entry ‘NNNNNN’ for key N : une solution *peut* être de supprimer la ligne concernée
  • Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave. : une solution *peut* être de réinitialiser complètement la réplication… par exemple via un CHANGE MASTER TO MASTER_HOST=’$MASTER_IP’; START SLAVE;
  • Si l’on veut zapper l’erreur en cours : SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
  • En cas d’erreur du type [ERROR] Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236), une solution possible est de réinitialiser la réplication avec un STOP SLAVE; RESET SLAVE; START SLAVE; mais cela va propoquer plein de conflit (voir ci-dessous)
  • Si pour une raison ou un autre, on a plein de DUPLICATE ENTRY mais que l’est sûr‘ de vouloir les ignorer, on peut faire cela en redémarrant MySQL avec le paramètre : slave-skip-errors = 1062 => On peut faire également cela avec d’autres types d’erreurs.

Diagnostic des erreurs

Errno 24

Si vous obtenez une erreur de ce type, lors d’un mysqldump par exemple :

mysqldump: Got error: 1016: Can't open file: './db/table.frm' (errno: 24) when using LOCK TABLES

C’est que votre serveur MySQL tente d’ouvrir trop de fichiers simultanément.

Pour augmenter le nombre maximal de fichiers pouvant être ouverts, vous pouvez ajuster le paramètre suivant dans la section [mysqld] du fichier my.cnf, dans la limite permise par votre système d’exploitation :

open_files_limit = 2048

La valeur par défaut étant de 1024.

Error 2020 avec mysqldump

Si vous obtenez l’erreur suivante lors d’un mysqldump :

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `mytable` at row: 6542

Augmentez la valeur de max_allowed_packet dans la section [mysqldump] du fichier my.cnf :
[mysqldump]
max_allowed_packet      = 64M

Instances MySQL

Installation

À compléter…

Administration

Pour voir le statut de l’instance n°1, logiquement nommée mysqld1 (GNR=1) et tournant sur le port 3307 :

# mysqld_multi report 1
Reporting MySQL servers
MySQL server from group: mysqld1 is running

Pour l’arrêter / redémarrer, même principe (attention, mysqld_multi est peu verbeux…) :
# ps auwx | grep 3307
# mysqld_multi stop 1
# ps auwx | grep 3307
# mysqld_multi start 1
# ps auwx | grep 3307

VIA http://trac.evolix.net/infogerance/wiki/HowtoMySQL