coopernet Réussir son site web et ses applications de la conception à la réalisation

Historique

MySQL a été créé par la société suédoise MySQL AB (co-fondée par David Axmark, Allan Larsson et Michael Widenius). C’est ce dernier qui nommera l’application du prénom de sa première fille (My) et de l’accronyme SQL. La première version de MySQL date de 1995. La société fut rachetée par Sun Microsystems en 2008 puis par Oracle en 2009.

Oracle assurant ainsi le contrôle total des deux serveurs de bases de données les plus utilisés au monde, MySQL et Oracle Database.

Depuis 2012, Michael Widenius développe une version libre de MySQL appelée MariaDB, du prénom de
sa deuxième fille.

MySQL est encore distribuée aujourd’hui sous deux licences, l’une libre MySQL Community Server et
l’autre commerciale MySQL Standard ou Entreprise.

Un serveur de base de données structuré

Mysql, comme de nombreux autres serveurs du même type, structure ses données, en tables, puis en colonnes. Les lignes de ces tables étant les enregistrements inscrits dans la base de données.
Les tables contiennent généralement des index permettant au moteur de rechercher rapidement des données. Par exemple : Une table des utilisateurs avec les colonnes nom, prénom et email. L’index pouvant, éventuellement, être créé sur la colonne email.
MySQL est également un système de bases de données dit « relationnel » car nous allons pouvoir établir des relations entre différentes tables. Par exemple, lier la table des utilisateurs à celles des achats réalisés dans le cadre d’une application de commerce électronique.

SQL, un langage (quasi) universel

Pour réaliser des requêtes auprès d’un serveur MySQL (ou MariaDB) nous utilisons le langage SQL (Structured Query Language). Il existe de nombreuses versions de SQL en fonction des serveurs.
Cependant, un socle commun existe et permet aux développeurs de communiquer aisément avec les différents serveurs de bases de données SQL.

Quelques requêtes de base

Se connecter à un server local

mysql -u root -p // pour l'utilisateur root

Lister les bases de données présentes 

mysql> show databases;

Se connecter à une base de données en particulier

mysql>  use nomdelatable;

Créer la table "node"

CREATE TABLE `node` (
  `nid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(32) CHARACTER SET ascii NOT NULL COMMENT 'The type of the node.',
  `title` varchar(255) NOT NULL,
  `body` longtext,
  `path` varchar(255) DEFAULT NULL,
  UNIQUE (path),
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='The base table for node.';

Ajouter un champ à une table

ALTER TABLE node ADD COLUMN seo_title VARCHAR(255) AFTER title;

Utiliser une base de données

mysql> use nomdelabd;

Lister les tables 

mysql> show tables;

Voir la structure d’une table

DESCRIBE nomdelatable;

Rechercher un enregistrement

SELECT nom, prénom, email FROM utilisateurs WHERE email=‘yvan@coopernet.fr'

Ajouter un enregistrement

INSERT INTO utilisateurs SET nom=‘nison’, prenom=‘laurent’, email=‘laurent@neore.com'

Modifier un enregistrement

UPDATE utilisateurs SET email=‘laurent@neore.fr’ WHERE email=‘laurent@neore.com'

Supprimer un enregistrement

DELETE FROM utilisateurs WHERE email=‘sundar-pichai@google.com’

Exporter (dump) et importer une base de données

L'export comprend à la fois la structure de votre base de données mais également les données dans un fichier ".sql" que vous pouvez ensuite importer.

Exemple d'export :

mysqldump -u username -p database_name > 2021-03-03.sql

Exemple d'import :

mysql -u username -p database_name < 2021-03-03.sql

Installation de MySQL sous Ubuntu

L’installation de MySQL sous Ubuntu peut se faire en téléchargement les fichiers binaires directement sur le site de MySQL. Cependant, nous préférerons utiliser « apt » pour installer et maintenir MySQL à jour. MySQL n’utilise pas les serveurs de dépôts génériques, nous allons donc devoir adapter un peu notre installation.Voici les commandes à réaliser :

Mise à jour de la base de données des paquets

sudo apt update

Télécharger le paquet de configuration de apt

sudo wget https://repo.mysql.com/mysql-apt-config_0.8.12-1_all.deb

Installation du paquet

sudo dpkg -i mysql-apt-config_0.8.12-1_all.deb

Nouvelle mise à jour des paquets qui intégrera les serveurs de dépôt de MySQL

sudo apt update

On lance l’installation

sudo apt install mysql-server

Une fois l’installation réalisée, nous pouvons configurer le compte root de MySQL grâce à l’utilitaire suivant :

sudo mysql_secure_installation

On vérifie que tout fonctionne

sudo service mysql status

Démarrer et Arrêter MySQL

MySQL est installé avec toutes les options de gestion des services sous Ubuntu. Il est ainsi très facile de démarrer et d’arrêter le serveur MySQL

Démarrer MySQL

sudo service mysql start

Arrêter MySQL

sudo service mysql stop

Connaître le statut actuel de MySQL

sudo service mysql status

Travaux pratiques pour utiliser le client mysql

Un client mysql est également installé lors de l’installation de la version serveur. Nous pouvons donc l’utiliser pour réaliser quelques exercices simples :
Commençons par nous connecter sur notre serveur avec le compte root

mysql -u root -p

Créons une nouvelle base de données : test

CREATE DATABASE test;

Créons un utilisateur local : machin

CREATE USER machin@localhost IDENTIFIED BY ‘motdepasse’;

Donnons tous les droits à machin sur la base de données test

GRANT ALL PRIVILEGES ON test.* TO machin@localhost;

On quitte le client mysql

quit;

Nous pouvons donc maintenant utiliser l’utilisateur machin pour faire tout ce qu’on veut, mais
uniquement dans la base de données test.

innoDB ou MyISAM

Pour savoir quel moteur de stockage est utilisé par votre table :

SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='node' AND TABLE_SCHEMA='mycms';

Un moteur de stockage est constitué d’algorithmes qui seront utilisés par le Système de Gestion de Base de Données Relationnelles pour enregistrer les données et les récupérer au moyen de requêtes SQL.

En résumé, il faut favoriser innoDB car :

• Il gère les transactions (instructions sql BEGIN, COMMIT, ROLLBACK…)
• il permet de s'assurer que que chaque enregistrement sera complètement réussi, ou complètement échoué,
• Il supporte les clés étrangères et les intégrités référentielles
• Il possède un système de récupération automatique en cas de crash

Transactions

Les transactions permettent de sécuriser vos développements qui utilisent une base de données. Sans elles, on risquerait de se retrouver avec des opérations qu'à moitié accomplies. Avec elles, on peut regrouper les requêtes sous forme de "blocs" pour s'assurer que :

• soit l'ensemble des requêtes du bloc est entièrement exécuté
• soit aucune des requêtes n'est exécuté

On dit que cela permet de préserver l'intégrité des données de la base.

Exemple de cas d'utilisation 

Imaginons que vous vous appêtiez à supprimer une page de votre site web. Pour que votre site web reste intègre, il serait bien que tous les liens provenant des autres pages de votre site vers cette page soient supprimés. Il ne faut pas que le bloc de requêtes que cela consitute s'arrête au milieu, sinon, vous aurez  des liens pointant vers une page qui n'existe plus...

Exemple de code 

try {  
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $dbh->beginTransaction();
  $dbh->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
  $dbh->exec("insert into salarychange (id, amount, changedate) 
      values (23, 50000, NOW())");
  $dbh->commit();

} catch (Exception $e) {
  $dbh->rollBack();
  echo "Failed: " . $e->getMessage();
}