Vous n’êtes pas sans savoir que derrière chaque site web se cache une base de données. Si vous développez des sites ou applis web, que ce soit en PHP, Ruby ou Python, vous devriez savoir maîtriser MySQL en ligne de commande.

À la question, “à quoi ça sert ?”, je répondrai que PhpMyAdmin n’est pas forcement installé sur le serveur que l’on doit administrer, et dans ce cas, il va falloir goûter à l’interface en ligne de commande. En outre, ce sont les mêmes commandes qui vous servent à faire les requêtes dans votre code, alors autant les apprendre ou les réviser !

Si vous n’êtes pas bien familier du SQL et que les différents types de colonnes de MySQL vous sont inconnus, vous devriez peut-être commencer par jeter un oeil à mon précédent article sur les types de champs MySQL. Sinon, on peut commencer !

Avant toute chose, on oublie assez souvent qu’un tutoriel ne peut en aucun cas se substituer à une documentation exhaustive de l’outil. Voici donc tout de suite le lien vers la doc officielle de MariaDB, bien mieux que celle de MySQL]. Nous pouvons maintenant vraiment commencer !

Note : dans les commandes SQL, beaucoup de paramètres sont facultatifs. Ceux-ci seront entourés de crochets comme cela :

une_commande [param_facultatif [un_sous_param_facultatif]] [un_autre_param_facultatif];

C’est cette notation que vous retrouverez dans la doc officielle.

Connexion

La première chose à faire lorsque l’on veut utiliser MySQL en ligne de commande est déjà de s’y connecter. Pour ça, une fois dans votre shell, tapez, mysql -h hôte_mysql -u username -p. Dans l’exemple ci-dessous, MySQL demande ensuite le mot de passe, vous n’avez plus qu’à entrer le mot de passe avant de vous retrouver dans l’interpréteur de MySQL.

Dans les dernières version de Debian/Ubuntu, le mot de passe n’est pas nécessaire lorsque l’on est root sur la machine et que l’on veut se connecter avec l’utilisateur root de MySQL. L’argument -p n’est donc pas nécessaire.

mysql -h localhost -u root
Enter password:

Encodage de la connexion

Nous allons le voir juste après, vous définissez un encodage pour les données que vous stockez. Aussi, lors de la connexion, il est important de s’assurer que vous communiquez dans la bonne langue avec la base de données.

Cette option permet l’échange de données entre MySQL et un client n’utilisant pas le même encodage. Ainsi, MySQL se charge de dynamiquement convertir toute les données sortantes dans l’encodage du client et inversement, de les convertir dans le bon format lors de la réception.

Cependant, si votre shell est en UTF-8 et que vous utilisez une connexion en Latin-1, il va y avoir une conversion du set de caractères aboutissant à une corruption des données. Qu’il s’agisse donc de la connexion au client MySQL en CLI ou depuis une application web (PHP, Python, etc) veillez à toujours bien définir le jeux de caractère de la connexion.

Pour le client MySQL, vous pouvez définir l’encodage par défaut dans /etc/mysql/mariadb.conf.d/50-client.cnf ou directement via l’option --default-character-set. Le plus simple pour s’assurer que tout fonctionne correctement est de réaliser un test avec des caractères spéciaux ; par exemple : “éêà ù®ñ€ 😅”.

Gestion des bases

C’est super, on est connecté, mais jusque là, ça ne sert pas à grand chose. On va donc voir comment créer, modifier et supprimer des bases de données.

Créer une base

Pour créer une base, la syntaxe n’est pas bien complexe.

CREATE DATABASE nom_de_la_base

Il est possible de spécifier directement à la création de la base l’encodage de caractères que l’on veut utiliser. Pour cela, il faut ajouter des arguments à la commande de création de BDD. Par exemple, pour un encodage en UTF-8 :

CREATE DATABASE maBase DEFAULT CHARACTER SET utf8mb4;

Il existe en plus du set de caractères, ce qu’on appelle l’interclassement, ou collation. L’interclassement précise au set de caractères les spécificités d’une langue. Par exemple, que le “E” est semblable au “e” ou au “é”. Si on ne précise pas d’interclassement, MySQL choisi celui qui est par défaut avec le set de caractères choisi.

# consulter les charset disponibles
SHOW CHARACTET SET;

# consulter les collations disponibles
SHOW COLLATION

Voici un exemple de définition pour l’UTF-8 avec une collation unicode multilingue insensible à la casse :

CREATE DATABASE maBase DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

On a a peu près fait le tour de la commande de création de bases. La commande générique complète est donc la suivante :

CREATE DATABASE db_name [DEFAULT CHARACTER SET character_set_name [COLLATE collation_name]];

Modifier une base

En fait, il n’y a pas grand chose à modifier sur une base, à part le charset et la collation. Voici donc la commande :

ALTER DATABASE nom_de_la_base CHARACTER SET character_set_name COLLATE collation_name;

Supprimer une base

Maintenant, pour la supprimer, commande au combien complexe :

DROP DATABASE nom_de_la_base;

Travailler dans une base

Il est possible d’avoir plusieurs bases de données avec MySQL, on doit donc lui préciser dans laquelle on va travailler. Pour cela, il faut tout simplement utiliser la commande USE nom_BDD. Cette commande, contrairement aux autres, ne doit pas obligatoirement se terminer par un point-virgule.

Gestion des tables

On sait maintenant créer, modifier et supprimer une base, il est grand temps de la peupler un peu !

Les moteurs de tables

MySQL permet de choisir entre différents moteurs de tables. Parmi ceux-ci, deux sont plus connus. INNODB, qui est le moteur par défaut, et MyISAM qui est un autre moteur très utilisé. Lequel choisir ?

