ASIX-M10-UF2. MySQL II. Capa d'Aplicació. Capa Lògica. Capa Física

De wikijoan
Salta a la navegació Salta a la cerca

Referències, teoria

BDvsTradicional.jpg

La teoria està explicada a ASIX-M10-UF2._Evolució_i_funcions_dels_SGBD._Arquitectura_general_d’un_SGBD

Concretament, en el següent pdf:

Objectius

En aquesta pràctica aprofitarem en l'arquitectura d'un SGBDR. Concretament el MySQL. Per fer aquesta pràctica també s'hagués pogut triar d'altres SGBD Relacionals com Postgres o Oracle, i hauríem de veure com els conceptes es repeteixen.

Concretament, ens interessa distingir entre les tres capes:

  • Capa d'Aplicació
  • Capa lògica
  • Capa física

Els administradors de sistema i administradors de bases de dades han de conèixer bé la capa física, és a dir, l'Arquitectura. Concretament, haurem de localitzar i entendre els fitxers de dades (datafiles), diccionari de dades, índex i logs.

Desenvolupament

Utilitzarem la base de dades langtrainer amb la qual ja estàs familiaritzat.

Capa d'Aplicació

La capa d'aplicació representa la interfície amb els diferents usuaris del sistema i de la base de dades. Proporciona els mitjans de manera que el món extern pugui interactuar amb el servidor de base de dades. Els diferents usuaris que interacturan amb la base de dades es poden categoritzar en diferents grups:

  • usuaris finals
  • usuaris avançats
  • programadors de la base de dades
  • administradors de la base de dades

Cadascun dels usuaris tindrà la seva visió i les seves eines per interactuar amb la base de dades. Per exemple, l'usuari final pot tenir una aplicació web. Els usuaris avançats poden tenir una consola mysql per fer consultes SQL directament (i només permisos per fer selects, inserts, deletes i updates). El programador web pot tenir accés a la consola SQL i al phpMyAdmin. L'administrador de sistemes té permís per fer totes les operacions sobre la base de dades (per ex, CREATE, DROP, ALTER).

En el cas de la base de dades langtrainer, l'usuari final pot interactuar amb la base de dades a través d'una aplicació web per gestionar el diccionari d'anglès. Per exemple, el professor pot accedir a l'aplicatiu web muntat sobre la base de dades:

Aquest aplicatiu és per gestionar vocabularis d'idiomes, i l'usuari respon al perfil d'usuari final.

A continuació, tres exemples senzills d'aplicatius que interactuen amb la base de dades, que tenen com a missió volcar una llista del vocabulari personal. Els exemples proposats estan fets amb PHP i bash, però podríem haver utilitzat altres llenguatges de programació (C/C++, Python, JAVA,...).

script llista_vocabulari.php (script php, pàgina web)

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="es" lang="es">
<head>
<title>Vocabulari Langtrainer</title>
</head>
<body>
<h1>Vocabulari personal</h1>
<?php
$con=mysqli_connect("localhost","alumne","keiL2lai","langtrainer");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"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;");

echo "<ul>";
while($row = mysqli_fetch_array($result)) {
	if ($row['word'] == $word) { //afegim un nou significat
		echo "; " . $row['translation'];
	} else { //nova paraula
		echo "</li>";
		echo "<li>".$row['word'] . ": " . $row['translation'];
	}

	$word = $row['word'];
}
echo "</ul>";

mysqli_close($con);
?> 
</body>
</html>

script llista_vocabulari_cli.php (script php, línia de comandes)

<?php
echo "Vocabulari personal";

$con=mysqli_connect("localhost","alumne","keiL2lai","langtrainer");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"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;");

$word = "";

while($row = mysqli_fetch_array($result)) {
	if ($row['word'] == $word) { //afegim un nou significat
		echo "; " . $row['translation'];
	} else { //nova paraula
		echo "\n";
		echo $row['word'] . ": " . $row['translation'];
	}

	$word = $row['word'];
}

mysqli_close($con);
?> 

Execució:

$ php llista_vocabulari_cli.php
Vocabulari personal
deserve (to): mer�ixer, ser digne de
dismiss (to): acomiadar, destituir. Refusar
draw (to): dibuixar; Estirar (pull), treure, atraure
draw breath (to): agafar aire
gills: branquies, agalles
hump: pujol, puig
lump: terr�s, tros
shame: vergonya, ll�stima
snout: morro, hocico
spit: escupinada, saliva
weird: rar, extrany
wound: ferida; ferir (pp, ferit)

