✎ mysql [Wiki ubuntu-fr]

Vous affichez une ancienne révision du document ! Si vous l'enregistrez, vous créerez une nouvelle version avec ce contenu.

----

====== MySQL ======

**[[https://www.mysql.com/|MySQL]]** est un [[wpfr>SGBD|système de gestion de bases de données relationnelles]] (SGBDR) libre, gratuit, performant, très populaire, multi-threadé, multi-utilisateurs…

<note tip>MySQL appartient à [[wpfr>Oracle_(entreprise)|Oracle]]. Il existe un fork plus communautaire, 100% compatible MySQL et a priori plus performant créé et maintenu par Michael Wideniusm fondateur de MySQL. Il s'agit de [[:MariaDB]], qui est également disponible sur Ubuntu, et choisi par défaut sur [[wpfr>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 ''mysql'', ou couramment d'un script [[:PHP]]. Il faudra dans ce 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 [[terminal]] via la commande : <code>sudo apt install mysql-server</code>
[[apt>mysql-server]] est un [[:tutoriel:creer un meta paquet|méta-paquet]] qui installera MySQL 5.7 sur [[:Xenial]] et sur [[:Bionic]].

===== Utilisation =====

==== Démarrage ====

Pour démarrer le serveur MySQL, tapez la [[:commande_shell|commande]] suivante dans un [[:terminal]]:

sudo systemctl start mysql

==== Redémarrage ====

Pour redémarrer le serveur MySQL, tapez la [[:commande_shell|commande]] suivante dans un [[:terminal]]:

sudo systemctl restart mysql

==== Arrêt ====

Pour arrêter le serveur MySQL, tapez la [[:commande_shell|commande]] suivante dans un [[:terminal]]:
  sudo systemctl stop mysql

==== Rechargement de la configuration ====

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

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

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

==== Connaître la version ====
Il est parfois utile de connaître la version installée:\\
<code>mysqld --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 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]], 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. Cela 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 ====

**Avant [[:bionic|Ubuntu 18.04 Bionic]]**\\
L’authentification de l'utilisateur root de MySQL se fait par mot de passe (//mysql_native_password//).
  * Si vous n'avez pas défini de mot de passe (déconseillé) : <code>mysql -u root</code>
  * Si vous avez défini un mot de passe : <code>mysql -u root -p</code> et tapez votre mot de passe.

**À partir de [[:bionic|Ubuntu 18.04 Bionic]]**\\
À partir ce cette version l'utilisateur root de MySQL est authentifié par son compte système (plugin //auth_socket//) et non plus par un mot de passe (plugin //mysql_native_password//).
<code>sudo mysql</code>

**La console MySQL**\\
Vous arriverez alors sur un prompt du type :

mysql>

Vous pouvez alors taper vos requêtes MySQL. N'oubliez pas le point-virgule à la fin de la requête. Si vous l'oubliez le prompt passe à la ligne.

<code>
mysql> select * from
    ->
</code>

Ce passage vous indique que la requête n'est pas terminée. Vous pouvez entrer vos requêtes de cette façon sur plusieurs lignes et enfin la clôturer avec le « **;** ».

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

Soit directement lorsque vous lancez le client MySQL en ligne de commande:
<code>mysql -u root -D votre_base -p</code>
<code>sudo mysql -D votre_base</code>

Soit une fois connecté à MySQL en tapant la [[:commande_shell|commande]] suivante dans un [[:terminal]]:

mysql> use votre_base

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

<note warning>
À partir de [[:Bionic]] 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]].
</note>

Pour changer ou attribuer un mot de passe ou l'utilisateur root (qui n'est pas forcément le même que l'utilisateur root du système) ou activer son authentification par mot de passe :

Si vous êtes déjà connecté dans mysql :

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'le_mot_de_pass_choisi';