Il n’y en a pas un de meilleur que l’autre, tout dépendra de votre utilisation et de vos besoins en fonctionnalités. MyISAM permet l’index FULLTEXT (si vous ne savez pas de quoi il s’agit nous en parlerons plus tard). C’est très puissant pour effectuer des recherches dans la table, en revanche, elle ne gère pas les clefs étrangères ni les transactions (nous verrons tout ça plus tard aussi). À l’inverse, INNODB ne dispose pas du FULLTEXT mais elle sait gérer les clefs étrangères et les transactions.

Il faut aussi prendre en considération le fait que MyISAM utilise un verrou lors de l’ajout ou la modification de données, ce qui pose problème lorsque la table est très sollicitée en écriture puisque qu’il va se créer une file d’attente. De ce fait, si votre application requiert de nombreuses modifications parallèles sur la base de données – un blog avec des commentaires ou un forum par exemple – vous devrez utiliser une base INNODB.

Bien entendu, il existe d’autres moteurs de tables. Pour plus d’info sur les deux sus-cités ou sur les autres moteurs disponibles et leurs fonctionnalités, il y a toute une partie de la doc qui traite du sujet.

Créer une table

La syntaxe pour créer une table est assez courte :

CREATE TABLE nom_table;

Vous voilà avec une table toute neuve… et toute vide. Il est bien entendu possible de créer une table vide et d’ajouter des colonnes ensuite, cependant, on préférera généralement directement créer une table complète. Syntaxe :

CREATE TABLE nom_table (
    colonne_1 type de champs [NULL | NOT NULL] [AUTO_INCREMENT] [DEFAULT 'valeur'],
    [colonne_2 type de champs [NULL | NOT NULL] [AUTO_INCREMENT] [DEFAULT 'valeur']]
    …
    [PRIMARY KEY (nom_colonne)]
    [DEFAULT CHARACTER SET character_set_name [COLLATE collation_name]]
)
[ENGINE = MYISAM | INNODB];

Oui, je ne vous l’ai pas montré dans l’exemple, mais on peut aussi définir charset et collation au niveau des tables. Plus fort, on peut même les spécifier pour chaque colonne ! Au niveau de la table, l’utilisation est simple. C’est exactement la même que lorsqu’on définit au niveau de la base, mais pour les colonnes. Cette utilisation étant nettement moins fréquente, le mieux est que vous consultiez la doc au besoin.

Petit point sur la définition des colonnes : vous devez pour chaque colonne choisir parmi différents types de données. Si vous ne les connaissez pas, je vous invite à lire mon précédent article sur les types de champs MySQL.

Ensuite, vous pouvez spécifier si vous autorisez les champs à avoir une valeur nulle (être vide). AUTO_INCREMENT vous permet, dans le cas d’un INT (et de ses dérivés), d’incrémenter pour chaque nouvel enregistremet la valeur de 1. Par conséquent, les valeurs des champs sont automatiquement définies.

Enfin, vous pouvez précisez une valeur par défaut, c’est à dire que, si vous ne précisez aucune valeur lors de l’ajout de données, la valeur du champs prendra alors comme valeur la valeur par défaut.

Vous vous demandez peut-être à quoi correspond PRIMARY KEY. Vous pouvez voir dans l’exemple ci-dessous que j’utilise une colonne id, et que je place une clef primaire sur cette colonne. En effet, si vous avez deux membres avec le même pseudo, la colonne id permettra de les différencier. Une clef primaire est une contrainte (nous verrons les contraintes un peu plus loin), elle doit être unique.

Prenons pour exemple une table dans laquelle nous allons stocker les pseudos des membres d’un site.

CREATE TABLE membres (
    id SMALLINT NOT NULL AUTO_INCREMENT,
    pseudo char(255) NOT NULL,
    PRIMARY KEY (id)
)
ENGINE=INNODB;

Modifier une table

Pour modifier une table, on utilisera la commande ALTER TABLE avec les arguments ADD, DROP, CHANGE ou MODIFY.

-- ajouter une colonne
ALTER TABLE test
ADD nom_colonne desc [AFTER colonne];

-- supprimer une colonne
ALTER TABLE test DROP COLUMN nom;

Il est possible de préciser la position de l’insertion avec AFTER.

Pour modifier une colonne, c’est un tout petit peu plus complexe. Vous vous doutez bien qu’il faudra utiliser les commandes CHANGE et MODIFY pour modifier une colonne. Quelle est la différence entre les deux ? Pas bien grande à vrai dire. CHANGE permet de renommer la colonne tandis que MODIFY ne le permet pas.

-- CHANGE permet de renommer une colonne
ALTER TABLE nom_table
CHANGE nom_colonne nouveau_nom_colonne TYPE [NULL|NOT NULL] [AUTO_INCREMENT];
-- ici on s'est donc contenté de changer le nom

-- MODIFY permet de faire tout le reste
MODIFY nom_colonne TYPE [NULL|NOT NULL] [AUTO_INCREMENT] [DEFAULT 'valeur par defaut']

-- CHANGE a les même fonctionnalités mais on peut renommer en même temps
CHANGE pseudo pseudo_forum VARCHAR(50) NOT NULL

Renommer une table

La commande est à un mot près, la même que pour renommer une base, de la même manière, on ne l’utilisera pas tous les jours – c’est d’ailleurs préférable – mais il peut arriver qu’on soit amené à modifier un nom de table.

RENAME TABLE ancien_nom TO nouveau_nom;

Supprimer une table

Comme pour supprimer une base, supprimer une table s’avère assez facile. Finalement tellement facile que, comme pour une base, on a parfois trop vite fait de la supprimer. Ces commandes sont sans appel, une fois supprimées, vous ne pourrez plus récupérer vos données. Alors prenez gare !

