Salta el contingut

PR05 — Pràctica: Replicació amb Docker Compose

Objectius

  • Configurar un entorn de replicació primari-rèplica de MySQL usant Docker Compose.
  • Crear un usuari de replicació i configurar el binlog al primari.
  • Configurar la rèplica amb CHANGE REPLICATION SOURCE TO i verificar la connexió.
  • Provar la replicació inserint dades al primari i verificant-les a la rèplica.
  • Monitorar el lag de replicació amb SHOW REPLICA STATUS.
  • Simular la fallada del primari i promoure la rèplica a primari.
  • (Opcional) Configurar una replicació streaming bàsica amb PostgreSQL.

Prerequisits

Requisit Versió mínima Verificació
Docker Engine 24.x o superior docker --version
Docker Compose Plugin v2 docker compose version
Pràctiques anteriors Completades Coneixement bàsic de MySQL
Temps estimat 5 hores

Noms de contenidors personalitzats

Al llarg d'aquesta pràctica, substitueix [nom-alumne] pel teu nom d'usuari (sense espais ni accents). Per exemple: mysql-primary-joan, mysql-replica-joan.

Escenari

Simularàs un entorn de producció senzill en el qual:

  • Un servidor MySQL actua com a primari: accepta totes les escriptures.
  • Un servidor MySQL actua com a rèplica: rep els canvis del primari via el binlog, és de lectura.

Les aplicacions de lectura intensiva (informes, dashboards) es connectarien a la rèplica per descarregar el primari.

graph LR
    APP_W["App d'escriptura\n(insercions, actualitzacions)"] --> PRI
    APP_R["App de lectura\n(informes, dashboard)"] --> REP
    PRI[("MySQL Primari\nmysql-primary-[nom]\n:3306")] -->|"Binlog\nreplicació"| REP[("MySQL Rèplica\nmysql-replica-[nom]\n:3307")]
    PRI -.->|"SHOW BINARY LOG STATUS"| ADM["Administrador"]
    REP -.->|"SHOW REPLICA STATUS"| ADM

    style PRI fill:#337ab7,color:#fff
    style REP fill:#5cb85c,color:#fff

Pas 1: Estructura de fitxers del projecte

Crea la següent estructura de directoris:

replicacio-mysql/
├── docker-compose.yml
├── primary/
│   ├── my.cnf
│   └── init/
│       └── 01_create_replication_user.sql
└── replica/
    └── my.cnf

mkdir -p replicacio-mysql/primary/init replicacio-mysql/replica
cd replicacio-mysql

Pas 2: Configuració del primari

Fitxer de configuració MySQL per al primari

Crea primary/my.cnf:

[mysqld]
# ─── Identificació del servidor ──────────────────────────────────────
server-id               = 1
# El server-id ha de ser únic al clúster de replicació (1 = primari)

# ─── Binary Log (necessari per a la replicació) ──────────────────────
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_format           = ROW
# ROW: replica les files modificades (deterministme, recomanat)
# STATEMENT: replica les sentències SQL (pot ser no determinista)
# MIXED: combina els dos mètodes

binlog_expire_logs_seconds = 604800  # Conserva binlogs 7 dies (604800s)
max_binlog_size         = 100M
binlog_row_image        = FULL       # FULL: replica tots els camps (segur)

# ─── Identificació de la base de dades a replicar ───────────────────
# binlog_do_db          = nom_bd     # Si vols replicar UNA BD específica
# binlog_ignore_db      = mysql      # BD a excloure de la replicació

# ─── Rendiment i seguretat ──────────────────────────────────────────
sync_binlog             = 1          # Escriu el binlog a disc en cada COMMIT (segur, lent)
innodb_flush_log_at_trx_commit = 1   # ACID complet (segur, lent)

# ─── Connexions ──────────────────────────────────────────────────────
max_connections         = 100

# ─── GTID (Global Transaction IDs) ──────────────────────────────────
# gtid_mode             = ON        # Activa GTIDs (recomanat per a producció)
# enforce_gtid_consistency = ON

Script d'inicialització (usuari de replicació)

Crea primary/init/01_create_replication_user.sql:

-- Crear la base de dades de prova
CREATE DATABASE IF NOT EXISTS botiga_online
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE botiga_online;

-- Crear taules de prova
CREATE TABLE IF NOT EXISTS productes (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    nom         VARCHAR(150) NOT NULL,
    preu        DECIMAL(10,2) NOT NULL,
    estoc       INT DEFAULT 0,
    creat_a     DATETIME DEFAULT CURRENT_TIMESTAMP,
    actualitzat DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS comandes (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    producte_id INT NOT NULL,
    quantitat   INT NOT NULL,
    total       DECIMAL(10,2) NOT NULL,
    creat_a     DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (producte_id) REFERENCES productes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Inserir dades inicials
INSERT INTO productes (nom, preu, estoc) VALUES
    ('Portàtil 15"', 799.99, 25),
    ('Ratolí sense fil', 29.99, 150),
    ('Teclat mecànic', 89.99, 75),
    ('Monitor 27" 4K', 449.99, 30),
    ('Auriculars Bluetooth', 59.99, 200);

-- ─────────────────────────────────────────────────────────────────────
-- Crear l'usuari de replicació
-- Aquest usuari serà usat per la rèplica per connectar-se al primari
-- ─────────────────────────────────────────────────────────────────────
CREATE USER IF NOT EXISTS 'replica_user'@'%'
    IDENTIFIED WITH mysql_native_password BY 'ReplicaPassword2024!';

-- Concedir el privilegi de replicació
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'replica_user'@'%';

FLUSH PRIVILEGES;

-- Verificar que s'ha creat correctament
SELECT user, host, plugin FROM mysql.user WHERE user = 'replica_user';

Pas 3: Configuració de la rèplica

Crea replica/my.cnf:

[mysqld]
# ─── Identificació del servidor ──────────────────────────────────────
server-id               = 2
# server-id ha de ser diferent del primari (2 = rèplica)

# ─── Mode de lectura ─────────────────────────────────────────────────
read_only               = ON
# Impedeix escriptures a usuaris no replicació/superadmin
# super_read_only       = ON  # Impedeix escriptures fins i tot a SUPER

# ─── Relay Log ───────────────────────────────────────────────────────
relay_log               = /var/log/mysql/mysql-relay-bin.log
relay_log_purge         = ON      # Elimina els relay logs ja aplicats

# ─── Binary Log a la rèplica (si volem cascaded replication) ─────────
# log_bin               = /var/log/mysql/mysql-bin.log
# log_slave_updates     = ON

# ─── Rendiment ───────────────────────────────────────────────────────
max_connections         = 50

Pas 4: Docker Compose

Crea docker-compose.yml a l'arrel del projecte:

version: "3.9"

# ─── Xarxa interna ──────────────────────────────────────────────────
networks:
  replicacio_net:
    driver: bridge
    ipam:
      config:
        - subnet: 172.28.0.0/24

# ─── Volums persistents ─────────────────────────────────────────────
volumes:
  mysql_primary_data:
  mysql_replica_data:

# ─── Serveis ─────────────────────────────────────────────────────────
services:

  # ── MySQL Primari ──────────────────────────────────────────────────
  mysql-primary-[nom-alumne]:
    image: mysql:8.4
    container_name: mysql-primary-[nom-alumne]
    hostname: mysql-primary
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: "RootPassword2024!"
      MYSQL_DATABASE: botiga_online
    volumes:
      - mysql_primary_data:/var/lib/mysql
      - ./primary/my.cnf:/etc/mysql/conf.d/replication.cnf:ro
      - ./primary/init:/docker-entrypoint-initdb.d:ro
    networks:
      replicacio_net:
        ipv4_address: 172.28.0.10
    ports:
      - "3306:3306"     # Accessible des de l'host per a connexions directes
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-uroot", "-pRootPassword2024!"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s

  # ── MySQL Rèplica ──────────────────────────────────────────────────
  mysql-replica-[nom-alumne]:
    image: mysql:8.4
    container_name: mysql-replica-[nom-alumne]
    hostname: mysql-replica
    restart: unless-stopped
    depends_on:
      mysql-primary-[nom-alumne]:
        condition: service_healthy
    environment:
      MYSQL_ROOT_PASSWORD: "RootPassword2024!"
    volumes:
      - mysql_replica_data:/var/lib/mysql
      - ./replica/my.cnf:/etc/mysql/conf.d/replication.cnf:ro
    networks:
      replicacio_net:
        ipv4_address: 172.28.0.11
    ports:
      - "3307:3306"     # Accessible des de l'host en el port 3307
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-uroot", "-pRootPassword2024!"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s

Substitueix [nom-alumne]

Recorda substituir tots els [nom-alumne] al fitxer docker-compose.yml pel teu nom d'usuari real. El nom ha de ser vàlid com a nom de contenidor Docker (només lletres, números i guions).

Pas 5: Iniciar els contenidors

# Arrencar els serveis
docker compose up -d

# Verificar que estan en funcionament
docker compose ps

# Veure els logs del primari (espera que mostri "ready for connections")
docker compose logs mysql-primary-[nom-alumne] -f

# En una nova terminal, veure els logs de la rèplica
docker compose logs mysql-replica-[nom-alumne] -f

Pas 6: Obtenir la posició del binlog al primari

# Connectar-se al primari
docker exec -it mysql-primary-[nom-alumne] \
    mysql -uroot -pRootPassword2024!

-- Al primari: obtenir la posició actual del binlog
-- Aquesta informació necessitem per configurar la rèplica
SHOW BINARY LOG STATUS;

Sortida esperada:

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      897 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

Anota els valors

Anota el valor de File i Position. Els necessitaràs al pas 7. En el teu cas seran diferent; no copies els de l'exemple.

-- Verificar que l'usuari de replicació existeix
SELECT user, host FROM mysql.user WHERE user = 'replica_user';

-- Verificar el binlog
SHOW BINARY LOGS;
EXIT;

Pas 7: Configurar la replicació a la rèplica

# Connectar-se a la rèplica
docker exec -it mysql-replica-[nom-alumne] \
    mysql -uroot -pRootPassword2024!

-- Verificar que la rèplica no té cap replicació activa
SHOW REPLICA STATUS\G
-- Ha de retornar buit (o "Empty set")

-- Configurar la connexió al primari
-- Substitueix mysql-bin.000003 i 897 pels valors del teu pas 6!
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST     = '172.28.0.10',
    SOURCE_PORT     = 3306,
    SOURCE_USER     = 'replica_user',
    SOURCE_PASSWORD = 'ReplicaPassword2024!',
    SOURCE_LOG_FILE = 'mysql-bin.000003',     -- valor del teu SHOW BINARY LOG STATUS
    SOURCE_LOG_POS  = 897,                    -- valor del teu SHOW BINARY LOG STATUS
    GET_SOURCE_PUBLIC_KEY = 1;               -- necessari per a autenticació RSA

-- Iniciar la replicació
START REPLICA;

-- Verificar l'estat de la replicació
SHOW REPLICA STATUS\G

Camps clau a verificar:

Replica_IO_Running: Yes        ← connexió IO al primari: OK
Replica_SQL_Running: Yes       ← aplicació de canvis SQL: OK
Seconds_Behind_Source: 0       ← lag en segons (0 = sincronitzat)
Last_Error:                    ← ha d'estar buit
Last_IO_Error:                 ← ha d'estar buit
Last_SQL_Error:                ← ha d'estar buit

Si Replica_IO_Running és NO

Comprova Last_IO_Error per veure l'error. Causes comunes: - Contrasenya incorrecta: verifica SOURCE_PASSWORD. - IP incorrecta: verifica SOURCE_HOST (usa la IP de la xarxa de Docker, no localhost). - Usuari de replicació no té privilegis: executa GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%'; al primari.

Pas 8: Provar la replicació

Inserir dades al primari

# Al primari
docker exec -it mysql-primary-[nom-alumne] \
    mysql -uroot -pRootPassword2024! botiga_online

-- Al primari: inserir noves comandes
INSERT INTO comandes (producte_id, quantitat, total) VALUES
    (1, 2, 1599.98),
    (3, 1, 89.99),
    (5, 3, 179.97);

-- Actualitzar l'estoc
UPDATE productes SET estoc = estoc - 2 WHERE id = 1;
UPDATE productes SET estoc = estoc - 1 WHERE id = 3;
UPDATE productes SET estoc = estoc - 3 WHERE id = 5;

-- Veure l'estat actual
SELECT * FROM productes;
SELECT * FROM comandes;
EXIT;

Verificar les dades a la rèplica

# A la rèplica
docker exec -it mysql-replica-[nom-alumne] \
    mysql -uroot -pRootPassword2024! botiga_online

-- A la rèplica: verificar que les dades han arribat
SELECT * FROM productes;   -- Ha de mostrar els mateixos productes amb estoc actualitzat
SELECT * FROM comandes;    -- Ha de mostrar les 3 comandes inserides al primari

-- Monitorar el lag de replicació
SHOW REPLICA STATUS\G
-- Seconds_Behind_Source hauria de ser 0 o molt proper

-- Verificar que la rèplica és de lectura (ha de retornar error)
INSERT INTO productes (nom, preu, estoc) VALUES ('Test', 1.00, 1);
-- ERROR 1290 (HY000): The MySQL server is running with the --read-only option
-- so it cannot execute this statement  ← CORRECTE!
EXIT;

Pas 9: Monitorar la replicació contínuament

-- A la rèplica: monitorar en temps real
-- Executa cada 5 segons (Ctrl+C per aturar)
SHOW REPLICA STATUS\G

-- Estadístiques de Performance Schema
SELECT * FROM performance_schema.replication_connection_status\G
SELECT * FROM performance_schema.replication_applier_status_by_worker\G

-- Generar activitat al primari per veure el lag en acció
-- (executa-ho al primari en una sessió diferent)
-- INSERT INTO comandes (producte_id, quantitat, total)
-- SELECT (rand()*5)::INT+1, 1, 50.00 FROM information_schema.columns LIMIT 1000;

Pas 10: Simular la fallada del primari i promoure la rèplica

Operació destructiva

Aquesta secció simula una fallada real del primari. Segueix els passos amb cura.

10.1: Aturar el primari

# Atura el contenidor del primari (simulació de fallada)
docker stop mysql-primary-[nom-alumne]

# Verificar que ha aturat
docker compose ps

10.2: Verificar l'estat de la rèplica

docker exec -it mysql-replica-[nom-alumne] \
    mysql -uroot -pRootPassword2024!

-- La rèplica detectarà que el primari no respon
SHOW REPLICA STATUS\G
-- Replica_IO_Running: Connecting (o No) ← intenta reconnectar
-- Last_IO_Error: Can't connect to MySQL server on '172.28.0.10' (error de connexió)
-- Seconds_Behind_Source: NULL ← no pot calcular el lag sense connexió

10.3: Promoure la rèplica a primari

-- Aturar la replicació
STOP REPLICA;

-- Esborrar la configuració de replicació (desconnectar del primari)
RESET REPLICA ALL;

-- Desactivar el mode de lectura per acceptar escriptures
SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0;

-- Verificar que ara accepta escriptures
INSERT INTO productes (nom, preu, estoc) VALUES ('Nou producte post-failover', 99.99, 50);
SELECT * FROM productes ORDER BY id DESC LIMIT 5;
EXIT;

10.4: Recuperar el primari original i reconverteir-lo en rèplica

# Tornar a arrencar l'antic primari
docker start mysql-primary-[nom-alumne]

# Espera que arrenqui completament
docker compose logs mysql-primary-[nom-alumne] -f

# Connectar a l'antic primari
docker exec -it mysql-primary-[nom-alumne] \
    mysql -uroot -pRootPassword2024!

-- Obtenir la posició actual del NOU primari (l'antiga rèplica)
-- (executa al nou primari: mysql-replica-[nom-alumne])
-- SHOW BINARY LOG STATUS;

-- Configurar l'antic primari com a rèplica del nou primari
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST     = '172.28.0.11',   -- IP de la nova rèplica (ara primari)
    SOURCE_PORT     = 3306,
    SOURCE_USER     = 'replica_user',
    SOURCE_PASSWORD = 'ReplicaPassword2024!',
    SOURCE_LOG_FILE = 'mysql-bin.000001',   -- del SHOW BINARY LOG STATUS del nou primari
    SOURCE_LOG_POS  = 4,                    -- del SHOW BINARY LOG STATUS del nou primari
    GET_SOURCE_PUBLIC_KEY = 1;

START REPLICA;
SHOW REPLICA STATUS\G
EXIT;

En producció: usa GTIDs per simplificar el failover

Amb GTIDs activats (gtid_mode = ON), no cal especificar SOURCE_LOG_FILE ni SOURCE_LOG_POS. La comanda seria simplement: 

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '...',
    SOURCE_USER = '...',
    SOURCE_PASSWORD = '...',
    SOURCE_AUTO_POSITION = 1;  -- Usa GTIDs automàticament

Annex: Replicació Streaming PostgreSQL (resum)

Tot i que la pràctica principal usa MySQL, aquí tens un resum de com seria la configuració equivalent en PostgreSQL.

docker-compose.yml per a PostgreSQL

services:
  pg-primary-[nom-alumne]:
    image: postgres:17
    container_name: pg-primary-[nom-alumne]
    environment:
      POSTGRES_PASSWORD: secret
    volumes:
      - pg_primary_data:/var/lib/postgresql/data
    command: >
      postgres
      -c wal_level=replica
      -c max_wal_senders=3
      -c wal_keep_size=64
      -c hot_standby=on
    networks:
      - pg_net

  pg-replica-[nom-alumne]:
    image: postgres:17
    container_name: pg-replica-[nom-alumne]
    depends_on:
      - pg-primary-[nom-alumne]
    environment:
      POSTGRES_PASSWORD: secret
      PGDATA: /var/lib/postgresql/data
    volumes:
      - pg_replica_data:/var/lib/postgresql/data
    networks:
      - pg_net

Configuració de la rèplica PostgreSQL

# A la rèplica: crear la còpia base des del primari
docker exec pg-replica-[nom-alumne] bash -c "
    rm -rf /var/lib/postgresql/data/*
    PGPASSWORD=secret pg_basebackup \
        -h pg-primary-[nom-alumne] \
        -U postgres \
        -D /var/lib/postgresql/data \
        -P -Xs --checkpoint=fast
    touch /var/lib/postgresql/data/standby.signal
    echo \"primary_conninfo = 'host=pg-primary-[nom-alumne] port=5432 user=postgres password=secret'\" \
        >> /var/lib/postgresql/data/postgresql.conf
"

-- Al primari: verificar que la rèplica es connecta
SELECT client_addr, state, sync_state, replay_lag
FROM pg_stat_replication;

-- A la rèplica: verificar que és en mode standby
SELECT pg_is_in_recovery();  -- retorna: t (true)
SELECT now() - pg_last_xact_replay_timestamp() AS lag;

Preguntes de reflexió

Preguntes per al lliurament

Respon aquestes preguntes al teu informe de pràctica (mínim 3-5 línies per resposta):

  1. Binlog vs WAL: Quina diferència conceptual hi ha entre el binlog de MySQL i el WAL (Write-Ahead Log) de PostgreSQL? Tots dos serveixen per a la replicació, però amb diferències importants.

  2. Row-based vs Statement-based: Si executes UPDATE productes SET preu = preu * RAND(), quin format de binlog és correcte usar i per què? Quines dades guardaria cada format?

  3. Split-brain: En el nostre escenari de failover manual (pas 10), si el primari original no hagués caigut realment (sinó que s'havia perdut la connexió de xarxa entre el teu host i el contenidor), i haguessis promogut la rèplica, quins problemes podrien sorgir? Com s'evita el split-brain en producció?

  4. RPO i RTO de la nostra configuració: Amb la configuració de la pràctica (binlog asíncron, failover manual), quins son aproximadament el RPO i el RTO? Com milloraries cadascun d'ells?

  5. read_only: Per a quins casos un usuari podria necessitar escriure a la rèplica tot i tenir read_only = ON? Com s'evita completament? Quin és el paper de super_read_only?

  6. Escalabilitat: Amb la configuració actual, pots afegir una tercera rèplica al clúster sense aturar ni el primari ni la primera rèplica? Explica el procés.

  7. Monitoratge: Quins indicadors (mètriques) usaries en producció per detectar que la replicació s'ha aturat o que el lag supera un llindar acceptable? Quines eines externes configuraries?