Ou avec mysqladmin pour changer le mot de passe (uniquement si l'authentification par mot de passe est déjà activée) :

sudo mysqladmin password Nouveau_mot_de_passe -p Ancien_mot_de_passe

Vous pourrez avoir besoin de mettre votre mot de passe entre guillemet si celui ci comporte des caractères spéciaux.

==== Redéfinir de force le mot de passe de root ====

<note important>Ceci ne s'applique que pour les versions n'utilisant pas //[[#authentification|auth_socket]]// par défaut pour le compte //root//, donc [[:Xenial]] et les versions précédentes ([[:Trusty]]).

Sur [[:Bionic]] MySQL n'utilise pas de mot de passe pour le compte //root//. Il se sert du système d'authentification des utilisateurs Ubuntu, et donc de [[:sudo]].</note>

Deux possibilités s'offrent à vous. Une méthode simple : <code>sudo dpkg-reconfigure mysql-server-5.7</code>

Une méthode plus compliquée, si vous avez perdu votre mot de passe root pour MySQL, suivez ces quelques étapes :
  * On se connecte à l'aide du compte spécial debian-syst-maint (défini dans le fichier /etc/mysql/debian.cnf) : <code>sudo mysql --defaults-file=/etc/mysql/debian.cnf</code>
  * On met en place le nouveau mot de passe : <code mysql>
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'le_mot_de_pass_choisi';</code><code mysql>FLUSH PRIVILEGES;</code>

* On quitte le prompt de MySQL : <code mysql>QUIT;</code>

==== 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 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 rechargez les privilèges.

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

<code mysql>
GRANT ALL ON *.* TO 'nom_utilisateur'@'localhost';
</code>
Puis rechargez 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 rechargez 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 mysql>
bind-address = x.x.x.x
</file>

où x.x.x.x est l'adresse ip de l'interface sur laquelle MySQL doit écouter. Cela permet de n'écouter que sur une ip privée si vous avez plusieurs interfaces réseaux.

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) Cela peut être dangereux si votre serveur est connecté à Internet directement.

Redémarrez le service après avoir modifié le fichier de configuration :
<code>sudo systemctl restart mysql</code>

Pour créer un utilisateur mysql qui est autorisé à se connecter à distance :

se connecter sur mysql en root :

<code>sudo mysql</code>
(on ajoute //-p// si on utilise un mot de passe pour //root//)
Puis une fois connecté :
<code mysql>GRANT ALL ON [base de donnée].* TO '[nom utilisateur]'@'[adresse IP]' IDENTIFIED WITH mysql_native_password BY '[mot de passe]';</code>

Dans cette requête remplacez ''[nom utilisateur]'' par le nom d'utilisateur que vous souhaitez créer, ''[base de donnée]'' par le nom de la base de données à laquelle il aura accès, ''[mot de passe]'' par le mot de passe que vous souhaitez lui attribuer, et ''[adresse IP]'' par l'adresse IP depuis laquelle vous souhaitez vous connecter à distance.

Pour autoriser l'accès venant de toutes machines (limité par l'authentification), remplacer ''[adresse IP]'' par le symbole ''%''.

Pour rendre ces modifications effectives, terminez par :
<code mysql>FLUSH PRIVILEGES;</code>

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

Il suffit d'ajouter avec les [[sudo|droits d'administration]] 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 loggé dans mysql grâce à la commande suivante :

<code mysql>
SHOW VARIABLES LIKE 'char%';
</code>

Résultat :

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