DROP TABLE nom_table

Gestion des utilisateurs

Par défaut, lorsque l’on installe la base, il n’y qu’un seul utilisateur de créé : root – où des utilisateurs spécifiquement dédiés aux tâches de fonds. Bien qu’en développement on puisse connecter nos applications avec root, un utilisateur qui a tous les droits sur toutes les tables représente toujours un cetain danger… surtout s’il vient à être compromis !

Les bonnes pratiques en terme de sécurité consistent à créer un utilisateur par application et de ne lui attribuer que les droits dont il a strictement besoin.

Créer un utilisateur

Il suffit d’utiliser la commande suivante :

CREATE USER 'buzut'@'localhost' IDENTIFIED BY 'XXXX';

Vous devinez bien entendu que “buzut” est à remplacer par le nom d’utilisateur et que “XXXX” doit être remplacé par un mot de passe de votre choix. Néanmoins, que signifie “localhost” ?

Chaque utilisateur est identifié par son nom et son mot de passe mais aussi l’hôte depuis lequel il se connecte. Ainsi, si vous voulez que l’utilisateur “buzut” puisse se connecter depuis une machine distante directement via le client MySQL (donc sans se connecter en ssh d’abord), il faudra que vous créiez un autre utilisateur avec l’ip adéquate après le “@”.

Il y a également une alternative moins sécurisée puisqu’elle permet d’autoriser tous les hôtes, il suffit pour cela de renseigner le signe pourcentage “%” à la place de l’hôte, comme ceci : 'buzut'@'%'.

Il peut aussi être bon de connaître la commande pour changer le mot de passe d’un utilisateur à posteriori. Notez que vous devez avoir des droits suffisants pour changer le mot de passe d’un autre utilisateur que celui avec lequel vous êtes connecté.

SET PASSWORD [FOR 'un_user'@'localhost'] = PASSWORD('nouveau_mdp');

Il est inutile de préciser FOR 'un_user'@'localhost' si vous changez le mot de passe de l’utilisateur courant.

Accorder des droits

Il y a plusieurs grands types de droits, les droits d’administration, les droits de schémas et les droits de tables. Les droits d’administration s’appliquent sur toutes les bases et, comme leurs noms l’indiquent, les droits de schémas et de tables s’appliquent respectivement à une base et à une table.

Juste avant de vous montrer comment on définit les droits, il serait peut-être bon de savoir comment on affiche les droits relatifs à un utilisateur. C’est très simple.

SHOW GRANTS FOR 'user'@'host';

Droits d’administration

Les droits d’administration s’appliquent sur toutes les bases de données :

GRANT ALL PRIVILEGES ON *.* TO 'superadmin'@'localhost';

Le tableau ci-dessous représente les différents droits d’administration.

Droits au niveau des bases

GRANT SELECT, INSERT, UPDATE, DELETE ON buzut.* TO 'buzut_app'@'localhost';

Le tableau ci-dessous représente les différents droits s’appliquant aux bases.

Droits au niveau des tables

On descend encore ici d’un niveau. En effet, nous attribuons ici les droits au niveau des tables. Par exemple, dans la base “buzut”, nous pourrions décider que l’utilisateur “buzut_app” possède tous les droits sur users, mais seulement INSERT et SELECT sur la table “payments”, afin qu’in ne puisse pas supprimer des paiements passés.

GRANT SELECT, INSERT, UPDATE, DELETE ON buzut.users TO 'buzut_app'@'localhost';
GRANT SELECT, INSERT ON buzut.payments TO 'buzut_app'@'localhost';

Les droits sont dans l’ensemble les mêmes que ceux s’appliquant aux bases, nous retrouvons ALTER, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT, UPDATE.

Le seul nouveau est TRIGGER, lequel permet de créer ou supprimer des déclencheurs (ou triggers).

Droits au niveau des colonnes

Nous arrivons à la plus faible granularité, il s’agit ici de donner les droits seulement sur certaines colonnes. Nous retrouverons seulement INSERT, SELECT et UPDATE. Les droits s’appliquent de la manière suivante :

# donne les droits en lecture et update pour nom et prénom de la table users mais seulement lecture pour le payments_id
GRANT SELECT(first_name, last_name, payments_id), UPDATE(first_name, last_name) ON buzut.users TO 'buzut_app'@'localhost';

Révoquer des droits

Donner c’est donner, reprendre c’est voler ! Mais c’est aussi plus sécurisé que de faire preuve de trop de générosité…

Pour enlever des droits précédemment GRANTed, on utilisera la commande REVOKE :

# on révoque DELETE à buzut_app sur toute la base buzut
REVOKE DELETE on buzut.* FROM 'buzut_app'@'localhost';

# on révoque SELECT et INSERT sur la table users de la base buzut
REVOKE SELECT, INSERT on buzut.users FROM 'buzut_app'@'localhost';

Gestion des données

On a une base de données, une ou plusieurs tables dans cette base, mais on voudrait maintenant travailler dans ces tables : ajouter, modifier et supprimer des données, c’est quand même le but d’une BDD !

Insertion de données

Il y a deux façons d’insérer des données :

• Soit on ne précise pas les colonnes, dans ce cas il faudra spécifier toutes les données et dans le même ordre que les colonnes dans la table,
• Soit on précise d’abord les colonnes que l’on renseigne, dans ce cas, on n’est pas obligé de toutes les spécifier et on peut les déclarer dans n’importe quel ordre.

Sans préciser les colonnes

INSERT INTO nom_table
VALUES('', '', '', …);

En précisant les colonnes

