ProxySQL y Percona XtraDB Cluster en Vagrant

By fernandoperez March 27, 2025, 3:13 p.m.

¿Qué es ProxySQL?

ProxySQL es un proxy para bases de datos MySQL que mejora el rendimiento, la escalabilidad y la disponibilidad del sistema. Actúa como una capa intermedia entre la aplicación y los servidores de bases de datos, gestionando las conexiones y optimizando la ejecución de consultas.

¿Por qué usar ProxySQL?

Una base de datos MySQL puede enfrentar problemas de rendimiento y disponibilidad.

ProxySQL ayuda a solucionarlos de varias maneras:

Balanceo de carga: Distribuye las consultas entre múltiples servidores, evitando la saturación de un solo nodo.
Alta disponibilidad: Si un servidor MySQL falla, ProxySQL puede redirigir el tráfico a otro servidor disponible sin afectar la aplicación.
Cacheo de consultas: Almacena resultados de consultas frecuentes para reducir la carga en la base de datos.
Optimización del enrutamiento: Puede enviar ciertas consultas a servidores específicos según reglas predefinidas, por ejemplo, enviando solo lecturas a los nodos de réplica.
Persistencia de conexiones: Mantiene conexiones abiertas con los servidores MySQL, reduciendo la sobrecarga de autenticación.

¿Cómo funciona ProxySQL?

Para entender su funcionamiento, imagina que es un intermediario entre la aplicación y MySQL:

Gestor de conexiones: ProxySQL maneja y reutiliza conexiones hacia los servidores MySQL, reduciendo el tiempo necesario para establecer nuevas conexiones.
Reglas de enrutamiento: Se pueden definir reglas para enviar consultas específicas a diferentes servidores según criterios como tipo de consulta, carga del servidor o latencia.
Cache de consultas: Si una misma consulta se ejecuta repetidamente, ProxySQL puede responder directamente sin consultar la base de datos, mejorando la velocidad.
Monitoreo en tiempo real: ProxySQL recopila estadísticas de rendimiento de las consultas y permite ajustar la configuración para mejorar la eficiencia.

¿Cómo se configura ProxySQL en términos simples?

Definir servidores de MySQL: Se configuran los servidores MySQL a los que ProxySQL enviará consultas.
Crear reglas de enrutamiento: Se establecen reglas para dirigir consultas a servidores específicos según criterios de carga y tipo de operación (lectura/escritura).
Habilitar cacheo: Se pueden configurar tiempos de vida para el cache de consultas más frecuentes.
Monitoreo y ajustes: ProxySQL permite analizar el tráfico en tiempo real y optimizar su configuración según las necesidades del sistema.

1) Levantar vagrant

Es necesario tener virtualbox instalado y vagrant configurado. Partiendo de eso creamos un archivo Vagrantfile con el siguiente contenido:

Vagrant.configure("2") do |config|
  # Configuración de la máquina 1
  config.vm.define "pxc-node-1" do |server|
    server.vm.box = "ubuntu/focal64"
    server.vm.hostname = "pxc-node-1"
    server.vm.network "private_network", ip: "192.168.56.11"

    server.vm.provider "virtualbox" do |vb|
      vb.memory = "1024"
      vb.cpus = 2
    end
  end


  # Configuración de la máquina 2
  config.vm.define "pxc-node-2" do |server|
    server.vm.box = "ubuntu/focal64"
    server.vm.hostname = "pxc-node-2"
    server.vm.network "private_network", ip: "192.168.56.12"

    server.vm.provider "virtualbox" do |vb|
      vb.memory = "1024"
      vb.cpus = 2
    end
  end


  # Configuración de la máquina 3
  config.vm.define "pxc-node-3" do |server|
    server.vm.box = "ubuntu/focal64"
    server.vm.hostname = "pxc-node-3"
    server.vm.network "private_network", ip: "192.168.56.13"

    server.vm.provider "virtualbox" do |vb|
      vb.memory = "1024"
      vb.cpus = 2
    end
  end


  # Configuración de la máquina 4
  config.vm.define "pxc-proxysql" do |server|
    server.vm.box = "ubuntu/focal64"
    server.vm.hostname = "pxc-proxysql"
    server.vm.network "private_network", ip: "192.168.56.14"

    server.vm.provider "virtualbox" do |vb|
      vb.memory = "512"
      vb.cpus = 1
    end
  end


  # Configuración de la máquina 5
  config.vm.define "pxc-app" do |server|
    server.vm.box = "ubuntu/focal64"
    server.vm.hostname = "pxc-app"
    server.vm.network "private_network", ip: "192.168.56.15"

    server.vm.provider "virtualbox" do |vb|
      vb.memory = "1024"
      vb.cpus = 2
    end
  end
