PR0101 — Pràctica: MySQL amb Docker

Informació de la pràctica

Camp	Detall
Mòdul	M0377 — Administració de SGBD
Resultat d'aprenentatge	RA1 — Instal·lació i configuració d'un SGBD
Durada estimada	3 hores
Modalitat	Individual
Entrega	Document PDF amb captures de pantalla i respostes

---

Objectius

En finalitzar aquesta pràctica, l'alumne serà capaç de:

• Descarregar i executar la imatge oficial de MySQL 8.4 amb Docker.
• Connectar-se al servidor MySQL des del client de línia de comandes.
• Crear una base de dades amb codificació i col·lació correctes.
• Crear taules i inserir dades de prova.
• Realitzar una exportació amb mysqldump i restaurar-la en una nova base de dades.
• Consultar i interpretar el log d'errors de MySQL.
• Aplicar una configuració bàsica de seguretat post-instal·lació.

---

Requisits previs

• Docker Desktop (Windows/macOS) o Docker Engine (Linux) instal·lat i funcionant.
• Mínim 2 GB de RAM lliure per al contenidor.
• Connexió a Internet per descarregar la imatge (aprox. 600 MB).
• Client mysql instal·lat al sistema amfitrió (opcional, però recomanat).

Substitució del nom d'alumne

Al llarg d'aquesta pràctica, substituïu [nom-alumne] pel vostre nom i cognoms en format nom_cognom (sense espais, sense accents, tot en minúscules). Per exemple: joan_puig.

---

Pas 1 — Descarregar la imatge de MySQL 8.4

Obriu un terminal i executeu:

# Descarregar la imatge oficial de MySQL 8.4
docker pull mysql:8.4

# Verificar que la imatge s'ha descarregat
docker images | grep mysql

Anoteu: - La mida de la imatge descarregada. - Les capes (layers) mostrades durant el docker pull.

Inspecció de la imatge

Podeu obtenir informació detallada de la imatge amb:

docker inspect mysql:8.4

Observeu el camp Env per veure les variables d'entorn disponibles i ExposedPorts per confirmar el port exposat.

---

Pas 2 — Executar el contenidor MySQL

# Crear i iniciar el contenidor amb el vostre nom d'alumne
docker run -d \
  --name mysql-[nom-alumne] \
  -e MYSQL_ROOT_PASSWORD=RootPass_2024! \
  -e MYSQL_DATABASE=sgbd_[nom_alumne] \
  -e MYSQL_USER=alumne_[nom_alumne] \
  -e MYSQL_PASSWORD=AlumnePass_2024! \
  -p 3306:3306 \
  -v mysql_[nom_alumne]_data:/var/lib/mysql \
  mysql:8.4 \
  --character-set-server=utf8mb4 \
  --collation-server=utf8mb4_unicode_ci

# Exemple real (substituïu per les vostres dades):
# docker run -d \
#   --name mysql-joan-puig \
#   -e MYSQL_ROOT_PASSWORD=RootPass_2024! \
#   ...

Verificació:

# Comprovar que el contenidor és en marxa
docker ps

# Veure els logs d'inicialització (pot trigar 20-30 segons)
docker logs -f mysql-[nom-alumne]

# Esperar fins que apareixi el missatge:
# [Server] /usr/sbin/mysqld: ready for connections.
# ^C per sortir del mode follow

---

Pas 3 — Connectar amb el client MySQL

Des del contenidor (recomanat)Des del sistema amfitrió

# Connexió com a root
docker exec -it mysql-[nom-alumne] mysql -u root -pRootPass_2024!

# Connexió com a usuari alumne
docker exec -it mysql-[nom-alumne] mysql \
    -u alumne_[nom_alumne] \
    -pAlumnePass_2024! \
    sgbd_[nom_alumne]

# Requereix tenir mysql-client instal·lat
# Ubuntu: sudo apt install -y mysql-client

mysql -h 127.0.0.1 -P 3306 \
    -u alumne_[nom_alumne] \
    -pAlumnePass_2024! \
    sgbd_[nom_alumne]

