Alta Disponibilidad en MySQL / ProxySQL

1) Arquitectura del laboratorio (lo que vas a montar)

3 VMs en VMware Workstation:
- db-master — MySQL (escrituras)
- db-replica — MySQL (réplica; lecturas)
- balancer — HAProxy que expondrá dos puertos:
  - puerto 3306 -> encaminado a master (escritura)
  - puerto 3307 -> encaminado a replica (lectura)
Red: usar la misma red virtual de VMware (Host-only o NAT). En el ejemplo usaré IPs estáticas privadas:
- db-master: 192.168.56.101
- db-replica: 192.168.56.102
- balancer: 192.168.56.103

Recomendación: en VMware crear las 3 VMs clonando la misma instalación de Ubuntu Server 22.04 LTS para facilitar.

2) Preparar la red (ejemplo con IPs estáticas)

En cada VM edita netplan (ajusta el nombre de la interfaz con ip a si no es ens33):

# /etc/netplan/01-netcfg.yaml
network:
  version: 2
  renderer: networkd
  ethernets:
    ens33:
      addresses: [192.168.56.101/24]   # cambia por .101/.102/.103 en cada VM
      gateway4: 192.168.100.1          # opcional; usa la que tenga tu red virtual
      nameservers:
        addresses: [8.8.8.8,8.8.4.4]

Luego aplicar:

sudo netplan apply

3) Actualizar y preparar cada VM

En las 3 máquinas:

sudo apt update &amp;&amp; sudo apt upgrade -y
sudo apt install -y vim curl wget openssh-server
sudo systemctl enable --now ssh

4) Instalar y configurar MySQL en db-master (192.168.56.101)

Instalar MySQL:

sudo apt install -y mysql-server

2. Editar ajustes importantes (crear archivo /etc/mysql/conf.d/my_custom.cnf para no tocar mucho lo existente). En el master:

# /etc/mysql/conf.d/my_custom.cnf
[mysqld]
bind-address = 0.0.0.0
server-id = 1
report_host=192.168.100.101
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

3. Reinicia MySQL:

sudo systemctl restart mysql

4. Asegurar instalación / password root (opcional):

sudo mysql_secure_installation

5. Crear usuario para replicación y una base de prueba:

# crear usuario de replicación y BD de prueba
sudo mysql -u root &lt;&lt;SQL
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_pass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
INSERT INTO customers (name) VALUES ('Alice'),('Bob');
FLUSH TABLES WITH READ LOCK;  -- bloquea mientras haces dump
SQL

6. Hacer un volcado consistente (con GTID):

sudo mysqldump --all-databases --single-transaction --routines --events --set-gtid-purged=ON -u root -p > /tmp/fulldump.sql
# te pedirá la contraseña de root si la configuraste; si usas auth_socket usa sudo mysqldump ...

7. Desbloquear tablas (en MySQL client si las dejaste bloqueadas):

sudo mysql -u root -e "UNLOCK TABLES;"

Ahora tienes /tmp/fulldump.sql listo para copiar a la réplica.

En MySQL necesitarías replicación multi-maestro replicación bidireccional:

CHANGE MASTER TO 
MASTER_HOST='192.168.100.102',  -- IP de slave 1
MASTER_USER='repl',
MASTER_PASSWORD='tu_password',  -- Asegúrate de usar la contraseña correcta
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
-- En la réplica, verifica si está recibiendo datos:
SHOW SLAVE STATUS\G

— Busca estas columnas:
— Slave_IO_Running: Yes
— Slave_SQL_Running: Yes
— Seconds_Behind_Master: 0

5) Instalar MySQL en db-replica (192.168.56.102) y configurarlo

1. Instalar y configurar:

sudo apt install -y mysql-server

2. Configurar /etc/mysql/conf.d/my_custom.cnf en replica con server-id = 2 y variables GTID:

[mysqld]
bind-address = 0.0.0.0
server-id = 2
report_host=192.168.100.102
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
read_only = 1

replicate-ignore-db=mysql

3. Reiniciar MySQL:

sudo systemctl restart mysql

4. Copiar el dump desde el master (en el master hacer):

scp /tmp/srv1_dump.sql usuario@192.168.100.102:/tmp/

