MySQL

MySQL est un système de gestion de bases de données relationnelles (SGBDR) propriétaire, très populaire, gratuit, performant, multithread…

MySQL est principalement un serveur de bases de données. Pour s'y connecter localement ou à distance, on utilise un client. Il peut s'agir de la commande mysql, ou couramment d'un script ou JavaScript, ou PHP. Il faudra dans ce dernier cas installer le module php-mysql qui permet à PHP de communiquer avec un serveur MySQL.

L'installation de ce paquet doit se faire de préférence en utilisant le terminal via la commande :

sudo apt install mysql-server

Sinon installer le paquet mysql-server.

Certains paquets fournissent des fonctionnalité spécifiques à certains usages (à moins de développer une application, ce sont généralement des dépendances qui s'installent automatiquement avec APT).

Par exemple, pour la bibliothèque Qt5 pour MySQL, installer le paquet libqt5sql5-mysql.

Pour lister les modules disponibles, avec apt :

apt list ~n-mysql$

Pour démarrer le serveur MySQL, entrer la commande suivante dans un terminal :

sudo systemctl start mysql

Pour redémarrer le serveur MySQL, entrer la commande suivante dans un terminal :

sudo systemctl restart mysql

Pour que MySQL prenne en compte les modifications de sa configuration, entrer commande suivante dans un terminal:

sudo systemctl reload mysql

Pour forcer MySQL à recharger ses fichiers de configuration, entrer la commande suivante dans un terminal:

sudo systemctl force-reload mysql

Il est parfois utile de connaître la version installée:

mysql --version

L'inscription des utilisateurs de MySQL se fait sur la table users de la base de données mysql. On peut donc les administrer manuellement depuis cette table, mais il existe des outils pour simplifier leur gestion (tels que mysqladmin, mysql_secure_installation, adminer ou phpMyAdmin).

Il existe plusieurs méthodes d'authentifications. Nativement MySQL permet de définir ses propres couples d'utilisateurs et de mots de passe. Mais il existe des plugins permettant d'implémenter d'autres comportements, dont auth_socket qui permet de s'identifier au moyen de comptes UNIX (qui correspondent donc aux utilisateurs Ubuntu).
Il faut tout de même créer le compte sur MySQL pour pouvoir l'utiliser, mais il n'est plus nécessaire de renseigner le mot de passe. Il faut par contre lancer le client (la commande mysql ou le script PHP) avec l'utilisateur correspondant.

Pour choisir l'un ou l'autre des comportement, on renseigne le champs plugin de la table user :

• mysql_native_password ou vide pour utiliser l'authentification native de MySQL.
• auth_socket pour se connecter avec un utilisateur Ubuntu.

Depuis Bionic 18.04, Ubuntu lie par défaut le compte root de Ubuntu avec auth_socket en installant MySQL.
Auparavant il fallait spécifier un mot de passe lors de l'installation. Ceci a différents impacts sur les méthodes de travail.¹⁾

La console MySQL permet d'entrer des requêtes SQL manuellement.

L'utilisateur root de MySQL est authentifié par son compte utilisateur système (plugin auth_socket) et non plus par un mot de passe (plugin mysql_native_password). On peut donc simplement utiliser la commande sudo.

Ainsi, pour accéder à la console MySQL, on entre donc la commande suivante dans un terminal :

sudo mysql

Le prompt de cette console est le suivant :

mysql>

On peut dès lors entrer des requêtes SQL. Ne pas oublier le point-virgule ; à la fin de chaque requête, sans quoi le prompt passe à la ligne, attendant la suite de la requête :

mysql> SELECT * FROM
    ->

Ici la requête n'est pas terminée, ni exécutée. Ceci permet d'entrer une requête sur plusieurs lignes avant de la clôturer avec ;.

Soit directement lorsqu'on lance le client mysql avec l'option -D :

mysql -u root -D votre_base -p

sudo mysql -D votre_base

Soit une fois connecté, à l'aide de l'instruction SQL USE dans la console MySQL :

USE votre_base;

Il est vivement déconseillé de modifier la méthode d'authentification par défaut de l'utilisateur root (auth_socket). Le système utilise ce compte pour effectuer des tâches de maintenance (auparavant Ubuntu créait un compte spécial debian-sys-maint à cette fin).

Si vous souhaitez créer un accès "super-administrateur" pour gérer l'intégralité de vos bases de données et de leurs utilisateurs avec une application tierce (phpMyAdmin, Adminer, ou autre), suivez cette autre procédure.

Vous pouvez lister les utilisateurs ainsi que leurs méthodes d'authentification avec la requête suivante :

SELECT user, host, plugin, authentication_string FROM mysql.user;

Vous avez le choix entre créer un utilisateur identifié au moyen d'un mot de passe (méthode mysql_native_password, celle par défaut pour MySQL) ou créer un accès MySQL à un utilisateur Ubuntu existant (méthode auth_socket). La plupart du temps, on utilisera mysql_native_password.

Pour créer un utilisateur MySQL identifié au moyen d'un mot de passe :

CREATE USER 'nom_utilisateur_choisi'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mot_de_passe_solide';

ou si cette commande échoue avec une erreur de syntaxe :

CREATE USER 'nom_utilisateur_choisi'@'localhost' IDENTIFIED BY 'mot_de_passe_solide';

Ou pour créer un accès MySQL à un utilisateur Ubuntu (seulement dans le cadre d'une utilisation avancée, et si vous comprenez ce que vous faites) :

CREATE USER 'nom_utilisateur'@'localhost' IDENTIFIED WITH auth_socket;

Pour modifier l'accès d'un utilisateur MySQL existant, et l'identifier par mot de passe (ou simplement changer son mot de passe) :

ALTER USER 'nom_utilisateur'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mot_de_passe_solide';

Pour lier un compte MySQL existant à un utilisateur Ubuntu (il faut que les noms d'utilisateur soient les mêmes) :

ALTER USER 'nom_utilisateur'@'localhost' IDENTIFIED WITH auth_socket;

Pour supprimer un utilisateur :

DROP USER 'nom_utilisateur'@'localhost';

GRANT ALL ON nom_base.* TO 'nom_utilisateur'@'localhost';

Puis recharger les privilèges.

GRANT ALL ON *.* TO 'nom_utilisateur'@'localhost';

Puis recharger les privilèges.

REVOKE ALL ON nom_base.* FROM 'nom_utilisateur'@'localhost';

Puis recharger les privilèges.

Après chacune de ces opérations il est nécessaire de recharger les privilèges des utilisateurs MySQL :

FLUSH PRIVILEGES;

Par défaut, MySQL n'écoute que les connexions venant de localhost (127.0.0.1), c'est-à-dire du système sur lequel il est installé.

Pour pouvoir accéder à MySQL depuis un autre ordinateur, ajoutez la ligne suivante au fichier /etc/mysql/mysql.conf.d/mysqld.cnf dans la section [mysqld] (sous Mariadb il s'agit du fichier /etc/mysql/mariadb.conf.d/50-server.cnf) :

mysqld.cnf

bind-address = XXX.XXX.XXX.XXX

où XXX.XXX.XXX.XXX est l'adresse IP de l'interface sur laquelle MySQL doit écouter. Ceci permet de n'écouter que sur une IP privée si vous avez plusieurs interfaces réseaux, et de bloquer votre adresse publique (et donc Internet).

Pour forcer MySQL à écouter sur toutes les interfaces réseau, il suffit de commenter la ligne bind-address = 127.0.0.1 (ou écrire bind-address = 0.0.0.0), mais c'est très dangereux si le serveur est exposé sur Internet.

Redémarrer le service après avoir modifié le fichier de configuration.

Par exemple avec 'nom_utilisateur'@'localhost', l'utilisateur ne peut se connecter que depuis la machine locale.

Pour créer un utilisateur MySQL qui soit autorisé à se connecter à distance, remplacer @'localhost' par @'XXX.XXX.XXX.XXX', dans les requêtes le concernant, où XXX.XXX.XXX.XXX est l'adresse IP d'où l'utilisateur doit pouvoir se connecter.

Pour autoriser l'accès venant de n'importe quel emplacement (ce qui est très peu sécurisé !), remplacer l'adresse IP par le caractère % : 'nom_utilisateur'@'%'.

Il suffit d'ajouter avec les permissions administrateur les lignes suivantes dans les sections adéquates (ne pas hésiter à les créer si elles n'existent pas) dans le fichier /etc/mysql/mysql.conf.d/mysqld.cnf :

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

puis de relancer le serveur MySQL :

sudo systemctl restart mysql

Vous pouvez vérifier le résultat une fois connecté grâce à la requête suivante :

SHOW VARIABLES LIKE 'char%';

Toutes ces commandes sont à entrer dans le prompt de MySQL une fois connecté au serveur (voir comment lancer la console MySQL ci-dessus).

USE nom_de_la_base;

CREATE DATABASE nom_de_la_base;

SHOW DATABASES;

DROP DATABASE nom_de_la_base;

SOURCE fichier.sql;

Cette procédure est la plus courante, c'est celle qu'on réalise lorsqu'on installe une application web en production. Pour des raisons de sécurité chaque application doit se connecter avec un utilisateur MySQL qui lui est dédié, et qui n'a accès qu'à la base correspondante.

CREATE DATABASE application;
CREATE USER 'utilisateur'@'localhost' IDENTIFIED BY 'mot_de_passe';
GRANT ALL ON application.* TO 'utilisateur'@'localhost';
FLUSH PRIVILEGES;
QUIT;

Il faut remplacer dans ces requêtes application par le nom de l'application web ou du site internet (sans espace ni caractère spécial) , utilisateur par un nom d'utilisateur (qui peut aussi être celui de l'application, pour s'y retrouver facilement) et mot_de_passe par un vrai mot de passe complexe qu'on note : on devra renseigner ces identifiants lors de l'installation de l'application.

On vient de créer la base de donnée application à laquelle l'utilisateur utilisateur aura accès.

SHOW TABLES;

DESCRIBE nom_table;

ALTER TABLE nom_table RENAME AS nouveau_nom;

Pour la syntaxe des requêtes SQL, reportez-vous à la documentation MySQL.

Pour exporter la base de donnée maBase, utilisez la commande mysqldump :

sudo mysqldump maBase > maBase_backup.sql

Ceci fera l'export dans un fichier maBase_backup.sql.

Pour exporter une table (par exemple ma_table)appartenant à une base des données (par exemple maBase) :

sudo mysqldump maBase ma_table > ma_table.sql

Ainsi nous aurons une exportation seulement de la table ma_table sans toucher à d'autres tables de la base des données.

Pour importer une base de données ou une sauvegarde sur maBase, utilisez la commande :

sudo mysql maBase < maBase_backup.sql

D'autres commandes telles que la suppression des tables, de la base, l'ajout des données dans la base des données, etc. peuvent être trouvées sur la documentation officielle de MySQL.

MySQL stocke ses informations et bases de données dans le répertoire /var/lib/mysql. Il arrive avec le temps et l'utilisation que le fichier ibdata1 occupe un espace disque très important qui ne sera pas récupéré même en supprimant toutes les bases.

Pistes pour comprendre et corriger ce problème :

• Supprimer les tables InnoDB pour récupérer l'espace
• "Bogue" associé
• Exporter toutes les bases de données, arrêter MySQL, supprimer le fichier, démarrer MySQL et importer toutes les bases.

Il est possible d'administrer un serveur MySQL via une interface graphique avec par exemple phpMyAdmin ou Adminer qui sont d'excellentes applications web. D'autres outils sont disponibles, vous en trouverez une liste non exhaustive sur la page Outils MySQL.

De nombreux paramètres du serveur MySQL (tels que ses utilisateurs) sont stockés directement en base de données.

Ces bases de données ne seront jamais supprimées par APT (et heureusement : on risquerait d'y perdre de précieuses données).

En cas de problème de configuration lors d'un premier déploiement d'un serveur MySQL on doit donc effectuer un certain nombre d'opérations manuelles afin de réinitialiser complètement son installation. Attention cela rendra les bases de données existantes inaccessibles !

sudo apt autoremove --purge ~n^mysql-server ~n^mariadb-server

Puis :

sudo mv /var/lib/mysql /var/lib/mysql.bak
sudo mv /etc/mysql /etc/mysql.bak
sudo mkdir -p /etc/mysql/conf.d
sudo apt clean

On peut ensuite choisir d'installer MySQL ou MariaDB :

sudo apt install mysql-server

ou

sudo apt install mariadb-server

Consultez le journal de systemd :

journalctl -xe -u mysql

et les logs dans /var/log/mysql/error.log.

Si vous y trouvez des messages du type :

AVC apparmor="DENIED"

lié à votre serveur MySQL, c'est que AppArmor bloque l'exécution de mysqld.

Vous pouvez corriger le problème à l'aide des commandes suivantes :

sudo apt install apparmor-utils
sudo aa-complain /usr/sbin/mysqld

Il faut ensuite redémarrer l'ordinateur (redémarrer le service apparmor ne suffit pas).

Si vous rencontrez une erreur de ce type lors de l'installation de MySQL :

Aborting downgrade from (at least) 10.0 to 5.7.
If are sure you want to downgrade to 5.7, remove the file
/var/lib/mysql/debian-*.flag and try installing again.

ou encore :

mysql_upgrade: Got error: 1524: Plugin 'unix_socket' is not loaded while connecting to the MySQL server
Upgrade process encountered error and will not continue.

Il s'agit probablement d'un conflit entre MySQL et MariaDB. Le plus simple est de réinstaller complètement MySQL.

Il est également possible que des fichiers systemd pour MariaDB persistent après une mauvaise désinstallation, et empêchent une nouvelle installation propre. Pour résoudre ce problème, suivez cette procédure.

• Le site officiel de MySQL [en]
• Le site officiel francophone [fr]
• Aller plus loin avec MySQL [fr]
• La page dédiée aux outils MySQL
• GreenSQL : Pare-feu applicatif SQL
• La section dédiée à MySQL sur Developpez.com [fr]
• La page des systèmes de gestion de base de données
• MariaDB → Le serveur alternatif 100% compatible MySQL
• La liste des pages taguées avec MySQL

Contributeurs : _Enchained, Hugo, Oscar Nsarhaza, Ner0lph, david96, Gounlaf, McPeter, Rewiiz, ernest-le-voyage, krodelabestiole.