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>
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> GRANT ALL PRIVILEGES ON *.* TO adminmysql@localhost IDENTIFIED BY 'XXX' WITH GRANT OPTION; mysql> 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 >> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> 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 (> 16M) join_buffer_size (> 128.0K, or always use indexes with joins) table_cache (> 64) <span class="searchword1">innodb</span>_buffer_pool_size (>= 34M)
Administration ¶
On crée une base de données et un utilisateur associé :
# mysqladmin create NOM_BASE # mysql mysql> 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> 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> 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> FLUSH PRIVILEGES;
On pourra ainsi régler finement les droits d’un utilisateurs en connaissant la signification de chaque colonne :
mysql> 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> 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> UPDATE user SET Show_view_priv='Y' WHERE User='<span class="searchword2">debian</span>-sys-maint'; mysql> FLUSH PRIVILEGES;
On peut aussi gérer des droits sur les tables :
mysql> 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> CHECK TABLE mabase.matable; mysql> REPAIR TABLE mabase.matable; mysql> 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 > NOM_FICHIER
Pour restaurer une base de données :
# mysqadmin create NOM_BASE # mysql NOM_BASE < NOM_FICHIER
Sauvegarder une base complète :
# mysqldump --opt --all-databases > NOM_FICHIER
Utilisation ¶
Créer une nouvelle base de données nommée NOM_BASE :
mysql> CREATE DATABASE NOM_BASE;
Voir les bases de données créées :
mysql> SHOW DATABASES;
Utiliser la base de données NOM_BASE :
mysql> USE NOM_BASE
Voir les tables créées :
mysql> SHOW TABLES;
Créer une table nommée test avec différents champs :
mysql> 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> ALTER TABLE test ADD another TEXT;
Supprimer un champ à une table :
mysql> ALTER TABLE test DROP another;
Insertion de données dans une table :
mysql> 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> SELECT * FROM test;
Effacer des données d’une table :
mysql> 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> 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> FLUSH TABLES WITH READ LOCK;
# mysqldump --all-databases > mysql.dump
mysql> SHOW MASTER STATUS; -- notez les valeurs de File et Position mysql> 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