(o usar carpeta compartida/drag&drop si estás en la misma máquina host)

Importar en la réplica:

sudo mysql -u root -p &lt; /tmp/fulldump.sql

6. Conectar la réplica al master usando GTID auto position:

sudo mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST='192.168.56.101', MASTER_USER='repl', MASTER_PASSWORD='repl_pass', MASTER_AUTO_POSITION = 1;"
sudo mysql -u root -p -e "START SLAVE;"

7. Verificar replicación:

sudo mysql -u root -p -e "SHOW SLAVE STATUS\G"
# Verifica: Slave_IO_Running: Yes  y Slave_SQL_Running: Yes

Si ambos son Yes y Seconds_Behind_Master = 0 (o pequeño) → replicación OK.

6) (Opcional pero recomendable) activar semisíncrono en el laboratorio

Semisíncrono reduce riesgo de pérdida de datos: el master esperará la confirmación de al menos un slave.

En el master:

sudo mysql -u root -p -e "INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; SET GLOBAL rpl_semi_sync_master_enabled = 1;"

En la réplica:

sudo mysql -u root -p -e "INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; SET GLOBAL rpl_semi_sync_slave_enabled = 1;"

(En producción pones estas opciones en el archivo de configuración para que se apliquen al reinicio).

7) Instalar HAProxy en la VM balancer (192.168.56.103)

1 Configuración Básica del Balanceador

# Actualizar sistema
sudo apt update &amp;&amp; sudo apt upgrade -y

# Instalar herramientas básicas
sudo apt install -y curl wget net-tools tcpdump

2 Instalar HAProxy

# En el balanceador (192.168.100.103)
sudo apt install -y haproxy

# Verificar instalación
haproxy -v

3 Configurar HAProxy para MySQL

  GNU nano 6.2                                                                   /etc/haproxy/haproxy.cfg                                                                            
global
    daemon
    maxconn 4000
    log /dev/log local0

defaults
    mode tcp
    timeout connect 5s
    timeout client 50s
    timeout server 50s
    option tcplog

# Panel de estadísticas
listen stats
    bind 0.0.0.0:8404
    mode http
    stats enable
    stats uri /
    stats refresh 5s
    stats auth admin:Pa$$w0rd

# Balanceador MySQL
listen mysql-balancer
    bind 0.0.0.0:3306
    mode tcp
    balance first
    option tcp-check
    tcp-check connect port 3306

    # Servidores con check básico
    server db-master 192.168.100.101:3306 check inter 2s fall 2 rise 2
    server db-replica1 192.168.100.102:3306 check inter 2s fall 2 rise 2 backup

4 Habilitar y Probar HAProxy

# Habilitar y iniciar HAProxy
sudo systemctl enable haproxy
sudo systemctl start haproxy

# Verificar estado
sudo systemctl status haproxy

# Ver logs en tiempo real (en otra terminal)
sudo tail -f /var/log/haproxy.log

8) Preparar los servidores MYSQL

1 Crear Usuario de Monitoreo en AMBOS MySQL

# En CADA servidor MySQL (101 y 102) ejecutar:
mysql -u root -p

# Ejecutar estos comandos dentro de MySQL:
CREATE USER IF NOT EXISTS 'haproxy_check'@'192.168.100.103' IDENTIFIED BY '';
CREATE USER IF NOT EXISTS 'haproxy_check'@'localhost' IDENTIFIED BY '';
CREATE USER IF NOT EXISTS 'app_user'@'192.168.100.103' IDENTIFIED BY 'AppPassword123!';
CREATE USER IF NOT EXISTS 'app_user'@'localhost' IDENTIFIED BY 'AppPassword123!';

GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'192.168.100.103';
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

2 Verificar que MySQL Acepte Conexiones Externas

# En CADA servidor MySQL, verificar:
sudo grep bind-address /etc/mysql/mysql.conf.d/mysqld.cnf

# Si no es 0.0.0.0, cambiarlo:
sudo sed -i 's/^bind-address.*/bind-address = 0.0.0.0/' /etc/mysql/mysql.conf.d/mysqld.cnf
sudo systemctl restart mysql

9) Probar la configuración