==== 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 [[http://dev.mysql.com/doc/|documentation MySQL]].

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

Qt4 avec MySQL:

Installer la librairie pour mysql afin de l'utiliser sous QT :

<code>sudo apt install libqt4-sql-mysql</code>

==== Sauvegarde et importation ====

<note>Ajoutez //-p// à ces commandes si vous utilisez un mot de passe pour le compte //root// :</note>

Pour **exporter** la base de donnée « maBase », utilisez la commande :
<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 le tutoriel traitant de la [[:tutoriel:sauvegarder_automatiquement_ses_bases_de_donnees|sauvegarde automatique de bases de données via cron]].
</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 le [[http://dev.mysql.com/doc/refman/5.0/fr/sql-syntax.html|site de documentation de MySQL]].

==== Restauration à partir du dump du datadir ====

<note important>Ne fonctionne que pour les tables au format MyISAM.</note>

Si vous n'avez pas pu faire un backup pour une raison quelconque (par exemple, vous récupérez un disque dur dont le système est mort), on peut sauvegarder le répertoire ''/var/lib/mysql/'' dans lequel chacune des bases est enregistrée dans un répertoire portant son nom.

Pour restaurer vos bases une par une, suivez la procédure ci-dessous (après avoir d'abord installé une version de mysql-server).

Créer une base de données du même nom que celle que vous voulez restaurer :
<code>echo "CREATE DATABASE MaBaseDeDonnee" | mysql -u root -h localhost -p</code>

Copier les fichiers du dump de la base de données (les *.FRM, *.MYD, *.MYI, *.opt), dans le dossier que MySQL a créé pour votre base de données :
<code>cd ~/MonDossierContenantLeDump/MaBaseDeDonnee
sudo cp * /var/lib/mysql/MaBaseDeDonnee</code>

Changer les droits sur les fichiers copiés :
<code>sudo chmod -R ug+rw /var/lib/mysql/MaBaseDeDonnee/
sudo chown -R mysql:mysql /var/lib/mysql/MaBaseDeDonnee/</code>

Voilà, votre base doit correctement fonctionner à présent.

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

MySQL stocke ses fichiers dans le dossier /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]]
  * [[http://bugs.mysql.com/bug.php?id=1287|"Bogue" associé]]
  * Exporter toutes les bases de données, arrêter mysql, supprimer le fichier, démarrer sql et importer toutes les bases.

==== Transférer une base de données entre deux serveurs via SSH ====

=== Préambule ===

Dans le cadre d'une infra sans serveur spécifique de base de données, et que l'on souhaite partager une base commune.

=== Mise en place ===

Dans un premier temps il faut remplir deux conditions :
  *  que l'utilisateur de la base de données soit reconnu par le second serveur
  *  que l'utilisateur de la base de données soit reconnu par les deux serveurs MySql.

Pour une reconnaissance utilisateur d'un serveur à un autre : [[ssh#authentification_par_un_systeme_de_cles_publiqueprivee|Authentification par clés SSH]] (sans passphrase)

Pour que MySql reconnaisse l'utilisateur sans devoir saisir son mot de passe à chaque commande (dans le cas d'un tâche [[cron|CRON]] par exemple), il faut ajouter un fichier dans le répertoire personnel de cet utilisateur et ce __**sur chaque serveur**__. [[tutoriel:comment_editer_un_fichier|Éditer]]/créer le fichier **~/.my.cnf**

<file>
[mysql]
user=nom_utilisateur
password=mot_de_passe_utilisateur

[mysqldump]
user=nom_utilisateur
password=mot_de_passe_utilisateur
</file>
Protéger le :

chmod 600 ~/.my.cnf
  
Transférer depuis le serveur **A** vers le **B** :

mysqldump nom_bdd_serveur_A | ssh utilisateur_serveur_B@ip_serveur_B mysql nom_bbd_serveur_B
  
Exemple de commande en tache [[cron|CRON]] :

10 0 * * * mysqldump nom_bdd_serveur_A | ssh utilisateur_serveur_B@ip_serveur_B mysql nom_bbd_serveur_B >/dev/null  #Mise à jour BDD
  
===== 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]].

===== 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</code>

=== AppArmor ===

Si vous y trouvez des messages du type :
<code>AVC apparmor="DENIED"</code>
lié à votre serveur MySQL, c'est que [[:AppArmor]] bloque l'execution 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).

==== 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]]. Consultez [[:lamp#conflit_entre_mysql_et_mariadb|cette documentation]].

===== Liens =====

* [[https://www.mysql.com/|Le site officiel de MySQL]] [en]
  * [[http://www.mysql.fr/|Le site officiel francophone]] [fr]
  * [[http://www.addedbytes.com/cheat-sheets/mysql-cheat-sheet/|Memento MySQL]] [en]
  * [[http://www.system-linux.eu/index.php?category/Mysql|Aller plus loin avec MySQL]] [fr]
  * [[http://www.generation-linux.fr/index.php?post/2007/12/17/9-cours-mysql|En savoir plus sur les commandes Mysql]] [fr] -> 3 petits cours sur MySQL à télécharger (PDF)
  * [[http://www.system-linux.eu/index.php?post/2008/12/29/Configure-et-Installation-de-mysql|Installation à la main]] [fr]
  * La page dédiée aux [[:mysql_outils|outils MySQL]]
  * [[:greensql|GreenSQL : Pare-feu applicatif SQL]]
  * [[http://www.system-linux.eu/index.php?post/2009/06/28/Proteger-son-server-Mysql-avec-GreenSQL|Protéger son serveur MySQL avec GreenSQL]] [fr]
  * [[http://mysql.developpez.com/|La section dédiée à MySQL sur Developpez.com]] [fr]
  * La page des [[:SGBD]].
  * [[: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]]//

Merci de recopier le code ci-contre pour prouver que vous êtes humain : L E E X M Merci de laisser ce champ vide :

Résumé

Note : En modifiant cette page, vous acceptez que le contenu soit placé sous les termes de la licence suivante : CC Paternité-Partage des Conditions Initiales à l'Identique 3.0 Unported