Mon wiki

Outils pour utilisateurs

Outils du site

Vous êtes ici : start » informatique » databases » mysql » requetes_administration
informatique:databases:mysql:requetes_administration

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Les deux révisions précédentesRévision précédente
informatique:databases:mysql:requetes_administration [2024/11/21 18:37] – supprimée - modification externe (Date inconnue) 127.0.0.1informatique:databases:mysql:requetes_administration [2024/11/21 18:37] (Version actuelle) – ↷ Page déplacée de informatique:mysql:requetes_administration à informatique:databases:mysql:requetes_administration alexis
Ligne 1: Ligne 1:
 +====== Requêtes d'administration ======
 +===== Affichage des erreurs =====
 +<WRAP prewrap>
 +<code sql>
 +SHOW ERRORS
 +</code>
 +</WRAP>
 +Plus d'informations [[http://dev.mysql.com/doc/refman/5.5/en/show-errors.html|ici]]
 +
 +===== Affichage des alertes =====
 +<WRAP prewrap>
 +<code sql>
 +SHOW WARNINGS
 +</code>
 +</WRAP>
 +Plus d'informations [[http://dev.mysql.com/doc/refman/5.5/en/show-warnings.html|ici]]
 +
 +===== Affichage de la description d'une table =====
 +<WRAP prewrap>
 +<code sql>
 +DESC <table>
 +</code>
 +</WRAP>
 +Plus d'informations [[http://dev.mysql.com/doc/refman/5.5/en/explain.html|ici]]
 +
 +===== Purge des caches =====
 +<WRAP prewrap>
 +<code sql>
 +/* Ferme les tables ouvertes, force la fermeture des tables utilisées, purge le tampon de requêtes */
 +FLUSH TABLES
 +
 +/* Purge le cache des hôtes lorsqu'ils changent d'IP ou lorsqu'ils génèrent trop de connexions */
 +FLUSH HOSTS
 +</code>
 +</WRAP>
 +Plus d'informations [[http://dev.mysql.com/doc/refman/5.5/en/flush.html|ici]]
 +
 +===== Changement de mot de passe =====
 +<WRAP prewrap>
 +<code sql>
 +SET PASSWORD = PASSWORD('<password>')
 +</code>
 +</WRAP>
 +
 +===== Changer les droits d'un utilisateur =====
 +<WRAP prewrap>
 +<code sql>
 +/* Pour donner les droits à tous les hôtes, il faut utiliser % */
 +GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'<host>';
 +
 +FLUSH PRIVILEGES;
 +</code>
 +</WRAP>
 +
 +===== Récupérer la liste des index d'une base de données =====
 +<WRAP prewrap>
 +<code sql>
 +SELECT DISTINCT s.INDEX_SCHEMA
 +, s.INDEX_NAME
 +, s.TABLE_NAME
 +FROM INFORMATION_SCHEMA.STATISTICS s
 +LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t ON t.TABLE_SCHEMA = s.TABLE_SCHEMA AND t.TABLE_NAME = s.TABLE_NAME
 +AND s.INDEX_NAME = t.CONSTRAINT_NAME
 +WHERE t.CONSTRAINT_NAME IS NULL
 +AND s.TABLE_SCHEMA = '<database>'
 +</code>
 +</WRAP>
 +
 +===== Gérer les processus en cours =====
 +<WRAP prewrap>
 +<code sql>
 +-- Affiche la liste des processus en cours
 +show processlist;
 +
 +-- Arrête un processus en le sélectionnant par son id
 +kill <id>;
 +</code>
 +</WRAP>
 +
 +===== Comparer le schéma de deux tables =====
 +<WRAP prewrap>
 +<code sql>
 +SELECT COLUMN_NAME
 +, ORDINAL_POSITION
 +, DATA_TYPE
 +, COLUMN_TYPE
 +FROM information_schema.`COLUMNS`
 +WHERE TABLE_SCHEMA = DATABASE()
 +AND TABLE_NAME IN ('<table1>', '<table2>')
 +GROUP BY COLUMN_NAME
 +, ORDINAL_POSITION
 +, DATA_TYPE
 +, COLUMN_TYPE
 +HAVING COUNT(1)=1
 +</code>
 +</WRAP>
 +La requête précédente est un peu violente car elle valide que les champs sont à la même position.
 +En voici une qui est moins restrictive car elle ne fait pas cette vérification :
 +<WRAP prewrap>
 +<code sql>
 +SELECT COLUMN_NAME
 +, DATA_TYPE
 +, COLUMN_TYPE
 +FROM information_schema.`COLUMNS`
 +WHERE TABLE_SCHEMA = DATABASE()
 +AND TABLE_NAME IN ('<table1>', '<table2>')
 +GROUP BY COLUMN_NAME
 +, DATA_TYPE
 +, COLUMN_TYPE
 +HAVING COUNT(1)=1
 +</code>
 +</WRAP>
 +
 +===== Récupérer la liste des tables avec une colonne manquante =====
 +Dans certaines tables, on travaille avec les couples de colonnes ''createdAt''/''createdBy'', ''updatedAt''/''updatedBy'' et/ou ''deletedAt''/''deletedBy''. Mais parfois, on oublie l'une des deux colonnes. Trouver ces tables n'est pas un vrai problème quand on n'a que quelques tables mais avec plusieurs centaines, il vaut mieux écrire une requête appropriée.
 +<WRAP prewrap>
 +<code sql>
 +SELECT @tableSchema := 'database'
 +, @columnName := 'deleted';
 +
 +-- Première écriture de la requête (naïve). Le temps d'exécution est très long (≈ 10 secondes).
 +SELECT t.TABLE_NAME
 +FROM information_schema.TABLES AS t
 +WHERE t.TABLE_SCHEMA = @tableSchema
 +AND EXISTS (
 + SELECT 1
 + FROM information_schema.`COLUMNS` AS c
 + WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA
 + AND c.TABLE_NAME = t.TABLE_NAME
 + AND c.COLUMN_NAME = CONCAT(@columnName, 'At')
 +)
 +AND NOT EXISTS (
 + SELECT 1
 + FROM information_schema.`COLUMNS` AS c
 + WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA
 + AND c.TABLE_NAME = t.TABLE_NAME
 + AND c.COLUMN_NAME = CONCAT(@columnName, 'By')
 +);
 +
 +-- Seconde écriture de la requête. Le temps d'exécution est nettement meilleur (≈ 30 milli-secondes) pour un résultat identique.
 +SELECT c.TABLE_NAME
 +FROM information_schema.`COLUMNS` AS c
 +WHERE c.TABLE_SCHEMA = @tableSchema
 +AND c.COLUMN_NAME LIKE CONCAT(@columnName, '%')
 +GROUP BY c.TABLE_NAME
 +HAVING COUNT(1) = 1;
 +</code>
 +</WRAP>
 +
 +===== Calculer la taille des données =====
 +<WRAP prewrap>
 +<code sql>
 +-- Afficher la taille globale de l'ensemble des bases de données
 +SELECT TABLE_SCHEMA AS "Nom de la base de données"
 +, CONCAT(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 0), " MB") AS "Taille de la base de données"
 +FROM information_schema.TABLES
 +GROUP BY TABLE_SCHEMA
 +
 +-- Afficher le détail de la taille des tables d'une base de données
 +SELECT TABLE_NAME AS "Nom de la table"
 +, TABLE_ROWS AS "Nombre de lignes"
 +, CONCAT(ROUND(DATA_LENGTH / 1024 / 1024, 2), " MB") AS "Taille des données"
 +, CONCAT(ROUND(INDEX_LENGTH / 1024 / 1024, 2), " MB") AS "Taille de l'index"
 +, CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2), " MB") AS "Taille de la table"
 +FROM information_schema.TABLES
 +WHERE TABLE_SCHEMA = "resamania"
 +ORDER BY (DATA_LENGTH + INDEX_LENGTH)
 +, TABLE_NAME
 +</code>
 +</WRAP>
 +
 +===== Logguer les requêtes =====
 +<WRAP prewrap>
 +<code sql>
 +-- Définition de la destination (mysql.general_log)
 +SET GLOBAL log_output = 'TABLE';
 +-- Activation du logging
 +SET GLOBAL general_log = 'ON';
 +-- Désactivation du logging
 +SET GLOBAL general_log = 'OFF';
 +-- Purge de la table (uniquement quand le logging est arrêté)
 +TRUNCATE table mysql.general_log
 +</code>
 +</WRAP>
 +
 +<WRAP info>
 +Pour logguer dans un fichier :
 +<WRAP prewrap>
 +<code sql>
 +-- Définition de la destination
 +SET GLOBAL log_output = 'FILE';
 +-- Définition du fichier
 +SET GLOBAL general_log_file = "</path/to/file>";
 +</code>
 +</WRAP>
 +</WRAP>