$ mysql -u root mysql -p
Configuration des droits
Après avoir installé MySQL, il est nécessaire de
configurer la table des droits d'accès. En effet, MySQL repose sur un principe
d'accès restreint qui permet de contrôler quel(s) utilisateur(s) peut accéder
à quelles bases, et quelles commandes il peut exécuter sur celles-ci
(SELECT, DROP, etc.). Avant toute utilisation de MySQL, on s'assurera au
préalable d'avoir bien lancé le démon, généralement mysqld.
[Par
défaut, sous Linux, on peut utiliser les scripts Sys V, /etc/rc.d/mysql
start.]
Dans un premier temps, il faut créer un (ou plusieurs) utilisateur(s) ; pour cela, on se connecte en root sur la table mysql, qui contient la table des droits :
et on s'alloue tous les droits (ce n'est pas forcément une bonne idée, mais ça suffit pour tester les fonctionnalités de MySQL) :
GRANT ALL PRIVILEGES ON *.* TO 'utilisateur'@'%'
IDENTIFIED BY 'mot_de_passe' WITH GRANT OPTION;
où utilisateur et mot_de_passe sont bien évidemment à remplacer par les valeurs souhaitées. On notera que l'on utilisera % pour inclure n'importe quel domaine pour la connexion. Le cas échéant, il faut spécifier localhost (lorsqu'on n'est pas connecté à un réseau) et le nom d'hôte renvoyé par
$ hostname
Dans la commande sql décrite plus haut, on a donné tous les privilèges à l'utilisateur utilisateur. Pour rendre effectives les modifications, il faut en plus taper
FLUSH PRIVILEGES;
En effet, pour améliorer la rapidité, MySQL dispose d'une copie de la base de données mysql en mémoire, et celle-ci est actualisée par la commande FLUSH PRIVILEGES.
On peut ensuite vérifier que le compte utilisateur est présent dans la table des droits (on travaille toujours sous l'identité root) :
SELECT user, host, password FROM user;
On peut ensuite quitter la ligne de commande avec quit; et se reconnecter sous l'identifiant nouvellement crée :
$ mysql -u chl -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 5.0.26-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Création d'une base de test
Cas général
En premier lieu, il est nécessaire de créer une nouvelle base, avec la commande :
CREATE DATABASE my_test;
On pourra vérifier que la base a bien été créer en tapant
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | my_test | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec)
Notons que l'on pourrait utiliser la base test puisque celle-ci est fournie par défaut lors de l'installation (cf. note ci-dessous). Puis on indique que l'on travaille sur celle-ci (les nouvelles commandes sql s'appliqueront désormais sur la base my_test) :
|
À propos des connexions sur des tables SQL
Par défaut également, les connexions anonymes sont autorisées et n'importe qui peut agir sur cette base de test. En fait, toutes les bases débutant par test sont accessibles aux utilisateurs. On peut préférer une autre solution qui consiste à permettre à chaque utilisateur de créer sa propre base, en la préfixant par son nom d'utilisateur, e.g. dupont_unebase. Pour cela, on ne peut pas utiliser directement GRANT, car celle-ci n'autorise pas la saisie de caractères génériques, et on est obligé de jongler un peu : USE my_test;
GRANT USAGE ON *.* TO username@localhost IDENTIFIED BY 'xxx';
INSERT INTO mysql.db
(Host, Db, User, Select_priv, Insert_priv, Update_priv,
Delete_priv, Create_priv, Drop_priv, Grant_priv, Reference_priv,
Index_priv, Index_prov, Alter_priv, Create_tmp_table_priv,
Lock_tables_priv, Create_view_priv, Show_view_priv)
VALUES
('localhost', 'username%', 'username', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
FLUSH PRIVILEGES;
On effectue ainsi les modifications nécessaires directement dans la base db, et les noms de bases de données autorisés sont constitués de username suivi de n'importe quel nombre de caractères (cf. le joker %). La première commande (GRANT…) sert à créer les utilisateurs si besoin. |
On peut ensuite créer notre première table, par exemple une table incluant un identifiant unique, automatiquement attribué, un score et une date de saisie.
CREATE TABLE une_table (
id INT NOT NULL AUTO_INCREMENT,
choice TINYINT NOT NULL,
ts TIMESTAMP,
PRIMARY KEY (id));
On notera que l'on a définit la clé primaire sur le champ id, et que les champs id et choice doivent obligatoirement être renseignés (attribut NOT NULL).
Ensuite, on peut commencer à alimenter la base avec des données :
INSERT INTO une_table (choice) VALUES (3); INSERT INTO une_table (choice) VALUES (5);
On peut vérifier l'état de la base avec la construction classique SELECT … FROM … :
SELECT COUNT(choice) FROM une_table; +---------------+ | COUNT(choice) | +---------------+ | 2 | +---------------+ 1 row in set (0.00 sec)
Le résultat indique le nombre d'enregistrements présents dans la table une_table (dans le cas présent, 2 enregistrements).
Plutôt que de remplir manuellement la base avec la clause INSERT, on peut charger directement un fichier texte contenant les valeurs à ajouter. De même, on peut créer le tableau de données à l'aide d'un tableur, puis exporter la feuille de données au format txt (ou csv), en spécifiant un séparateur (tabulation ou ;). Pour charger les données dans la table, il suffira de taper :
LOAD DATA INFILE '~/un_fichier.txt' INTO TABLE une_table
FIELDS OPTIONALLY ENCLOSED BY '"';
On se rappelera que la tabulation est le séparateur de données par défaut sous MySQL. Si le fichier texte utilise le ; comme séparateur de données, il faut le préciser lors de l'appel. Si tout se passe bien, on a le message suivant :
Query OK, 8 rows affected (0.00 sec) Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
On a bien rajouté 8 enregistrements provenant du fichier, et on peut vérifier
que la table comporte à présent 10 enregistrements (les 2 crées manuellement
les 8 importés) :
SELECT COUNT(choice) FROM une_table; +---------------+ | COUNT(choice) | +---------------+ | 10 | +---------------+ 1 row in set (0.00 sec)
Pour un script d'importation automatique en PHP, voir http://www.infres.enst.fr/~danzart/mysql/mysqlimporte.phtml
Outils SQL sous Mac OS X
Les mêmes instructions, à l'exception de la gestion des scripts dans /etc, sont sous environnement Mac OS X. Après tout, c'est l'interaction avec un shell SQL qui a été présentée (certes très succinctement).
Sous Mac OS X, il existe des applicatifs qui “facilitent” la gestion des bases de données (maintenance, requêtes, etc.). Mentionnons en deux :
CocoaMySQL est une application permettant de gérer des bases de données MySQL locales ou en ligne (création, suppression, modification, requêtes, sauvegardes). Voici à quoi ressemble l'interface générale (ici, connectée à une simple base de données) : MySQL GUI Tools, proposé sur le site mysql est un ensemble de trois applications :
-
MySQL Administrator
-
MySQL Query Browser
-
MySQL Migration Toolkit
Les figures suivantes illustrent la gestion du serveur [Fig. 1], ainsi que des tables [Fig. 2] (MySQL Administrator).
Figure: Gestionnaire d'administration des bases SQL.
Figure: Gestionnaire d'administration des bases SQL.
Dans la pratique, j'utilise assez rarement ces utilitaires puisque je construis mes tables dans des fichiers que je “source” directement dans le shell SQL. Quant aux requêtes, je les effectue généralement directement en ligne de commande ou à partir d'un script dans un autre langage (R, par exemple).
Effectuer des requêtes (notions basiques)
À présent, on peut effectuer des requêtes sur la base pour tester les fonctionnalités de mysql.
Langage de requêtes et exemples
Cette partie est à écrire…
Interfaces graphiques sous Mac OS X
Nous avons mentionné dans la section précédente l'existence d'utilitaires graphiques. Outre l'interface de gestion du serveur mysql, les outils proposés par mysql incluent un utilitaire de requête. À l'aide de celui-ci, on peut formuler une requête et visualiser le résultat dans des fenêtres organisées par onglets [Fig. 3]. Ici, on a simplement effectué une requête de type SELECT * FROM Fiche (dans une base appelée, tcf). La clé primaire est indiquée par un carré dans la colonne correspondante (ici, idFiche). Le nombre de tuples retournés est indiqué dans la partie basse de l'interface.
Figure: Résultat d'une requête dans MySQL Query Browser.
La palette d'outils proposée sur une barre latérale [Fig. 4] est assez commode puisqu'elle contient tous les éléments du langage SQL et propose une vue des schémas de la base.
Figure: Panneau latéral de MySQL Query Browser.
Interfacer SQL avec d'autres langages
MySQL et R
On peut également utiliser R pour interroger notre base. Il y a alors deux solutions :
-
soit on dispose d'un fichier texte dans lequel se trouve les données de la base (on aura au préalable exporté celle-ci),
-
soit on veut travailler directement en se connectant sur le serveur MySQL.
Dans le premier cas, il suffit de charger le fichier avec read.csv() :
> a <- read.csv("une_table.csv", header=T)
Dans le second cas, on a besoin du paquetage RODBC.
> install.packages('RODBC')
Le package RMySQL permet de travailler directement avec une base de données MySQL. La procédure est assez simple puisqu'il suffit de définir un descripteur pour la connexion (il faut naturellement que le serveur soit démarré) :
> library(RMySQL)
> link <- dbConnect(MySQL(), user="******", password="******",
dbname="tcf", host="localhost")
> dbListTables(link)
Pour effectuer des requêtes, on utilise la fonction dbSendQuery(), par exemple :
> result <- dbSendQuery(link, "SELECT * FROM Correcteur")
L'ensemble des résultats est à présent contenu dans la variable result. Pour afficher le résultat, on utilise la fonction fetch() qui prend en argument la variable précédente.
> fetch(result, n=10)
MySQL et Perl
Pour utiliser l'interface Perl/MySQL, il faut installer le module DBI et DBD::mysql. On peut utiliser l'interface cpan :
> sudo perl -MCPAN -e 'install Bundle::DBD::mysql'
|
Problème rencontré sous MacOS X
Par défaut, Perl s'attend à trouver la librairie (statique) libmysqlclient.15.dylib dans le répertoire /usr/local/mysql/lib/mysql/, or il semble que depuis les dernières versions de MySQL, celle-ci soit installée dans le répertoire /usr/local/mysql/lib/. On peut imaginer recréer des liens symboliques pour faire correspondre ces deux répertoires, mais il existe une méthode plus simple de mise à jour du système de référencement des librairies sous MacOS X : la commande install_name_tool. Voici ce qu'il faut faire (en root) : $ sudo install_name_tool -change $ /usr/local/mysql/lib/mysql/libmysqlclient.15.dylib $ /usr/local/mysql/lib/libmysqlclient.15.dylib $ /Users/chl/.cpan/build/DBD-mysql-4.005/blib/arch/auto/DBD/mysql/mysql.bundle Cette remarque vaut également pour la configuration de Rails. |
Avec Perl, il est nécessaire de définir, comme pour R, le serveur, le nom de la base, et les informations de connexion (nom d'utilisateur et mot de passe).
my $link = DBI->connect("dbi:mysql:$db:$server", $user, $pwd);
On forme ensuite la requête sql et on l'exécute :
my $query = "show tables"; my $sql = $link->prepare($query); $sql->execute();
Pour récupérer et afficher les résultats, on peut utiliser une boucle formulée comme suit :
while (my $row = $sql->fetchrow_arrayref) {
print join("\t", @$row), "\n";
}
On n'oubliera pas de refermer la connexion une fois les requêtes effectuées.
$link->disconnect;
MySQL et Python
Enfin, on peut également préférer le langage Python pour la manipulation et l'interrogation de la base MySQL. Il est nécessaire dans ce cas d'installer le package mysql pour Python. Sous MacOS X, on peut le trouver sur le site sourceforge.net/projects/mysql-python/. Pour compiler, il suffit de taper :
$ python setup.py build
puis en root :
$ sudo python setup.py install
On peut tester l'installation en tentant de se connecter à une base et en affichant les résultats du requête SELECT.
>>> import MySQLdb;
>>> link = MySQLdb.connect('localhost','user','pwd','dbName')
>>> curs = link.cursor()
>>> result = curs.execute("select * from Correcteur")
>>> result=curs.fetchall()
>>> print result;
>>> curs.close()
>>> link.close()