INSERT INTO nom_table (colonne1, colonne4, colonne2)
VALUES ('data_colonne1', 'data_colonne4', 'data_colonne2');

Cette manière de procéder paraît un peu plus longue à écrire, cependant, elle est bien plus claire dans la mesure où l’on voit immédiatement le nom des colonnes dans lesquelles on va insérer des données. En outre, elle est plus puissante car elle permet des insertions multiples. Voyez ci-dessous :

INSERT INTO nom_table (colonne1, colonne4, colonne2)
VALUES ('data_colonne1', 'data_colonne4', 'data_colonne2'),
       ('data2_colonne1', 'data2_colonne4', 'data2_colonne2');

Enfin, MySQL propose une autre manière d’insérer les données. Cette méthode est propre à MySQL et ne fait pas partie du standard SQL. Cependant, elle a l’avantage d’avoir une syntaxe proche de celle utilisée pour la modification d’enregistrements.

INSERT INTO nom_table SET colonne1 = 'data_colonne1', colonne2 = 'data_colonne2', colonne3 = 'data_colonne3';

Modification de données

On va utiliser la commande UPDATE pour modifier des données déjà enregistrée.

UPDATE nom_table
SET col1 = val1 [, col2 = val2, ...]
[WHERE ...];

Attention à bien préciser la clause WHERE qui permet de localiser la ligne dans laquelle vous voulez modifier une valeur. Sans elle, toutes les lignes de la colonne concernée seront modifiées ! Nous reparlerons de WHERE plus en détails juste après.

Prenons comme exemple une table qui représente notre collection de films. Cette table contient quatre colonnes : une colonne id, titre, description, format. Si on veut modifier le titre du film “avatar”, voici la requête qu’il faut :

UPDATE films
SET titre = 'Avatar 2'
WHERE titre = 'avatar';

Évidemment, on peut modifier plusieurs ligne à la fois. Mettons que l’on veuille préciser dans la description le format des films en mp4 :

UPDATE films
SET description = 'Ce film a été encodé en mp4'
WHERE format = 'mp4';

Automatiquement, toutes les descriptions des films ayant pour format mp4 seront changées. Vous comprenez donc surement l’utilité du WHERE, qui ajoute une condition. Sans cette condition, toutes les lignes sont concernées !

Suppression des données

La suppression de données est chose aisée. Pareil, utilisez bien le WHERE, aucune confirmation ne vous est demandée, et si vous avez oublié le WHERE, dites au-revoir à toutes les données de votre table !

DELETE FROM nom_table
[WHERE critères];

Je ne vous redonne pas d’exemple, le fonctionnement est exactement le même que pour UPDATE. Vous saurez vous débrouiller, j’en suis certain.

Sélection et affichage

Dans cette partie, nous allons seulement demander à MySQL de nous envoyer les données qui sont enregistrées dans la base, mais bien entendu, pas toutes les données, juste celle dont nous avons besoin. Et mine de rien, c’est là que nous allons passer le plus clair de notre temps.

La syntaxe de base est la suivante :

SELECT colonne1, [colonne2, …]
FROM nom_table
[WHERE arguments [ORDER BY DESC|ASC]] [LIMIT un_nombre [OFFSET un_nombre]]

Simple et complexe à la fois. On se rend bien compte que si on n’utilise pas trop les arguments facultatifs, finalement, c’est ultra-simple (mais pas très puissant), inversement, ça se complique avec les autres arguments (mais on grimpe en puissance). On va donc faire le tour de ces arguments, histoire d’être à l’aise avec eux.

Préciser les conditions

On a déjà brièvement vu à quoi servait WHERE et comment s’en servir. Petit rappel, WHERE permet d’ajouter une ou des conditions à la requête. Seulement jusque là, on l’a toujours utilisé avec l’opérateur =, alors que bien d’autres opérateurs existent.

Je crois qu’une explication s’impose pour la dernière ligne du tableau. On ne peut en fait pas faire de comparaison directe avec une valeur qui est égale à NULL (absence de valeur en fait), cependant, avec l’opérateur “<=>”, on peut demander les colonnes qui n’ont pas de valeur.

Il est possible d’adjoindre des opérateurs logiques à ces opérateurs de comparaison. La combinaison des deux ajoute encore un peu de complexité (pour beaucoup de puissance supplémentaire).

Comme dans beaucoup de langages de programmation, vous pouvez utiliser indifféremment l’opérateur ou le symbole. Ainsi, WHERE id=2 OR id=6 est équivalent à WHERE id=2 || id=6.

Prenons un exemple concret, on va rester sur notre table de films, pour mémoire, elle contient les colonnes suivantes : id, titre, description, format.

-- sélection de toutes les colonnes des films au format mp4 et webm
SELECT *
FROM films
WHERE format = 'mp4' OR format = 'webm';

Pour ceux qui se demandent ce que signifie l’étoile *, elle veut dire tout. Ici, elle permet donc de sélectionner toutes les colonnes sans avoir besoin de les lister une par une. Cette notation est à utiliser avec parcimonie, et est à éviter lorsque vous n’avez pas besoin de sélectionner toutes les colonnes de votre base, surtout si celle-ci sont nombreuses.

En effet, MySQL sélectionnera toutes les colonnes, ce qui ralentira potentiellement le traitement de la requête. Ainsi, lorsque vous le faites manuellement dans l’invite de commande MySQL, si la commande met une seconde à s’exécuter n’a pas grande importance, cependant, si vous écrivez ceci dans vos codes pour une application, cette dernière risque de se retrouver ralentie inutilement.

Ordonner la sélection