script llista_vocabulari.sh (script bash)

#!/bin/sh
#### Definim els paràmetres de la connexió a la bd
SQL_HOST="localhost"
SQL_USER="alumne"
SQL_PASSWORD="keiL2lai"
SQL_DATABASE="langtrainer"
#### Muntem els paràmetres de la connexió.
SQL_ARGS="-h $SQL_HOST -u $SQL_USER -p$SQL_PASSWORD -D $SQL_DATABASE -s -e"
#### Muntem la sentència SQL i la llencem:
echo "Llista de vocabulari:"
llista_vocabulari=$(mysql $SQL_ARGS "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;")
echo $llista_vocabulari

echo
echo

#formatem millor la sortida
llista_vocabulari=$(mysql $SQL_ARGS "SELECT CONCAT(word,': ',translation,'\n') 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;")
echo $llista_vocabulari

Execució:

$ ./llista_vocabulari.sh 
Llista de vocabulari:
deserve (to) merèixer, ser digne de dismiss (to) acomiadar, destituir. Refusar draw (to) dibuixar draw (to) Estirar (pull), treure, atraure draw breath (to) agafar aire gills branquies, agalles hump pujol, puig lump terròs, tros shame vergonya, llàstima snout morro, hocico spit escupinada, saliva weird rar, extrany wound ferida wound ferir (pp, ferit)


deserve (to): merèixer, ser digne de
 dismiss (to): acomiadar, destituir. Refusar
 draw (to): dibuixar
 draw (to): Estirar (pull), treure, atraure
 draw breath (to): agafar aire
 gills: branquies, agalles
 hump: pujol, puig
 lump: terròs, tros
 shame: vergonya, llàstima
 snout: morro, hocico
 spit: escupinada, saliva
 weird: rar, extrany
 wound: ferida
 wound: ferir (pp, ferit)

Capa lògica

En la capa lògica (pàgina 8 del pdf), obtenim el processador de consultes (query processor, tant DDL com DML), gestió de seguretat, optimitzador de consultes, motor d'execuó.

Gràcies al llenguatge SQL podem moure'ns com peix a l'aigua i realitzar tasques dins d'aquesta capa lògica. Podem realitzar selects, operacions DML (insert, update, delete), operacions DDL (create, drop, alter). Accedir a la gestió de seguretat (create user, grant).

És en l'àmbit d'aquesta capa lògica que nosaltres hem modelat la nostra base de dades amb unes taules relacionades entre elles, formades per camps. I també trobem vistes, index, usuaris, rols,...

La potència dels SGBDR és la separació de les 3 capes, i nosaltres ens sentim còmodes pensant en termes de taules i camps, sense haver de pensar l'arquitectura física que hi ha per sota. Recordem a mode d'execmple el script per crear la base de dades langtrainer:

/*
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 tinyint 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;

Capa física

En la capa física d'un RDBMS s'implementa l'arquitectura del sistema. En el fitxer principal de configuració veiem diverses variables i directives del MySQL relacionades amb el rendiment que podem esperar del servidor. Aquests paràmetres fan referència a tamanys de buffer de memòria, caché, connexions concurrents permeses. Normalment no s'haurà de tocar. Ara bé, si el nostre servidor s'extralimita en el seu ús normal (moltes connexions concurrents, molt tamany de les dades,...), haurem de tunejar aquestes directives en el fitxer de configuració.

/etc/mysql/my.cnf -> /etc/mysql/mysql.conf.d/mysqld.cnf

...
# * Fine Tuning
#
key_buffer              = 16M 
max_allowed_packet      = 16M 
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M 
query_cache_size        = 16M
...


Data files

Els datafiles emmagatzemen les dades d'usuari en la base de dades.

En el cas del MySQL hem de distingir entre dos motors (dues maneres d'emmagatzematge): InnoDB i NDB:

InnoDB és la manera habitual de treballar amb MySQL, és un motor d'emmagatzematge d'alt rendiment i prestacions. NDB fa referència al Cluster de MySQL, que és una versió del MySQL d'alta-disponibilitat i alta-redundància adaptat a un entorn de computació distribuïda.

El nostre servidor de MySQL té un motor InnoDB, i això implica entre d'altres coses que les bases de dades les podem localitzar en carpetes, i les taules i els index seran fitxers dins d'aquestes carpetes.

En el fitxer de configuració /etc/mysql/my.cnf (/etc/mysql/mysql.conf.d/mysqld.cnf) llegim:

# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

En el cas del MySQL InnoDB per saber on estan els datafiles:

En el fitxer de configuració /etc/mysql/my.cnf (/etc/mysql/mysql.conf.d/mysqld.cnf)

...
datadir         = /var/lib/mysql
...

o bé:

mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |

La informació del diccionari de dades la trobem a la base de dades information_schema.

Si fem una consulta sobre la taula FILES, veiem que està buida. Trobaríem informació aquí dins si treballéssim amb el motor NBD (cluster).

mysql> SELECT TABLE_NAME FROM information_schema.FILES;
Empty set (0.00 sec)

Per tant, no esperem trobar aquí informació dels datafiles en el cas InnoDB.

mysql> select * from information_schema.ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE             | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA   | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
...
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |

Diccionari de dades

Emmagatzema informació de les metadades sobre l'estructura de la base de dades. La informació del diccionari de dades està en la base de dades information_schema.

Per exemple, si volem llistar totes les dades del diccionari:

mysql> SELECT TABLE_NAME, TABLE_SCHEMA FROM information_schema.TABLES;

i si vull filtrar per la base de dades langtrainer:

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'langtrainer';

Ara vull obtenir informació sobre taules i columnes de la base de dades langtrainer:

SELECT `TABLE_NAME`, `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='langtrainer' ;
SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='langtrainer' 
    AND `TABLE_NAME`='language';

MySQL Data Dictionary Query Toolbox

Tenim una llista completa de consultes que podem fer per extreure informació de la base de dades. Per exemple, per llistar les taules creades més recentment:

select table_schema as database_name,
       table_name,
       create_time
from information_schema.tables
where create_time > adddate(current_date,INTERVAL -60 DAY)
      and table_schema not in('information_schema', 'mysql',
                              'performance_schema','sys')
      and table_type ='BASE TABLE'
      -- and table_schema = 'your database name' 
order by create_time desc,
         table_schema;

Index

Els index proporcionen un accés ràpid a les dades.

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.

Hem de tenir present que les PRIMARY KEY ja són index de per sí, doncs se suposa que són camps pels quals es fa habitualment cerques, i també són camps que serveixen per enllaçar amb altres taules a través de les claus forànees. Els camps definits com a UNIQUE també són index.

Ara bé, en qualsevol moment jo puc crear un index sobre un camp d'una taula. Això ho farem quan tinguem una taula molt llarga amb un camp que no sigui clau primària però sobre el qual fem cerques habituals i filtratges.

Per exemple, anem a crear un index sobre el camp arthropoda.FOTO.id_bug:

mysql> desc FOTO;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| id_foto        | mediumint(9) | NO   | PRI | NULL    |       |
| nom_fitxer     | varchar(50)  | NO   | UNI | NULL    |       |
| titol          | varchar(255) | YES  |     | NULL    |       |
| tamany         | int(11)      | YES  |     | NULL    |       |
| width          | smallint(6)  | YES  |     | NULL    |       |
| height         | smallint(6)  | YES  |     | NULL    |       |
| exif           | varchar(255) | YES  |     | NULL    |       |
| id_bug         | smallint(6)  | YES  | MUL | NULL    |       |
...
CREATE INDEX idx_id_bug ON FOTO (id_bug);

Ara, quan cerquem tots els fitxers on apareix el gènere Cryptocephalus, la cerca en teoria serà més ràpida:

mysql> select nom_fitxer from FOTO F, BUG B where F.id_bug=B.id_bug and B.genere = 'Cryptocephalus' order by B.id_bug, ordre;
+-------------------+
| nom_fitxer        |
+-------------------+
| 120727_104050.JPG |
| 120727_114004.JPG |
| 120727_104052.JPG |
| 120820_073818.JPG |
| 120820_073808.JPG |
| 120820_073718.JPG |
| 120803_165844.JPG |
| 120803_165754.JPG |
| 120803_165734.JPG |
| 120803_165732.JPG |
| 120803_165730.JPG |
| 120811_124110.JPG |
| 120811_124116.JPG |
| 120811_124058.JPG |

Per saber si estem fent un bon ús dels index i obtenir informació de com es realitzen les nostres queries existeix la comanda EXPLAIN:

mysql> SHOW INDEX FROM FOTO;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| FOTO  |          0 | PRIMARY    |            1 | id_foto     | A         |         969 |     NULL | NULL   |      | BTREE      |         |               |
| FOTO  |          0 | nom_fitxer |            1 | nom_fitxer  | A         |         969 |     NULL | NULL   |      | BTREE      |         |               |
| FOTO  |          1 | id_bug     |            1 | id_bug      | A         |         323 |     NULL | NULL   | YES  | BTREE      |         |               |
| FOTO  |          1 | id_map     |            1 | id_map      | A         |          24 |     NULL | NULL   | YES  | BTREE      |         |               |
| FOTO  |          1 | idx_id_bug |            1 | id_bug      | A         |         193 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+----------

Com veiem, no calia crear l'index idx_id_bug, doncs ja existia un index sobre aquest camp degut a que és una clau forànea (id_bug és clau primària en la taula BUG). La conclusió és que més val no preocupar-se especialment pels index, doncs les claus primàries, forànies i UNIQUES ja estan gestionats.

Dades estadístiques

Which store statistical information about the data in the database; it is used by the query processor to select efficient ways to execute a query.

La informació dels index que hem trobat abans també la podem trobar mirant la taula INFORMATION_SCHEMA.STATISTICS.

mysql> SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'FOTO' AND table_schema = 'arthropoda';
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT |
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+
| def           | arthropoda   | FOTO       |          0 | arthropoda   | PRIMARY    |            1 | id_foto     | A         |         856 |     NULL | NULL   |          | BTREE      |         |               |
| def           | arthropoda   | FOTO       |          0 | arthropoda   | nom_fitxer |            1 | nom_fitxer  | A         |         856 |     NULL | NULL   |          | BTREE      |         |               |
| def           | arthropoda   | FOTO       |          1 | arthropoda   | id_bug     |            1 | id_bug      | A         |         285 |     NULL | NULL   | YES      | BTREE      |         |               |
| def           | arthropoda   | FOTO       |          1 | arthropoda   | id_map     |            1 | id_map      | A         |          21 |     NULL | NULL   | YES      | BTREE      |         |               |
| def           | arthropoda   | FOTO       |          1 | arthropoda   | idx_id_bug |            1 | id_bug      | A         |         171 |     NULL | NULL   | YES      | BTREE 

Per tant, les dues sentències són equivalents:

SHOW INDEX FROM FOTO;
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'FOTO' AND table_schema = 'arthropoda';

I aquí l'important factor per determinar l'estratègia per fer les queries i optimitzar les cerques és la cardinality:

In SQL (Structured Query Language), the term cardinality refers to the uniqueness of data values contained in a particular
column (attribute) of a database table. The lower the cardinality, the more duplicated elements in a column. Thus, a column
with the lowest possible cardinality would have the same value for every row. SQL databases use cardinality to help
determine the optimal query plan for a given query.

Logs

used to keep track of executed queries such that the recovery manager can use the information to successfully recover the database in the case of a system crash.

En el MySQL, on estaran definits els logs? Doncs com és habitual, hem de veure el fitxer principal de configuració: /etc/mysql/my.cnf (/etc/mysql/mysql.conf.d/mysqld.cnf):

# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow_query_log                = 1
# slow_query_log_file   = /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id             = 1
# log_bin                       = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds    = 2592000
max_binlog_size   = 100M
# binlog_do_db          = include_database_name
# binlog_ignore_db      = include_database_name

Si ens fixem bé, les línies:

# general_log_file        = /var/log/mysql/query.log
# general_log             = 1

estan comentades. Les descomentem, reiniciem el mysql per tal de què tinguin efecte.

En el fitxer query.log quedarà registrada tota l'activitat que fem a la base de dades. Per exemple, un intent denegat de connexió:

$ mysql -u alumne -p
ERROR 1045 (28000): Access denied for user 'alumne'@'localhost' (using password: YES) (no vàlid)

o bé una select qualsevol:

mysql# select * from word;

Podem veure la informació en el fitxer /var/log/mysql/query.log.

Quan veiem com funciona ho tornem a deixar com estava. Sempre que volem monitoritzar la base de dades, podem descomentar-ho.

bin logs

La seva missió és aguantar tots els canvis que es produeixen entre backups, de manera que puguem restaurar la base de dades en cas d'incidència, encara que no tinguem un backup recent.

En l'actual versió, els logs s'aguanten durant un mes:

# binlog_expire_logs_seconds    = 2592000

2592000/60 = 43200 minuts
43200 / 60 = 720 hores
720 / 24 = 30 dies -> un mes

Threads del servidor. Consum de CPU i RAM

mysqladmin és un client per realitzar tasques administratives. Es pot utilitzar per mirar la configuració del servidor, per crear i esborrar bases de dades, i molt més. Moltes de les operacions que podem fer amb mysqladmin també les podem fer des de dins de la consola del MySQL.

Volem veure els threads (fils) actius del servidor. Es pot fer de dues maneres: amb mysqladmin o amb la comanda SHOW PROCESSLIST.

# mysqladmin -p processlist
Enter password: 
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 67 | root | localhost |    | Sleep   | 251  |       |                  |
| 78 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
mysql> show processlist;

Obtenim la mateixa informació. Si tenim vàries connexions obertes, obtenim varis fils.

si volem saber la CPU que consumeixen els processos a la nostra màquina: (ordenat per cpu, i eliminem els que consumeixen el 0%)

$ ps -e -o pcpu,cpu,nice,state,cputime,args --sort pcpu | sed '/^ 0.0 /d'

i concretament el mysql:

$ ps -e -o pcpu,cpu,nice,state,cputime,args | grep mysql

i si llistem per consum de RAM:

$ ps -e -orss=,args= | sort -b -k1,1n | pr -TW$COLUMNS

i concretament el mysql:

$ ps -e -orss=,args= | sort -b -k1,1n | pr -TW$COLUMNS | grep mysql
  816 grep --color=auto mysql
 2252 mysql -u root -p
41044 /usr/sbin/mysqld

veiem el que consumeix el servidor de MySQL, i el client que està connectat.

Cas pràctic: còpia d'una base de dades mysql a partir dels datafiles (NO FER)

(de moment no feu això, m'ho he de mirar, TBD)

En la segona pràctica vam estat treballant amb els codis postals de l'Estat espanyol.

Hi havia tres versions de la base de dades: txt, XML i mysql. Ara és el moment de poder utilitzar els fitxers de la versió per a mysql, i podrem fer una restauració (recovery) de la base de dades a partir dels fitxers. Si mirem la carpeta, veiem que hi ha tres tipus d'arxius.

.frm:- table definition
.MYD:-table data
.MYI:-table indices

El primer que farem és aturar el mysql:

$ sudo /etc/init.d/mysql stop

On estan els datafiles al mysql? A /etc/mysql hi ha el fitxer de configuració, my.cnf. Si l'editem, veurem en les primeres línies els directoris importants. Concretament:

...
datadir         = /var/lib/mysql
...

Si anem a aquest directori (com a root), veurem que en aquest directori hi ha una carpeta per cada base de dades. I dins de cada carpeta, hi ha com a mínim un arxiu .frm per cada definició de taula.

Per tant, crearem la carpeta cp/, i copiarem tots els nostres arxius a aquesta carpeta.

 
# mkdir cp
# chown mysql:mysql cp/
# cp /home/joan/M10_UF2_1415/PR2_XML_JSON/cp_mysql/* ./cp
# chown mysql:mysql -R cp/

Ja podem tornar a arrencar el mysql, i veiem com podem accedir a la base de dades dels codis postals:

$ sudo /etc/init.d/mysql start
$ mysql -u root -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
...
| cp                 |
...

mysql> use cp
Database changed

mysql> show tables;
+--------------+
| Tables_in_cp |
+--------------+
| t_calles     |
| t_municipios |
| t_provincias |
+--------------+

mysql> select * from t_provincias;
+---------+--------------+
| CodProv | Provincia    |
+---------+--------------+
| 01      | Alava        |
| 02      | Albacete     |
| 03      | Alicante     |
...

Per tant, aquesta és una manera senzilla i pràctica de fer còpies de seguretat de bases de dades mysql.

Ara ja podem trobar tots els carrers de Girona ciutat:

mysql> desc t_municipios;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| idMunicipio | int(11)      | NO   | PRI | NULL    | auto_increment |
| CodProv     | char(2)      | YES  |     | NULL    |                |
| CodPostal   | varchar(5)   | YES  |     | NULL    |                |
| Municipio   | varchar(150) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

mysql> desc t_calles;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| idCalle   | int(11)      | NO   | PRI | NULL    | auto_increment |
| CodPostal | varchar(5)   | YES  |     | NULL    |                |
| Calle     | varchar(150) | YES  |     | NULL    |                |
select Calle from t_municipios m, t_calles c where m.CodPostal=c.CodPostal and Municipio='Girona';

...
| RIERA DE CAN CAMARET |
| RIMAU |
| RISSEC, PARATGE |
| ROQUES ALTES |
...

Practica alguna select més.

Per entregar a la pràctica

1. Crea la base de dades 'municipis'. (recorda encoding utf8)

2. Importa la base de dades dades_municipis_mysql.sql. Comprova que no hi hagi errors en la importació. Recorda la sintaxi per controlar els possibles errors en el procés d'importació:

mysql -u root -p municipis -f < dades_municipis_mysql.sql > /home/pepito/control_errors.log 2>&1

3. Genera un informe amb totes les taules i camps d'aquesta base de dades (consulta el diccionari de dades)

solució:

mysql> use information_schema
mysql> SHOW TABLES; -> tinc la taula TABLES i la taula COLUMNS (que és la que m'interessa)
mysql> desc COLUMNS;

...
| TABLE_SCHEMA
| TABLE_NAME               | varchar(64)         | NO   |     |         |       |
| COLUMN_NAME              | varchar(64)         | NO   |     |         
...

select TABLE_NAME, COLUMN_NAME FROM COLUMNS WHERE TABLE_SCHEMA='municipis';

+------------+-------------+
| TABLE_NAME | COLUMN_NAME |
+------------+-------------+
| comunitats | id_com      |
| comunitats | comunitat   |
| comunitats | abr_com     |
| municipis  | id_prov     |
| municipis  | id_mun      |
| municipis  | municipi    |
| municipis  | superficie  |
| municipis  | habitants   |
| provincies | id_prov     |
| provincies | provincia   |
| provincies | id_com      |
+------------+-------------+


$ mysql -u root -p information_schema -s -e "select TABLE_NAME, COLUMN_NAME FROM COLUMNS WHERE TABLE_SCHEMA='municipis'" > informe_municipis.txt

Realitza les següents consultes:

4. Trobar tots els municipis d'Extremadura

Fer alguna cosa similar a (però amb la base de dades de municipis, no de cp):

$ mysql -h localhost -u root -p -s -e "select Calle from t_municipios m, t_calles c where m.CodPostal=c.CodPostal and Municipio='Girona'" cp > carrers_girona.txt

5. De la base de dades de codis postals, troba tots els carrers que pertanyen al codi postal 08025.

6. Trobar tots els municipis que el seu nom està repetit (per exemple, Mieres)

7. Fer còpies de seguretat i importacions:

Base de dades arthropoda (bitxos)

Importa la base de dades d' artròpodes. Llista totes les taules i columnes d'aquesta base de dades fent una consulta al diccionari de dades, igual que has fet amb municipis:

Realitza les següents consultes (només necessites les taules BUG, FOTO, MAP):

8. Troba quants bitxos diferents tenim del gènere Cryptocephalus (gènere i espècie)

9. Quantes fotos tenim del gènere Cryptocephalus? Quines són les fotos?

10. On s'han fet aquestes fotos (distinct)

11. Dirigeix a un fitxer de text la informació següent amb format CSV (a classe ho hem vist de dues maneres diferents)(pots utilitzar CONCAT):

foto;genere;especie;localitzacio

NOTA: el proper dia publicarem la solució

Entrega

En aquesta pràctica hauràs de reproduir totes les proves que s'han explicat a classe, i mirar d'entendre bé l'arquitectura del MySQL.

Recorda la normativa per entregar les pràctiques al Schoology: ASIX-M10-UF2#Normativa_d.27entrega_de_les_pr.C3.A0ctiques_al_Schoology

Durarda

4 hores



creat per Joan Quintana Compte, octubre 2018