Un cop connectat, verifiqueu la connexió:

-- Versió del servidor
SELECT VERSION();

-- Usuari actual
SELECT CURRENT_USER();

-- Hora del servidor
SELECT NOW();

-- Mostrar la base de dades seleccionada
SELECT DATABASE();

Captureu la sortida d'aquestes comandes.

---

Pas 4 — Crear la base de dades de l'alumne

Treballant com a root, confirmeu que la base de dades s'ha creat automàticament i configureu-la correctament:

-- Connexió com a root
-- docker exec -it mysql-[nom-alumne] mysql -u root -pRootPass_2024!

-- Verificar que existeix la base de dades
SHOW DATABASES;

-- Si no existís (o per crear-ne una addicional de proves):
CREATE DATABASE sgbd_[nom_alumne]
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Verificar la configuració de la base de dades
SELECT schema_name,
       default_character_set_name,
       default_collation_name
FROM information_schema.schemata
WHERE schema_name = 'sgbd_[nom_alumne]';

-- Verificar que l'usuari alumne té accés
SHOW GRANTS FOR 'alumne_[nom_alumne]'@'%';

---

Pas 5 — Configurar el joc de caràcters i la col·lació

-- Connecteu-vos com a root i comproveu la configuració global
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- Si el servidor no té utf8mb4 per defecte, configureu-lo
SET GLOBAL character_set_server = 'utf8mb4';
SET GLOBAL collation_server = 'utf8mb4_unicode_ci';

-- Seleccioneu la vostra base de dades
USE sgbd_[nom_alumne];

-- Verificar que la base de dades té la codificació correcta
SELECT @@character_set_database, @@collation_database;