end

Ahora levantamos los servidores con vagrant

vagrant up

2) Instalar Percona XtraDB Cluster

Entrar a los nodos del cluster (pxc-node-1, pxc-node-2, pxc-node-3).

vagrant ssh pxc-node-1

vagrant ssh pxc-node-2

vagrant ssh pxc-node-3

Instalar en las tres máquinas percona xtradb cluster:

sudo apt update \
    && wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb \
    && sudo dpkg -i percona-release_latest.generic_all.deb \
    && sudo percona-release setup pxc57 \
    && sudo apt install -y libdbi-perl libdbd-mysql-perl libmecab2 socat  libcurl4-openssl-dev libev4 \
    && sudo apt install -y percona-xtrabackup-24  qpress \
    && sudo apt install -y percona-xtradb-cluster-full-57=5.7.40-31.63-1.focal percona-xtradb-cluster-server-5.7=5.7.40-31.63-1.focal percona-xtradb-cluster-client-5.7=5.7.40-31.63-1.focal percona-xtradb-cluster-test-5.7=5.7.40-31.63-1.focal percona-xtradb-cluster-5.7-dbg=5.7.40-31.63-1.focal percona-xtradb-cluster-server-debug-5.7=5.7.40-31.63-1.focal percona-xtradb-cluster-garbd-5.7=5.7.40-31.63-1.focal percona-xtradb-cluster-garbd-debug-5.7=5.7.40-31.63-1.focal percona-xtradb-cluster-common-5.7=5.7.40-31.63-1.focal \
    && sudo systemctl start mysql

3) Configurar nodos

Nos conectamos al nodo y entramos a la consola de mysql, en caso de que nos pida contraseña utilizaremos "root", por ejemplo:

vagrant ssh pxc-node-1

sudo mysql

En el nodo 1 creamos el usuario SST

CREATE USER 'sstuser'@'%' IDENTIFIED BY 'temporal1';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'%';
FLUSH PRIVILEGES;

En cada nodo cambiamos la configuracion para agregarlo al cluster.

Editar el siguiente archivo:

sudo nano /etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf

Cambiamos unicamente la siguiente informacion:

wsrep_cluster_address=gcomm://192.168.56.11,192.168.56.12,192.168.56.13
wsrep_node_address=<ip_nodo>
# Nombre del nodo de preferencia la terminacion de la ip
wsrep_node_name=pxc-cluster-node-1
pxc_strict_mode=PERMISSIVE
# La misma contraseña del usuario SST
wsrep_sst_auth="sstuser:temporal1"

Nuestro archivo deberia verse de la siguiente manera pero con las configuraciones correspondientes al nodo actual,

[mysqld]
# Path to Galera library
wsrep_provider=/usr/lib/galera3/libgalera_smm.so

# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://192.168.56.11,192.168.56.12,192.168.56.13

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# Slave thread to use
wsrep_slave_threads=2

wsrep_log_conflicts

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node IP address
wsrep_node_address=192.168.56.11
# Cluster name
wsrep_cluster_name=pxc-cluster

#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=pxc-cluster-node-1

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=PERMISSIVE

# SST method
wsrep_sst_method=xtrabackup-v2

#Authentication for SST method
wsrep_sst_auth="sstuser:temporal1"

3.1 Iniciar el primer nodo

En todos los nodos detenemos mysql:

sudo service mysql stop