Il est souvent bien pratique de classer notre sélection par ordre croissant, décroissant ou alphabétique. On utilisera pour cela la commande ORDER BY. Elle n’est pas bien compliquée à utiliser, on lui dit quelle colonne servira au classement, et si on veut un ordre croissant ESC ou décroissant DESC. On reprend la requête des films pour l’exemple :

-- sélection de toutes les colonnes des films au format mp4 et webm
-- et tri alphabétique selon le titre du film
SELECT *
FROM films
WHERE format = 'mp4' OR format = 'webm'
ORDER BY titre;

-- on veut par exemple avoir en premier les derniers films ajoutés,
-- donc les ids les plus grands d'abord
SELECT *
FROM films
WHERE format = 'mp4' OR format = 'webm'
ORDER BY id DESC;

Vous remarquez que je n’ai pas précisé ESC dans le premier exemple, la valeur par défaut de ORDER BY étant l’ordonnancement alphabétique ou croissant, il est facultatif de le préciser.

Eliminer les doublons

Mettons que l’on veuille savoir tous les âges représentés par les personnes enregistrées sur notre site. Si on fait :

SELECT ages
FROM membres
ORDER BY ages;

Alors on aura 10 entrées pour l’age 14 ans, 150 pour 20 ans… Pas très pratique pour une vue d’ensemble. On peut donc utiliser DISTINCT qui, comme son nom le laisse à penser, ne sélectionne une valeur que si elle est différente d’une déjà sélectionnée.

SELECT DISTINCT ages
FROM membres
ORDER BY ages;

On aura donc la liste de tous les âges représentés par les membres de notre site, sans doublons, et ordonnée par ordre croissant (ORDER BY).

Grouper pour mieux régner

Ok normalement c’est l’inverse… Pour autant, en SQL, grouper les résultats est d’une extraordinaire puissance car c’est moins d’informations inutiles à traiter.

Admettons que nous ayons un blog avec un système de commentaires. Chaque commentaire possède notamment l’email de son auteur. Si on veut récupérer l’email de tous les auteurs, inutile d’afficher autant de lignes que de fois que chaque personne à commenté. On dira donc à MySQL de grouper le résultat par email comme ceci :

SELECT email
FROM comments
GROUP BY email;

Ça représente immédiatement un sacré gain en clarté ! Et puis si vous deviez traiter ces données avec un autre langage après, vous gagnez en concision dans votre code mais aussi en performance (MySQL n’envoie pas de données inutiles).

Pour la route, admettons que nous voulions compter le nombre de commentaires de chaque auteur ? Facile.

SELECT count(email), email
FROM comments
GROUP BY email;

COUNT() est une fonction native SQL dont nous ne parlons pas ici. MySQL propose de nombreuses fonctions très puissante et il n’est pas possible de toutes les lister. As usual, la doc est votre amie.

Prendre une petite partie de la sélection

Parfois, on ne voudra que les x premiers résultats de notre sélection. On va pour ça utiliser la commande LIMIT. Elle prend deux paramètres :

• Le premier est le nombre de lignes à sélectionner,
• Le second, OFFSET, précise les x premières lignes à ignorer.

-- n'affiche que les 10 premiers résultats
SELECT pseudos
FROM table_membres
ORDER BY pseudos
LIMIT 10;

On peut aussi vouloir commencer l’affichage à partir de n ligne. On précisera donc l’OFFSET a n-1. En effet, l’OFFSET ignore les x premières lignes que vous lui passez en paramètre, et commence la sélection à la ligne suivante.

-- commence à la 5eme ligne et affiche 10 ligne, donc les ligne 5 à 15
SELECT pseudos
FROM table_membres
ORDER BY pseudos
LIMIT 10 OFFSET 4;

On peut aussi écrire la commande sans le mot-clef OFFSET, dans ce cas, on commence par préciser l’OFFSET puis, séparé par une virgule, le nombres de lignes à sélectionner :

SELECT pseudos
FROM table_membres
ORDER BY pseudos
LIMIT 4, 10;

LIKE, plus de puissance pour WHERE

LIKE permet l’utilisation de joker, comme ?, + ou * dans les expression régulières. Ces jokers sont _ et % :

• _ représente 0 ou 1 caractère,
• % représente 0, 1 ou plus caractères.

-- sélectionne tous les titres qui contiennent l'expression "james bond"
SELECT titre
FROM films
WHERE titre LIKE '%james bond%';

Tous les titres contenant l’expression “james bond” seront sélectionnés car on a placé un signe % avant et après l’expression. En revanche, si on ne l’avait placé qu’après par exemple, la sélection aurait fonctionné pour un titre comme “james bond – golden eye” mais pas pour “golden eye – james bond”.

On peut aussi utiliser NOT LIKE. De plus, si on veut que la recherche soit sensible à la casse, il faut rechercher en binaire :

SELECT titre
FROM films
WHERE titre BINARY LIKE '%james bond%';

BETWEEN pour les intervalles

Pour sélectionner une intervalle, par exemple une date. On pourrait dire qu’il faut que ce soit plus grand que telle date, et moins que telle autre.

SELECT *
FROM membres
WHERE birthdate <= '2000' AND >= '1990';

On aurait donc les dates de naissances comprises entre 90 et 2000. Mais on peut le faire plus facilement et plus élégamment ! La commande BETWEEN est faite pour ça :

SELECT *
FROM membres
WHERE birthdate BETWEEN '2000' AND '1990';

BETWEEN marche aussi pour les lettres, dans ce cas c’est évidemment l’ordre alphabétique qui est pris en compte, en ne tenant pas compte de la casse, sauf à utiliser BINARY. Bien entendu, on peut aussi utiliser NOT BETWEEN.

OR en chaine, c’est de l’or en barre

C’est un peu contraignant d’accumuler les OR pour préciser des critères. On a donc une écriture plus rapide pour cela :

