Sistemes Gestors de Bases de Dades. MySQL. Instal.lació, configuració i ús

De Wikijoan
Dreceres ràpides: navegació, cerca

Contingut

instal.lació mysql

$ sudo apt-get install mysql-server

és un metapaquet que apunta a la última versió del mysql. En el procés d'instal.lació ens demana el pwd del root: ****

$ man mysql

Un cop descarregat i instal.lat (105MB), ja podem entrar

$ mysql -h localhost -u root -p****
$ mysql -u root -p****
$ mysql -u root -p -> demana el password

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.01 sec)

mysql> help

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

si només volem instal.lar el client,

$ sudo apt-get install mysql-client

$ man mysql
$ mysql

En aquest cas podem utilitzar el client mysql connenctant-nos a un servidor de mysql que hi hagi disponible en la xarxa:

$ mysql -h xxx.xxx.xxx.xxx -u root -p

Per tal que la connexió a través de la xarxa funcioni (connectar-nos a un servidor Mysql en la IP xxx.xxx.xxx.xxx) és necessari tenir els permisos correctes (veure més avall), i que el firewall de la xarxa no estigui blocat (el port per defecte del servei Mysql és el 3306).

accedir a la consola

Per connectar-me directament a una base de dades: (noota: com que la bd langtrainer encara no existeix, donarà error):

$ mysql -u root -p -D langtrainer
$ mysql -u root -p langtrainer

Amb aquesta última forma fixem-nos que langtrainer és la base de dades, no el password. Si vull proporcionar el password, recordem que no hi ha un espai després de l'opció -p.

crear una base de dades i les taules

$ mysql -u root -p

Com a root ara ja pots crear la base de dades langtrainer i tota la seva estructura. El propietari de la base de dades serà el root (després crearem un altre usuari amb permisos sobre la bd langtrainer).

Primer de tot creem la base de dades langtrainer:

mysql > CREATE DATABASE langtrainer DEFAULT CHARACTER SET utf8;
mysql > USE langtrainer;

L'esquema de la base de dades en MySQL és: (pots ficar totes les sentències en el fitxer langtrainer.sql)

/*
login(id_login (PK), login, pwd, name, mail, default_lang_w, default_lang_t)
language(id_language (PK), language, abr)
word(id_word (PK), id_language(FK), id_login(FK), word, probability,day,day_learnt)
translation(id_translation (PK), id_language (FK), id_word (FK), translation, type, sample)
on tipus és : verb, adjective, phraseal verb
quiz(id_quiz,id_login,date_time,number_quest,result)
quiz_detail(id_quiz,id_quest,id_word,result)
STATS(id_login, date_time)
*/

DROP TABLE quiz_detail;
DROP TABLE quiz;
DROP TABLE translation;
DROP TABLE word;
DROP TABLE login;
DROP TABLE language;

CREATE TABLE language(
id_language smallint primary key,
language varchar(20) not null,
abr varchar(3) not null
);

CREATE TABLE login(
id_login smallint primary key,
login varchar(10) not null,
pwd varchar(50) not null,
name varchar(30) not null,
mail varchar(30),
default_lang_w smallint not null,
default_lang_t smallint not null
);

ALTER TABLE login ADD FOREIGN KEY(default_lang_w) REFERENCES language(id_language) ON DELETE CASCADE;
ALTER TABLE login ADD FOREIGN KEY(default_lang_t) REFERENCES language(id_language) ON DELETE CASCADE;

CREATE TABLE word(
id_word smallint primary key,
id_language smallint not null,
id_login smallint not null,
word varchar(25) not null,  
probability smallint not null default 100,
day date not null,
day_learnt date
);

ALTER TABLE word ADD FOREIGN KEY(id_language) REFERENCES language(id_language) ON DELETE CASCADE;
ALTER TABLE word ADD FOREIGN KEY(id_login) REFERENCES login(id_login) ON DELETE CASCADE;

CREATE TABLE translation(
id_translation smallint primary key, 
id_language smallint not null,
id_word smallint not null, 
translation varchar(255) not null,
type varchar(15),
sample tinytext
);
# noun, adjective, verb, adverb, preposition, phrasal verb, acronym

ALTER TABLE translation ADD FOREIGN KEY(id_language) REFERENCES language(id_language) ON DELETE CASCADE;
ALTER TABLE translation ADD FOREIGN KEY(id_word) REFERENCES word(id_word) ON DELETE CASCADE;