1 Probar Conexiones desde el Balanceador

# Desde el balanceador (103), probar conexiones directas:
telnet 192.168.100.101 3306
telnet 192.168.100.102 3306

# Probar usuarios MySQL
mysql -h 192.168.100.101 -u app_user -pAppPassword123! -e "SELECT @@hostname;"
mysql -h 192.168.100.102 -u app_user -pAppPassword123! -e "SELECT @@hostname;"

2 Probar HAProxy

# Probar cada puerto del balanceador:

# Solo escrituras (maestro)
mysql -h 127.0.0.1 -P 3306 -u app_user -pAppPassword123! -e "SELECT @@hostname as 'Escrituras';"

# Solo lecturas (esclavo)  
mysql -h 127.0.0.1 -P 3307 -u app_user -pAppPassword123! -e "SELECT @@hostname as 'Lecturas';"

# Balanceo automático
mysql -h 127.0.0.1 -P 3308 -u app_user -pAppPassword123! -e "SELECT @@hostname as 'Auto-Balanceo';"

3 Ver Panel de Monitoreo

# Abrir en navegador web:
echo "Panel de monitoreo: http://192.168.100.103:8404/monitor

Accede a: http://192.168.100.103:8404
Usuario: admin
Password: AdminPassword123!

lineas importantes

Verificar firewall

# En el maestro
sudo ufw status
sudo ufw allow 3306
# o en CentOS/RHEL
sudo firewall-cmd --list-all
sudo firewall-cmd --add-port=3306/tcp --permanent

Reinicia servicios después de cambios:

# En el maestro
sudo systemctl restart mysql

# En la réplica, reinicia la replicación
STOP SLAVE;
START SLAVE;
#Verifica nuevamente:
SHOW SLAVE STATUS\G

Verificar Estado de HAProxy

# Verificar si HAProxy está corriendo
sudo systemctl status haproxy

# Si no está activo, iniciarlo
sudo systemctl start haproxy
sudo systemctl enable haproxy

# Ver logs de error
sudo journalctl -u haproxy -f

# Verificar que HAProxy está escuchando en los puertos
sudo netstat -tlnp | grep haproxy

# Verificar específicamente puerto 3306 y 8404
sudo ss -tlnp | grep ':3306\|:8404'

Verificar Configuración HAProxy

# Verificar sintaxis del archivo de configuración
sudo haproxy -c -f /etc/haproxy/haproxy.cfg

# Ver contenido actual del archivo
sudo cat /etc/haproxy/haproxy.cfg

VERIFICAR QUE HAPROXY ESTÉ EJECUTÁNDOSE

# Verificar procesos
ps aux | grep haproxy

# Verificar puertos abiertos
sudo netstat -tlnp | grep haproxy

# Verificar que escucha en los puertos
sudo ss -tlnp | grep haproxy

VERIFICAR CONEXIÓN DIRECTA A MYSQL

# Desde el balanceador (103), probar conexión directa a MySQL
echo "=== PROBANDO CONEXIÓN DIRECTA A MYSQL ==="

# Probar conexión al maestro
mysql -h 192.168.100.101 -u app_user -pAppPassword123! -e "SELECT @@hostname as 'Master';"

# Probar conexión al esclavo
mysql -h 192.168.100.102 -u app_user -pAppPassword123! -e "SELECT @@hostname as 'Slave';"

# Probar usuario de check
mysql -h 192.168.100.101 -u haproxy_check -e "SELECT 1 as 'Check_OK';" 2>/dev/null &amp;&amp; echo "✅ Check user funciona en 101" || echo "❌ Check user falla en 101"
mysql -h 192.168.100.102 -u haproxy_check -e "SELECT 1 as 'Check_OK';" 2>/dev/null &amp;&amp; echo "✅ Check user funciona en 102" || echo "❌ Check user falla en 102"

VERIFICACIÓN FINAL

# Script de verificación completo
cat > verificar_todo.sh &lt;&lt; 'EOF'
#!/bin/bash
echo "=== VERIFICACIÓN COMPLETA DEL SISTEMA ==="

echo "1. Estado de HAProxy:"
sudo systemctl status haproxy --no-pager -l

