Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Les deux révisions précédentesRévision précédente
Prochaine révision		Révision précédente
utilisateurs:bcag2:sql [Le 14/11/2017, 21:56] – [SQL] ajout note interfaces graphiques bcag2utilisateurs:bcag2:sql [Le 10/02/2021, 14:07] (Version actuelle) – [Autres opérations] bcag2
Ligne 1: Ligne 1:
 +{{tag>Brouillon Xenial Trusty sgbd sql serveur réseau}}
  
 +----
 +
 +====== SQL ======
 +
 +Le langage informatique [[wpfr>Structured_Query_Language|Structured Query Language, SQL en abrégé]] est utilisé pas les SQBD les plus courants.
 +
 +Le but de cette page n'est pas d'être une référence en la matière car il n'y a pas de particularité sous Ubuntu et les ressources ne manquent pas sur ce langage. Il s'agit juste de mettre en commun les quelques requêtes de base en les dissociant des pages qui peuvent s'y référer, telle que [[:mysql]], [[:mariadb]], [[:postgresql]]…
 +
 +<note tip>Les manipulations suivantes sont en ligne de commande. \\
 +Pour un usage occasionnel, il est bien plus confortable de recourir à une interface graphique telle que\\ [[:phpmyadmin]] (mysql et mariadb), [[:postgresql#clients_graphiques| pgadmin3 ou phppgadmin]] pour postgresql… </note>
 +===== Utilisation =====
 +
 +==== Lancer la console SQL ou le gestionnaire graphique ====
 +
 +Vous arriverez alors sur un prompt du type :
 +
 +  mysql>
 +  ou
 +  MariaDB [(none)]>
 +
 +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 clore avec le « **;** ».
 +
 +==== Sélectionner la base de données à utiliser ====
 +
 +Soit directement lorsque vous lancez le client MySQL en [[:commande_shell|ligne de commande]]:
 +
 +  mysql -u root -D votre_base
 +
 +Soit une fois connecté à MySQL en tapant la [[:commande_shell|commande]] suivante dans un [[:terminal]]:
 +
 +  mysql> use votre_base
 +
 +
 +==== Configuration en UTF-8 ====
 +Il suffit d'ajouter avec les [[sudo|droits d'administration]] les deux lignes suivantes à la fin de la section « ''[mysqld]'' » du fichier **/etc/mysql/mysql.conf.d/mysqld.cnf** :
 +<file>
 +character-set-server=utf8
 +skip-character-set-client-handshake
 +</file>
 +
 +puis de relancer le serveur mysql :
 +
 +  sudo service mysql restart
 +
 +Vous pouvez vérifier le résultat une fois loggé dans mysql grâce à la commande suivante :
 +
 +<code SQL>
 +SHOW VARIABLES LIKE 'char%';
 +</code>
 +
 +Résultat :
 +
 +<file>
 +mysqld> SHOW VARIABLES LIKE 'char%';
 ++--------------------------+----------------------------+
 +| Variable_name            | Value                      |
 ++--------------------------+----------------------------+
 +| character_set_client     | utf8                       | 
 +| character_set_connection | utf8                       | 
 +| character_set_database   | utf8                       | 
 +| character_set_filesystem | binary                     | 
 +| character_set_results    | utf8                       | 
 +| character_set_server     | utf8                       | 
 +| character_set_system     | utf8                       | 
 +| character_sets_dir       | /usr/share/mysqld/charsets/ | 
 ++--------------------------+----------------------------+
 +8 rows in set (0.00 sec)
 +</file>
 +
 +===== 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 SQL>
 +use nom_de_la_base;
 +</code>
 + 
 +=== Créer une base de données ===
 +
 +<code SQL>
 +create database nom_de_la_base;
 +</code>
 +
 +=== Liste des bases de données ===
 +
 +<code SQL>
 +show databases ;
 +</code>
 +
 +=== Supprimer une base de données ===
 +
 +<code SQL>
 +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 SQL>
 +source fichier.sql;
 +</code>
 +
 +
 +==== Tables ====
 +
 +=== Liste des tables de la base de données active ===
 +<code SQL>
 +SHOW tables;
 +</code>
 +
 +=== Structure d'une table ===
 +<code SQL>
 +DESCRIBE nom_table;
 +</code>
 +
 +=== Renommer une table ===
 +<code SQL>
 +ALTER TABLE nom_table RENAME AS nouveau_nom;
 +</code>
 +
 +=== Vider une table ===
 +<code SQL>
 +TRUNCATE nom_table;
 +</code>
 +
 +Pour la syntaxe des requêtes SQL, reportez-vous à la [[http://dev.mysql.com/doc/|documentation MySQL]].
 +
 +
 +==== Utilisateurs ====
 +
 +=== Lister les utilisateurs ===
 +<code>SELECT User FROM mysql.user;</code>
 +
 +=== Créer un utilisateur ===
 +
 +<code SQL>
 +CREATE USER 'nom_utilisateur'@'localhost';
 +# ou directement avec le mot de passe avec :
 +CREATE USER 'nom_utilisateur'@'localhost' IDENTIFIED BY 'mon-mot-de-passe';
 +</code>
 +
 +=== Définir un mot de passe ===
 +
 +<code SQL>
 +set password for 'nom_utilisateur'@'localhost' = password('mot_de_passe');
 +</code>
 +
 +=== Supprimer un utilisateur ===
 +
 +<code SQL>
 +drop user 'nom_utilisateur'@'localhost';
 +</code>
 +
 +==== Privilèges ====
 +
 +=== Donner tous les droits sur une base de données à un utilisateur précis ===
 +
 +<code SQL>
 +grant all on nom_base.* to 'nom_utilisateur'@localhost;
 +</code>
 +
 +=== Supprimer les droits sur une base de données à un utilisateur précis ===
 +
 +<code SQL>
 +revoke all privileges on nom_base.* from 'nom_utilisateur'@localhost;
 +</code>
 +
 +
 +===== Autres opérations =====
 +
 +Qt avec Mysql:
 +
 +Consulter la page [[https://doc.qt.io/qt-5/sql-driver.html|drivers SQL pour Qt5]]
 +
 +Installer la librairie pour mysql afin de l'utiliser sous QT :
 +
 +<code>sudo apt-get install libqt4-sql-mysql</code>
 +
 +==== Sauvegarde et importation ====
 +
 +Pour **exporter** la base de donnée « maBase », utilisez la commande :
 +
 +<code bash>mysqldump -u root -p 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 bash>mysqldump -u root -p 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 bash>mysql -u root -p maBase < maBase_backup.sql</code>
 + 
 +Puis tapez votre mot de passe.
 +
 +ou si le dump est compressé :\\
 +<code bash>gunzip < mabase2donnees.sql.gz | mysql -u [uname] -p[pass] [dbname]</code>
 +
 +
 +<note tip>
 +Si vous souhaitez mettre en place une solution de sauvegarde automatique de bases de données à intervalle régulier (quotidien/hebdomadaire/mensuel), rendez-vous sur la page [[:automysqlbackup]].
 +</note>
 +
 +==== 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 :
 +
 +  echo "CREATE DATABASE MaBaseDeDonnee" | mysql -u root -h localhost -p
 +
 +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 :
 +
 +  cd ~/MonDossierContenantLeDump/MaBaseDeDonnee
 +  sudo cp * /var/lib/mysql/MaBaseDeDonnee
 +
 +Changer les droits sur les fichiers copiés :
 +
 +  sudo chmod -R ug+rw /var/lib/mysql/MaBaseDeDonnee/
 +  sudo chown -R mysql:mysql /var/lib/mysql/MaBaseDeDonnee/
 +
 +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.
 +
 +
 +==== Déplacer ses bases de données MySQL ====
 +
 +<note warning>Attention !! lors d'une migration ubuntu14.04 -> 16.04 ne lancez pas la migration sans avoir remis mysql en état d'origine (/var/lib/mysql). Faites les opérations inverses décrites ci-dessous pour remettre vos données dans /var/lib/mysql.</note>
 +L'emplacement des données de MySQL est indiqué dans la variable **datadir** qui se trouve dans le fichier ''my.cnf''.
 +Toutefois, il peut exister plusieurs fichiers ''my.cnf'' :
 +<code>
 +$ mysqld --verbose --help | head -n13 | tail -n2
 +Default options are read from the following files in the given order:
 +/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 
 +</code>
 +Éventuellement ce fichier contient des directives **!includedir**. Il faut alors chercher dans ces répertoires le fichier qui contient **datadir**.
 +
 +Dans la suite, nous allons déplacer les données dans **/home/mysql**.
 +
 +Tout d'abord on arrête le service :
 +<code>$ sudo service mysql stop</code>
 +[[:tutoriel:comment_modifier_un_fichier|On édite le fichier]] qui contient **datadir** et on met la nouvelle valeur :
 +<code>
 +datadir = /home/mysql
 +</code>
 +On suppose que l'ancienne valeur de **datadir** était **/var/lib/mysql**.
 +On déplace les données :
 +<code>$ sudo mv /var/lib/mysql /home</code>
 +
 +Malheureusement cela ne suffit pas.
 +Le premier problème vient du fichier''/usr/share/mysql/mysql-systemd-start'' :
 +<code>
 +$ head  /usr/share/mysql/mysql-systemd-start -n25 | tail -n16
 +sanity () {
 +  if [ ! -r /etc/mysql/my.cnf ]; then
 +    echo "MySQL configuration not found at /etc/mysql/my.cnf. Please create one."
 +    exit 1
 +  fi
 +
 +  if [ ! -d /var/lib/mysql ] && [ ! -L /var/lib/mysql ]; then
 +    echo "MySQL data dir not found at /var/lib/mysql. Please create one."
 +    exit 1
 +  fi
 +
 +  if [ ! -d /var/lib/mysql/mysql ] && [ ! -L /var/lib/mysql/mysql ]; then
 +    echo "MySQL system database not found. Please run mysql_install_db tool."
 +    exit 1
 +  fi
 +}
 +</code>
 +
 +Il faut donc créer le répertoire **/var/lib/mysql/mysql** avec les bons droits :
 +<code>$ sudo mkdir -p /var/lib/mysql/mysql
 +$ sudo chown -R mysql:mysql /var/lib/mysql
 +$ sudo chmod -R 700 /var/lib/mysql</code>
 +
 +Il faut maintenant configurer [[:apparmor|AppArmor]]. D'après ce qui suit, nous devons modifier le fichier ''local/usr.sbin.mysqld'' :
 +<code>
 +$ tail /etc/apparmor.d/usr.sbin.mysqld -n3
 +  # Site-specific additions and overrides. See local/README for details.
 +  #include <local/usr.sbin.mysqld>
 +</code>
 +[[:tutoriel:comment_modifier_un_fichier|Modifier le fichier]] ''/etc/apparmor.d/local/usr.sbin.mysqld'' en ajoutant :
 +<code>
 +# Allow data dir access
 +  /home/mysql/ r,
 +  /home/mysql/** rwk,
 +</code>  
 +Rechargez :
 +<code>sudo service apparmor reload</code>
 +Redémarrez le service :
 +<code>sudo service mysql start</code>
 +Vérifiez le changement :
 +<code>
 +$ mysql -u root -p -e 'select @@datadir'
 +Enter password: 
 ++--------------+
 +| @@datadir    |
 ++--------------+
 +| /home/mysql/ |
 ++--------------+
 +</code>
 +
 +==== 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 Web =====
 +
 +Il est possible d’administrer un serveur MySQL via une interface Web avec [[:phpMyAdmin]]. D'autres outils sont également disponibles et vous en trouverez une liste non exhaustive sur la page [[:mysql_outils|Outils MySQL]].
 +
 +===== 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
CC Paternité-Partage des Conditions Initiales à l'Identique 3.0 Unported Sauf mention contraire, le contenu de ce wiki est placé sous les termes de la licence suivante : CC Paternité-Partage des Conditions Initiales à l'Identique 3.0 Unported