A l'installation de MySQL, le compte root est créé (quel que soit le système d'exploitation hôte). Ce compte est sans mot de passe, et permet à n'importe qui de se connecter à MySQL ! il est donc important de bien modifier ce mot de passe, en utilisant par exemple l'utilisatire mysqladmin :
mysqladmin -u root password 'm0n_M0t/p@Ss&'
Bien entendu, il devrait s'agir là de la première tâche à réaliser ...
En plus des traditionnelles paires login / password, MySQL utilise la notion d'hôte (le système depuis lequel est lancé la demande d'authentification) pour déterminer le niveau d'accès à une base. Le processus d'exécution des requêtes est donc soumis à deux points :
si l'utilisateur est autorisé ou non à se connecter
si il dispose des privilèges suffisant pour exécuter le requêtes ou la tâche d'administration.
Le premier point, l'étape de connection, vérifie la correspondance des informations fournies (login, mot de passe et hôte) avec un enregistrement de la table users de la base grant de mysql. Si l'enregistrement existe, les droits sont examinés, et MySQL vérifie si la requête peut-être exécutée.
Le SGBDR MySQL utilise sa propre base d'administration , mysql, pour gérer les connexions et les droits d'exécution. Seul root doit avoir accès à cette base. 5 tables sont utilisés pour décider qui à la permission de faire quoi sur quelle base et à partir de quelle machine :
userqui contient la liste des utilisateurs enregistrés sous MySQLhostqui contient la liste des machines répertoriéesdbqui contient la liste des base de données hébergées sur le serveurcolumns_privqui contient les différentes permissions de chaque utilisateur pour chaque colonne de chaque tabletables_privqui contient les différentes permissions de chaque utilisateur sur chaque table des bases de données.
Les différentes actions possibles sur les bases de données et leurs enregistrements via des requêtes SQL sont listées par les privilèges, que l'on peut accorder ou non à chaque utilisateur de la base de données. La liste des privilèges s'obtient par la commande show privileges; :
mysql> show privileges; +-------------------------+---------------------------------------+-------------------------------------------------------+ | Privilege | Context | Comment | +-------------------------+---------------------------------------+-------------------------------------------------------+ | Alter | Tables | To alter the table | | Alter routine | Functions,Procedures | To alter or drop stored functions/procedures | | Create | Databases,Tables,Indexes | To create new databases and tables | | Create routine | Functions,Procedures | To use CREATE FUNCTION/PROCEDURE | | Create temporary tables | Databases | To use CREATE TEMPORARY TABLE | | Create view | Tables | To create new views | | Create user | Server Admin | To create new users | | Delete | Tables | To delete existing rows | | Drop | Databases,Tables | To drop databases, tables, and views | | Execute | Functions,Procedures | To execute stored routines | | File | File access on server | To read and write files on the server | | Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess | | Index | Tables | To create or drop indexes | | Insert | Tables | To insert data into tables | | Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) | | Process | Server Admin | To view the plain text of currently executing queries | | References | Databases,Tables | To have references on tables | | Reload | Server Admin | To reload or refresh tables, logs and privileges | | Replication client | Server Admin | To ask where the slave or master servers are | | Replication slave | Server Admin | To read binary log events from the master | | Select | Tables | To retrieve rows from table | | Show databases | Server Admin | To see all databases with SHOW DATABASES | | Show view | Tables | To see views with SHOW CREATE VIEW | | Shutdown | Server Admin | To shut down the server | | Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. | | Update | Tables | To update existing rows | | Usage | Server Admin | No privileges - allow connect only | +-------------------------+---------------------------------------+-------------------------------------------------------+ 27 rows in set (0.00 sec)
Gérer la sécurité de MySQL au niveau utilisateur consiste à allouer ou révoquer des privilèges pour chaque utilisateur du SGBDR.
La requête GRANT permet de :
Créer un utilisateur
Lui allouer des permissions
Changer son mot de passe
A l'inverse, la requête REVOKE permet de retirer des permission à un utilisateur.
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[WITH GRANT OPTION]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
Ainsi, ajouter deux utilisateurs à MySQL ayant des droits particuliers sur la base bibliotheque reviendrait à exécuter les requêtes suivantes :
mysql> GRANT ALL ON bibliotheque.* TO bibliothecaire IDENTIFIED BY "m0rD~P@ssE"; Query OK, 0 rows affected (0.00 sec) mysql> GRANT select ON bibliotheque.* TO lecteur IDENTIFIED BY "l&ct~Ur"; Query OK, 0 rows affected (0.00 sec)
Limiter les droits de l'utilisateur bibliothecaire se ferait via la requête suivante :
mysql> REVOKE drop on bibliotheque.* FROM bibliothecaire; Query OK, 0 rows affected (0.00 sec)
Outre l'allocation et le retrait des permissions aux utilisateurs, un administrateur de base de données peut-être amené à :
Changer le mot de passe d'un utilisateur : on utilise la requête GRANT USAGE
mysql> GRANT USAGE ON *.* TO lecteur IDENTIFIED BY "motdepasse"; Query OK, 0 rows affected (0.00 sec)
Créer un super-utilisateur : on accorde tous les privilèges sur toutes les bases
mysql> GRANT ALL PRIVILEGES ON *.* TO sysop@localhost IDENTIFIED BY 'mot_passe_sysop' WITH GRANT OPTION;
Enlever un utilisateur : on supprime tous les privilèges, puis l'utilisateur :
mysql> REVOKE ALL PRIVILEGES FROM sysop@localhost mysql> DROP USER sysop;
Recharger les privilèges après une modification : une fois les modifications effectuées dans la base
mysql, il faut les recharger en mémoire pour le serveurmysql> FLUSH PRIVILEGES;
Il existe plusieurs manières de vérifier l'attribution de permissions à un utilisateur :
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from user; + +-----------+------------------+-----------------------------------.... | Host | User | Password | .... +-----------+------------------+-------------------------------------------..... | localhost | root | *3A1326C400CB311270D73F7F14E509F785CC8D45 |.... | ix | root | |.... | 127.0.0.1 | root | | .... | localhost | debian-sys-maint | *55A2341B8CC7DD9F78B6026DF251D3BF8A40377E | .... | % | bibliothecaire | *444AA4A3616C9F59009AF61EB3448E204F9B7E5F | .... | % | lecteur | *1F48A8CB9F3BAAE4504A9A4549B0AA290BD4E27B | .... +-----------+------------------+-------------------------------------------.... 6 rows in set (0.04 sec)
mysql> select * from user where user="lecteur"
-> ;
+------+---------+-------------------------------------------....
| Host | User | Password | ....
+------+---------+-------------------------------------------...
| % | lecteur | *1F48A8CB9F3BAAE4504A9A4549B0AA290BD4E27B ...
+------+---------+-------------------------------------------....
1 row in set (0.00 sec)
mysql> show grants for lecteur; +--------------------------------------------------------------------------------------------------------+ | Grants for lecteur@% | +--------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'lecteur'@'%' IDENTIFIED BY PASSWORD '*1F48A8CB9F3BAAE4504A9A4549B0AA290BD4E27B' | | GRANT SELECT ON `bibliotheque`.* TO 'lecteur'@'%' | +--------------------------------------------------------------------------------------------------------+ 2 rows in set (0.03 sec)
Vous devez créer des utilisateurs pour la base hotel :
gerant: le gérant a tous les droits sur la base hotel, y compris de modifier les droits des autres utilisateurscomptable: il a le droit de sélectionner et mettre à jour les tables liées aux clients et aux facturesaccueil: les personnes de l'accueil ont droit de sélection sur toutes les bases, et de mise à jour sur les tables liées aux clients
Vous devez vérifier les permissions en testant chaque utilisateur un par un.
Une des tâches fondamentales à réaliser avant de mettre un serveur en production est de le sécuriser. Ce vaste domaine est hors du contexte de ce cours, mais voici tout de même quelques rappels des opérations à faire au minimum :
Maintenir son sytème à jour : il est important d'appliquer les mises à jour et les patches, et ceci quelque soit le système d'exploitation.
Désactiver les programmes et services non nécessaires : par exemple, l'exécution de commandes du type netstat permet de voir quels sont les programmes à l'écoute d'un port réseau.
Sécuriser les comptes utilisateurs : l'emploi de mots de passe à forte complexité et la suppression des comptes utilisateurs inutiles est bien sûr une étape obligatoire.
Installer un firewall : si le serveur MySQL doit pouvoir être accessible depuis le réseau, il est nécessaire e firewallé le serveur.
Voici quelques points à respecter pour sécuriser convenablement MySQL !
Mettre à jour le serveur MySQL : de la même manière qu'il est nécessaire de maintenir son système d'exploitation, il est important de mettre à jour le serveur MySQL, en appliquant les patchs fournis par MySQL AB.
Lancer le serveur MySQL sous un autre compte que le compte
root: en utilisant l'option--usernamede mysqld, il est possible de démarrer le serveur sous une autre identité que le compte root.Communiquer avec le serveur en SLL : si des communications réseau sont nécessaire, dans ce cas les authentifications MySQL passent en clair par défaut. L'utilisation de tunnel SSL est dans ce cas fortement recommandé !
Désactiver l'écoute réseau de MySQL : si le serveur MySQL n'a pas besoin d'être accessible depuis des machines autres que l'hôte, on peut alors restreindre l'écoute de MySQL à
localhost(voir le fichiermy.conf)Désactiver le DNS
Supprimer la base de données
test
Le but du mode transactionnel est d'assurer l'intégrité de la base. Un ensemble d'instructions sont exécutées comme si il s'agissait une seule unité de travail.
En mode transactionnel, si une application a été écrite en dépendant de l'appel de ROLLBACK au lieu de COMMIT dans les situations critiques, les transactions s'assurent que les modifications non achevées ou les activitées corrosives ne sont pas archivées dans la base. Le serveur a l'opportunité d'annuler automatiquement l'opération, et votre base de données est sauve.
Le mode transactionnel est dépendant du format InnoDB sous MySQl, seul format de tables à pouvoir accepter les transactions.
Par défaut, le mode de fonctionnement de MySQL est AUTOCOMMIT. Ce mode implique que toutes les instructions sont exécutées immédiatement. Pour passer en mode transactionnel, il faut utiliser la commande :
mysql> set autocommit=0;
Les différentes requêtes que l'ont souhaitent exécutées de manière transactionnelle doivent être encadrées des instructions START TRANSACTION; et COMMIT;. L'exécution de 'linstruction COMMIT fait repasser MySQL en mode AUTOCOMMIT. Les modifications seront validées si l'instruction ROLLBACK est passé au lieu de COMMIT
Dans l'exemple suivant, nous souhaitons mettre à jour la table ecrit de la base bibliothèque : en effet, une erreur de saisie a ajouté un an à toutes les dates. Si pendant l'exécution de la requête, un problème survient, nous auront alors seulement une partie des livres qui aura été modifiée, sans pouvoir savoir lesquel ! Utiliser une transaction garanti que les modifications ne seront validés que si toutes les opérations peuvent être réalisées.
mysql> select * from ecrit; +---------------+----------------+-----------+ | ECR_NUM_LIVRE | ECR_NUM_AUTEUR | ECR_ANNEE | +---------------+----------------+-----------+ | 1 | 3 | 1968 | | 2 | 3 | 1971 | | 3 | 3 | 1973 | | 4 | 3 | 1975 | | 5 | 3 | 1979 | | 6 | 3 | 1982 | | 7 | 1 | 2000 | | 8 | 1 | 2001 | | 9 | 1 | 1985 | | 9 | 3 | 1985 | | 10 | 1 | 2003 | | 10 | 2 | 2003 | | 11 | 5 | 1998 | | 12 | 5 | 1999 | | 13 | 4 | 2002 | | 14 | 5 | 2000 | | 15 | 5 | 2001 | | 16 | 5 | 2002 | +---------------+----------------+-----------+ 18 rows in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update ecrit set ECR_ANNEE=ECR_ANNEE-1; Query OK, 18 rows affected (0.00 sec) Rows matched: 18 Changed: 18 Warnings: 0 mysql> select * from ecrit; +---------------+----------------+-----------+ | ECR_NUM_LIVRE | ECR_NUM_AUTEUR | ECR_ANNEE | +---------------+----------------+-----------+ | 1 | 3 | 1967 | | 2 | 3 | 1970 | | 3 | 3 | 1972 | | 4 | 3 | 1974 | | 5 | 3 | 1978 | | 6 | 3 | 1981 | | 7 | 1 | 1999 | | 8 | 1 | 2000 | | 9 | 1 | 1984 | | 9 | 3 | 1984 | | 10 | 1 | 2002 | | 10 | 2 | 2002 | | 11 | 5 | 1997 | | 12 | 5 | 1998 | | 13 | 4 | 2001 | | 14 | 5 | 1999 | | 15 | 5 | 2000 | | 16 | 5 | 2001 | +---------------+----------------+-----------+ 18 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.01 sec) mysql> select * from ecrit; +---------------+----------------+-----------+ | ECR_NUM_LIVRE | ECR_NUM_AUTEUR | ECR_ANNEE | +---------------+----------------+-----------+ | 1 | 3 | 1967 | | 2 | 3 | 1970 | | 3 | 3 | 1972 | | 4 | 3 | 1974 | | 5 | 3 | 1978 | | 6 | 3 | 1981 | | 7 | 1 | 1999 | | 8 | 1 | 2000 | | 9 | 1 | 1984 | | 9 | 3 | 1984 | | 10 | 1 | 2002 | | 10 | 2 | 2002 | | 11 | 5 | 1997 | | 12 | 5 | 1998 | | 13 | 4 | 2001 | | 14 | 5 | 1999 | | 15 | 5 | 2000 | | 16 | 5 | 2001 | +---------------+----------------+-----------+ 18 rows in set (0.00 sec) mysql> select * from ecrit; +---------------+----------------+-----------+ | ECR_NUM_LIVRE | ECR_NUM_AUTEUR | ECR_ANNEE | +---------------+----------------+-----------+ | 1 | 3 | 1967 | | 2 | 3 | 1970 | | 3 | 3 | 1972 | | 4 | 3 | 1974 | | 5 | 3 | 1978 | | 6 | 3 | 1981 | | 7 | 1 | 1999 | | 8 | 1 | 2000 | | 9 | 1 | 1984 | | 9 | 3 | 1984 | | 10 | 1 | 2002 | | 10 | 2 | 2002 | | 11 | 5 | 1997 | | 12 | 5 | 1998 | | 13 | 4 | 2001 | | 14 | 5 | 1999 | | 15 | 5 | 2000 | | 16 | 5 | 2001 | +---------------+----------------+-----------+ 18 rows in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update ecrit set ECR_ANNEE=ECR_ANNEE-1; Query OK, 18 rows affected (0.00 sec) Rows matched: 18 Changed: 18 Warnings: 0 mysql> select * from ecrit; +---------------+----------------+-----------+ | ECR_NUM_LIVRE | ECR_NUM_AUTEUR | ECR_ANNEE | +---------------+----------------+-----------+ | 1 | 3 | 1966 | | 2 | 3 | 1969 | | 3 | 3 | 1971 | | 4 | 3 | 1973 | | 5 | 3 | 1977 | | 6 | 3 | 1980 | | 7 | 1 | 1998 | | 8 | 1 | 1999 | | 9 | 1 | 1983 | | 9 | 3 | 1983 | | 10 | 1 | 2001 | | 10 | 2 | 2001 | | 11 | 5 | 1996 | | 12 | 5 | 1997 | | 13 | 4 | 2000 | | 14 | 5 | 1998 | | 15 | 5 | 1999 | | 16 | 5 | 2000 | +---------------+----------------+-----------+ 18 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.02 sec) mysql> select * from ecrit; +---------------+----------------+-----------+ | ECR_NUM_LIVRE | ECR_NUM_AUTEUR | ECR_ANNEE | +---------------+----------------+-----------+ | 1 | 3 | 1967 | | 2 | 3 | 1970 | | 3 | 3 | 1972 | | 4 | 3 | 1974 | | 5 | 3 | 1978 | | 6 | 3 | 1981 | | 7 | 1 | 1999 | | 8 | 1 | 2000 | | 9 | 1 | 1984 | | 9 | 3 | 1984 | | 10 | 1 | 2002 | | 10 | 2 | 2002 | | 11 | 5 | 1997 | | 12 | 5 | 1998 | | 13 | 4 | 2001 | | 14 | 5 | 1999 | | 15 | 5 | 2000 | | 16 | 5 | 2001 | +---------------+----------------+-----------+ 18 rows in set (0.00 sec)
Attention, certaines instructions ne peuvent pas être annulées par des ROLLBACK. Il s'agit des opérations:
ALTER TABLE
CREATE INDEX
DROP DATABASE
DROP TABLE
Le mode transactionnel n'est pas la panacée. Il est 3 à 4 fois plus lent que le mode non-transactionnel des tables MyISAM
On peut arriver à de meilleures performances tout en assurant l'intégrité des tables avec le « verrouillage de table » sur des tables MyISAM. La méthode est bien plus rapide que ne le proposent les transactions, avec des annulations ROLLBACK possibles mais pas certaines.
Les modifications de données transactionnelles fatales peuvent être réécrites de manière atomique. En général, tous les problèmes d'intégrité que les transactions résolvent peuvent être corrigés avec la commande LOCK TABLES ou des modifications atomiques, qui assurent qu'il n'y aura jamais d'annulation automatique de la base. Atomique, signifie simplement qu'on peut être certain que lorsqu'on modifie des données dans une table, aucun autre utilisateur ne peut interférer avec votre opération
L'instruction LOCK TABLES table WRITE permet ainsi de vérouiller un table pendant une séquence de requêtes, afin d'empêcher tout accès en écriture (ou en lecture avec READ). UNLOCK TABLES permet de déverouiller les accès.
Personne n'insistera jamais assez sur l'importance de pratiquer des sauvegardes régulières .. Pour MySQL, nous disposons principalement de trois outils :
mysqlhotcopy, script Perl permettant de faire la sauvegarde d'une base de données en exploitationmysqldump, fourni la liste des requête SQL nécessaire à la re-création complète (structure et données) d'une table ou d'une base de donnéesBACKUP TABLE, requête SQL permettant de sauvegarder le contenu d'une table précise
Utilitaire recommandé par MySQL AB pour faire la sauvegarde des bases de données, mysqlhotcopy est disponible uniquement sur plate-forme Unix/Linux. La syntaxe généarel de la commande est :
mysqlhotcopy db_name[./table_regex/] [new_db_name | directory]
L'exemple ci dessous montre la sauvegarde de la base hôtel dans un répertoire. Les options passées permettent de conserver les anciennes sauvegardes. Les fichiers copiés sont les fichiers contenant les structures des bases, les index, et les donées.
root@kaitan:/home/ikare# mysqlhotcopy --allowold --keepold hotel /home/ikare/sav_hotel Locked 14 tables in 0 seconds. Flushed tables (`hotel`.`TJ_CHB_PLN_CLI`, `hotel`.`TJ_TRF_CHB`, `hotel`.`T_ADRESSE`, `hotel`.`T_CHAMBRE`, `hotel`.` T_CLIENT`, `hotel`.`T_EMAIL`, `hotel`.`T_FACTURE`, `hotel`.`T_LIGNE_FACTURE`, `hotel`.`T_MODE_PAIEMENT`, `hotel`.`T_PLANNING`, `hotel`.`T_TARIF`, `hotel`.`T_TELEPHONE`, `hotel`.`T_TITRE`, `hotel`.`T_TYPE`) in 0 seconds. Copying 43 files... Copying indices for 0 files... Unlocked tables. mysqlhotcopy copied 14 tables (43 files) in 1 second (1 seconds overall). root@kaitan:/home/ikare# ls /home/ikare/sav_hotel/ hotel root@kaitan:/home/ikare# mysqlhotcopy --allowold --keepold hotel /home/ikare/sav_hotel Existing hotcopy directory renamed to '/home/ikare/sav_hotel/hotel_old' Locked 14 tables in 0 seconds. Flushed tables (`hotel`.`TJ_CHB_PLN_CLI`, `hotel`.`TJ_TRF_CHB`, `hotel`.`T_ADRESSE`, `hotel`.`T_CHAMBRE`, ` hotel`.`T_CLIENT`, `hotel`.`T_EMAIL`, `hotel`.`T_FACTURE`, `hotel`.`T_LIGNE_FACTURE`, `hotel`.`T_MODE_PAIEMENT`, `hotel`.`T_PLANNING`, `hotel`.`T_TARIF`, `hotel`.`T_TELEPHONE`, `hotel`.`T_TITRE`, `hotel`.`T_TYPE`) in 0 seconds. Copying 43 files... Copying indices for 0 files... Unlocked tables. mysqlhotcopy copied 14 tables (43 files) in 0 seconds (0 seconds overall). root@kaitan:/home/ikare# ls /home/ikare/sav_hotel/ hotel hotel_old root@kaitan:/home/ikare# ls /home/ikare/sav_hotel/hotel db.opt T_CLIENT.MYD TJ_CHB_PLN_CLI.frm T_LIGNE_FACTURE.MYI T_TARIF.MYD T_TYPE.frm T_ADRESSE.frm T_CLIENT.MYI TJ_CHB_PLN_CLI.MYD T_MODE_PAIEMENT.frm T_TARIF.MYI T_TYPE.MYD T_ADRESSE.MYD T_EMAIL.frm TJ_CHB_PLN_CLI.MYI T_MODE_PAIEMENT.MYD T_TELEPHONE.frm T_TYPE.MYI T_ADRESSE.MYI T_EMAIL.MYD TJ_TRF_CHB.frm T_MODE_PAIEMENT.MYI T_TELEPHONE.MYD T_CHAMBRE.frm T_EMAIL.MYI TJ_TRF_CHB.MYD T_PLANNING.frm T_TELEPHONE.MYI T_CHAMBRE.MYD T_FACTURE.frm TJ_TRF_CHB.MYI T_PLANNING.MYD T_TITRE.frm T_CHAMBRE.MYI T_FACTURE.MYD T_LIGNE_FACTURE.frm T_PLANNING.MYI T_TITRE.MYD T_CLIENT.frm T_FACTURE.MYI T_LIGNE_FACTURE.MYD T_TARIF.frm T_TITRE.MYI
La restauration de la base de données consiste à lancer la commande en inversant l'ordre des arguments.
root@kaitan:/home/ikare# ls /var/lib/mysql/hotel/ db.opt T_CLIENT.MYD TJ_CHB_PLN_CLI.frm T_LIGNE_FACTURE.MYI T_TARIF.MYD T_TYPE.frm T_ADRESSE.frm T_CLIENT.MYI TJ_CHB_PLN_CLI.MYD T_MODE_PAIEMENT.frm T_TARIF.MYI T_TYPE.MYD T_ADRESSE.MYD T_EMAIL.frm TJ_CHB_PLN_CLI.MYI T_MODE_PAIEMENT.MYD T_TELEPHONE.frm T_TYPE.MYI T_ADRESSE.MYI T_EMAIL.MYD TJ_TRF_CHB.frm T_MODE_PAIEMENT.MYI T_TELEPHONE.MYD T_CHAMBRE.frm T_EMAIL.MYI TJ_TRF_CHB.MYD T_PLANNING.frm T_TELEPHONE.MYI T_CHAMBRE.MYD T_FACTURE.frm TJ_TRF_CHB.MYI T_PLANNING.MYD T_TITRE.frm T_CHAMBRE.MYI T_FACTURE.MYD T_LIGNE_FACTURE.frm T_PLANNING.MYI T_TITRE.MYD T_CLIENT.frm T_FACTURE.MYI T_LIGNE_FACTURE.MYD T_TARIF.frm T_TITRE.MYI
mysqlhotcopy peut également générer directement une nouvelle base de données sur le serveur (man mysqlhotcopy). Les opérations réalisées avec cet utilitaire sont rapides, mais ne peuvent s'effectuer que sur le serveur lui même.
Si mysqlhotcopy permet de copier directement les fichiers de la base de données, mysqldump extrait l'ensemble des requêtes SQL nécessaires à sa régénération. Dans le cas de bases de données au volume conséquent, la restauration (l'exécution de toutes les requêtes SQL pour la création des structures et pour l'insertion des données) peut être très longue. Par contre, cette sauvegarde peut-être réalisée depuis un poste distant.
L'exemple ci dessous propose la sauvegarde de la base bibliotheque.
root@kaitan:/home/ikare/sav_biblio# mysqldump -p biblio > biblio.sql Enter password: root@kaitan:/home/ikare/sav_biblio# ls biblio.sql root@kaitan:/home/ikare/sav_biblio# more biblio.sql -- MySQL dump 10.11 -- -- Host: localhost Database: biblio -- ------------------------------------------------------ -- Server version 5.0.38-Ubuntu_0ubuntu1-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `auteur` -- DROP TABLE IF EXISTS `auteur`; CREATE TABLE `auteur` ( `AUT_NUM` smallint(5) unsigned NOT NULL auto_increment, `AUT_NOM` varchar(60) NOT NULL, ...... ....
La restauration se fait en exécutant le script dans mysql (mysql> source script.sql ou mysql -p < script.sql).
Dans certains cas, il peut également être intéssant de sauvegarder le contenu d'une table précise (avant des mises à jour massives par exemple). Dans ce cas, il est possible d'utiliser la requête SQL BACKUP TABLE.
BACKUP TABLE <tablename>(, <tablename>) TO <directory>
La restauration de la table se fait alors en utilisant la requête RESTORE TABLE.
RESTORE TABLE <tablename>(, <tablename>) FROM <directory>
Bien que MySQL soit considéré comme un SGBDR robuste, il peut arriver (coupure de courant, crash système, ..) que les fichiers de MySQL soient corrompus. Dans ce cas, il est nécessaier d'intervenir au moyen d'un outil, mysqlcheck. mysqlcheck vérifie (même si le serveur est démarré) l'état des tables d'une base de données. Sa syntaxe générale est la suivante :
mysqlcheck [OPTIONS] database [tables]
L'exemple ci dessous montre le résultat d'une analyse de la base hotel :
root@kaitan:/home/ikare/sav_biblio# mysqlcheck -p hotel Enter password: hotel.TJ_CHB_PLN_CLI OK hotel.TJ_TRF_CHB OK hotel.T_ADRESSE OK hotel.T_CHAMBRE OK hotel.T_CLIENT OK hotel.T_EMAIL OK hotel.T_FACTURE OK hotel.T_LIGNE_FACTURE OK hotel.T_MODE_PAIEMENT OK hotel.T_PLANNING OK hotel.T_TARIF OK hotel.T_TELEPHONE OK hotel.T_TITRE OK hotel.T_TYPE OK root@kaitan:/home/ikare/sav_biblio# mysqlcheck -p --analyze hotel Enter password: hotel.TJ_CHB_PLN_CLI Table is already up to date hotel.TJ_TRF_CHB Table is already up to date hotel.T_ADRESSE Table is already up to date hotel.T_CHAMBRE Table is already up to date hotel.T_CLIENT Table is already up to date hotel.T_EMAIL Table is already up to date hotel.T_FACTURE Table is already up to date hotel.T_LIGNE_FACTURE Table is already up to date hotel.T_MODE_PAIEMENT Table is already up to date hotel.T_PLANNING Table is already up to date hotel.T_TARIF Table is already up to date hotel.T_TELEPHONE Table is already up to date hotel.T_TITRE Table is already up to date hotel.T_TYPE Table is already up to date
Dans le cas d'insertion ou de suppression massive d'enregistrement, mysqlcheck permet également de défragmenter une base, en utilisant l'option --optimize.
Enfin, en cas de problème détectés, il est possible de tenter une réparation (qui peut se solder par une perte de données), mysqlcheck appelé avec l'option --repair ramène si possible les tables dans un état stable.
La surveillance d'un serveur est partie intégrante des tâches de maintenance d'une application. Pour MySQL, nous disposons d'un certain nombre de fichier journaux, situés sur les systèmes Linux dans le répertoire /va/log et /var/log/mysql. L'ensemble de l'activité de MySQL (requête, opérations de maintenance, ..) est stocké dans des fichiers binaires du répertoire /va/log/mysql/. Ces fichiers binaires permettent d'utiliser les fonctionnalités de réplication de MySQL.
Le fichier /var/log/mysql.err est quant à lui très utile en cas de problèmes au lancement de MySQL. Il recense en effet tous les messages d'erreur qui se produisent dans MySQL. De plus, il est généralement le dépositaire des rapports d'anomalies en cas de crash du serveur
Enfin, pour avoir des informations sur l'état actuel du serveur, il est possible d'utiliser l'utilitaire mysqladmin :
root@kaitan:/var/lib/mysql# mysqladmin -p status Enter password: Uptime: 164473 Threads: 1 Questions: 320 Slow queries: 0 Opens: 220 Flush tables: 1 Open tables: 38 Queries per second avg: 0.002 root@kaitan:/var/lib/mysql# mysqladmin -p extended-status Enter password: +-----------------------------------+----------+ | Variable_name | Value | +-----------------------------------+----------+ | Aborted_clients | 0 | | Aborted_connects | 4 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 1 | | Bytes_received | 38171 | | Bytes_sent | 78808 | | Com_admin_commands | 4 | | Com_alter_db | 0 | | Com_alter_table | 68 | | Com_analyze | 14 | | Com_backup_table | 0 | ...... ..... ... .
Nous avons vu dans le chapitre consacré aux requêtes de manipulation un certain nombre de règles concernant l'écriture des requêtes, et en particulier des jointures. Il est néanmoins bon de rappeler que l'instruction EXPLAIN permet d'obtenir des renseignements sur la manière dont MySQL utilise les différentes tables et index pour sélectionner les enregistrements à afficher.
De la même manière, nous avons vu au chapitre consacré à la définition des structures de tables les principes permettant d'obtenir de bons résultats de performance. Rappelons en quelques uns :
Utiliser des index sur les champs souvent employés dans les requêtes de sélection et de jointures
Choisir des types de données de taille minimal ou correspondant le plus au besoin de la colonne, pour gagner en place ET en rapidité
Défragmenter les tables en utilisant la requête
OPTIMIZE TABLEaprès des insertions ou suppressions massives d'enregistrement