echo -e "\n2. Puertos abiertos por HAProxy:"
sudo netstat -tlnp | grep haproxy || echo "HAProxy no está escuchando en puertos"

echo -e "\n3. Procesos HAProxy:"
ps aux | grep haproxy | grep -v grep

echo -e "\n4. Verificar conexión MySQL directa:"
for host in 192.168.100.101 192.168.100.102; do
    echo -n "MySQL $host: "
    timeout 3 mysql -h $host -u app_user -pAppPassword123! -e "SELECT 1;" &amp;>/dev/null &amp;&amp; echo "✅ OK" || echo "❌ FALLO"
done

echo -e "\n5. Probar HAProxy:"
echo -n "HAProxy puerto 3306: "
timeout 3 mysql -h 127.0.0.1 -P 3306 -u app_user -pAppPassword123! -e "SELECT 1;" &amp;>/dev/null &amp;&amp; echo "✅ OK" || echo "❌ FALLO"

echo -e "\n6. Panel web:"
echo "http://192.168.100.103:8404/"

echo -e "\n7. Logs recientes de HAProxy:"
sudo tail -10 /var/log/haproxy.log 2>/dev/null || echo "No hay logs disponibles"

echo "=== VERIFICACIÓN COMPLETADA ==="
EOF

chmod +x verificar_todo.sh
./verificar_todo.sh

 Verificar que el balanceador distribuye desde cliente

mysql -h 192.168.100.103 -u app_user -pAppPassword123! -e "SELECT @@hostname, NOW()"

PRUEBAS DE FALLOVER

Simular caída del maestro

En db-master:

sudo systemctl stop mysql

comprobar desde cliente

mysql -h 192.168.100.103 -u app_user -pAppPassword123! -e "SELECT @@hostname, NOW()"

Las conexiones deberían ir automáticamente a slave1

reiniciar el maestro

sudo systemctl restart mysql

comprobar desde cliente

debe mostrarle el master

mysql -h 192.168.100.103 -u app_user -pAppPassword123! -e "SELECT @@hostname, NOW()"

mysql -h 192.168.100.103 -P 3306 -u app_user -pAppPassword123! -e "SELECT @@hostname;"

Comprobar master y replica

Master

SHOW MASTER STATUS;

Replica

SHOW SLAVE STATUS\G

Ver binlogs actuales

SHOW BINARY LOGS;

En ambos server mysql, aumenta el tiempo de expiración de binlogs:

SET GLOBAL binlog_expire_logs_seconds = 2592000;  -- 30 días
-- O en my.cnf: binlog_expire_logs_seconds = 2592000

Si la conectividad falla, verificar:

A. Firewall:

# En los servidores MySQL
sudo ufw status
sudo ufw allow from 192.168.100.103 to any port 3306

# O en CentOS/RHEL
sudo firewall-cmd --list-all
sudo firewall-cmd --add-rich-rule='rule family="ipv4" source address="192.168.100.103" port port="3306" protocol="tcp" accept' --permanent

B. Bind address en MySQL:

-- En los servidores MySQL
SHOW VARIABLES LIKE 'bind_address';

-- Si es 127.0.0.1, cambiar a 0.0.0.0 o la IP específica
-- Editar /etc/mysql/my.cnf:
-- bind-address = 0.0.0.0

C. Configuración alternativa si persisten problemas:

listen mysql-balancer
    bind 0.0.0.0:3306
    mode tcp
    balance first
    timeout client 90s
    timeout server 90s
    
    # Sin health checks complejos
    server db-master 192.168.100.102:3306 check port 3306
    server db-replica1 192.168.100.101:3306 check port 3306 backup

Aplicar cambios:

# Verificar sintaxis
sudo haproxy -c -f /etc/haproxy/haproxy.cfg

# Reiniciar HAProxy
sudo systemctl restart haproxy

# Verificar estado
sudo systemctl status haproxy
echo "show stat" | sudo socat stdio /var/run/haproxy/admin.sock

Probar conectividad básica primero:

# ¿Puede el HAProxy conectar a MySQL directamente?
mysql -h 192.168.100.102 -P 3306 -u app_user -pAppPassword123! -e "SELECT @@hostname"

La instalación de un clúster MYSQL