SELECT *
FROM voyage
WHERE ville IN('madrid', 'paris', 'roma', 'kingston');

C’est plus rapide ET plus lisible. À utiliser sans modération !

Description des tables et des bases

Il peut être très utiles de décrire les tables et les bases. Par exemple, on ne se souvient plus de toutes les tables contenues dans une base, ou alors des colonnes et de leur types dans une table, il y a une solution à ça !

Pour voir toutes les tables contenues dans une base, on se place dans celle-ci (avec use database), puis :

SHOW TABLES;

# voir toutes les bases
SHOW DATABASES;

DESCRIBE Videos;
+------------------+-----------------------+------+-----+---------+----------------+
| Field            | Type                  | Null | Key | Default | Extra          |
+------------------+-----------------------+------+-----+---------+----------------+
| id               | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| name             | char(23)              | NO   | MUL | NULL    |                |
| length           | varchar(10)           | YES  |     | NULL    |                |
| size             | smallint(5) unsigned  | YES  |     | NULL    |                |
+------------------+-----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

Mais parfois, les descriptions de tables ne sont pas assez verbeuses… Si on veut par exemple connaitre le CHARSET et la collation. Voici donc la commande à utiliser :

SHOW CREATE TABLE Audios;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Audios | CREATE TABLE `Audios` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(23) COLLATE utf8_unicode_ci NOT NULL,
  `length` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `size` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=665 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

En dernier lieu, si l’on veut connaître le charset et la collation, non pas de la table, mais de la base, on peut faire une requête sur la base information_schema. Exemple :

SELECT default_character_set_name FROM information_schema.SCHEMATA
WHERE schema_name = "buzut";
+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8mb4                    |
+----------------------------+
1 row in set (0.00 sec)

SELECT default_collation_name FROM information_schema.SCHEMATA
WHERE schema_name = "buzut";
+----------------------------+
| default_collation_name     |
+----------------------------+
| utf8mb4_general_ci         |
+----------------------------+
1 row in set (0.00 sec)

Fonctions de base

MySQL recèle de nombreuses fonctions utiles, voyons-en quelques unes.

Vous voulez connaître la version du moteur que vous utilisez ?

SELECT VERSION();
+----------------------------------+
| VERSION()                        |
+----------------------------------+
| 10.1.38-MariaDB-0ubuntu0.18.04.1 |
+----------------------------------+
1 row in set (0.00 sec)

Connaître l’utilisateur avec lequel vous êtes connectés :

SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

Récupérer le dernier ID généré avec auto_increment. Ceci est particulièrement intéressant puisqu’il arrive souvent que lorsque plusieurs tables sont liées (avec des clefs étrangères afin de faire des jointures etc), on doive récupérer l’id d’une requête pour l’insérer dans une seconde table.

Évidemment, il n’est pas du tout indiqué de refaire une requête pour récupérer l’id. En effet, si de nombreux utilisateurs sont connectés à la base simultanément (site web à fort trafic), il se peut qu’une autre insertion ait eu lieu entre le moment où vous avez fait votre insertion dans la table A et le moment où vous tentez de récupérer son id ; auquel cas, vous aurez un résultat erroné.

Il existe une fonction permettant d’éviter ces problèmes : LAST_INSERT_ID(), elle retourne le dernier id inséré pour la connexion utilisée ; donc si une autre donnée a été inséré entre temps, pas de problème ! Elle vous évite de faire une requête intermédiaire inutile et d’obtenir des résultats incohérents. Nos requêtes précédentes ressembleraient donc à ceci :

# on insert une donnée dans la table A
INSERT INTO Table_a (ma_colonne) VALUES ('ma donnée');

# On insert notre donnée dans la table B
INSERT INTO Table_b (ma_colonne, ma_reference_table_a) VALUES ('ma donnée', LAST_INSERT_ID());

On ne peut pas ici faire une liste exhaustive, mais voici quelques unes des fonctions souvent utiles :

• SUM et COUNT permettent de faire des additions et de compter les données courantes,
• MIN, MAX et AVG permettent respectivement de sélection la valeur minimale, maximale et de calculer une moyenne,
• CONCAT permet de concaténer plusieurs valeurs,
• si vous travaillez avec les dates, les fonctions date & time vous seront certainement utiles pour faire des comparaisons, des regroupement et des calculs sur vos dates et heures,
• enfin, le IF peut aussi s’avérer fort pratique. Il retourne une valeur en fonction de l’évaluation d’une condition.

Taille des bases et tables

Il est fort utile de connaître le poids de ses différentes tables et bases de données en administration système. Si les gestionnaires graphiques MySQL comme phpMyAdmin ou Adminer affichent ces données par défaut, ici, nous œuvrons en CLI.

La tâche est évidemment possible, comment croyez-vous d’ailleurs que font les interfaces graphiques ? Elles exécutent bien des commandes SQL sous le capot !

# poids de toutes les bases
SELECT table_schema "DB name",
    round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size (MB)"
FROM information_schema.tables
GROUP BY table_schema;

# poids des tables d'une base
SELECT
    table_name AS `Table`,
    round(((data_length + index_length) / 1024 / 1024), 2) `Table Size (MB)`
FROM information_schema.TABLES
WHERE table_schema = "nom_de_ma_table";

Si vous lisez attentivement la requête, vous constatez que les tailles ici considérées contiennent les données ainsi que les index. Vous pouvez donc facilement connaître la taille des index seuls ou des données seules en modifiant légèrement la requête.

Exécuter des commandes depuis bash

Il peut être très pratique d’exécuter des commandes sans avoir à se connecter au shell MySQL. Notamment car cela permet d’insérer des commandes directement dans des scripts.

