Requêtes d'administration

Affichage des erreurs

SHOW ERRORS

Plus d'informations ici

Affichage des alertes

SHOW WARNINGS

Plus d'informations ici

Affichage de la description d'une table

DESC <table>

Plus d'informations ici

Purge des caches

/* 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

Plus d'informations ici

Changement de mot de passe

SET PASSWORD = PASSWORD('<password>')

Changer les droits d'un utilisateur

/* Pour donner les droits à tous les hôtes, il faut utiliser % */
GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'<host>';
 
FLUSH PRIVILEGES;

Récupérer la liste des index d'une base de données

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>'

Gérer les processus en cours

-- Affiche la liste des processus en cours
SHOW processlist;
 
-- Arrête un processus en le sélectionnant par son id
KILL <id>;

Comparer le schéma de deux tables

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

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 :

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

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.

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;

Calculer la taille des données

-- 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

Logguer les requêtes

-- 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

Pour logguer dans un fichier :

-- Définition de la destination
SET GLOBAL log_output = 'FILE';
-- Définition du fichier
SET GLOBAL general_log_file = "</path/to/file>";