View
228
Download
0
Category
Preview:
Citation preview
8/8/2019 Cours Mysql[1]
1/135
Administration et exploitation du SGBDR MySQL
Maurice Libes
libes@com.univ-mrs.fr Centre d'Ocanologie de Marseille
UMS 2196 CNRS
15-18 Octobre 2004
8/8/2019 Cours Mysql[1]
2/135
ML MySQL 102/09/04
Plan du cours MySQL
Installation de MySQL
Lancement du serveur mysqld Configuration et Paramtrage du serveur Cration d'une Base de Donnes
La gestion des droits sur les BasesLa base d'administration de mysql
8/8/2019 Cours Mysql[1]
3/135
ML MySQL 102/09/04
Plan du cours MySQL
Gestion des comptes utilisateursAjout et suppression d'utilisateursAffectation des permissions pour des utilisateurs
Design et Cration d'une BaseCration des tablesLes types de donnes
8/8/2019 Cours Mysql[1]
4/135
ML MySQL 102/09/04
Plan du cours MySQL
Manipulation des donnes dans les tablesInsertion, selection, modification, destructionInstructions de transactions et de verrouillage
Administration, sauvegarde, rparation desBasesInstructions d'optimisation des BasesUtilisation d'une API PHP ou Perl
8/8/2019 Cours Mysql[1]
5/135
ML MySQL 102/09/04
Premiers pas
Installation de MySQLLancement du serveur Arrt du serveur Configuration de MySQLConnexion au serveur mysqld
8/8/2019 Cours Mysql[1]
6/135
ML MySQL 102/09/04
Installation
partir des RPMrpm -ivh
rpm -qa | grep -i MySQL php-mysql-4.3.2-3mdk Libmysql12-4.0.15-1mdk
perl-Mysql-1.22_19-8mdk MySQL-4.0.15-1mdk MySQL-client-4.0.15-1mdk MySQL-common-4.0.15-1mdk
partir des sources./configure -helptar zxvf mysql.tar.gz
./configure prefix=/opt/mysql with-charset=latin1make && make install
8/8/2019 Cours Mysql[1]
7/135
ML MySQL 102/09/04
Installation des binairesA partir des binaires proposs sur le site de MySQL http://dev.mysql.com/downloads/mysql/4.1.html
shell> useradd -g mysql mysql shell> cd /usr/local shell> gunzip < /PATH/TO/MYSQL-VERSION-OS.tar.gz | tar xvf
-shell> ln -s FULL-PATH-TO-MYSQL-VERSION-OS mysql shell> cd mysql shell> scripts/mysql_install_db --user=mysql shell> chown -R root .shell> chown -R mysql datashell> chgrp -R mysql .shell> bin/mysqld_safe --user=mysql &
8/8/2019 Cours Mysql[1]
8/135
ML MySQL 102/09/04
Installation de la base
d'administrationDans l'installation partir des rpm, la based'administration mysql est installe par dfaut dans /var/lib/mysql (paramtre datadir du daemon mysqld)
Si on installe partir des binaires, ou que l'on souhaiterecrer une nouvelle bases d'administration
Lancer le script./bin/mysql_install_db
8/8/2019 Cours Mysql[1]
9/135
ML MySQL 102/09/04
Lancement Crer un utilisateur et un groupe mysql qui servira dfinir l'identit du serveur mysqld. Il faut faire appartenir les bases cet utilisateur afin qu'il ait toutes les permissions dans lestables
$ chown -R mysql .mysql /opt/mysql/var/
Lancer le daemon serveur mysqld$ /opt/mysql/bin/mysqld_safe --basedir=/opt/mysql --
datadir=/opt/mysql/var/ --user=mysql --log --log_isam --log-update&
Starting mysqld daemon with databases from /opt/mysql/var/
8/8/2019 Cours Mysql[1]
10/135
ML MySQL 102/09/04
Lancement de MySQL$ /etc/rc.d/init.d/mysql -?
Utilisation : /etc/rc.d/init.d/mysql {start | stop | status | reload | restart }$ ps ax |grep mysql 1944 ? S 0:00 /bin/sh /usr/bin/mysqld_safe --datadir =/var/lib/mysql
pid-file=/var/lib/mysql/localhost.pid log --log-update1977 ? S 0:00 /usr/sbin/mysqld --basedir =/ --datadir =/var/lib/mysql--user =mysql -- pid-file=/var/lib/mysql/localhost.pid skip-locking
1978 ? S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/localhost.pid skip-locking
--log-update permet de loguer toutes les changements dans une base
et de raliser des backup incrmentaux. Toute l'histoire d'une basepeut etre loggue dans /var/lib/mysql/localhost.log
8/8/2019 Cours Mysql[1]
11/135
ML MySQL 102/09/04
Lancement de MySQLComment est lanc le serveur dans /etc/rc.d/init.d/mysql?$ cat /etc/rc.d/init.d/mysql
gprintf "Starting MySQL Server"$bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file 2>&1 |
logger -t mysqld_safe & success
$ opt/mysql/bin/mysqld_safe --basedir=/opt/mysql/ --datadir=/opt/mysql/var/ --user=mysql --log --log_isam --log-update
Starting mysqld daemon with databases from /opt/mysql/var/
Pour connatre l'ensemble des options de mysqldman mysqld
man mysqld_safe
8/8/2019 Cours Mysql[1]
12/135
ML MySQL 102/09/04
Arrt du serveur MySQL
$ mysqladmin -u root -p shutdown
$ /etc/rc.d/init.d/mysql stop
$ killall mysqld
$ mysqld_pid=`cat /opt/mysql/var/localhost.pid`$ kill $mysqld_pid
8/8/2019 Cours Mysql[1]
13/135
ML MySQL 102/09/04
Configuration de MySQLOptions du serveur mysqld :
Soit au lancement sur la ligne de commandeSoit dans un fichier de conf /etc/ my.cnf
mysqld lit ses options dans les sections [mysqld] et [server].mysqld_safe lit ses options dans les sections [mysqld],
[server], [mysqld_safe], et [safe_mysqld] mysql.server lit ses options dans la partie [mysqld] et
[mysql.server]
Pour voir les options que l'on peut placer dans ces sections : mysqld help
8/8/2019 Cours Mysql[1]
14/135
ML MySQL 102/09/04
Options disponibles de my.cnf [client]
host=localhostuser=user_mysqlpassword=toto
[mysqld]
datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockskip-innodb
[mysql.server]user=mysqlbasedir=/var/lib
[safe_mysqld]err-log=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid
8/8/2019 Cours Mysql[1]
15/135
ML MySQL 102/09/04
Connexion au serveur
Connexion au serveur MySQLmysql -u root -h localhostLa premire fois pas de mot de passe pour se connecter !!
Mettre tout de suite un mot de passe pour l'utilisateur root de MySQL !
/opt/mysql/bin/mysqladmin -u root password 'titi'
Quelques commandes intressantes:$ mysqladmin variables | mysql> show variables$ mysqladmin version$ mysqladmin status | mysql> show status
8/8/2019 Cours Mysql[1]
16/135
ML MySQL 102/09/04
Utiliser une base courantePour spcifier l'utilisation d'une base particulire :
1. Au lancement du clientMysql -u root -p CHOCOLATS
2.1 Sous l'interpreteur mysqlMysql>Use CHOCOLATSMysql> select * from CLIENTS
2.2 En notation pointe en prfixant les tables par lenom de base [BASE.table]Mysql> select * fromCHOCOLATS.CLIENTS;
8/8/2019 Cours Mysql[1]
17/135
ML MySQL 102/09/04
Gestion des comptes
utilisateurs
Gestion des Accs la Base
8/8/2019 Cours Mysql[1]
18/135
ML MySQL 102/09/04
Scurit des accsMySQL utilise sa propre base d'administration /var/lib/mysql/mysql pour grer la scurit des accs auxautres bases.Seul le user root de MySQL doit avoir accs cette
base!La base mysql utilise 5 tables pour dcider qui a lapermission de faire quoi sur quelle base , partir de quelle machine
La base mysql contient 5 tables d'administration$ ls /var/lib/mysql/mysql/
user.frm , host.frm , db.frmcolumns_priv.frm func.frm tables_priv.frm
8/8/2019 Cours Mysql[1]
19/135
8/8/2019 Cours Mysql[1]
20/135
ML MySQL 102/09/04
Les permissions MySQLmysql> SHOW PRIVILEGES;
SELECT : droit d'effectuer des recherches avec select INSERT : droit d'effectuer des insertions avec insert
UPDATE : droit d'effectuer des mises jour avec update DELETE : droit d'effectuer des destructions d'enregistrementsdans des tablesINDEX : droit de crer ou dtruire des index de tables
ALTER : droit de modifier la structure des tables avec alter CREATE : droit de crer des bases ou des tables avec create USAGE : droit de se connecter au serveur, sans rien faire d'autre
( utile uniquement pour changer le mot de passe de connexion )LOCK : droit de verrouiller/dverrouiller des tables
8/8/2019 Cours Mysql[1]
21/135
ML MySQL 102/09/04
Les permissions MySQLDrop : droit de dtruire des bases ou des tablesGrant : permet d'affecter des droits et permission un utilisateur References : (droit li aux foreign keys , inutilis encore dansles version actuelle de mysql.. )
Privilges globaux, ne s'appliquent pas UNE base particulireReload : permission de relancer le serveur mysql et d'ecrire lestables sur disques.
Shutdown : droit d'arrter le serveur mysqld Process : droit de contrler les processurs utilisateurs.File : droit d'crire ou lire dans des fichiers ascii avec lescommandes load data et into outfile
8/8/2019 Cours Mysql[1]
22/135
ML MySQL 102/09/04
Allouer des permissions:GRANT
Les permissions des tables se manipulent avec lescommandes sql GRANTet REVOKEou bien avecl'utilitairemysqlaccessGRANT permet de crer un utilisateur, lui allouer des
droits et changer son mot de passeGRANT priv_type [(column_list)] [, priv_type [(column_list)]] ON {tbl_name | * | *.* | db_name.*} TO user [IDENTIFIED BY [PASSWORD] 'password']
mysql> GRANT all ON test.* TO momo IDENTIFIED BY titi ;
mysql> GRANT select,insert ON mysql.* TO momo@localhost identified by "titi" [with GRANT OPTION];
les privilges file , reload , shutdown sont globaux pour tout leserveur et non pas pour une base particulire
mysql> grant file on *.* to momo@localhost ;
8/8/2019 Cours Mysql[1]
23/135
ML MySQL 102/09/04
affichage des droits d'accs
Les exemples prcdents insrent le user momo avec son motde passe, et la machine autorise, dans la table user , de la basemysql, sans aucun droit. Les droits sont placs pour la base mysqldans la table db
Vrification des permissions pour un utilisateur Les 2 privilges SELECT,INSERT sont placs dans la table DB pour une base particulireMysql > select * from db where user= momo
| localhost | mysql | momo | Y | Y | N | N | N | N | N | N| N | N | N | N |
Mysql>select * from user where user= momo | localhost | momo | 398e7500242ab90c | N | N | N | N | N | N | N
| N | N | N | N | N | N | N | N | N | N| N | N | N | N | |
mysql> SHOW GRANTS FOR root;mysql> SHOW GRANTS FOR 'root'@'localhost' ;$ mysqlaccess -t -d CHOCOLATS -u momo -U root -P
8/8/2019 Cours Mysql[1]
24/135
ML MySQL 102/09/04
2 commandes utiles
Changer le mot de passe d'un utilisateur GRANT USAGE ON *.* TOmomo@localhost IDENTIFIED BY'titi'
Crer un super-utilisateur (~root)GRANT ALL PRIVILEGES ON *.* TO supermomo@localhostIDENTIFIED BY 'titi' WITH GRANT OPTION;L'option GRANT donne le droit d'utiliser la commande GRANT etdonc de permettre un utilisateur d'en crer d'autre(NB: ne pas multiplier les super-utilisateurs !!)
8/8/2019 Cours Mysql[1]
25/135
ML MySQL 102/09/04
Oter des permissions: REVOKE
REVOKE permet de supprimer des droits pour des utilisateursREVOKE priv_type [(column_list)] [, priv_type [(column_list)]]
ON {tbl_name | * | *.* | db_name.*}FROM user [, user]
mysql> REVOKE ALL on *.* FROM momo ;mysql> REVOKE ALTER,DELETE,DROP on mysql.* FROM momo
mysql> REVOKE GRANT OPTION on mysql.* FROM momo@localhost mysql> REVOKE SELECT on mysql.* FROM 'momo'@'pcml.dom.fr'
8/8/2019 Cours Mysql[1]
26/135
ML MySQL 102/09/04
Enlever un utilisateur partir de la version 4.1.1 (ncessit d'enlever tous lesprivileges avant de dtruire l'utilisateur)
mysql> SHOW GRANTS FORtoto@localhost mysql> REVOKE ALL PRIVILEGES FROM
toto@localhost
mysql> DROP USER toto;
Pour des versions antrieuresmysql> DELETE FROM mysql.user WHERE
user='user_name' and Host='host_name';
Les permissions ont t places dans la table mysql.*, il faut les recharger en mmoire pour le serveur
mysql> FLUSH PRIVILEGES;
8/8/2019 Cours Mysql[1]
27/135
ML MySQL 102/09/04
Cration & destruction d'une base
Cration d'une base = crer un rpertoire1. $ mysqladmin -u root -p create NOMDEBASE 2. $ mkdir /var/lib/NOMDEBASE && chown mysql /
var/lib/NOMDEBASE 3. $ mysql -u root -p
mysql> CREATE DATABASE ma_base
Destruction d'une base$ mysqladmin -p drop NOMDEBASE mysql> DROP DATABASE ma_base
TP i d' B
8/8/2019 Cours Mysql[1]
28/135
ML MySQL 102/09/04
TP : cration d'une BaseCONFISEUR
Regarder le script script_creation_tables_base_confiseurs.txt
Se crer une base vierge /opt/mysql/bin/mysqladmin -u root -p create CHOCO
Crer les tables en batch avec le script /opt/mysql/bin/mysql -u root -p show tablesMysql> desc BOITESMysql> select * from BOITES
8/8/2019 Cours Mysql[1]
29/135
ML MySQL 102/09/04
TP : Mettre des droits sur la BaseCrer plusieurs utilisateurs et affecter des droits diffrents cesutilisateurs par GRANT et REVOKE
Un utilisateur qui aura tous les droits sur la BaseUn utilisateur qui n'aura que le droit de slectionner Un utilisateur qui aura le droit d'crire dans les tables et lesmodifier et rcuprer les slections dans un fichier
Conclusions?
GRANT all ON test.* TO momo@localhost IDENTIFIED BY titi ;Ouinsert into host (host,Db,Select_priv) values("localhost","CHOCOLATS","Y");delete from user where host="localhost" and user="";
8/8/2019 Cours Mysql[1]
30/135
ML MySQL 102/09/04
TP : Les droits sur la BaseModifier un mot de passe pour un utilisateur Enlever le droit update et file pour l'utilisateur qui les a.
8/8/2019 Cours Mysql[1]
31/135
ML MySQL 102/09/04
Maintenance et Administration de MySQL
Structures des tablesInstructions de maintenance des Tables
BACKUP TABLERESTORE TABLEREPAIR TABLECHECK TABLE
ANALYZE TABLEOPTIMIZE TABLE
8/8/2019 Cours Mysql[1]
32/135
8/8/2019 Cours Mysql[1]
33/135
ML MySQL 102/09/04
Structure des tables
Le fichier .MYI contient les index, et est le plus importantpour les performances des accs aux tables.La commande myisamchk est entirement consacreaux oprations sur ce fichier Les oprations vont concerner:
L'analyse et le listage des cls,La rparation de tables endommages, la re-cration etdestruction des cls...
8/8/2019 Cours Mysql[1]
34/135
ML MySQL 102/09/04
Backup des basesLes bases sont des rpertoires, et les tables desfichiers... les oprations de sauvegarde et de backupsont donc simples!
Avec un serveur en activit, pour assurer des copiesscurises:mysql>LOCK TABLES;
mysql>FLUSH TABLES( assure que tous les index et donnes sont bien crits avant
de commencer la copie.)Hors du contrle du serveur, et serveur arrt, on peutfaire rgulirement (cron) une simple copie des bases:
$ rsync -av /var/lib/mysql/unebase /opt/SAUVEBASE/
8/8/2019 Cours Mysql[1]
35/135
ML MySQL 102/09/04
Backup des bases
L'utilitairemysqldump permet de faire dessauvegardes compltes en ascii
Backup : $ mysqldump --opt database [table] >backup-file.sql
$ /opt/mysql/bin/mysqldump -u root -ptoto
CHOCOLAT BOITES
Restoration:$ mysql database < backup-file.sql
8/8/2019 Cours Mysql[1]
36/135
ML MySQL 102/09/04
Backup avec mysqlhotcopy Sauvegarde d'une base complte avec toutes lestables dans datadir (/var/lib/mysql/)
$ /usr/bin/mysqlhotcopy --debug -u root password='titi' CHOCOLATS CHOCOLATS-SAV
Sauvegarde de la table CLIENTS de la baseCHOCOLATS dans un rpertoire extrieur /var/lib/mysql
$ /usr/bin/mysqlhotcopy --debug --addtodest -u root -- password='aaa' CHOCOLATS./CLIENTS/ "/home/momo"
8/8/2019 Cours Mysql[1]
37/135
ML MySQL 102/09/04
BACKUP / RESTORE Mysql>BACKUP TABLE COMMANDES to"/var/lib/mysql/SAUVE/";
(NB1:le user mysql doit avoir le droit d'crire dans le rpertoireindiqu)NB2:cette commande n'est plus supporte... Il est prfrable
d'utiliser la commande mysqlhotcopy NB3: Ne sauvegarde que .frm et .MYD, les index doivent etrereconstruits (avec REPAIR TABLE matable USE_FRM )NB4: Ne fonctionne que sur des tables myisam
La commande RESTORE TABLE sert remettre enservice des tables sauvegardes par BACKUP TABLES mysql> RESTORE TABLE BOITES FROM
"/var/lib/mysq/SAUVE/";
8/8/2019 Cours Mysql[1]
38/135
ML MySQL 102/09/04
Maintenance de Tables
Pour vrifier et rparer des tables MyISAMCHECK TABLEREPAIR TABLE
Pour optimiser des tables MyISAM :OPTIMIZE TABLE
ANALYZE TABLE
L'utilitaire myisamchk hors du contrle duserveur
8/8/2019 Cours Mysql[1]
39/135
ML MySQL 102/09/04
Les problmes avec les Tables MyISAM
Le format des tables MyISAM est trs sr. Mais on peut avoir destables corrompues dans quelques cas malchanceux:
le serveur mysqld s'arrte pendant une critureArrt brutal de l'ordinateur Erreur hardware (contrleur de disque...)Utilisation d'un programme externe (myisamchk) sur une tablequi est en train d'tre modifie par le serveur en mme temps..
Les symptmes lors de l'accs une table
Incorrect key file for table: '...'. Try to repair it Utiliser
CHECK TABLE REPAIR TABLE Myisamchk
8/8/2019 Cours Mysql[1]
40/135
ML MySQL 102/09/04
Verification des tables : check En cas de problme tel que les donnes semblent corrompues,ou si l' on obtient constamment des erreurs lors d'accs unetable, il faut essayer de rparer la table suspecte avecl'utilitaire de ligne de commande myisamchk ou lescommandes SQL CHECK TABLE et REPAIR TABLE.
Vrification de la structure des tables... calcul et recherche desindex, tests de corruption sur les tables MyISAM
CHECK TABLEBOITES {QUICK | FAST | MEDIUM | EXTENDED |CHANGED}
identique : $ myisamchk --medium-check /path/tbl_name myisamchk --medium-check /opt/mysql/var/CHOCOLAT/BOITES
8/8/2019 Cours Mysql[1]
41/135
ML MySQL 102/09/04
Verification des tables : check En cas de corruption de tables, les problmes viennent le plussouvent du fichier des index et non pas des donnes.Pour vrifier simplement une table il faut utiliser l'optionQUICK. Si MYSQL trouve un erreur dans la table des donnes,la table est marque comme tant Corrupted et ne peut
plus tre utilise jusqu' ce qu'elle soit rpare!!!
FAST and CHANGED doivent tre utilise dans des scripts pour vrifier rgulirement l'etat des tablesMEDIUM passe en revue chaque ligne pour vrifier que les liens versles index sont corrects. Calcule un checksum pour chaque ligne et lecompare avec un checksum calcul sur les clsEXTENDED doit tre utilis aprs dtection d'une erreur qui pourrraitsubsister
8/8/2019 Cours Mysql[1]
42/135
ML MySQL 102/09/04
Rparation des tables : repair REPAIR TABLE rpare une table potentiellement corrompue.
En cas de dsastre, REPAIR TABLE devrait russir rcuprer les donnes partir de la table. Dans la majeurepartie des cas on ne devrait pas avoir besoin de cettecommande.
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLEtbl_name... [QUICK] [EXTENDED] [USE_FRM]
Cette commande a le mme effet que :$ myisamchk [ --recover | safe_recover ] tbl_nameCette commande ne fonctionne QUE sur des tables MyISAM
8/8/2019 Cours Mysql[1]
43/135
ML MySQL 102/09/04
Rparation des tables : repair Si l'option QUICK est donne, REPAIR TABLE essaye derparer uniquement l'arbre des index. La commande est equivalente
myisamchk --recover --quick.
Si l'option EXTENDED est donne, MySQL recalcule et re-cre les index ligne par ligne. quivalent :
myisamchk safe-recover.
A partir de MySQL 4.0.2, l'option USE_FRM de REPAIRTABLE permet de recrer le fichier des index `.MYI'
partir du fichier `.frm'
8/8/2019 Cours Mysql[1]
44/135
ML MySQL 102/09/04
Optimisation des tablesANALYZE[LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name
Cette instruction analyse et stocke la distribution des cls d'unetable. (Pendant l'analyse la table est verrouille (lock table))L'instruction fonctionne sur les tables MyISAM et InnoDB
Pour les tables MyISAM l'instruction est quivalente : myisamchk -a --description verbose /path/vers/latable
MySQL utilise la distribution des cls stockes pour dcider del'ordre dans lequel seront faites les jointures des tables.
8/8/2019 Cours Mysql[1]
45/135
ML MySQL 102/09/04
Optimisation des tables
ANALYSE TABLErecalcule les cls et utilise la distribution descls stockes pour dcider de l'ordre dans lequel seront faites les
jointures des tables.Il est recommend d'excuter myisamchk -a rgulirementlors de la cration d'une base (insertion des donnes dans lestables). En effet la majeure partie des donnes sont insres audbut de la cration des bases.
En excutant une analyse de table myisamchk -a rgulirement lors de l'insertion de donnes, on permet ce queles donnes soient conserves le plus efficacement possible
8/8/2019 Cours Mysql[1]
46/135
ML MySQL 102/09/04
Optimisation des tablesOPTIMIZE TABLErorganise une table en rcuprant les espacevides et en dfragmentant la table.
Syntaxe: OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
OPTIMIZE TABLE doit tre utilis dans les cas o :on a dtruit une grande quantit de donnes dans les tables.On a fait des changements de types sur les donnes (lignes delongueur variables comme VARCHAR, TEXT ou BLOB)
Les enregistrements dtruits sont conservs dans une liste chaine etles futures insertions de donnes pourront rutiliser les anciennespositions d'enregistrements dtruits. .
8/8/2019 Cours Mysql[1]
47/135
ML MySQL 102/09/04
Optimisation des tablesOPTIMIZE TABLE effectue les oprations suivantes :
1. rpare la table en rcuprant la place laisse par des lignesvides2. trie les index, s'ils ne sont pas tris3. met jour les statistiques des tables
8/8/2019 Cours Mysql[1]
48/135
ML MySQL 102/09/04
L'utilitaire myisamchk Commande entirement consacre l'analyse et lamaintenance (rparation) du fichier d'index .MYI et dedonnes .MYDMyisamchk peut tre utilis pour obtenir de l'informationsur les tables, pour les vrifier, les rparer ou les optimiser.On prconise d'effectuer des backups des bases avantd'effectuer des rparations sur les fichiers d'index.La plupart des actions de myisamchk sont galementralises par les commandes SQL prcdentes ( check,repair, analyse optimize ) sous le contrle du serveur mysqld.
8/8/2019 Cours Mysql[1]
49/135
ML MySQL1
02/09/04
L'utilitaire myisamchk Myisamchk est une commande qui est excute sous le shellet n'est donc pas sous le contrle du serveur MySQL. Lesmodifications engendres ncessiteront alors un reload duserveur ou un FLUSH des tables .L'avantage des commandes SQL est que les oprations
effectues sont sous le contrle du serveur (les locks de tablessont effectus, vidage des tampons)Avec myisamchk, on doit s'assurer que le serveur n'utilise pasles tables en mme temps.Si le serveur tourne, il faut forcer l'criture des tables qui sontbufferises en mmoire en utilisant FLUSH TABLES. Il fauts'assurer que personne n'utilise les tables pendant qu'onexcute myisamchk. CHECK TABLE est prfrable myisamchk pour vrifier les tables.
M i hk ifi
8/8/2019 Cours Mysql[1]
50/135
ML MySQL1
02/09/04
Myisamchk pour vrifier
Sans option : simple vrification des erreurs dans les tables$ myisamchk /opt/mysql/var/CHOCOLAT/*.MYI
--check, -c : cherche des erreurs dans la table (option par dfaut)--check-only-changed, -C : vrifie les tables qui ont changesdepuis la dernire vrification--extend-check, -e : Vrification complte et approfondie destables.Opration d'autant plus longue que la table a beaucoupd'index. Option utiliser dans des cas extrmes. myisamchk --medium-check devrait suffire pour trouver des erreurs dans latable.--extend-check : ncessite beaucoup de mmoire. On peut mettrela variable key_buffer_size a une plus grande valeur pour acclrer les calculs.
M i hk ifi
8/8/2019 Cours Mysql[1]
51/135
ML MySQL1
02/09/04
Myisamchk pour vrifier
--force, -f provoque une rparation automatique des tables simyisamchk trouve des erreurs. Le type de rparation est celui spcifidans l'option. --repair ou -r.--information, -i : affiche des informations statistiques sur la table
--medium-check, -m : vrification plus rapide qu'avec l'option --extend-check .--update-state, -U : stocke les informtions dans le fichier .MYI pour indiquer quand la table a t vrifie. Option inressante pour tirer bnfice de l'option --check-only-changed. On ne doit pas utiliser cetteoption si le serveur mysqld utilise la table sans l'option --skip-external-locking .
M i hk h h d
8/8/2019 Cours Mysql[1]
52/135
ML MySQL1
02/09/04
Myisamchk pour chercher des erreurs
myisamchk tbl_name : trouve 99.99% des erreurs.myisamchk -m tbl_name
trouve 99.999% des erreurs. Vrification des index de chaque ligne :Calcul d'un checksum pour chaque cl des lignes et vrifie que cettevaleur de checksum est gale au checksum des cls dans l'arbre des
index. myisamchk -e tbl_name :Vrification complte des donnes (``extended check''). Vrification enlecture de toutes les cls de chaque ligne pour vrifier qu'elles pointnt sur la bonne ligne.
myisamchk -e -i tbl_name idem ci dessus mais avec l'option -i affiche des informations statistiques
supplmentaires.
M i hk
8/8/2019 Cours Mysql[1]
53/135
ML MySQL1
02/09/04
Myisamchk pour rparer
--recover , -r : rpare une table endommage. Dfragmente les tables.Cette option convient presque tous les cas de problmes.Essayer l'option -o si les problmes persistent. Si la machine abeaucoup de mmoire, on peut augmenter la valeur du paramtre
sort_buffer_size /opt/mysql-4.1/bin/mysqladmin -u root -p variables |grep sort_buffer
--safe-recover, -o rparation avec une autre mthode, qui lit toutes les lignes dans
l'ordre et mets jour tous les index bass sur les lignes trouves.Mthode plus lente qu'avec -r et qui utilise plus de place disque
Essayer d'abord les rparations avec -r puis avec -o si chec.
M i hk l
8/8/2019 Cours Mysql[1]
54/135
ML MySQL1
02/09/04
Myisamchk pour analyser--analyse , -a : Analyse la distribution des cls. Cela amliore les performances
de jointure en permettant l'optimiseur de mieux choisir l'ordre dans lequelsera faite la jointure des tables et quelle cl sera utilise.
SHOW KEYS FROM tbl_name .
--description, -d : donne les principales informations sur la table. Lesinformations les plus importantes sont :Data records : 18 Deleted blocks: 0Recordlength: 320
Key Start Len Index Type1 2 4 unique char
--sort-index, -S tri l'arbre des index. Cela optimize les recherches par cl et les rends plusrapides.
Myisamchk en cas de crash
8/8/2019 Cours Mysql[1]
55/135
ML MySQL1
02/09/04
Myisamchk en cas de crashEtre sur que personne n'utilise les tables travers le serveur, pendant qu'onutilise myisamchk. Faire un backup avant d'utiliser myisamchk.faire mysqladmin flush-tables avant de commencer vrifier les tables. Sinonarrter le serveur mysqld pendant les vrifications et rparations de tables.La plupart des problemes surviennent sur les fichiers d'index .MYI ou de
donnes .MYD.Myisamchk cre une copie du fichier`.MYD' ligne ligne. Il termine larparation en dtruisant l'ancien fichier .MYD et en renommant le nouveauAvec l'option --quick , myisamchk ne cre pas le fichier .MYD temporaire , ilconsidre que le fichier .MYD est correct, et ne recre que le fichier des index
On peut spcifier l'option --quick 2 fois. Dans ce cas , myisamchk essaye deresoudre les erreurs en modifiant le fichier `.MYD' .
l hk
8/8/2019 Cours Mysql[1]
56/135
ML MySQL1
02/09/04
Conclusions sur myisamchkCommande utile pour conserver des Bases de donnes sansanomalies et rparer les erreurs ventuelles :
Inspecter : Excuter quotidiennement myisamchk -d -c -i -s conserver le compte rendu et le consulter Rparer : Lors de problme de crash (disque, serveur, machine..)analyser les tables des bases et passer myisamchk -a -r -eDfragmenter : Lancer rgulirement myisamchk -r -o ... d'autantplus souvent que les mises jour des tables (insert, update,delete) sont intenses et frquentesOptimiser,analyser : myisamchk-a -d
TP sur maintenance et analyse
8/8/2019 Cours Mysql[1]
57/135
ML MySQL1
02/09/04
TP sur maintenance et analysedes tables
Faire une sauvegarde avec mysqldumpFaire une sauvegarde avec mysqlhotcopy Faire une sauvegarde avec backup
Utiliser les commandesCheckRepair AnalyseOptimizemysqlchk
S ill d
8/8/2019 Cours Mysql[1]
58/135
ML MySQL1
02/09/04
Surveillance du serveur Le serveur MySQL gre les connexion des clients. Pour chaqueclient mysqld cre un processus fils (un thread ) qui prend encharge les requtes de ce client. Il y a donc autant de threads quede connexions.
Chaque thread accde aux donnes des tables en fonction desrequtes de son clientLe serveur gre un ensemble de base de donnes (chacune tantun rpertoire plac sous la racine du serveur, indique par l'option
datadir au lancement du serveur)Mysql fournit un ensemble d'utilitaires pour surveiller l'tat duserveur : mysqladmin, kill, show
Mysqladmin KILL
8/8/2019 Cours Mysql[1]
59/135
ML MySQL 102/09/04
Mysqladmin KILLCharge et status du serveur
$ mysqladmin -u user -p [ status | extended-status | variables ] Uptime: 36707 Threads: 2 Questions: 65 Slow queries: 0 Opens: 26 Flush tables: 3
Open tables: 9 Queries per second avg: 0.002
Surveillance des threads
$ mysqladmin -u user -p processlist Mysql> SHOW PROCESSLIST | Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----------+---------+------+-------+------------------+| 21 | momo | localhost | CHOCOLAT | Sleep | 2 | | |
| 22 | root | localhost | | Query | 0 | | show processlist |Tuer un thread
$ mysqladmin -u user -p kill 21mysql> KILL 21
S i i d
8/8/2019 Cours Mysql[1]
60/135
ML MySQL 102/09/04
Statistiques du serveur $ mysqladmin -p statusEnter password:Uptime: 27266 Threads: 1 Questions: 22 Slow queries: 0
Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 0.001
Statistiques TRES dtailles sur l'tat du serveur, nombre defichiers ouverts, de tables ouvertes, taux d'E/S en octets,nombre de cls utilises, nombre de query ...
SHOW STATUSToutes les variables qu'utilise le serveur
SHOW VARIABLESIdentique : $ mysqladmin variables
SHOW
8/8/2019 Cours Mysql[1]
61/135
ML MySQL 102/09/04
SHOWSHOW DATABASES : montre les bases existantes
Equivalent /opt/mysql/bin/mysqlshow -u root -ptoto
SHOW TABLES [like expr] : montre les tables d'une BaseEquivalent /opt/mysql/bin/mysqlshow CHOCOLAT -u root -ptoto
SHOW CREATE TABLE BONBONS;
SHOW COLUMNS : affiche les colonnes d'une table donnesSHOW [FULL] COLUMNS FROM tbl_name [LIKE 'pattern']
DESCRIBE [nom_de_table]Identique SHOW FIELDS FROM BOITES;
/opt/mysql/bin/mysqlshow CHOCOLAT CLIENTS -u root -ptoto
SHOW PROCESSLIST : affiche les threads actifs du serveur
SHOW
8/8/2019 Cours Mysql[1]
62/135
ML MySQL 102/09/04
SHOWSHOW PRIVILEGES : affiche la liste des privilges systme de mysql( partir de la version 4.1)SHOW INDEX : affiche les informations sur les index des tables
SHOW KEYS : identique$ mysqlshow -k nom_base nom_table : identique
SHOW GRANTS : affiche les privilges d'un utilisateur SHOW GRANTS FOR momo@localhost;
| GRANT USAGE ON *.* TO 'momo'@'localhost' IDENTIFIED BY PASSWORD'398e7500242ab90c' |
| GRANT ALL PRIVILEGES ON `CHOCOLAT`.* TO 'momo'@'localhost' WITH GRANTOPTION |
quivalent : mysqlaccess -t -d CHOCOLAT -u momo -U root -P
SHOW
8/8/2019 Cours Mysql[1]
63/135
ML MySQL 102/09/04
SHOWStatistiques TRES dtailles sur l'tat du serveur, nombre defichiers ouverts, de tables ouvertes, taux d'E/S en octets, nombrede cls utilises, nombre de query ...
SHOW STATUS
Toutes les variables qu'utilise le serveur SHOW VARIABLES
Identique : $ mysqladmin variables
Prise en compte des modifications d'accs :FLUSH
8/8/2019 Cours Mysql[1]
64/135
ML MySQL 102/09/04
FLUSH
Au lancement du serveur les tables sont charges en mmoire,pour des accs plus rapide. Si on modifie des tables, sur disque l'image en mmoire ne correspond pas. L'oprationflush indique au serveur de mettre en correspondance lesdonnes sur le disque et celles qui sont en mmoire.
Il est donc ncessaire de recharger les tables en mmoireaprs modification des tables de la base mysql
$ mysqladmin reload = $ mysqladmin flush-privileges$ mysqladmin flush-tables (ecrit les tables sur disques)$mysqladmin flush-logs (ecrit les fichiers de log) $mysqladmin refresh (les 2 prcdentes)
oumysql> FLUSH PRIVILEGES;
8/8/2019 Cours Mysql[1]
65/135
FLUSH
8/8/2019 Cours Mysql[1]
66/135
ML MySQL 102/09/04
FLUSHFLUSH PRIVILEGES :recharge en mmoire (et donc prend encompte) les privilges de la table des permissions de la basemysql (inutile avec GRANT),
FLUSH TABLES [nom_table] : criture des mises joursdiffres.. la commande ferme toutes les tables en cours d'utilisation et les rouvre . La commande dtruit galement lammoire cache des rsultats issus des requtes SQL comme lacommande RESET QUERY CACHE.FLUSH STATUS : remise zro de la plupart des variables de
mysql. Utile en cas de debuggage de certaines requetes SQL.Ces commandes SQL, FLUSH, ralisent les mmes fonctions quela commande mysqladmin avec les options flush-hosts, flush-logs, flush-privileges, flush-status, ou flush-tables.
RESET
8/8/2019 Cours Mysql[1]
67/135
ML MySQL 102/09/04
RESETRESET est utilis pour annuler l'tat de plusieurs opration duserveur. RESET agit comme un FLUSH gnralis en vidant lammoire cache de toutes les structures utilises par le serveur,sur le disque. Il faut avoir le privilge RELOAD pour utiliser
RESET
RESET MASTER : dtruit les fichiers de log binaires et en creun nouveauRESET QUERY CACHE enlve les rsultats de commandeSQL du query cache RESET SLAVE : indique au serveur esclave d'oublier saposition de replication dans les logs binaires
Les logs du serveur
8/8/2019 Cours Mysql[1]
68/135
ML MySQL 102/09/04
Les logs du serveur Avoir lanc le serveur avec certaines options:
log-update (jusqu' la version 4 de MySQL, les logssont en ascii) --bin-log ( partir de la version 5 les logs sont binaireset peuvent tre lus avec l'utilitaire mysqlbinlog)
permet de loguer toutes les modifications faites sur lesbases (alter, insert, update)
Les diffrents logs du
8/8/2019 Cours Mysql[1]
69/135
ML MySQL 102/09/04
gserveur
Diffrentes options de logs au lancement du serveur :--log-error : trace des problmes rencontrs au lancement, excution
du serveur mysqld dans un fichier .err.--log-isam : trace de tous les changements effectus sur les tables
ISAM.--log :Trace normale des connexions des clients et des excutions de
requtes SQL.--log-update : Trace des instructions qui provoquent des modificationsdes donnes (ALTER, UPDATE..). Cette trace sera inoprante dans lesprochaines versions >=5.0--log-bin : Trace des instructions qui provoquent des modifications des
donnes, en format binaire ( partir de la version 4.1.3). Cette trace estutilise galement pour la rplication des bases.--log-slow-queries : trace des requetes qui prennent plus de
long_query_time seconds pour s'excuter, oui n'utilisent pas d'index.
Les logs du serveur
8/8/2019 Cours Mysql[1]
70/135
ML MySQL 102/09/04
Les logs du serveur Par dfaut, tous les fichiers de logs sont crs dans le rpertoiredonn par datadir /var/lib/mysql .
Si on donne le nom du fichier au lancement --log-update[=file_name] option, mysqld cre un fichier qui contient toutesles instructions SQL qui ont modifi les donnes
Sans nom de fichier le nom du fichier par dfaut est le nom dela machine /opt/mysql/var/localhost.log
On peut forcer le serveur fermer et r-ouvrir les fichiersde logs par les instructions :
mysql> FLUSH LOGS ; mysqladmin flush-logsmysqladmin refresh.
TP sur Surveillance du serveur
8/8/2019 Cours Mysql[1]
71/135
ML MySQL 102/09/04
TP sur Surveillance du serveur
DEUXIEME PARTIE
8/8/2019 Cours Mysql[1]
72/135
ML MySQL 102/09/04
DEUXIEME PARTIE
Les types de donnes mysqlLes commandes MySQL
Cration de tablesCration d'indexManipulation de donnes
Select, insert, replace, update, delete
Les instructions de transaction
Les types de donnes de MySQL
8/8/2019 Cours Mysql[1]
73/135
ML MySQL 102/09/04
Les types de donnes de MySQL
types numriquestypes Datestype caractres et TEXTtype BLOB (binary Long Object)type ENUM
type SET
Les types numriques
8/8/2019 Cours Mysql[1]
74/135
ML MySQL 102/09/04
yp qTINYINT 1 octet non ANSISMALLINT 2 octets ANSIMEDIUMINT 3 octets non ANSIINTEGER(M) ZEROFILL 4 octets ANSI
BIGINT 8 octets ANSIFLOAT(M,D) ZEROFILL 4 octets ANSIDOUBLE{PRECISION} 8 octets ANSIREAL 8 octets ANSI
NUMERIC(M,D) M,(D+2 si M
8/8/2019 Cours Mysql[1]
75/135
ML MySQL 102/09/04
Les types de DATEDATE
Une date reprsente un intervalle entre le '1000-01-01' et le '9999-12-31'.MySQL affiche les DATE au format 'YYYY-MM-DD' Toutefois desfonctions de conversion comme date_format() permettent de changer
de format.DATETIMEpermet de combiner une date et un horaire. L'affichage se fait au formatAAAA-MM-JJ HH:MM:SS entre le1000-01-01 00:00:00' et '9999-12-31 23:59:59 .
TIMESTAMP[(M)]Ce type stocke une date et un horaire sous forme d'un nombre desecondes coules depuis le 1er janvier 1970. La syntaxe est timestamp(M) o M est la longueur de l'affichage (14 par defaut)
Les types de DATE
8/8/2019 Cours Mysql[1]
76/135
ML MySQL 102/09/04
Les types de DATETIME A time reprsente un horaire seulement. Le format est en
HH:MM:SS .YEAR[(2|4)] Format pour avoir une date sur 2 ou 4 positions? Sur 4 position on
reprsente des dates dans la gamme de 1901 2155. Sur 2positions les valeurs permises vont de 70 69 (1970 2069)L'affichage se fait dans le format YYYY .
Les types caractres
8/8/2019 Cours Mysql[1]
77/135
ML MySQL 102/09/04
Les types caractres Le type CHAR(n) [BINARY]: permet de stocker des chaines decaractres d'une taille maximale fixe de n caractres (avecn nL'option BINARY permet de prendre en compte la casse descaractres (majuscules/minuscules). Avec BINARY, AAA sera diffrent de aaa
Le type VARCHAR(n) [BINARY] :permet de stocker des chanesde longueur variable
Les types caractres longs
8/8/2019 Cours Mysql[1]
78/135
ML MySQL 102/09/04
Les types caractres longsLes type BLOB (binary large object) et TEXT permettent dereprsenter des chaines de longueur variables .
TINYBLOB , TINYTEXT: longueur max 2^8 car. BLOB , TEXT: longueur max 2^16 car.MEDIUMBLOB, MEDIUMTEXT: longueur max 2^24car.LONGBLOB , LONGTEXT: longueur max 2^32 car.
Les comparaisons sur des valeurs de type TEXTne distinguent
pas majuscules et minuscules.TEXTet BLOBsont des VARCHAR de grandes tailles, avecl'option BINARY active pour le type BLOB
CHAR ou VARCHAR ?
8/8/2019 Cours Mysql[1]
79/135
ML MySQL 102/09/04
CHAR ou VARCHAR ?Avec CHARles enregistrements ont une taille fixe (=somme destailles de chaque attribut). Enregistrement facile grer puisqu'onpeut le rfrencer par son numro d'ordre. La position d'un enr detaille fixe = numro d'ordre * tailleAccs direct facile, Rparations faciles avec myisamchk
Avec VARCHAR, BLOB ou TEXTles enr ont une taille variable.Lors de l'insertion d'un N-uplet on doit calculer la taille exacte desenr d'aprs le nombre d'octets de chaque attribut. La taille totaleest stocke au dbut de l'enregistrementGestion plus complexe par chainage des enregistrementsFragmentation invitable, et performances moindre en tempsd'accs, mais gain de place
Les types ENUM et SET
8/8/2019 Cours Mysql[1]
80/135
ML MySQL 102/09/04
Les types ENUM et SETENUM: permet d'indiquer un type numr dont les instancesprennent leur unique valeur parmi un ensemble explicitementspcifi
Exemple: ENUM ('bleu', 'blanc', 'rouge')
MySQL contrle contrle qu'une valeur rentre appartient bien l'ensemble numr.
SET : identique ENUM, mais un attribut de type SET peutprendre plusieurs valeurs parmi l'ensemble numr.
Les COMMANDES MySQL
8/8/2019 Cours Mysql[1]
81/135
ML MySQL 102/09/04
y Q
La cration des basesDestruction de bases
Cration des tablesDestruction des tablesModifications de la structure des tables
Cration d'index
Cration/Destruction de Base
8/8/2019 Cours Mysql[1]
82/135
ML MySQL 102/09/04
mysql> CREATE DATABASEnomBaseCre un rpertoire vide dans /var/lib/mysql
mysql>DROP DATABASE[IF EXISTS]nomBaseDtruit tous les fichiers (toutes les tables) d'un baseLe script s'interrompt si la base n'existe pas, sauf si
l'option IF EXISTS est spcifie
Cration de Tables
8/8/2019 Cours Mysql[1]
83/135
ML MySQL 102/09/04
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nom_table [(create_definition,...)]
[table_options] [select_statement]
CREATE TABLE CLIENTS (
codeclient` int(11) AUTO_INCREMENT ,nom char(20) NOT NULL default, prenom char(20) NOT NULL default ',rue char(30) default NULL, ville char(15) default NULL,region char(20) default NULL, codepostal char(5) default NULL,pays char(15) default NULL, telephone char(24) default NULL,
PRIMARY KEY (nom,prenom),INDEX (profession)
) TYPE=MyISAM
Destruction de Tables
8/8/2019 Cours Mysql[1]
84/135
ML MySQL 102/09/04
mysql> DROP TABLE nom_table1, table2 [IF EXISTS] ;Il est ncessaire d'avoir le privilege DROP sur toutes lestables.
Prudence avec cette commande car toutes les tables et lesdonnes sont dfinitivement perdues.
Modification de la structure des Tables
8/8/2019 Cours Mysql[1]
85/135
ML MySQL 102/09/04
La cration d'un schma de table n'est qu'une tape dans la vie de la
base. On peut tre amen rajouter, enlever des attributs, des index,modifier le type des attributs...mysql> ALTER TABLE nom_table1 ACTION ;
avec ACTION =ADD COLUMNnom_col clause_de_creationADD INDEXnomindex (col1, col2)ALTERnom_col SET DEFAULTvaleur CHANGEMODIFYnom_col clause_de_creation
alter table COMMANDES modify codecommande int(8);DROP INDEXnom_indexDROP COLUMNnom_colRENAME new_table
Exemples de modification de structure detables
8/8/2019 Cours Mysql[1]
86/135
ML MySQL 102/09/04
Rajout d'un attribut dans une table :
mysql> ALTER TABLE DETAILCOMMANDES ADD datecommandeDATETIME;
Mysql> ALTER TABLE DETAILCOMMANDES MODIFY datecommande DATETIME NOT NULL;
Plus frquement on peut tre amen rajouter ou modifier des indexdans les tables
Les Index
8/8/2019 Cours Mysql[1]
87/135
ML MySQL 102/09/04
Dans une table volumineuse la recherche squentielle d'unenregistrement est une opration pnalisante en terme detemps de rechercheLes index (contenus dans .MYI) permettent d'amliorer les
temps de recherche en accdant directement auxenregistrements du fichier de donnes (.MYD). Les index sontdes valeurs (adresses) auxquelles on a rang lesenregistrements dans le fichier de donnes. Grce aux index
on accde alors aux enregistrements directementOn a intrt crer des index pour les attributs sur lesquelsseront effectus de nombreuses recherches
les index la Cration de Tables
8/8/2019 Cours Mysql[1]
88/135
ML MySQL 102/09/04
PRIMARY KEY : la cl primaire est synonyme d'index valeur unique non nulle qui sera utilis plus particulirement dans les
jointures de tables. Il y a une seule PK par table. (la PK a un statut
particulier (unique et not null), mais c'est un index comme lesautres)KEY : KEY est un synonyme de INDEX. INDEX nom_index (nom,prenom): cration d'un index sur unattribut donne, ou une concatnation d'attributs
UNIQUE : un index valeur unique. Une insertion de 2 valeursidentiques sur un attribut dclar UNIQUE chouera.
Cration / Destruction d'index a posteriori
8/8/2019 Cours Mysql[1]
89/135
ML MySQL 102/09/04
Si on a oubli des index lors de la cration des tables de la base,on peut rajouter des index pendant la vie la base de donnes, sansavoir reconstruire les tables
mysql> CREATE INDEX nom_index ON nom_table(colonne1, colonne2)
mysql> CREATE INDEX codeclient ON COMMANDES(codeclient);
Destruction des index a posterioriMysql>DROP INDEX nom_index ON nom_tableMysql> ALTER TABLE nomtable DROP INDEX nom_index
Quelques optimisations en temps derecherche
8/8/2019 Cours Mysql[1]
90/135
ML MySQL 102/09/04
recherche
Rcuprer les emplacement vides (dfragmenter) avecmyisamchk -r ou optimize tableChoix de type de donnes compact (SMALLINT au lieu deINTEGER, chaines de tailles fixes au lieu de chaines variables)viter les valeur nulles, dclarer les attributs NOT NULL le plussouvent possiblePrfrer CHAR VARCHAR (rflchir vitesse? Ou place disque?)
Utiliser, quand cela est possible des tables temporaires avecl'option TEMPORARY ou des tables de type HEAPCrer des index adquats sur les attributs qui le ncessitent
TP sur Creation, DestructionModification
8/8/2019 Cours Mysql[1]
91/135
ML MySQL 102/09/04
ModificationCrer une base disque Cration des 3 tables de type InnoDB
ArtisteDisquechanson
Modifications de la structure des tablesCration d'index
Les COMMANDES MySQL
8/8/2019 Cours Mysql[1]
92/135
ML MySQL 102/09/04
La manipulation des donnes des tables:SelectionInsertionModificationDestruction
SELECTSELECT ili b i d i d'
8/8/2019 Cours Mysql[1]
93/135
ML MySQL 102/09/04
SELECT est utilis pour obtenir des enregistrements venant d'une ou
plusieurs tables. Le support des commandes UNION et des sous-requtes est disponibles depuis MySQL 4.0 et 4.1
SELECT [STRAIGHT_JOIN] [DISTINCT]listeattributs INTO OUTFILE 'nomfichier'
FROMnomdetableWHEREconditionGROUP BYnomattribut HAVINGconditionORDER BYnomattribut [ ASC | DESC ]LIMITdebut,nbligne
STRAIGHT_JOIN indique que la jointure des tables doit se faire dansl'ordre indiqu
Jointure avec SELECTLes jointures permettent d'exprimer des requtes portant sur des
8/8/2019 Cours Mysql[1]
94/135
ML MySQL 102/09/04
Les jointures permettent d exprimer des requtes portant sur des
donnes rparties dans plusieurs tablesOn donne la liste des tables dans la clause FROM, et on exprimeles critres de rapprochement dans la clause WHERELa plupart des jointures s'expriment par une galit entre la clprimaire d'une table et la cl trangre correspondante dans l'autretable.L'optimiseur de MySQL fait au mieux pour utiliser les meilleursindex pour minimiser le nombre de lignes parcourues.
Pour forcer l'optimiseur utiliser un ordre spcifique de jointuredans une commande SELECT,il faut ajouter l'attributSTRAIGHT_JOIN la clause SELECT .
Jointure avec SELECTQuel est le nom et le prix des boites commands par quantit de 2 ?
8/8/2019 Cours Mysql[1]
95/135
ML MySQL 102/09/04
Quel est le nom et le prix des boites commands par quantit de 2 ?
mysql> SELECT D.codecommande,D.codeboite,D.quantite,B.nom_boite,B.prix_boite from DETAILCOMMANDES D, BOITES B where D.quantite=2 andD.codeboite=B.code_boite group by D.codeboite;Dtail des commandes de Mr Fadiman (3 jointures de tables) ?
select C.nom,C.codeclient,M.codecommande, from CLIENTS C,COMMANDES M where C.nom='Fadiman' and C.codeclient=M.codeclient ;
select C.nom,C.codeclient,M.codecommande,D.codeboite,D.quantite fromCLIENTS C,COMMANDES M,DETAILCOMMANDES D whereC.nom='Fadiman' and C.codeclient=M.codeclient and M.codecommande=D.codecommande;
select C.nom,C.codeclient,M.codecommande,D.codeboite,D.quantite,B.nom_boit e,B.prix_boite from CLIENTS C,COMMANDES M,DETAILCOMMANDES D,BOITES B where C.nom='Fadiman' and C.codeclient=M.codeclient and M.codecommande=D.codecommande and D.codeboite=B.code_boite;
EXPLAIN
8/8/2019 Cours Mysql[1]
96/135
ML MySQL 102/09/04
EXPLAIN, permet d'identifier les index qui seront utiliss lorsd'une requte SELECT et de mettre en vidence les index qu'ilfaut ajouter pour acclrer les commandes SELECT.
EXPLAIN indique egalement l'ordre dans lequel seront faites les
jointures. Cela permet galement de vrifier que l'optimiseur faitles jointures dans un ordre vraiment optimal.
Pour forcer l'optimiseur utiliser un ordre spcifique de jointuredans une commande SELECT,il faut ajouter l'attributSTRAIGHT_JOIN la clause SELECT .L'optimiseur peut se tromper et ne pas choisir les index corrects.L'utilitairemyisamchk analyse permet d'tudier la distributiondes index par colonnes et de choisir le meilleur index
EXPLAIN
8/8/2019 Cours Mysql[1]
97/135
ML MySQL 102/09/04
explain selectC.nom,C.codeclient,M.codecommande,D.codeboite,D.quantite,B.nom_boite,B.prix_boite from CLIENTS C,COMMANDES M,DETAILCOMMANDES D,BOITESB where C.nom='Fadiman' and C.codeclient=M.codeclient andD.codecommande=M.codecommande and D.codeboite=B.code_boite;
| table | type | possible_keys | key | key_len | ref | rows | Extra |+-------+--------+---------------+---------+---------+----------------+------+-------------+| C | ref | PRIMARY | PRIMARY | 20 | const | 1 | Using where || D | ALL | PRIMARY | NULL | NULL | NULL | 1359 | || M | eq_ref | PRIMARY | PRIMARY | 4 | D.codecommande | 1 | Using
where || B | eq_ref | PRIMARY | PRIMARY | 4 | D.codeboite | 1 | |+-------+--------+---------------+---------+---------+----------------+------+-------------+4 rows in set (0.00 sec)
EXPLAINla colonne type indique l'efficacit de la jointure
8/8/2019 Cours Mysql[1]
98/135
ML MySQL 102/09/04
SystemLa table a une seule ligne (c'est une table systme). C'est un cas spcialdu type de jointure const (constante).
const La table a au plus une ligne correspondante, qui sera lue ds le dbut de larequte. Les tables const sont trs rapides, car elles ne sont lues qu'unefois. const est utilis lorsque on compare toutes les parties d'une clPRIMAIRE avec une constante WHERE email='libes@com.univ-mrs.fr'
eq_ref C'est le meilleur type de jointure possible, l'exception des prcdents. Ilest utilis lorsque toutes les parties d'un index sont utilises par la jointure,et que l'index est UNIQUE ou PRIMARY KEY.eq_ref est utilis pour lescolonnes indexes, qui sont compares avec l'oprateur =. L'lmentcompar doit tre une constante ou une expression qui utilise les colonnesde la table qui est avant cette table.
EXPLAINf
8/8/2019 Cours Mysql[1]
99/135
ML MySQL 102/09/04
ref ref est utilis si la jointure n'utilise que le prfixe de gauche de la cl, ou si lacl n'est pas UNIQUE ou PRIMARY KEY (en d'autres termes, si la jointure ne
peut pas slectionner qu'une seule ligne en fonction de la cl). Si la cl qui estutilise n'identifie que quelques lignes chaque fois, la jointure est bonne. ref peut tre utilis pour les colonnes indexes, qui sont compares avecl'oprateur =.
rangeSeules les lignes qui sont dans un intervalle donn seront lues, en utilisantl'index pour slectionner les lignes. La colonne key indique quel est l'indexutilis. key_len contient la taille de la partie de la cl qui est utilise. Lacolonne ref contiendra la valeur NULL pour ce type. range peut tre utilislorsqu'une colonne indexe est compare avec une constante comme =, , >,>=,
8/8/2019 Cours Mysql[1]
100/135
ML MySQL 102/09/04
Index Identique ALL, hormis le fait que seul l'arbre d'index est tudi. C'estgnralement plus rapide que ALL, car le fichier d'index est plus petit quele fichier de donnes. Cette mthode peut tre utilise lorsque la requteutilise une colonne qui fait partie d'un index.
ALL
Une analyse complte de la table est fate pour chaque combinaison delignes issue des premires tables. Ce n'est pas bon si la premire tablen'est pas une jointure de type const et c'est trs mauvais dans les autrescas. Normalement vous pouvez viter ces situations de ALL en ajoutant desindex base sur des parties de colonnes.
EXPLAINpossible_keys
La colonne possible_keys indique quels index MySQL va utiliser pour trouver les
8/8/2019 Cours Mysql[1]
101/135
ML MySQL 102/09/04
lignes. Cette colonne est totalement dpendante de l'ordre des tables. Cela signifieque certaines cls de la colonne possible_keys pourraient ne pas tre utilises dansd'autres cas d'ordre de tables. Si cette colonne est vide, il n'y a pas d'index pertinent.On peut amliorer les performances en examinant la clause WHERE pour voir si descolonnes sont susceptibles d'tre indexe.key
La colonne key indique l'index que MySQL va dcider d'utiliser. Si la cl vaut NULL,aucun index n'a t choisi. Pour forcer MySQL utiliser un index list dans la colonnepossible_keys, utilisez USE KEY/IGNORE KEY dans votre requte. See section 14.1.7Syntaxe de SELECT.key_len
La colonne key_len indique la taille de la cl que MySQL a dcid d'utiliser. Cela vousindique combien de partie d'une cl multiple MySQL va rellement utiliser.
ref La colonne ref indique quelle colonne ou quelles constantes sont utilises avec la clkey, pour slectionner les lignes de la table.rows
La colonne rows indique le nombre de lignes que MySQL estime devoir examiner pour excuter la requte.
EXPLAIN
8/8/2019 Cours Mysql[1]
102/135
ML MySQL 102/09/04
Dans la table D aucun index n'est utilis... parcours squentiel de 1359lignesRajout d'un index sur le codeclient dans la table COMMANDES
mysql> ALTER table COMMANDES ADD INDEX client (codeclient);Query OK, 407 rows affected (0.01 sec)Records: 407 Duplicates: 0 Warnings: 0
| C | ref | PRIMARY | PRIMARY | 20 | const | 1 | Using where || M | ref | PRIMARY,client | client | 5 | C.codeclient | 1 | Using where || B | ALL | PRIMARY | NULL | NULL | NULL | 18 | || D | eq_ref | PRIMARY,boite | PRIMARY | 8 | M.codecommande,B.code_boite | 1
| |+-------+--------+----------------+---------+---------+-----------------------------+------+-------------+
JOIN jointure externeLes jointures externes permettent de trouver les donnes prsentes dansla table de gauche qui N'ont PAS de valeurs correspondantes dans la
8/8/2019 Cours Mysql[1]
103/135
ML MySQL 102/09/04
la table de gauche, qui N ont PAS de valeurs correspondantes dans latable de droiteSi pour une valeur de la table de gauche on trouve une valeur pour latable de droite, la jointure s'effectue normalement, sinon les attributs dela table de droite sont affichs NULL
Quels sont les clients qui n'ont PAS fait de commandes?select CL.codeclient,CL.nom,C.codecommande from CLIENTS CL LEFT
JOIN COMMANDES C ON C.codeclient=CL.codeclient ;select CL.codeclient,CL.nom,C.codecommande from CLIENTS CLNATURAL LEFT JOIN COMMANDES C WHERE C.codecommandeIS
NULL;S'crit aussi select CL.codeclient,CL.nom,C.codecommande from CLIENTS CL
NATURAL left join COMMANDES C;
TP sur SELECT et EXPLAIN et jointures
8/8/2019 Cours Mysql[1]
104/135
ML MySQL 102/09/04
j2 grosses jointures crire et amliorer:
Indiquer les Noms prenoms des clients qui ont commandsdes bonbons M13 (W06) ?
Trouver les commandes qui ont t passes pour Mr Smith?
2 jointures externesQui n'a pas fait de commandes?
trouver les bonbons qui NE SONT PAS dans des boites
UNION
8/8/2019 Cours Mysql[1]
105/135
ML MySQL 102/09/04
Dans un SGBDR les tables sont considres comme desensembles sur lesquel on peut effectuer les opration d'UNION,INTERSECTION..etc..UNION permet de combiner les rsultats de plusieurs instructions
SELECT en un seul ensemble rsultant . UNION est support partir de MySQL 4.0.0
Mysql> SELECT codecommande FROM COMMANDESUNION SELECT codecommande from DETAILCOMMANDES;
Requtes ImbriquesLes requetes SELECT / WHERE permettent de retrouver des
8/8/2019 Cours Mysql[1]
106/135
ML MySQL 102/09/04
q p
valeurs scalaires.Avec le mot cl IN on peut retrouver unevaleur parmi un ensembleSELECT * from BOITES WHERE code_boiteIN ('ROMA','SWEE');SELECT * from BOITES WHERE code_boiteNOT IN ('ROMA', 'SWEE');
Les requtes imbriques sont une gnralisation de cetteconstruction. Au lieu de donner un ensemble de valeurs
constantes ( en dur ), on le construit dynamiquement avec une2me requte imbrique dans la premire.Les requtes imbriques sont supportes partir de MySQL4.1
REQUETES IMBRIQUEES
8/8/2019 Cours Mysql[1]
107/135
ML MySQL 102/09/04
A partir de la version 4.1 on pourra crire SELECT * FROM BOITES WHERE code_boiteIN
(SELECT code_boite FROM DETAILCOMMANDES WHERE quantite="1");
Avant la version 4.1, ce type de requte imbrique peut toutefoiss'crire encore avec des jointures
SELECT * FROM BOITES B, DETAILCOMMANDES D whereB.code_boite=D.codeboite AND D.quantite="3";
TP requte imbriques
8/8/2019 Cours Mysql[1]
108/135
ML MySQL 102/09/04
Trouver les boites qui ont t commandespar quantit suprieure 2
select * from BOITES where code_boite in (select codeboite fromDETAILCOMMANDES where quantite>"2");
trouvez les boites dans lesquelles il ya desbonbons base de noix??
Ecrire la requete en requete imbriqueLa mme SANS requete imbrique
INSERTPour insrer une ou plusieurs lignes dans une table
8/8/2019 Cours Mysql[1]
109/135
ML MySQL 102/09/04
Pour insrer une ou plusieurs lignes dans une table
Insertion de donnes explicites dans les colonnes dsignes Mysql>INSERT INTOBOITES (code_boite,nom_boite,taille)
VALUES("MOMO","orange au chocolat", 110);Mysql>INSERT INTOBOITES (code_boite,nom_boite,taille)VALUES("MOMO","orange au chocolat", 110), ("GOOD","praline",
245);
Insertion de valeurs retournes par une instruction SELECT ,copie de lignes d'une table dans une autre
Mysql>INSERT INTOBOITES (code_boite,nom_boite,taille)SELECT code_boite,nom_boite,taille FROM CHOCOLATS2.BOITESwhere CHOCOLATS2.BOITES.taille > "400";
Insertion de donnes partir d'un fichier
LOAD DATA[LOW PRIORITY | CONCURRENT] [LOCAL]INFILE
8/8/2019 Cours Mysql[1]
110/135
ML MySQL 102/09/04
LOAD DATA[LOW_PRIORITY | CONCURRENT] [LOCAL]INFILE 'file_name.txt' [REPLACE| IGNORE] INTO TABLE tbl_name[FIELDS Pour indiquer les sparateurs de colonnes
[TERMINATEDBY '\t'][[OPTIONALLY] ENCLOSED BY ''][ESCAPED BY '\\' ]
][LINES [STARTING BY ''] [TERMINATED BY '\n'] ][IGNORE number LINES] Pour sauter n lignes en dbut [(col_name,...)] Pour sauter la colonne col_name
LOAD DATA infilePermet d'insrer des donnes dans une table partir d'un fichier de texte dont les colonnes sont spares par un certain sparateur
8/8/2019 Cours Mysql[1]
111/135
ML MySQL 102/09/04
de texte dont les colonnes sont spares par un certain sparateur Le fichier boites.txt doit avoir une ligne pour chaqueenregistrement insr dans la tableLe mot cl LOCALindique que le fichier se situe sur la mmemachine que le client. Sinon mysql cherche le fichier sur le
serveur, dans le rpertoire datadir Par dfaut les colonnes sont spares par des tabulations, leslignes termines par \n
LOAD DATA LOCAL INFILE "/home/momo/boites.txt" INTOTABLE BOITES;LOAD DATA INFILE "/home/momo/bonbons.txt" REPLACE INTO
TABLE BONBONS;
REPLACEREPLACE est identique INSERT mais porte sur des lignes qui existentdj (si la ligne existe dj INSERT choue)
8/8/2019 Cours Mysql[1]
112/135
ML MySQL 102/09/04
gAvec REPLACE si un enregistrement a la mme valeur quel'enregistrement que l'on insre, l'ancien enregistrement est dtruit et lenouveau est insr.Si l'enregistrement n'existe pas (valeur de Cl primaire absente), il estinsr comme avec INSERT), les colonnes que l'on ne mentionne passont initialises NULL !!
Mysql>REPLACE INTOBOITES(code_boite,nom_boite,taille,description_boite) VALUES("SWEE","Doux et Amer","500"," tres bonne boite de bonbons");Une mthode rapide pour recharger toute une table partir d'un fichier
LOAD DATA INFILE "/home/momo/cours-mysql/bonbons.txt" REPLACE INTOTABLE BONBONS;
UPDATE
8/8/2019 Cours Mysql[1]
113/135
ML MySQL 102/09/04
Pour mettre jour des attributs existants dans une table,
Toute ligne qui satisfait l'expression de recherche de la clause WHERE est modifie.
Mysql>UPDATE BOITESSET taille="400", nom_boite="tendrescremes" WHEREcode_boite="SWE2";
On peut utiliser le nom d'une colonne comme valeur sur elle mmeUPDATEBOITESSET taille=taille+2 WHEREcode_boite="SWE2";
NB:Il faut le privilege UPDATE pour excuter cette instruction
DELETEPour dtruire un enregistrement
Mysql> DELETE FROMBOITESWHERE
8/8/2019 Cours Mysql[1]
114/135
ML MySQL 102/09/04
y qcode_boite="SWEU";
ATTENTION: Si on ne met pas de clause de slection WHERE toute la table entire est crase, et une nouvelle
table vide est recre.
Lors de la destuction d'enregistrement, on ne dtruit qu'unpointeur dans une liste chaine. L'espace des lignes dtruites n'estPAS physiquement restitu au systme. L'espace peut etreroccup par des instructions d'insertion futures.Pour rcuprer physiquement la place disque il faut lancer
OPTIMIZE TABLE ou Myisamchk -r
TP insert, delete, load data
8/8/2019 Cours Mysql[1]
115/135
ML MySQL 102/09/04
Les tables de type InnoDB
8/8/2019 Cours Mysql[1]
116/135
ML MySQL 102/09/04
Les apports des tables de type InnoDB
Les contraintes d'intgrits grce aux clstrangres (Foreign Keys)Les requtes transactionnelles
Les tables de type InnoDBL'organisation des tables est diffrente des tables MyISAM. On ne trouve
plus que les fichier .frm. Les fichiers d'index .MYI et de donnes .MYD
8/8/2019 Cours Mysql[1]
117/135
ML MySQL 102/09/04
n'existent plusInnoDB dispose d'un buffer spcial pour mettre en cache les donnes et
les index en mmoire centrale. InnoDB stocke les tables et index dans untablespaceDans /etc/my.cnf section [mysqld ]
spcifier innodb_data_file_path pour donner les noms et tailles de fichiers dedonnes. innodb_data_file_path = /ibdata/ibdata1:10M:autoextend spcifier innodb_data_home_dir pour donner le rpertoire de donnes, Par dfaut les fichiers sont crs dans le dossier de donnes de MySQL.
Le comportement par dfaut est de crer un fichier de donnes auto-
croissant de 10Mo appel ibdata1Pour ne pas utiliser les tables InnoDB, on peut rajouter l'option skip-innodb dans le fichier d'options MySQLmy.cnf.
Les tables de type InnoDBInnoDB fournit MySQL un gestionnaire de table transactionnelleavec validation (commits), annulations (rollback ) et possibilit de
8/8/2019 Cours Mysql[1]
118/135
ML MySQL 102/09/04
( ), ( ) prestauration aprs crash. InnoDB utilise un verrouillage de lignes, etfournit des lectures cohrentes sans verrous. Ces fonctionnalitsaccroissent les possibilits d'utilisation simultanes des tables, et lesperformances.
Les tables InnoDB sont les premires tables MySQL qui supportentles contraintes de cls trangres (FOREIGN KEY)InnoDB a t conu pour maximiser les performances lors dutraitement de grandes quantits de donnes. Son efficacitprocesseur n'est gale par aucun autre moteur de base dedonnes.Le support des tables InnoDB est inclus par dfaut partir deMySQL 4.0 (ou en compilant le code source partir des versionsMySQL-3.23
Les tables de type InnoDBOn ne peut pas dtruire un fichier dans le tablespace! Pour diminuer lataille du fichier tablespace (ibdata1), il faut faire de la manire suivante :
8/8/2019 Cours Mysql[1]
119/135
ML MySQL 102/09/04
1. mysqldump to dumper toutes les tables InnoDB /opt/mysql/bin/mysqldump -u root -ptoto CHOCOINNO BOITES
2. Arrter le serveur (mysql shutdown)3. Dtruire tous les fichiers tablespace4. Configurer un nouveau tablespace.
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend 5. Redmarrer le serveur.6. Importer les fichiers de donnes prcdemment dumps
Creation de tables de type InnoDBIl suffit de spcifier type=InnoDBlors de la cration de table
Mysql>create table IF NOT EXISTS BOITES (
8/8/2019 Cours Mysql[1]
120/135
ML MySQL 102/09/04
code_boite char(4) primary key ,INDEX (nom_boite char(40)), taille int(5),description_boite varchar(255), prix_boite decimal(8,2),qte_en_stock int(5), rupture_stock tinyint
) type = InnoDB;La table et l'index sont crs dans le tablespace InnoDB qui consiste en les
fichiers de donnes qu'on a spcifi dans `my.cnf'.MySQL cre un fichier `BOITES.frm' dans le rpertoire CHOCOINNO (dans --
datadir). En interne InnoDB rajoute son dictionnaire une entre pour latable CHOCOINNO/BOITES. On peut crer des tables de noms identiquesdans d'autres bases InoDB.
myql> show table status from CHOCOINNO like 'BOITES';
Les cls trangres FOREIGN KEYS Une cl trangre est l'identifiant unique (la cl primaire) d'une
8/8/2019 Cours Mysql[1]
121/135
ML MySQL 102/09/04
table de l'autre cot d'une relation 1,NLes foreign keys (FK) permettent d'indiquer quels sont les attributsqui font rfrence une ligne dans une autre tableLes FK sont implmentes dans les tables de type InnoDB (lasyntaxe est accepte dans les tables myisam, mais n'est pasutilise)Les cls trangres SQL sont utilises pour assurer la cohrencedes donnes dans les tables lies par des relations, et non paspour faire jointures
Les cls trangres permettent d'viter d'insrer des valeursincohrentes dans la base (valeur orpheline = 1 valeur de cl dansune relation qui n'existerait pas dans la table correspondante).
Les cls trangres FOREIGN KEYS
8/8/2019 Cours Mysql[1]
122/135
ML MySQL 102/09/04
La seule chose que MySQL ne fait pas (avec les types autres queInnoDB), est de s'assurer que la clef qu'on utilise dans une tableexiste bien dans la ou les tables que vous rfrencezEn l'absence de vrification du cot du serveur, l'application doit secharger des vrifications. Elle doit s'assurer que les lignes ne sont
pas orphelines. Il faut aussi pouvoir rattraper une erreur au milieud'une opration multiple.MySQL permet aux dveloppeurs de BD le choix de leur approche. Si on n'a pas besoin des cls trangres, et que on veutviter leur surcot, il est prfrable d'utiliser des tables MyISAM.
Les tables MyISAM sont extrmement rapides pour lesapplications qui font essentiellement des oprations INSERT etSELECT.
Les cls trangres FOREIGN KEYS Avec les foreign keys dans les tables de type INNODB , MySQL
8/8/2019 Cours Mysql[1]
123/135
ML MySQL 102/09/04
permet une vrification centralise de contraintes, ce qui rendinutile l'excution de ces vrifications du cot de l'application quiinsre les donnes. Cela limine la possibilit que d'autresapplications ne fassent pas les vrifications de la mme faon queles autres.
Ces avantages entrainent un cot suprieur pour le serveur debases, qui de ce fait doit effectuer les tests. Avec les clstrangres, c'est le serveur MySQL qui vrifie les contraintesd'intgrits (i.e les valeurs pour une FK d'une relation doivent exister comme PK dans l'autre relation
Les vrifications supplmentaires par mysql affectent donc lesperformances. Certaines applications commerciales choisissent enconsquence de placer la logique de vrification dans l'application.
Cration de FOREIGN KEYS dans lestablescreate table COMMANDES (
codecommande int primary key,codeclient int UNIQUE,
8/8/2019 Cours Mysql[1]
124/135
ML MySQL 102/09/04
FOREIGN KEY (codeclient) REFERENCES CLIENTS,codetransporteur int,datecommande date, cadeau tinyint, modecommande int,nomlivraison char(50), prenomlivraison char(50),adresselivraison varchar(255), villelivraison char(50),identlivraistran varchar(255), datelivraison datetime,modepaiement int UNIQUE, FOREIGN KEY (modepaiement) REFERENCES MODE_PAIEMENT;
) type InnoDB;La FK codeclient rfrence la cl primaire de la table CLIENT
Lors de toute modification ou insertion, MySQL doit vrifier que la valeur de codeclient correspond bien une ligne de la table CLIENT
Ces vrifications garantissent qu'il n'ya pas de fausses rfrences dans labase (codeclient qui n'existerait pas dans la table CLIENT)
Utilisation des FOREIGN KEYS eninsertion
8/8/2019 Cours Mysql[1]
125/135
ML MySQL 102/09/04
CREATE TABLE song ( idsong INT NOT NULL ,PRIMARY KEY (idsong),titre VARCHAR(50),duree TIME,
id_cd INT UNIQUE,id_artiste INT UNIQUE,FOREIGN KEY (id_cd) REFERENCES disque(idcd),FOREIGN KEY (id_artiste) REFERENCES artiste(idartiste)) type = InnoDB;
mysql> insert into song (idsong,titre,duree,id_cd,id_artiste) values(2,"impressions","00:11:20",3,4);ERROR 1216: Cannot add or update a child row: a foreign key constraintfails
TP sur Foreign Keys
8/8/2019 Cours Mysql[1]
126/135
ML MySQL 102/09/04
Crer une base disque Cration des 3 tables de type InnoDB
ArtisteDisquechanson
Modifications de la structure des tablesCration d'index
Instructions de TRANSACTIONLe but du mode transactionnel est d'assurer l'intgrit de la base .Un ensemble d'instructions sont excutes comme si c'tait une
l i d il
8/8/2019 Cours Mysql[1]
127/135
ML MySQL 102/09/04
seule unit de travail.
En mode transactionnel, si une application a t crite endpendant de l'appel de ROLLBACK au lieu de COMMIT dans lessituations critiques, les transactions s'assurent que lesmodifications non acheves ou les activites corrosives ne sontpas archives dans la base. Le serveur a l'opportunit d'annuler automatiquement l'opration, et votre base de donnes est sauve.
Instructions de TRANSACTIONIntroduites rcemment dans MySQL avec le format des tables InnoDBPar dfaut MySQL est en autocommit i.e toutes les instructions sontexcutes immdiatement
8/8/2019 Cours Mysql[1]
128/135
ML MySQL 102/09/04
Pour passer en mode transactionnel : mysql> SET AUTOCOMMIT=0;Pour dmarrer une transaction : mysql> BEGIN ou START TRANSACTION;
Excuter un ensemble d'instructionsupdate BOITES set taille=590 where taille=500 ;Les modifications ne seront pas permanentes tant qu'on n'aura pastermin la transaction avec l'instruction COMMIT;
Pour TERMINER la transaction (ON REPASSE EN AUTOCOMMIT)Mysql> COMMIT; (enregistre les modifications de la transaction)Mysql> ROLLBACK; (annule les modifications de la transaction)
Instructions de TRANSACTION
8/8/2019 Cours Mysql[1]
129/135
ML MySQL 102/09/04
Attention, certaines instructions ne peuvent pas tre annules par des ROLLBACK.
Il s'agit des oprations: ALTER TABLE CREATE INDEX DROP DATABASE DROP TABLE
Transactionnel ou pas ???Le mode transactionnel n'est pas la panace. Il est 3 4 fois plus lent quele mode non-transactionnel des tables MyISAMO t i d ill f t t t l'i tg it
8/8/2019 Cours Mysql[1]
130/135
ML MySQL 102/09/04
On peut arriver de meilleures performances tout en assurant l'intgritdes tables avec le verrouillage de table sur des tables MyISAM. Lamthode est bien plus rapide que ne le proposent les transactions, avecdes annulations ROLLBACK possibles mais pas certaines.
Les modifications de donnes transactionnelles fatales peuvent trercrites de manire atomique . En gnral, tous les problmesd'intgrit que les transactions rsolvent peuvent tre corrigs avec lacommande LOCK TABLESou des modifications atomiques, qui assurentqu'il n'y aura jamais d'annulation automatique de la base.
" Atomique", signifie simplement qu'on peut tre certain que lorsqu'on modifiedes donnes dans une table, aucun autre utilisateur ne peut interfrer avec votre opration
Transactionnel ou pas?LOCK TABLES table WRITE pour verrouiller les tables en cours
d'utilisationTest des conditions Modification des donnes si tout est correct
8/8/2019 Cours Mysql[1]
131/135
ML MySQL 102/09/04
Test des conditions , Modification des donnes si tout est correct. UNLOCK TABLESpour librer les tables.
Il existe 3 types de LOCK read, read local, et writeLOCK TABLES READ: verrouille la table en lecture i.e les crituresne sont pas possiblesLOCK TABLES WRITEverrouille la table en lecture et criture. Lesautres clients mysql ne sont autoriss ni lire ni crire
Pendant le verrouillage on est certain que personne ne modifie les donnesou ne recueille de fausses informations.mysql> update BOITES set taille=590 where taille=500 ;ERROR 1099: Table 'BOITES' was locked with a READ lock and can't be updated
TP sur les transactions
E t ti SQL
8/8/2019 Cours Mysql[1]
132/135
ML MySQL 102/09/04
Engager une transaction SQLInsrer des donnes dans la table CLIENTSVrifier avec commit ou rollback si lesdonnes ont t crites.
Une session PHP-MySQL de base
mysql connect() : connexion au serveur mysqld
8/8/2019 Cours Mysql[1]
133/135
ML MySQL 102/09/04
mysql_connect() : connexion au serveur mysqldmysql_pconnect() : idem avec connexion persistante
$connect =mysql_pconnect("localhost::/tmp/mysql.sock",$user,$passwd)mysql_select_db() : selectionne un BD courante
mysql_select_db($nombase, $connect );mysql_query() : envoie une requte SQL au serveur et rcupre lersultat sous forme de tableau associatif ou indic
$res =mysql_query($query,$connect);mysql_fetch_array() :
$ligne=mysql_fetch_array( $res ,MYSQL_ASSOC);En tableau associatif les champs du tableaux sont indics par le nomde l'attribut correspondant de la table
Une session PHP-MySQL de base
Boucle de lecture : tant qu'il ya des lignes accder aux champs du
8/8/2019 Cours Mysql[1]
134/135
ML MySQL 102/09/04
Boucle de lecture : tant qu il ya des lignes, accder aux champs dutableau par le nom de l'attribut colonneecho "nom$ligne[nom] "; echo "prenom $ligne[prenom] ";
ou: While ( $ligne=mysql_fetch_array( $res ,MYSQL_ASSOC) ){if ($ligne) {
foreach ($ligne as $key => $f) {echo " $f ";
}}
Utilitaires Graphiques de gestiondes Bases
Phpm admin:
8/8/2019 Cours Mysql[1]
135/135
ML MySQL 102/09/04
Phpmyadmin:http://www.phpmyadmin.net/
Mysqlcc :http://dev.mysql.com/downloads/other/mysqlcc.html
MysqlAdministrator Http://dev.mysql.com/downloads/administrator/
Recommended