Una vez que hemos finalizado al configuración de nuestros 3 nodos, accedemos al nodo 1 y lo inicializamos usando el siguiente comando

sudo /etc/init.d/mysql bootstrap-pxc

Este comando iniciara nuestro nodo pero ademas indicara que es nuestro nodo principal

3.2 Iniciar el segundo y tercer nodo

Accedemos a los nodos 2 y 3 respectivamente y ejecutamos el siguiente comando

sudo /etc/init.d/mysql start

Eso inicializara nuestro nodo y deberia sincrinizarse con nuestro nodo principal, nodo 1

Una vez hecho esto accedemos a mysql

sudo mysql

Y vamos a verificar el estado de nuestro cluster usando el siguiente comando

show status like 'wsrep%';

La salida de nuestro comando deberia mostrarnos que el tamaño del cluster es de 3 nodos, podemos observarlo en la variable wsrep_cluster_size.

+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | b598af3e-ace3-11e2-0800-3e90eb9cd5d3 |
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
| wsrep_cluster_size         | 2                                    |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+
40 rows in set (0.01 sec)

3.3 Crear base de datos

Para poder realizar las pruebas podemos crear una base de datos con datos de prueba, para el ejercicio trabajaremos con la siguiente base de datos: CompanyDB

Creamos una base de datos desde cualquier nodo del cluster.

sudo mysql -e "CREATE DATABASE companydb;"

En el mismo nodo descargamos el archivo backup para crear las tablas y los registros.

sudo apt install unzip

wget https://ahkinws.s3.amazonaws.com/media/chat/media/nowe/1035335611310920-2024-08-29-15-59-27.zip

unzip 1035335611310920-2024-08-29-15-59-27.zip  -d $(pwd)

Mandamos a aplicar el backup.

sudo mysql companydb < backup.sql

Si te da error importar la base de datos, porque el collation no corresponde a tu version de MySQL, puede ejecutar lo siguiente:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' backup.sql # Actualizar la bd

3.4 Comprobación

Esto nos importara la información de la base de datos que creamos previamente, ademas, al tener configurados nuestros nodos deberia replicarse la información de nuestra base de datos, para ello podemos comprobarlo accediendo a mysql en alguno de nuestros nodos

sudo mysql -e "use companydb; show tables;"

La salida del comando deberia mostrarnos las tablas de la base de datos que creamos.

+---------------------+
| Tables_in_companydb |
+---------------------+
| Companies           |
| Customers           |
| Invoices            |
| Purchases           |
+---------------------+

Una vez hecho esto en ambos nodos nuestro cluster deberia estar configurado correctamente por lo que ya podriamos realizar nuestras pruebas de replicación.

4) Pruebas de replicacion

4.1) Verificamos en todos los nodos el ultimo ID: Lista la lista de Companies

sudo mysql -e "select * from companydb.Companies order by CompanyID DESC LIMIT 3\G"

4.2) Hacer inserciones en el cualquier nodo:

sudo mysql -e "INSERT INTO companydb.Companies (CompanyID, Name, Address, Phone, Email) VALUES (NULL, 'Other Company', 'Unit 5699 Box 8173 DPO AP 62802', '+1-635-243-2015x623', '[email protected]');"

4.3 En todos los nodos, verificamos que la información se este replicando correctamente:

sudo mysql -e "select * from companydb.Companies order by CompanyID DESC LIMIT 3\G"

5) Configurar ProxySQL

5.1. Instalación

Ingresamos a la maquina del proxy

vagrant ssh pxc-proxysql

Añadimos el repositorio para la instalación

sudo apt-get install -y --no-install-recommends net-tools lsb-release wget apt-transport-https ca-certificates gnupg
wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key' | sudo apt-key add - 
sudo echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./ | sudo tee /etc/apt/sources.list.d/proxysql.list

sudo apt-get update
sudo apt-get install proxysql

Una vez instalado tenemos que inicializarlo:

sudo systemctl start proxysql

Podemos ver que está activo y escuchando en dos puertos TCP y dos sockets

sudo netstat -lpn | grep proxysql