CREATE TABLE quiz(
id_quiz smallint primary key,
id_login smallint not null,
day datetime not null,
number_quest smallint,
result decimal(4,2)
);

ALTER TABLE quiz ADD FOREIGN KEY(id_login) REFERENCES login(id_login) ON DELETE CASCADE;

CREATE TABLE quiz_detail(
id_quiz smallint,
id_quest smallint not null,
id_word smallint not null,
result char(1),
PRIMARY KEY (id_quiz, id_quest)
);

ALTER TABLE quiz_detail ADD FOREIGN KEY(id_quiz) REFERENCES quiz(id_quiz) ON DELETE CASCADE;
ALTER TABLE quiz_detail ADD FOREIGN KEY(id_word) REFERENCES word(id_word) ON DELETE CASCADE;

INSERT INTO language(id_language,language, abr) VALUES(1,'English','ENG');
INSERT INTO language(id_language,language, abr) VALUES(2,'Català','CAT');

INSERT INTO login(id_login,login,pwd,name,default_lang_w,default_lang_t) VALUES(1,'admin','a5302fbf1b07ed51d09716ba5907a179','Admin',1,2);


INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(1,1,1,'deserve (to)',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(2,1,1,'weird',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(3,1,1,'gills',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(4,1,1,'draw breath (to)',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(5,1,1,'draw (to)',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(6,1,1,'snout',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(7,1,1,'wound',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(8,1,1,'dismiss (to)',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(9,1,1,'shame',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(10,1,1,'hump',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(11,1,1,'lump',100,'2009/09/01');
INSERT INTO word(id_word, id_language, id_login, word, probability, day) VALUES(12,1,1,'spit',100,'2009/09/01');


INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(1,2,1,'merèixer, ser digne de','v',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(2,2,2,'rar, extrany','a',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(3,2,3,'branquies, agalles','n','who has either grown gills from spending 45 years underwater or learned to draw breath only once every few weeks: que, després de 45 anys sota l''aigua ha desenvolupat brànquies, o bé ha après a agafar aire només una vegada cada poques setmanes.');
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(4,2,4,'agafar aire','v','who has either grown gills from spending 45 years underwater or learned to draw breath only once every few weeks: que, després de 45 anys sota l''aigua ha desenvolupat brànquies, o bé ha après a agafar aire només una vegada cada poques setmanes.');
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(5,2,5,'dibuixar','v',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(6,2,5,'Estirar (pull), treure, atraure','v',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(7,2,6,'morro, hocico','n',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(8,2,7,'ferida','n',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(9,2,7,'ferir (pp, ferit)','v',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(10,2,8,'acomiadar, destituir. Refusar','v','the shark dismissed me as worthy of neither fearing nor eating: el tauró em va refusar de considerar-me ni per fer-li por ni per menjar-me');
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(11,2,9,'vergonya, llàstima','n',NULL);
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(12,2,10,'pujol, puig','n','4000 islands, islets, cays, humps, lumps, and spits');
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(13,2,11,'terròs, tros','n','4000 islands, islets, cays, humps, lumps, and spits');
INSERT INTO translation(id_translation, id_language, id_word, translation,type,sample) VALUES(14,2,12,'escupinada, saliva','n','4000 islands, islets, cays, humps, lumps, and spits');

Nota: si fas Copiar i enganxar de tot el bloc, és possible que el script no s'executi fins al final de forma completa. Fer Ctrl-C i Ctrl-V no és la manera més recomanable, poden haver-hi problemes amb el buffer de comandes. Més avall es comenta la manera més correcta de fer-ho. Pensa que aquest script és curt, però podries tenir un script amb 10000 línies.

crear usuaris amb permisos sobre una base de dades

Moltes vegades ens trobem en la situació, a l'institut, que els alumnes (que no han de conèixer el password del root del mysql), han de tenir permisos sobre una base de dades. És el cas de les bases de dades MantisBT, toner, control_wifi, videovigilancia,... que es donen d'alta en els Crèdits de Síntesi o en alguna assignatura.

El professor, com a administrador/root del Mysql, donarà d'alta l'usuari alumne amb permís de només treballar a la base de dades langtrainer:

$ mysql -u root -p

mysql> CREATE DATABASE langtrainer DEFAULT CHARACTER SET utf8;
mysql> CREATE USER alumne IDENTIFIED BY 'keiL2lai';
mysql> GRANT ALL ON langtrainer.* TO alumne@localhost identified by "keiL2lai";
mysql> flush privileges;

Ara l'alumne ja es podria connectar directament a aquesta base de dades, però només localment (valor per defecte: -h localhost):

$ mysql -h localhost -u alumne -p langtrainer (compte! això és connectar-se a la base de dades langtrainer, i després demana el password!)
Enter password: 
mysql>
o senzillament no cal ficar -h localhost:

$ mysql -u alumne -p langtrainer
Enter password: 

En aquest cas, el propietari de la base de dades és el root, i es dóna permisos totals de l'usuari alumne sobre la bd langtrainer. Aquesta és la manera més segura, doncs l'usuari alumne no té permisos per crear una base de dades:

$ mysql -u alumne -p
Enter password: 
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> create database video;
ERROR 1044 (42000): Access denied for user 'alumne'@'localhost' to database 'video'

opcions per connectar-se a una base de dades. Accedir a un mysql que no sigui localhost

Si volem connectar-nos des del nostre portàtil a un servidor mysql que estigui disponible des de la xarxa, s'haurà de complir dues condicions:

A l'institut, degut a la configuració del firewall, només podràs connectar-te a la base de dades langtrainer que està en la IP 192.168.0.15:

El professor farà:

mysql> CREATE USER alumne IDENTIFIED BY 'keiL2lai';
mysql> grant all on langtrainer.* to alumne@"%" identified by "keiL2lai";
mysql> flush privileges;

I ara l'alumne ja pot accedir a la bd langtrainer des del seu portàtil:

$ mysql -h 192.168.0.15 -u alumne -p langtrainer

NOTA: Actualització octubre 2012: Utilitzo la versió mysql 5.5. Per defecte està capat l'accés des de IP's diferents de localhost. Per modificar-ho s'ha de comentar la línia que posa bind-adress = 127.0.0.1, i per tant queda:

$ sudo joe /etc/mysql/my.cnf

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address            = 127.0.0.1

$ sudo /etc/init.d/mysql restart

Accedir a un mysql que no sigui localhost. Access denied for user 'root'@'PROFE06.local'

Encara que hagi canviat el fitxer de configuració my.cnf, no em deixa connectar-me des d'una màquina remota. Ens volem connectar amb dos usuaris: root (l'alumne no ho fa això), i alumne.

$ mysql -h 192.168.0.15 -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'PROFE06.local' (using password: YES)
$ mysql -h 192.168.0.15 -u alumne -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'alumne'@'PROFE06.local' (using password: YES)

La clau està en:

mysql> SELECT User, Host, Password FROM mysql.user;

i el que he de fer és crear una nova entrada per a la màquina des de la que em vull connectar:

mysql> insert into mysql.user(User,Host) values ('root','192.168.0.57');
mysql> insert into mysql.user(User,Host) values ('alumne','192.168.0.57');
mysql> flush privileges

mysql> SET PASSWORD FOR 'root'@'192.168.0.57' = PASSWORD('*****');
mysql> SET PASSWORD FOR 'alumne'@'192.168.0.57' = PASSWORD('keiL2lai');


o bé

mysql> UPDATE mysql.user SET Password = PASSWORD('*****') WHERE User = 'root';
mysql> UPDATE mysql.user SET Password = PASSWORD('keiL2lai') WHERE User = 'alumne';

The FLUSH statement causes the server to reread the grant tables. Without it, the password change remains unnoticed by the server until you restart it.

$ mysql -h 192.168.0.15 -u root -p

Ara ja podem entrar, però només veiem les bases de dades information_schema i test. Encara falta una altra coseta, donar permisos sobre la base de dades a la qual ens volem connectar:

mysql> GRANT ALL ON langtrainer.* TO root@192.168.0.57 identified by "*****";
mysql> GRANT ALL ON langtrainer.* TO alumne@192.168.0.57 identified by "keiL2lai";
mysql> flush privileges;

I ara ja ens podem connectar a una base de dades remota i a una base de dades concreta:

$ mysql -h 192.168.0.15 -u root -p langtrainer
$ mysql -h 192.168.0.15 -u alumne -p langtrainer

practicar amb select's

Evidentment, l'operació més habitual que fem amb les bases de dades és fer consultes. Aquí van dos exemples de consultes:

mysql> SELECT word,translation FROM word w, translation t WHERE w.id_word=t.id_word AND w.id_login=1 AND w.id_language=1 AND t.id_language=2 ORDER BY WORD;  

| score (to)               | marcar                                                                                          | 
| scorn (to)               | despreciar                                                                                      | 
| scroll                   | rotllo                                                                                          | 
| seldom                   | rarament                                                                                        | 
| selfish                  | egoista                                                                                         | 
| selfish                  | selfishness: egoisme   
mysql> SELECT language,COUNT(*) FROM word, language WHERE word.id_language=language.id_language GROUP BY word.id_language;

+-----------+----------+
| language  | COUNT(*) |
+-----------+----------+
| English   |      735 | 
| Français |        9 | 
+-----------+----------+
2 rows in set (0.00 sec)

Si no et satisfà la formatació del resultat en forma de taula, prova d'arrencar el client amb l'opció -s (--silent):

$ mysql -s -u alumne -p langtrainer

mysql> SELECT language,COUNT(*) FROM WORD, LANGUAGE WHERE WORD.id_language=LANGUAGE.id_language GROUP BY WORD.id_language;

language	COUNT(*)
English	735
Français	9

Val a dir que aquesta és l'opció correcta si vols enganxar els resultats en un full de càlcul.

També pot ser interessant aquesta forma de fer (des del prompt del SO), utilitzar l'opció -e per executar una comanda:

$ mysql -s -u alumne -pkeiL2lai -D langtrainer -e "SELECT word,translation FROM word w, translation t WHERE w.id_word=t.id_word AND w.id_login=1 AND w.id_language=1 AND t.id_language=2 ORDER BY word;" > resultat.txt

$ cat resultat.txt

executar un script SQL. Errors generats

Habitualment el DBA (Administrador de bases de dades) treballa amb script SQL, que inclouen moltes sentències SQL (inserts, updates, deletes,...) a executar-se en lot. Al script li hem de demanar:

$ mysql -h localhost -u alumne -p langtrainer -f  < vocabulari.sql  > error.log 2>&1

Normalment en el mysql quan executem una sentència i es produeix un error s'emet un beep.

Si volem executar el script des de dins del client mysql, aleshores utilitzar l'opció \.:

source    (\.) Execute an SQL script file. Takes a file name as an argument.

L'opció -f (--force) fa que el script continuï endavant encara que hi hagi errors.

NOTA: la instrucció 2>&1 és per redirigir els possibles errors al fitxer error.log. Si vols més informació, veure el següent enllaç:

còpia de seguretat i restauració

$ man mysqldump

$ mysqldump --add-drop-table -i -C -ualumne -p -r copia_langtrainer_041011.dmp -v langtrainer

anem a fer la prova de foc:

$ mysql -u root -p
mysql> drop database langtrainer
mysql> use langtrainer -> dóna error
mysql> create database langtrainer DEFAULT CHARACTER SET utf8; -> amb el mètode que utilitzaré, necessito que existeixi la bd langtrainer
mysql> \q

fem el restore:

$ mysql -u root -p langtrainer < /home/joan/mysql_backup/copia_langtrainer_041011.dmp

També puc fer una còpia de seguretat en què l'arxiu que genera pugui veure les comandes SQL per restaurar la BD:

$ mysqldump -i --complete-insert -u root -pkeiL2lai -r /home/joan/mysql_backup/copia_langtrainer_041011.sql -v langtrainer

Eina gràfica: mysql workbench

Langtrainer.png

Per instal.lar aquesta eina gràfica hi ha diferents opcions: des de compilar a partir del codi font (no recomanable) fins a buscar el paquet adequat per instal.lar.

mysql-workbench - MySQL Workbench - a visual database modeling, administration and queuing tool

$ sudo apt-get install mysql-workbench


Entrem en el programa, i és bastant intuïtiu. Bàsicament, el que interessa ara és dibuixar l'esquema de la base de dades per tal de què els alumnes puguin veure gràficament les relacions que s'estableixen entre les taules. Ho podem fer tant a partir del script (langtrainer.sql) o a partir de la base de dades. L'enginyeria inversa funciona correctament com es pot veure en el gràfic.

D'entrada no queda clar quins camps de les diferents taules estan relacionats. La clau està en posar-se sobre d'una línia, i aleshores queda remarcat els camps que estan relacionats.


creat per Joan Quintana Compte, octubre 2011

Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
IES Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines