Monitoreo y Troubleshooting de Replicación

Monitorear el Lag

-- Lag clásico
SHOW SLAVE STATUS\G
-- Revisar: Seconds_Behind_Master

-- Lag basado en heartbeat (más preciso)
-- En el primario:
CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = 1;

-- Vía performance_schema
SELECT
    channel_name,
    last_error_number,
    last_error_message,
    last_heartbeat_timestamp
FROM performance_schema.replication_connection_status;

Monitoreo de GTID

-- Verificar sets de GTID
SHOW MASTER STATUS;         -- executed_gtid_set
SHOW SLAVE STATUS\G         -- executed/retrieved_gtid_set

-- Encontrar transacciones faltantes
SELECT GTID_SUBTRACT(
    @@global.gtid_purged,
    (SELECT received_transaction_set
     FROM performance_schema.replication_connection_status)
) AS gtids_faltantes;

Resolver Errores Comunes

-- Error 1062: Clave duplicada — saltar de forma segura
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- Con GTID: inyectar transacción vacía
STOP SLAVE;
SET GTID_NEXT = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx:N';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;

-- Reconstruir réplica desde cero
STOP SLAVE;
RESET SLAVE ALL;
-- Tomar nuevo dump del primario con --master-data=2

Replicación Semi-Síncrona

-- Instalar en el primario
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10000;  -- fallback async 10s

-- Instalar en la réplica
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- Monitorear
SHOW STATUS LIKE 'rpl_semi_sync%';

Replicación con Retardo

-- Mantener réplica 1 hora por detrás
STOP SLAVE;
CHANGE MASTER TO MASTER_DELAY = 3600;
START SLAVE;

SHOW SLAVE STATUS\G
-- SQL_Delay: 3600
-- SQL_Remaining_Delay: 847

Replicación Paralela

[mysqld]
slave_parallel_type         = LOGICAL_CLOCK
slave_parallel_workers      = 8
slave_preserve_commit_order = 1

-- Monitorear trabajadores paralelos
SELECT THREAD_ID, NAME, PROCESSLIST_STATE
FROM performance_schema.threads
WHERE NAME LIKE 'thread/sql/slave_worker%';