-- Comprovar que els caràcters catalans es desen correctament
CREATE TABLE IF NOT EXISTS prova_charset (
    id INT AUTO_INCREMENT PRIMARY KEY,
    text_cat VARCHAR(200)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

INSERT INTO prova_charset (text_cat) VALUES
    ('Àngels, Còdols, Llàgrimes, Güell'),
    ('El català és la llengua d''ensenyament'),
    ('Ñoño, però no és català 😄');

SELECT * FROM prova_charset;
DROP TABLE prova_charset;

---

Pas 6 — Crear taules i inserir dades

Creeu un esquema de base de dades senzill per a una biblioteca:

USE sgbd_[nom_alumne];

-- Taula d'autors
CREATE TABLE autors (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    nom        VARCHAR(100)  NOT NULL,
    cognoms    VARCHAR(150)  NOT NULL,
    nacionalitat VARCHAR(50) DEFAULT 'Espanya',
    any_naixement YEAR,
    INDEX idx_cognoms (cognoms)
) ENGINE=InnoDB
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci
  COMMENT='Taula d autors de la biblioteca';

-- Taula de gèneres literaris
CREATE TABLE generes (
    id    TINYINT AUTO_INCREMENT PRIMARY KEY,
    nom   VARCHAR(80) NOT NULL UNIQUE
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Taula de llibres
CREATE TABLE llibres (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    titol       VARCHAR(250)   NOT NULL,
    autor_id    INT            NOT NULL,
    genere_id   TINYINT,
    any_publicacio YEAR,
    isbn        CHAR(13)       UNIQUE,
    disponible  TINYINT(1)     NOT NULL DEFAULT 1,
    preu        DECIMAL(8,2),
    sinopsi     TEXT,
    creat_a     TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
    actualitzat_a TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_autor  FOREIGN KEY (autor_id)  REFERENCES autors(id) ON DELETE RESTRICT,
    CONSTRAINT fk_genere FOREIGN KEY (genere_id) REFERENCES generes(id) ON DELETE SET NULL,
    INDEX idx_autor (autor_id),
    INDEX idx_genere (genere_id),
    INDEX idx_any (any_publicacio)
) ENGINE=InnoDB
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Verificar que les taules s'han creat
SHOW TABLES;
DESCRIBE autors;
DESCRIBE llibres;

-- Inserir dades de prova
INSERT INTO generes (nom) VALUES
    ('Novel·la'), ('Poesia'), ('Teatre'),
    ('Assaig'), ('Ciència ficció'), ('Thriller');

INSERT INTO autors (nom, cognoms, nacionalitat, any_naixement) VALUES
    ('Mercè', 'Rodoreda', 'Catalunya', 1908),
    ('Salvador', 'Espriu', 'Catalunya', 1913),
    ('Joanot', 'Martorell', 'País Valencià', 1413),
    ('Víctor', 'Català', 'Catalunya', 1869),
    ('Gabriel', 'García Márquez', 'Colòmbia', 1927),
    ('George', 'Orwell', 'Regne Unit', 1903);

INSERT INTO llibres (titol, autor_id, genere_id, any_publicacio, isbn, preu, sinopsi) VALUES
    ('La plaça del Diamant', 1, 1, 1962, '9788429753240', 12.50,
     'La vida de la Natàlia durant la guerra civil espanyola.'),
    ('Cementiri de Sinera', 2, 2, 1946, '9788429732023', 9.95,
     'Recull poètic inspirat en el poble natal d Espriu.'),
    ('Tirant lo Blanc', 3, 1, 1490, '9788484371946', 18.00,
     'Novel·la de cavalleria considerada la primera novel·la moderna.'),
    ('Solitud', 4, 1, 1905, '9788429760088', 11.00,
     'La Mila busca la pau en una ermita de muntanya.'),
    ('Cent anys de solitud', 5, 1, 1967, '9780307474728', 14.95,
     'La saga de la família Buendía a Macondo.'),
    ('1984', 6, 5, 1949, '9780451524935', 10.50,
     'Una distopia totalitària on el Gran Germà ho controla tot.');

-- Comprovar les dades inserides
SELECT l.titol, CONCAT(a.nom, ' ', a.cognoms) AS autor,
       g.nom AS genere, l.any_publicacio, l.preu
FROM llibres l
JOIN autors a ON l.autor_id = a.id
LEFT JOIN generes g ON l.genere_id = g.id
ORDER BY l.any_publicacio;

---

Pas 7 — Exportació i importació amb mysqldump

Exportació

# Exportar la base de dades completa (des del sistema amfitrió)
mysqldump -h 127.0.0.1 -P 3306 \
    -u root -pRootPass_2024! \
    --databases sgbd_[nom_alumne] \
    --add-drop-database \
    --routines \
    --triggers \
    --single-transaction \
    > /tmp/sgbd_[nom_alumne]_backup.sql

# O des del contenidor
docker exec mysql-[nom-alumne] mysqldump \
    -u root -pRootPass_2024! \
    --databases sgbd_[nom_alumne] \
    --add-drop-database \
    --routines \
    --triggers \
    --single-transaction \
    > /tmp/sgbd_[nom_alumne]_backup.sql

# Verificar el fitxer generat
ls -lh /tmp/sgbd_[nom_alumne]_backup.sql
head -50 /tmp/sgbd_[nom_alumne]_backup.sql

Opcions importants de mysqldump

• --single-transaction: Exporta InnoDB de forma consistent sense bloquejar les taules.
• --add-drop-database: Afegeix DROP DATABASE IF EXISTS al dump.
• --routines: Inclou procediments emmagatzemats i funcions.
• --triggers: Inclou triggers.
• --no-data: Exporta només l'estructura (DDL), sense dades.

Importació en una nova base de dades

-- Crear una base de dades de restauració
-- docker exec -it mysql-[nom-alumne] mysql -u root -pRootPass_2024!

CREATE DATABASE sgbd_[nom_alumne]_restore
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

EXIT;

# Restaurar des del fitxer SQL
docker exec -i mysql-[nom-alumne] mysql \
    -u root -pRootPass_2024! \
    < /tmp/sgbd_[nom_alumne]_backup.sql

# Verificar la restauració
docker exec -it mysql-[nom-alumne] mysql \
    -u root -pRootPass_2024! \
    -e "USE sgbd_[nom_alumne]; SHOW TABLES; SELECT COUNT(*) AS num_llibres FROM llibres;"

Exportació de taules individuals

# Exportar només la taula de llibres amb dades
docker exec mysql-[nom-alumne] mysqldump \
    -u root -pRootPass_2024! \
    sgbd_[nom_alumne] llibres \
    > /tmp/llibres.sql

# Exportar a format CSV
docker exec -it mysql-[nom-alumne] mysql \
    -u root -pRootPass_2024! \
    -e "SELECT * FROM sgbd_[nom_alumne].llibres" \
    --batch --silent \
    > /tmp/llibres.tsv

---

Pas 8 — Consultar el log d'errors

# Opció 1: Veure els logs del contenidor Docker
docker logs mysql-[nom-alumne] 2>&1 | head -100
docker logs mysql-[nom-alumne] 2>&1 | grep -i error
docker logs mysql-[nom-alumne] 2>&1 | grep -i warning

# Opció 2: Consultar el log d'errors des de MySQL
docker exec -it mysql-[nom-alumne] mysql -u root -pRootPass_2024!

-- Localitzar el fitxer de log d'errors
SHOW VARIABLES LIKE 'log_error';

-- Veure l'estat actual del servidor
SHOW STATUS LIKE 'Uptime%';
SHOW STATUS LIKE 'Connections%';
SHOW STATUS LIKE 'Threads%';

-- Veure el log d'errors des de SQL (MySQL 8.4+)
SELECT logged, prio, error_code, subsystem, data
FROM performance_schema.error_log
WHERE prio IN ('Error', 'Warning')
ORDER BY logged DESC
LIMIT 20;

-- Veure l'estat del motor InnoDB
SHOW ENGINE INNODB STATUS\G

Interpretació del log d'errors

Els nivells de gravetat al log d'errors de MySQL són: - [ERROR] — Error que ha impedit una operació. - [Warning] — Problema que no ha aturat l'execució però que cal revisar. - [Note] — Informació de diagnòstic (inici/aturada del servidor, etc.). - [System] — Esdeveniments del sistema (canvis de mode, inici de servei).

---

Verificació final

Executeu les comandes següents i captureu la sortida per a l'entrega:

USE sgbd_[nom_alumne];

-- Resum de l'esquema creat
SELECT
    table_name,
    table_rows,
    data_length,
    index_length,
    engine,
    table_collation
FROM information_schema.tables
WHERE table_schema = 'sgbd_[nom_alumne]'
ORDER BY table_name;

-- Comprovar les relacions (claus foranes)
SELECT
    kcu.constraint_name,
    kcu.table_name,
    kcu.column_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name
FROM information_schema.key_column_usage kcu
JOIN information_schema.referential_constraints rc
    ON kcu.constraint_name = rc.constraint_name
WHERE kcu.table_schema = 'sgbd_[nom_alumne]';

---

Preguntes de reflexió

Responeu les preguntes següents al document d'entrega. Les respostes han de ser raonades (mínim 3-5 línies per pregunta).

1. Quina diferència hi ha entre docker run amb l'opció -v (volum) i sense? Què passa amb les dades si eliminem el contenidor sense volum?
2. Per quina raó s'usa l'opció --single-transaction a mysqldump per a bases de dades InnoDB? Quina alternativa existia amb MyISAM?
3. La variable MYSQL_ROOT_PASSWORD es passa com a variable d'entorn al docker run. Quins riscos de seguretat pot tenir exposar contrasenyes d'aquesta manera? Com es podria millorar en un entorn de producció?
4. Quin és l'efecte de la restricció ON DELETE RESTRICT a la clau forana fk_autor? Proveu-la: intenteu eliminar un autor que té llibres i captureu l'error.
5. Compareu la mida del fitxer sgbd_[nom_alumne]_backup.sql generat amb mysqldump amb la mida que ocupa el volum Docker (docker system df -v). Per quina raó el fitxer SQL sol ser molt més petit?
6. Quin avantatge té usar un usuari dedicat (alumne_[nom_alumne]) en lloc de connectar-se sempre com a root?