{{tag>Noble Focal sgbd serveur réseau}}

{{ https://www.mysql.com/common/logos/logo-mysql-170x115.png?120|Logo de MySQL}}
====== MySQL ======

**[[https://www.mysql.com/|MySQL]]** est un [[wpfr>SGBD|système de gestion de bases de données relationnelles]] (SGBDR) très populaire, gratuit, performant, //multithread//, sous double licence [[wpfr>logiciel libre|libre]] ou [[wpfr>logiciel propriétaire|propriétaire]] (il est libre quand distribué avec un logiciel libre, ce qui est le cas d'Ubuntu, mais pas forcément d'autres applications qui l'utilisent).

<note important>
**MySQL** appartient à [[wpfr>Oracle_(entreprise)|Oracle]].

Il existe un fork //[[wpfr>open source]]// et complètement [[wpfr>logiciel libre|libre]], plus communautaire, 100% compatible **MySQL** et a priori plus performant, créé et maintenu par le fondateur de MySQL, Michael Wideniusm. Il s'agit de **[[:MariaDB]]**, qui est également disponible sur Ubuntu, et choisi par défaut sur [[:distributions#Debian]].
</note>

**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 ''[[man>mysql]]'', d'une application, ou pour le [[:Web]] d'un script [[wpfr>Common_Gateway_Interface|CGI]] : [[:nodejs|JavaScript]], [[:Python]], [[:Ruby]], [[:installation_non_standard_de_logiciel#Perl]], etc. ou [[:PHP]]. Il faudra dans ce dernier cas installer le [[:php#association_avec_un_serveur_de_base_de_donnees|module]] ''[[apt>php-mysql]]'' qui permet à PHP de communiquer avec un serveur MySQL. 

===== Installation =====

L'installation de ce paquet doit se faire de préférence en utilisant le [[:terminal]] via la commande : <code>sudo apt install mysql-server</code>
Sinon [[:deb#installer_un_paquet_deb|installer le paquet]] ''[[apt>mysql-server]]''.

==== Modules ====

Certains [[:paquet|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 [[pu>libqt5sql5-mysql|Qt5]] pour MySQL, [[:deb#installer_un_paquet_deb|installer le paquet]] ''[[apt>libqt5sql5-mysql]]''.

Pour lister les modules disponibles, avec ''[[:apt-cli|apt]]'' :
  apt list ~n-mysql$

===== Utilisation =====

==== Démarrer ====

Pour démarrer le serveur MySQL, entrer la [[:commande_shell|commande]] suivante dans un [[:terminal]] :
<code bash>sudo systemctl start mysql</code>

==== Redémarrer ====

Pour redémarrer le serveur MySQL, entrer la [[:commande_shell|commande]] suivante dans un [[:terminal]] :
<code>sudo systemctl restart mysql</code>

==== Recharger la configuration ====

Pour que MySQL prenne en compte les modifications de sa configuration, entrer [[:commande_shell|commande]] suivante dans un [[:terminal]]:
<code>sudo systemctl reload mysql</code>

==== Forcer la prise en compte de la nouvelle configuration ====

Pour forcer MySQL à recharger ses fichiers de configuration, entrer la [[:commande_shell|commande]] suivante dans un [[:terminal]]:
<code>sudo systemctl force-reload mysql</code>

==== Connaître la version ====

Il est parfois utile de connaître la version installée:\\
<code>mysql --version</code>

===== Configuration =====

==== Authentification ====

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'', [[:mysql_outils#adminer|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 //[[https://dev.mysql.com/doc/refman/5.7/en/socket-pluggable-authentication.html|auth_socket]]// qui permet de s'identifier au moyen de comptes UNIX (qui correspondent donc aux [[:utilisateur|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 ''[[man>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|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.((Voir [[https://forum.ubuntu-fr.org/viewtopic.php?id=2027272|ce fil]] sur le forum pour plus d'information au sujet des répercutions de l'utilisation de //auth_socket//.))

==== Lancer la console MySQL ====

La console MySQL permet d'entrer des requêtes [[wpfr>Structured_Query_Language|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 [[wpfr>Structured_Query_Language|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 :
<code>
mysql> SELECT * FROM
    ->
</code>
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 '';''.

==== Sélectionner la base de données à utiliser ====

Soit directement lorsqu'on lance le client ''[[man>mysql]]'' avec l'option ''-D'' :
<code>mysql -u root -D votre_base -p</code>
<code>sudo mysql -D votre_base</code>
  
Soit une fois connecté, à l'aide de l'instruction SQL ''[[https://dev.mysql.com/doc/refman/8.4/en/use.html|USE]]'' dans la console MySQL :
<code mysql>USE votre_base;</code>

==== Ajouter ou changer le mot de passe de root ====

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]], [[:mysql_outils#adminer|Adminer]], ou autre), suivez [[:phpmyadmin#acces_root|cette autre procédure]].

==== Utilisateurs ====

=== Lister les utilisateurs ===

Vous pouvez lister les utilisateurs ainsi que leurs [[#authentification|méthodes d'authentification]] avec la requête suivante :
<code mysql>
SELECT user, host, plugin, authentication_string FROM mysql.user;
</code>

=== Créer un utilisateur ===

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 :
<code mysql>
CREATE USER 'nom_utilisateur_choisi'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mot_de_passe_solide';
</code>
ou si cette commande échoue avec une erreur de syntaxe :
<code mysql>
CREATE USER 'nom_utilisateur_choisi'@'localhost' IDENTIFIED BY 'mot_de_passe_solide';
</code>

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) :
<code mysql>
CREATE USER 'nom_utilisateur'@'localhost' IDENTIFIED WITH auth_socket;
</code>

=== Modifier l'accès d'un utilisateur ===

Pour modifier l'accès d'un utilisateur MySQL existant, et l'identifier par mot de passe (ou simplement changer son mot de passe) :
<code mysql>
ALTER USER 'nom_utilisateur'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mot_de_passe_solide';
</code>

Pour lier un compte MySQL existant à un utilisateur Ubuntu (il faut que les noms d'utilisateur soient les mêmes) :
<code mysql>
ALTER USER 'nom_utilisateur'@'localhost' IDENTIFIED WITH auth_socket;
</code>

=== Supprimer un utilisateur ===

Pour supprimer un utilisateur :
<code mysql>
DROP USER 'nom_utilisateur'@'localhost';
</code>

==== Privilèges ====

=== Donner tous les droits sur une base de données à un utilisateur précis ===

<code mysql>
GRANT ALL ON nom_base.* TO 'nom_utilisateur'@'localhost';
</code>
Puis [[#recharger les privilèges]].

=== Donner tous les droits à un super-administrateur ===

<code mysql>
GRANT ALL ON *.* TO 'nom_utilisateur'@'localhost';
</code>
Puis [[#recharger les privilèges]].

=== Supprimer les droits sur une base de données à un utilisateur précis ===

<code mysql>
REVOKE ALL ON nom_base.* FROM 'nom_utilisateur'@'localhost';
</code>
Puis [[#recharger les privilèges]].

=== Recharger les privilèges ===

Après chacune de ces opérations il est nécessaire de recharger les privilèges des utilisateurs MySQL :
<code mysql>
FLUSH PRIVILEGES;
</code>

==== Connexions entrantes ====

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, [[:tutoriel:comment_editer_un_fichier|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'') :
<file - mysqld.cnf>
bind-address = XXX.XXX.XXX.XXX
</file>
où ''XXX.XXX.XXX.XXX'' est l'[[:reseau#lexique|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.

<note important>L'authentification de chaque utilisateur MySQL dépend de sa localisation.</note>

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'[[:reseau#lexique|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écurité|sécurisé]] !), remplacer l'adresse IP par le caractère ''%'' : '''nom_utilisateur'@'%%%'%%''.

==== Configuration en UTF-8 ====

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'' :
<file>
[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
</file>

puis de relancer le serveur MySQL :
<code>sudo systemctl restart mysql</code>

Vous pouvez vérifier le résultat une fois connecté grâce à la requête suivante :
<code mysql>
SHOW VARIABLES LIKE 'char%';
</code>

===== Quelques requêtes SQL =====

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

==== Base de donnée ====

=== Utiliser une base de données existante ===

<code mysql>
USE nom_de_la_base;
</code>
 
=== Créer une base de données ===

<code mysql>
CREATE DATABASE nom_de_la_base;
</code>

=== Liste des bases de données ===

<code mysql>
SHOW DATABASES;
</code>

=== Supprimer une base de données ===

<code mysql>
DROP DATABASE nom_de_la_base;
</code>

=== Importer une base de données située dans le répertoire courant (base de donnée exportée en .sql) ===

<code mysql>
SOURCE fichier.sql;
</code>

=== Créer une base de données et un utilisateur qui lui est associé ===

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.
<code mysql>
CREATE DATABASE application;
CREATE USER 'utilisateur'@'localhost' IDENTIFIED BY 'mot_de_passe';
GRANT ALL ON application.* TO 'utilisateur'@'localhost';
FLUSH PRIVILEGES;
QUIT;
</code>

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.

==== Tables ====

=== Liste des tables de la base de données active ===

<code mysql>
SHOW TABLES;
</code>

=== Structure d'une table ===

<code mysql>
DESCRIBE nom_table;
</code>

=== Renommer une table ===

<code mysql>
ALTER TABLE nom_table RENAME AS nouveau_nom;
</code>

Pour la syntaxe des requêtes SQL, reportez-vous à la [[https://dev.mysql.com/doc/|documentation MySQL]].

===== Autres opérations =====

==== Sauvegarde et importation ====

<note>Ajoutez ''-p'' à ces commandes si vous utilisez un mot de passe, et ''-u'' pour un autre utilisateur que //root//.</note>

Pour **exporter** la base de donnée ''maBase'', utilisez la commande ''[[man>mysqldump]]'' :
<code>sudo mysqldump maBase > maBase_backup.sql</code>

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'') :
<code>sudo mysqldump maBase ma_table > ma_table.sql</code>

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 :
<code>sudo mysql maBase < maBase_backup.sql</code>

<note tip>
Si vous souhaitez mettre en place une solution de [[:sauvegarde]] automatique de bases de données à intervalle régulier, rendez-vous sur la page [[:AutoMySQLBackup]].
</note>

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 [[https://dev.mysql.com/doc/refman/5.0/fr/sql-syntax.html|documentation officielle]] de MySQL.

==== Compacter l'espace utilisé par ibdata1 ====

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 :
  * [[http://bytes.com/groups/mysql/495219-mysql-ibdata1-compact-space|Supprimer les tables InnoDB pour récupérer l'espace]]
  * [[https://bugs.mysql.com/bug.php?id=1287|"Bogue" associé]]
  * Exporter toutes les bases de données, arrêter MySQL, supprimer le fichier, démarrer MySQL et importer toutes les bases.

===== Administrer MySQL via une une interface graphique =====

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

===== Réinstallation complète =====

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 !

<code>sudo apt autoremove --purge ~n^mysql-server ~n^mariadb-server</code>

Puis :
<code>
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</code>
On peut ensuite choisir d'installer [[:MySQL]] ou [[:MariaDB]] :
<code>sudo apt install mysql-server</code>
**__ou__**
<code>sudo apt install mariadb-server</code>

===== Problèmes courants =====

==== Le server MySQL ne démarre pas ("time out") ou s'arrête au bout de quelques secondes ====

Consultez le journal de [[:systemd]] :
<code>journalctl -xe -u mysql</code>
et les //logs// dans ''/var/log/mysql/error.log''.

=== AppArmor ===

Si vous y trouvez des messages du type :
<code>AVC apparmor="DENIED"</code>
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 :
<code>sudo apt install apparmor-utils
sudo aa-complain /usr/sbin/mysqld</code>
Il faut ensuite redémarrer l'ordinateur (redémarrer le service //apparmor// ne suffit pas).

==== Installation de MySQL impossible ====

=== Conflit entre MySQL et MariaDB ===

Si vous rencontrez une erreur de ce type lors de l'installation de MySQL :
<code>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.</code>
ou encore :
<code>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.</code>

Il s'agit probablement d'un conflit entre MySQL et [[:MariaDB]]. Le plus simple est de [[#reinstallation_complete|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 [[https://askubuntu.com/a/1235833|cette procédure]].

===== Voir aussi =====

  * [[https://www.mysql.com/|Le site officiel de MySQL]] [en]
  * [[http://www.mysql.fr/|Le site officiel francophone]] [fr]
  * [[https://www.system-linux.eu/index.php?category/Mysql|Aller plus loin avec MySQL]] [fr]
  * La page dédiée aux [[:mysql_outils|outils MySQL]]
  * [[:greensql|GreenSQL : Pare-feu applicatif SQL]]
  * [[https://mysql.developpez.com/|La section dédiée à MySQL sur Developpez.com]] [fr]
  * La page des [[:SGBD|systèmes de gestion de base de données]]
  * [[:MariaDB]] -> Le serveur alternatif 100% compatible MySQL
  * [[.:?do=showtag&tag=mysql|La liste des pages taguées avec MySQL]]

----
//[[:Contributeurs]] : [[utilisateurs:_Enchained]], Hugo, Oscar Nsarhaza, [[:utilisateurs:Ner0lph]], [[:utilisateurs:david96|david96]], Gounlaf, [[:utilisateurs:McPeter]], [[:utillisateurs:Rewiiz]], ernest-le-voyage, [[:utilisateurs:krodelabestiole]].//