📚ancien TD05 : MYSQL en ligne de commandes

OBJECTIF

Créer une base de données et un utilisateur dédié pour le projet SAE203 Importer vos tables de données dans la base.

PREAMBULE

La base de données devra se nommée sae203 L'utilisateur sera nommée sae203 avec le mot de passe de votre choix La base doit contenir deux tables nommées selon le choix de votre sujet. Les deux tables doivent répondre au cahier des charges données en début de SAE

PREREQUIS

Exemple de MLD + Fichier excel pour export .csv

Voici le MLD suivant : films(film_id, film_titre, film_annee, film_resume, film_duree, film_genre, film_photo, _real_id) realisateur(real_id, real_nom, real_prenom, real_nationalite)

Le fichier Excel de données correspond sera le suivant. Une feuille par table. Bien stocker la clé étrangère de la table principales (ici dans film : _real_id avec un underscore au début pour indiquer que c'est une clé étrangère)

EXPORTATION DES DONNEES

Après avoir créé vos 2 feuilles de calcul dans Excel, exportez-les au format csv en utilisant enregistrer-sous et en choisissant le format .csv (séparateur ; de préférence)

CREATION DE LA BASE sur le VPS

Comme vous avez pu le constater, vous ne pouvez pas créer de nouvelle base en utilisant phpmyadmin et votre login étudiant. Ceci pour des raisons de sécurité , mais aussi de bonne pratique : 1 Projet = 1 Base = 1 Utilisateur . En ne stockant pas toutes les données dans la même base, vous améliorez les performances et vous évitez de tout perdre en cas de crash ou de compromission d'un compte utilisateur.

Nous devons donc créer une nouvelle base et un nouvel utilisateur en utilisant la ligne de commande et le client mysql fournit sur vos VPS.

CONNEXION AU VPS et lancement du client MYSQL

1 - Connectez-vous a à votre VPS avec votre compte MMI 2 - Elevez vos privilèges en root 2 - Exécutez la commande mysql (sans aucun arguments)

Vous arrivez alors sur l'invite de commandes du client : MariaDB [(none)]>

CREATION D'UNE NOUVELLE BASE et d'un UTILISATEUR

A partir de l'invite de commandes du client , saisissez les commandes suivantes

create database sae203;
create user 'sae203'@'localhost' identified by 'MotDePasseFortDeVotreChoix';
grant all on sae203.* to 'sae203'@'localhost';
quit

1 - Création de la base de données sae203 2 - Création de l'utilisateur sae203 et son mot de passe (remplacez le mot de passe !!!) 3 - Attribution de tous les privilèges (all) à l'utilisateur sae203 sur toutes les tables (.*) de la base. 4 - On quitte (quit) le client mysql pour revenir à l'invite de commandes du shell (~#)

A chaque commande, le serveur vous répond Query OK pour vous signaler que la syntaxe est correcte et que la commande a été exécutée .

IMPORTATION DES DONNEES

Notre base et notre utilisateur étant créés , nous allons pouvoir importer nos données.

ETAPE 1 : Connexion à phpmyadmin

Laissez de coté votre terminal pour l'instant, nous y reviendrons en fin de TD pour découvrir quelques autres commandes. Pour continuer, ouvrez un navigateur avec l'URL: MMI.mmi-troyes.fr/adminsql

ETAPE 2 : CREATION DES TABLES

Sélectionnez la base sae203 et créez une nouvelle table avec le nom des champs que vous avez déterminés dans votre MLD.

Répétez l'opération pour la seconde table.

ETAPE 3 : IMPORTATION DES DONNEES

Nos tables étant maintenant créées, nous allons pouvoir les remplir en utilisant l'onglet importer

Jeu de caractères : UTF-8

Importer à partir du début : 1 (pour omettre la ligne contenant le nom des champs)

Désactiver la vérification des clés étrangères

Choisissez le format du fichier à importer : CSV par défaut

Choisissez votre séparateur de colonnes (dans l'exemple ; ) SI les colonnes (les champs) sont entourées de " , précisez-le (ce n'est pas le cas ici)

En CSV, toutes lignes sont terminées par auto

Cliquez sur Exécuter pour importer les données

Vous pouvez vérifier les données importées en cliquant sur le nom de la table dans la liste et en sélectionnant l'onglet parcourir.

Répétez l'opération avec la seconde table pour terminer l'importation des données L'importation des données est terminée

RETOUR DANS LE TERMINAL

Pour continuer ce TD, nous allons maintenant voir ensemble quelques commandes nous permettant de manipuler nos données et nos tables sans passer par phpmyadmin. Ouvrez de nouveau votre terminal, reconnectez-vous en ssh si nécessaire et relancez le client mysql

PETITE VISITE DE VOS BASES DE DONNEES:

1. show databases; pour voir la liste des bases de données sur votre serveur

2. use sae203; pour utiliser la base sae203 (l'invite devient MariaDB [sae203]>)

3. show tables; liste les tables de la base active

4. describe NomTable; pour voir la structure d'une table

5. select * from NomTable ; pour voir les enregistrements d'une table

QUELQUES AUTRES COMMANDES UTILES:

Attention les commandes de suppression (drop) sont irréversibles

1. drop database NomBase; Supprime une base de données et son contenu

2. drop table NomTable; Supprime une table et son contenu

3. drop user 'NomUser'@'localhost'; Supprime un utilisateur

ET POUR METTRE UN NOUVEAU MOT DE PASSE à un utilisateur si vous l'avez oublié:

UPDATE mysql.user SET password=PASSWORD("nouveau_mdp") where User="NomUser"; 
flush privileges;

SAUVEGARDE DE LA BASE DE DONNEES

Pour terminer ce TD, quittez le client mysql avec la commande quit Et sauvegardons notre base de données dans le dossier /var/www/sae203 en saisissant simplement la commande :

mysqldump sae203 > /var/www/sae203/sae203.sql

Si vous ouvrez le fichier sae203.sql, vous pouvez constatez qu'il ne contient que des commandes SQL(DROP TABLE, CREATE TABLE, INSERT INTO etc.). Pour restaurer la base en cas de Crash ou de pertes des données, il suffira simplement de faire la commande:

mysql sae203 < /var/www/sae203/sae203.sql

Donc, conservez bien ce fichier précieusement (sur le dépôt GitHub de la sae203 par exemple)