Diferència entre revisions de la pàgina «Sistemes Gestors de Bases de Dades. MySQL. Instal·lació, configuració i ús»

De wikijoan
Salta a la navegació Salta a la cerca
 
Línia 265: Línia 265:
 
$ mysql -u root -p
 
$ mysql -u root -p
  
mysql> GRANT ALL ON langtrainer.* TO alumne@localhost identified by "keiL2lai";
+
mysql> GRANT ALL ON langtrainer.* TO alumne@localhost;
mysql> GRANT ALL ON langtrainer.* TO 'alumne'@'%' identified by "keiL2lai";
+
mysql> GRANT ALL ON langtrainer.* TO 'alumne'@'%';
 
mysql> flush privileges;
 
mysql> flush privileges;
 
</pre>
 
</pre>

Revisió de 16:06, 28 oct 2020

instal·lació mysql

$ sudo apt-get install mysql-server

és un metapaquet que apunta a la última versió del mysql. Antigament en el procés d'instal.lació et demanava el pwd del root, Actualment (v8) no demana el password del root, i la primera vegada no podrem entrar amb l'usuari normal, hem d'entrar amb el root del sistema:

$ man mysql

Un cop descarregat i instal·lat (105MB) ja podem entrar, però com a root.

$ sudo su
root@HP:~#  mysql -h localhost -u root
mysql>

Hem entrat sense que ens demani el password, perquè de fet l'usauri root del mysql encara no té password.

Per entrar al mysql sense haver de fer sudo, amb el nostre usuari normal del sistema, hem de posar un password al root:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '*******';
mysql> FLUSH PRIVILEGES;
mysql> exit
root@HP:~# exit
$

ara ja puc entrar al mysql amb l'usuari normal:

$ mysql -h localhost -u root -p
mysql>

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
$ mysql -h localhost -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.

Accedir amb un usuari que no sigui el root. Accedir a un servidor local o a un servidor remot

Volem connectar-nos a la base de dades langtrainer. Anem a connectar-nos a la nostra base de dades amb l'usuari alumne, primer en el servidor local. Després ho farem amb l'usuari alumne però a un servidor mysql remot.

$ mysql -u root -p

mysql> CREATE USER alumne@localhost IDENTIFIED BY 'keiL2lai';

El CREATE USER només ho podem fer si estem identificats com a root. Fixem-nos que hem creat un usuari alumne que només té accés en el servidor local.

Si volem un usuari que tingui accés local farem:

mysql> CREATE USER alumne IDENTIFIED BY 'keiL2lai';
que és equivalent a:
mysql> CREATE USER 'alumne'@'%' IDENTIFIED BY 'keiL2lai';

Podem veure aquests usuaris que hem creat fent:

mysql> SELECT User, Host FROM mysql.user;

Ara bé, aquests usuaris encara no tenen permís per entrar a la base de dades langtrainer:

$ mysql -h localhost -u alumne -pkeiL2lai langtrainer
Access denied

Hem de donar privilegis sobre els objectes d'aquesta base de dades:

$ mysql -u root -p

mysql> GRANT ALL ON langtrainer.* TO alumne@localhost;
mysql> GRANT ALL ON langtrainer.* TO 'alumne'@'%';
mysql> flush privileges;

Fixem-nos que tractem els dos usuaris com si fossin diferents. Es distingeix entre l'usuari alumne connectant-se des de la màquina local, o connectant-se a un servidor remot.

$ 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 -h localhost -u alumne -pkeiL2lai langtrainer
mysql>
o senzillament no cal ficar -h localhost:

$ mysql -u alumne -ppkeiL2lai langtrainer

mysql> select count(*) from word;

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 fer res més que no sigui interaccionar amb la base de dades langtrainer. Per ex:

$ mysql -u alumne -p
mysql> create database video;
ERROR 1044 (42000): Access denied for user 'alumne'@'localhost' to database 'video'

Fins ara hem operat amb un servidor local (localhost).

Anem a mirar quina és la nostra IP (ifconfig), i ens connectarem a aquesta IP (encara que continua sent el nostre servidor local):

$ mysql -h 192.168.1.240 -u alumne -pkeiL2lai langtrainer

No ens deixa. Per què? És pel fitxer de configuració.

$ ls /etc/mysql/
conf.d	    debian-start  my.cnf.fallback  mysql.conf.d
debian.cnf  my.cnf	  mysql.cnf

joan@joan:~/railway/js$ ls /etc/mysql/mysql.conf.d/
mysqld.cnf  mysqld_safe_syslog.cnf

El fitxer que ens importa és el /etc/mysql/mysql.conf.d/mysqld.cnf.

Veiem que hi ha la línia:

bind-address = 127.0.0.1

Significa que aquest servidor mysql només accepta connexions locals (per seguretat). Caldrà comentar aquesta línia i reiniciar el mysql:

#bind-address = 127.0.0.1

$ sudo /etc/init.d/mysql restart

I ara ja podem entrar:

$ mysql -h 192.168.1.240 -u alumne -pkeiL2lai langtrainer

Això ho pots provar amb dues màquines virtuals. En una màquina tindràs el servidor mysql. En l'altra recorda que no cal tenir el servidor mysql, només cal que tinguis el client. Des de la màquina client podràs connectar-te al servidor mysql sabent quina és la IP, però prèviament hauràs de comentar el bind-address.

A classe, els alumnes es podrann connectar al servidor mysql del professor.

En un futur, habilitareu un servidor mysql a una màquina AWS en el núvol, i podreu accedir a les bases de dades que tinguem al núvol.

NOTA: quan ens volem connectar remotament, a part de la configuració de seguretat del mysql, haurem de tenir en compte els aspectes de xarxa: obrir el port 3306 (port del mysql per defecte), firewall,...

practicar amb select's

NOTA: per saber els camps d'una taula (i el tipus de dades), pots fer una d'aquestes opcions:

mysql> desc word;
mysql> describe word;
mysql> show columns in word;

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

  • vocabulari anglès-castellà que està estudiant l'usuari 1
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   
  • número de paraules que tenen els vocabularis de l'usuari 1
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:

  • que no s'aturi cada vegada que trobi un error. Per això utilitzem l'opció -f (force)
  • que generi un log per tal de poder estudiar quan hagi acabat l'execució si el procés ha anat correcte, i en cas contrari, veure quins errors s'han provocat.
$ 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 2018