El puerto 6032 es para administrar ProxySQL, mientras que el 6033 es para conexiones de clientes a servicios backend de MySQL. Por supuesto, el segundo aún no está listo, pero ya podemos iniciar sesión en la consola de administración de ProxySQL.

tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      8003/proxysql
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      8003/proxysql
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      8003/proxysql
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      8003/proxysql
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      8003/proxysql

Para acceder al servicio de proxysql necesitaremos instalar el cliente de MySQL

sudo apt install -y mysql-client-core-8.0

Acceder a la consola de administración de ProxySQL usando el cliente de MySQL.

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

5.2. Configurar los nodos del cluster

Para configurar los nodos backend del clúster Percona XtraDB en ProxySQL, inserte los registros correspondientes en la tabla mysql_servers.

Antes de agregar cualquier cosa, vamos a ver que servers estan en el runtime

SELECT * from mysql_servers;
SELECT * from runtime_mysql_servers;

Este ejemplo agrega tres nodos del clúster Percona XtraDB al grupo de hosts predeterminado 1, 2 y 3, y decidiremos cuál es el lector HG y cuál es el grupo de hosts RW.

INSERT INTO mysql_servers 
(hostgroup_id, hostname,        port,  status,  weight, max_connections) VALUES 
-- Servidores para lectura
(1,            '192.168.56.12', 3306, 'ONLINE', 1000,   10000),
(1,            '192.168.56.13', 3306, 'ONLINE', 1000,   10000),

-- Servidores para escritura
(2,            '192.168.56.11', 3306, 'ONLINE', 1000,   10000),

-- Servidores para backup escritura
(3,            '192.168.56.13', 3306, 'ONLINE', 1000,   10000);


-- Debemos cargarlo en el runtime para que ProxySQL empiece a usarlo
LOAD MYSQL SERVERS TO RUNTIME;

-- Tambien debemos agregarlo al disco para que sea persistente en los reinicios.
SAVE MYSQL SERVERS TO DISK;

Podemos consultamos que los servidores se allan configurado con los siguiente comandos

SELECT * FROM mysql_servers;
SELECT * FROM runtime_mysql_servers;

5.3. Configurar los Host Groups

Ahora podemos configurar el comportamiento de ProxySQL para nuestro clúster PXC. Configuramos max_writers=1 y queremos un escritor dedicado, que no manejaría el tráfico SELECT (writer_is_also_reader=0) en la configuración actual.

INSERT INTO mysql_galera_hostgroups (
    reader_hostgroup,
    writer_hostgroup,
    backup_writer_hostgroup,
    offline_hostgroup,
    active,
    max_writers,
    writer_is_also_reader,
    max_transactions_behind,
    comment
) VALUES (
    1,  -- Hostgroup de nodos en modo lectura (pxc-node-2 y pxc-node-3)
    2,  -- Hostgroup de nodos en modo escritura (pxc-node-1)
    3,  -- Hostgroup de nodos en modo escritura backup (pxc-node-3)
    99, -- Hostgroup de nodos offline
    1,  -- Activo
    1,  -- Solo 1 nodo puede estar en modo escritura
    0,  -- Los nodos de escritura NO atenderán lecturas
    100,-- Límite de transacciones en espera antes de marcar un nodo como saturado
    'Pycun Galera Conf' --
);

select * from mysql_galera_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+-------------------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment           |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+-------------------+
| 2                | 3                       | 1                | 99                | 1      | 1           | 0                     | 100                     | Pycun Galera Conf |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+-------------------+
1 row in set (0.00 sec)

5.4. Crear un usuario de monitoreo ProxySQL

Para habilitar la supervisión de los nodos del clúster Percona XtraDB en ProxySQL, cree un usuario con privilegios de USO en cualquier nodo del clúster y configure el usuario en ProxySQL.

Agrega un usuario de monitoreo en cualquier nodo del cluster

vagrant ssh pxc-node-2
sudo mysql

Como esta la replicación el usuario se creara en todos los nodos del cluster

CREATE USER 'proxysql_monitor'@'%' IDENTIFIED WITH mysql_native_password by 'ProxySQLPa55';

