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) Instalación de ProxySQL Servidor balanceador(192.168.0.128)

1 Instalar ProxySQL

wget https://github.com/sysown/proxysql/releases/download/v2.5.4/proxysql_2.5.4-ubuntu20_amd64.deb
sudo dpkg -i proxysql_2.5.4-ubuntu20_amd64.deb
sudo apt install -f

2 Iniciar servicio:

sudo systemctl enable proxysql
sudo systemctl start proxysql

3 Configuración de ProxySQL

3.1 Acceso administrativo:

mysql -u admin -padmin -h 127.0.0.1 -P 6032

Definición de hostgroups

Hostgroup	Uso
10	PRIMARY (lectura/escritura)
20	SECONDARY (solo lectura)

4 Registro de servidores

INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections, max_replication_lag) VALUES
(10,'192.168.0.114',3306,10000,1000,10),
(20,'192.168.0.117',3306,1000,1000,10),
(20,'192.168.0.124',3306,1000,1000,10);

5 Creación de usuarios de aplicación

INSERT INTO mysql_users(username, password, default_hostgroup, transaction_persistent, backend, frontend, active) VALUES
('app_admin','AppAdmin!789',10,1,1,1,1),
('app_readonly','AppRead!789',20,1,1,1,1);

6 Reglas de enrutamiento

INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply) VALUES
(1,1,'^SELECT',20,1),
(2,1,'^(INSERT|UPDATE|DELETE)',10,1);

7 Guardar y cargar:

SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

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

Arranque normal del sistema

# Verificar estado del cluster
cluster.status()

#Iniciar ProxySQL
sudo systemctl start proxysql

# Pruebas institucionales post-recuperación

#Prueba de escritura
INSERT INTO testdb.audit_log VALUES (NOW(), @@hostname);

#Prueba de lectura
SELECT * FROM testdb.audit_log ORDER BY 1 DESC LIMIT 5;

Verificar Configuración

SELECT * FROM performance_schema.replication_group_members;
SHOW VARIABLES LIKE 'group_replication%';

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

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

La instalación de un clúster MYSQL