Pour exécuter une seule commande, il faut utiliser l’option -e.

mysql -u [username] -p -e "CREATE DATABASE buzut"

# ou si l'on veut exécuter les commandes dans une base en particulier
# note, je suis root donc je ne précise pas le mdp
mysql -u root ma_base -e "CREATE TABLE Users (id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(100), hash CHAR(40) UNIQUE NOT NULL, locale CHAR(2) NOT NULL, PRIMARY KEY (id))"

Alternativement, on peut exécuter des commandes depuis un fichier.

mysql -u root ma_base < /home/buzut/monfichier.sql

Exécuter des commandes depuis MySQL

Aussi, si vous disposez d’un fichier de commandes à exécuter et êtes déjà connectés à l’interpréteur MySQL, il est possible de charger ces commandes sans vous déconnecter.

`\. /home/buzut/monfichier.sql` ou `SOURCE home/buzut/monfichier.sql`

Enfin, sachez qu’il est également possible d’exécuter des commandes bash depuis MySQL. Cela peut s’avérer fort pratique.

# il suffit de préfixer la commandes par "\! "
\! ls -l

# vous pouvez même éditer un fichier
\! nano /etc/mysql/mariadb.conf.d/50-client.cnf

# ou carrément accéder au shell bash complet (exit ou ctrl+D vous ramènera à MySQL)
\! bash

Exporter les données d’une table

Il est assez courant d’avoir à exporter les données d’une table ou d’une requête afin de les exploiter par la suite dans un tableur. Plusieurs méthodes s’offrent à nous.

SELECT *
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM Users

Vous pouvez bien entendu remplacer les séparateurs de colonnes et de ligne comme bon vous semble. Par ailleurs, MySQL doit avoir les droits d’écriture dans le répertoire sélectionné.

L’autre méthode présente la particularité de créer un fichier avec le nom des colonnes en première ligne. En revanche, il s’agit d’un fichier TSV – les tabs sont utilisé comme séparateurs. Cela n’empêchera pas votre tableur d’ouvrir le fichier.

mysql mydb -e "select * from Users" -B > users.tsv

Sauvegarder et restaurer sa base

Logique VS physique

Il existe deux types de sauvegardes : les sauvegardes logiques et les sauvegardes physiques. Voyons quels sont les avantages et inconvénients de chacun d’eux pour comprendre quand utiliser l’un ou l’autre.

Les backups logiques consistent en un ensemble de commandes SQL qui permettent de restaurer les bases et les données. On y trouve donc les commandes SQL qui nous sont familières telles que CREATE DATABASE, CREATE TABLE et INSERT.

De leurs côtés, les sauvegardes physiques sont une copie des différents répertoires et fichiers constituant le répertoire de données de la base de données.

Les backups logiques sont plus flexibles. Ils respectent le standard SQL et pourront facilement être restaurés sur un matériel différent, une version du moteur de BDD différent, voire même un autre SGBDR (de MySQL à PostgreSQL par ex.) s’il n’est pas fait usage de propriétés non standard dans les bases.

La souplesse des sauvegardes logiques permet aussi de ne sélectionner – ou d’exclure – certaines tables du dump. Ce n’est pas toujours possible avec les sauvegarde physiques (pas possible avec InnoDB en tout cas).

En revanche, les sauvegardes physiques sont moins volumineuses que les sauvegardes logiques, elles sont plus rapides lors du dump et également plus rapide à l’import – il faut rejouer toutes les commandes SQL pour re-créer une base depuis une sauvegarde logique !

En résumé, pour des backups peu volumineux, inutile de s’embêter, la sauvegarde logique est toute indiquée. En revanche, pour des bases de données contenant d’important volumes de données, il sera plus judicieux de procéder à un backup physique avec les outils adéquats.

Backup logique

Étant donnée que c’est de loin le plus utilisé, commençons par voir comment procéder avec les sauvegardes logiques. L’outil est intégré d’office lors de l’installation de MySQL ou ses forks. Il n’y a donc aucun prérequis avant usage.