GRANT USAGE ON *.* TO 'proxysql_monitor'@'%';

Configura este usuario en el nodo ProxySQL:

UPDATE global_variables SET variable_value='proxysql_monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='ProxySQLPa55' WHERE variable_name='mysql-monitor_password';

-- Debemos cargarlo en el runtime para que ProxySQL empiece a usarlo
LOAD MYSQL VARIABLES TO RUNTIME;
-- Tambien debemos agregarlo al disco para que sea persistente en los reinicios.
SAVE MYSQL VARIABLES TO DISK;

5.5. Definir las reglas de consulta para la división R/W

Ahora es el momento de definir las reglas de consulta, que manejarán la división de lectura/escritura. El default_hostgroup para el usuario de la aplicación en la tabla mysql_users está configurado en 1, es decir, el grupo de hosts del escritor.

Primera regla: el procesador de consultas escanea la regla de consulta para encontrar una coincidencia para el patrón ^SELECT.* y, si se encuentra una coincidencia, proxysql reenviará estas consultas a destino_hostgroup=1;

Segunda regla: las consultas con un patrón ^(INSERT|UPDATE|DELETE).* siempre deben entregarse desde el grupo de hosts del escritor, por lo que configuramos el destino_hostgroup=2;

INSERT INTO mysql_query_rules 
(active, match_digest,               destination_hostgroup, apply) VALUES 
-- Enviar SELECT a nodos de Lectura con el grupo 1
(1,      '^SELECT.*',                1,                     0),
-- Enviar INSERT, UPDATE y DELETE a nodos de escritura con el grupo 2
(1,      '^(INSERT|UPDATE|DELETE).*',2,                     1);


-- Debemos cargarlo en el runtime para que ProxySQL empiece a usarlo
LOAD MYSQL QUERY RULES TO RUNTIME;

-- Tambien debemos agregarlo al disco para que sea persistente en los reinicios.
SAVE MYSQL QUERY RULES TO DISK;

5.6 Importar usuarios de MySQL a ProxySQL

Importante: asegúrese de utilizar el complemento de autenticación predeterminado = mysql_native_password, ya que proxysql no admite caching_sha2_password en el momento de escribir este artículo. Para mas información puede consultar [ProxySQL Support for MySQL caching_sha2_password]https://www.percona.com/blog/proxysql-support-for-mysql-caching_sha2_password/(http://)

INSERT INTO mysql_users (username, password, active, default_hostgroup, transaction_persistent) VALUES ('apps', 'It_is_so_Secure1', 1, 2, 1);

-- Debemos cargarlo en el runtime para que ProxySQL empiece a usarlo
LOAD MYSQL USERS TO RUNTIME;

-- Tambien debemos agregarlo al disco para que sea persistente en los reinicios.
SAVE MYSQL USERS TO DISK;

Crear el usuario igual en el cluster

CREATE USER 'apps'@'%' IDENTIFIED BY 'It_is_so_Secure1';
GRANT ALL PRIVILEGES ON *.* TO 'apps'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

5.7 Ahora desde la maquina pxc-app nos conectamos como cliente a la instancia de ProxySQL

vagrant ssh pxc-app

Instalamos el cliente de MySQL

sudo apt update
sudo apt install -y mysql-client-core-8.0

Nos conectamos como cliente de la aplicación

mysql -h 192.168.56.14 -P 6033 -u apps -pIt_is_so_Secure1

5.8 Pruebas

En este comando solo debe consultar pxc-node-2 y pxc-node-3

mysql companydb -u apps -pIt_is_so_Secure1 -h 192.168.56.14 -P 6033 -e "select @@hostname;"

En este comando solo debe insertart pxc-node-1

mysql companydb -u apps -pIt_is_so_Secure1 -h 192.168.56.14 -P 6033 -e "INSERT INTO Companies (Name, Address, Phone, Email) VALUES (CONCAT('Empresa por: ', @@hostname), 'Calle Falsa 123, Ciudad X', '555-1234', '[email protected]'); SELECT CompanyID, Name from Companies order by CompanyID desc limit 3"