Une commande permet de sauvegarder d’un coup l’ensemble des schémas et des données d’une base de données. Cette commande bien utile s’appelle mysqldump. Elle dispose de nombreuses options, cependant, les principales à connaître sont les suivantes -A, qui signifie l’export de toutes les bases (--all-databases) et -B suivi des bases à exporter (à séparer par des virgules s’il y en a plusieurs. On utilisera aussi --single-transaction qui effectue le dump au sein d’une seule transaction. Cela permet de conserver la cohérence de l’ensemble des données.

En général, on redirige la sortie de cette commande vers un fichier. Si on automatise la procédure, pour avoir par exemple une sauvegarde par jour, on peut automatiquement nommer le fichier avec la date du jour.

# la rfc-3339 donne une date du type 2014-05-20, votre fichier s'appelerait donc "2014-05-20.sql"
mysqldump -u user [-h nom_hôte_ou_ip] --single-transaction ma_base [ma_table [mon_autre_table]] -p'mot de passe' > `date --rfc-3339 date`.sql

Il est aussi possible d’exporter plusieurs bases d’un coup. Dans ce cas, on ne peut en revanche pas sélectionner les tables et on obtient un dump de l’intégralité de la base. Il faut pour cela faire précéder le nom de la première base de l’argument -B. Alternativement, l’argument -A permettra de faire un dump de toutes les bases (avec toutes les tables).

mysqldump -u user [-h nom_hôte_ou_ip] --single-transaction -A -p'mot de passe' > `date --rfc-3339 date`.sql

Enfin, pour restaurer cette base sur un autre serveur par exemple, vous devrez tout d’abord créer la base dans laquelle vous voulez stockez ces tables, puis vous y importerez l’ensemble des données (cela fonctionne indifféremment qu’il s’agisse d’une base complète ou d’une table).

# après avoir créé la nouvelle base, dans le shell,
# donc après avoir quitté mysql !
mysql -u root [-h nom_hôte_ou_ip] -p ma_nouvelle_base < 2014-05-20.sql

Si vous souhaitez importer plusieurs bases et que vous voulez conserver les mêmes noms de bases, vous pouvez omettre le nom de la base. Il n’est pas non plus nécessaire de créer les bases avant l’import. Cependant, sachez qu’avec cette méthodes, toutes les bases contenues dans le fichier sql seront importées. Ainsi, la commande importera toutes les bases d’un coup. La commande devient la suivante.

mysql -u root [-h nom_hôte_ou_ip] -p < 2014-05-20.sql

Si vous avez dumpé toutes les bases d’un coup mais que vous ne voulez en restaurer qu’une en particulier, voici la commande qu’il vous faut :

# après avoir créé la nouvelle base, dans le shell,
# donc après avoir quitté mysql !
mysql -u root [-h nom_hôte_ou_ip] --one-database -p ma_nouvelle_base < toutes_mes_bases.sql

Et voilà ! Vous veillerez en revanche à recréer les utilisateurs nécessaires à votre application et à leur attribuer les bons droits. Les utilisateurs sont en effet stockés dans la base mysql, ces derniers ne sont donc pas restaurés avec les bases tierces !

Backup physique

En ce qui concerne les backups physiques, plusieurs outils peuvent être utilisés. Dans sa forme la plus bourrin, un simple coup de cp ou rsync depuis le répertoire de données de MySQL pourrait suffire. Cependant, cette méthode ne garantie pas l’intégrité des données et ne permet certainement pas de backup à chaud. Stopper le serveur SQL représente un inconvénient notable, nous n’explorerons donc pas cette méthode.

Pour les tables MyISAM, il existe l’outil mysqlhotcopy qui permet de réaliser un dump en choisissant de dumper toute la base ou seulement quelques tables. Je ne m’attarderai pas sur cet outil car il ne permet pas de travailler avec InnoDB, aujourd’hui plus répandu que MyISAM.

Enfin, il existe Percona XtraBackup et son fork, MariaDB Backup. Ce dernier supporte quelques options supplémentaires telles que le support de la compression InnoDB et le chiffrement des données. C’est sur ce dernier que nous allons nous attarder (cela dit, XtraBackup fonctionne exactement de la même manière).

Il est normalement installé par défaut. Cela dit, sur d’anciennes distribution, ce n’était pas forcement le cas. Pour l’installer, il suffit donc d’ajouter le paquet mariadb-backup-10.1.

apt-get install mariadb-backup-10.1

Ensuite, la backup se résume à une ligne, et le restore se fait en deux commandes. Avant toute chose, il vous faudra vider le répertoire contenant les bases et tables sur le serveur MySQL de destination. En effet, les éléments backupés seront intégralement restaurés ici, nous faisons table rase !

# le backup
mariabackup --backup --target-dir=répertoire_de_destination_du_backup --user root -p'le_mot_de_passe'

# préparation avant restore (s'assure de la cohérence des données)
# sans cette étape, MySQL crasherait au démarrage
mariabackup --prepare --target-dir=répertoire_indiqué_précédemment

# avant restore, sur le serveur de destination, on vide le dossier MySQL
rm -rf /var/lib/mysql/*

# enfin le restore
mariabackup --copy-back --target-dir=/dossier_de_backups/

# même chose mais en ne conservant pas la sauvegarde
mariabackup --move-back --target-dir=/dossier_de_backups/

# enfin, on restaure les bons droits sur tout le contenu de mysql
chown -R mysql:mysql /var/lib/mysql

Notez qu’il n’est pas obligatoire de backuper en root, il faut bien entendu avoir les privilèges adéquats. Il existe une fonction permettant de compresser le dump. Cependant, on obtiens de meilleurs résultats en utilisant gzip.

Par ailleurs, comme on backup la plupart du temps pour s’assurer que les données restent en sûreté en cas d’avarie, on voudra la plupart du temps mettre ces données sur une autre machine.

Plutôt que de d’abord dumper en local puis de transférer l’ensemble sur une autre machine, il est possible de directement streamer les données. Cela peut se faire avec socat ou netcat par exemple. Il faut alors ouvrir un port sur la machine cible et écouter également dessus, sans oublier de sécurisé la connexion s’il s’agit d’un réseau public.

L’autre option – plus simple à mon sens – est d’effectuer le dump à l’initiative de la machine de backup. Ainsi, tout peut passer par ssh.

ssh backup@$machine_cible 'mariabackup --backup --stream=xbstream --user root' | gzip > ~/db_backups/api_db_`date --rfc-3339 date`.xb.gz'

Et voilà, le tour est joué. Sachez aussi que lorsque vous backupez avec l’option --stream=xbstream, vous obtenez une archive binaire au format xbstream. Afin d’obtenir la structure habituelle du dump dans un répertoire, il vous faudra exécuter le commande suivante :

# on dézippe d'abord l'archive
gzip backup.xb.gz

# puis on l'extrait
mbstream  -x < backup.xb

Nous avons déjà vu pas mal de chose, on va donc s’arrêter là pour cet article. Ça vous laissera le temps de tout digérer. N’essayez pas de tout retenir d’un coup, les commandes viennent avec la pratique.

Pour aller plus loin, utiliser MySQL de manière encore plus poussée et découvrir des commandes plus complexes et puissantes, lisez la suite de cet article pour les utilisateurs avancés.