ProxySQL - ¿Que es? ¿Como Funciona? ¿Como se Configura y Gestiona?

ProxySQL es un proxy de MySQL de alto rendimiento que se utiliza para gestionar y optimizar la conexión de aplicaciones a bases de datos MySQL, MariaDB o Percona XtraDB. Actúa como una capa intermedia entre las aplicaciones y los servidores MySQL, permitiendo una gestión eficiente del tráfico, mejorando el balanceo de carga, la conmutación por error (failover), y el rendimiento general de las consultas.

Principales funciones y características de ProxySQL

1. Balanceo de Carga (Load Balancing):

ProxySQL distribuye automáticamente el tráfico entre varios servidores de base de datos, mejorando la disponibilidad y el rendimiento. Puedes configurarlo para balancear tanto consultas de lectura como de escritura entre servidores maestros y esclavos.

2. Conmutación por Error Automática (Failover):

En caso de que un servidor MySQL falle, ProxySQL detecta automáticamente el fallo y redirige las consultas a otro servidor disponible, sin que la aplicación lo perciba, asegurando la continuidad del servicio.

3. Routing Inteligente:

ProxySQL permite definir reglas para redirigir consultas específicas a ciertos nodos. Por ejemplo, puedes enviar las lecturas a los nodos esclavos y las escrituras al nodo maestro. Esto optimiza el rendimiento de la base de datos al reducir la carga del nodo maestro.

4. Cacheo de Consultas:

ProxySQL tiene la capacidad de almacenar en caché las consultas de lectura, lo que mejora considerablemente el rendimiento al reducir el número de consultas repetidas que llegan a los servidores de base de datos.

5. Alta Disponibilidad (HA):

ProxySQL es compatible con configuraciones de alta disponibilidad. Puede ser desplegado en modo redundante, con varios proxys ejecutándose al mismo tiempo, lo que elimina puntos únicos de fallo.

6. Monitoreo y Estadísticas:

ProxySQL incluye herramientas para monitorear el tráfico y el rendimiento de las consultas SQL. Puedes obtener estadísticas en tiempo real sobre las conexiones, latencia y uso de recursos.

7. Compatibilidad con Clústeres y Replicación:

ProxySQL es compatible con soluciones de clúster como Percona XtraDB Cluster, Galera Cluster, y replicación estándar de MySQL. Gestiona de manera inteligente las topologías de replicación, asegurando que las lecturas se dirijan a nodos esclavos y las escrituras al nodo maestro o nodos de escritura.

 8. Reescritura de Consultas:

ProxySQL permite la reescritura de consultas SQL en tiempo real. Esto es útil para mejorar el rendimiento o modificar ciertas consultas sin cambiar la aplicación.

¿Cómo funciona ProxySQL?

 ProxySQL se coloca entre la aplicación y los servidores de base de datos. Su arquitectura está diseñada para manejar grandes volúmenes de tráfico y permitir la configuración de reglas personalizadas para dirigir y optimizar el tráfico SQL. Los flujos de trabajo típicos incluyen los siguientes pasos:

1. Conexiones de la Aplicación:

Las aplicaciones no se conectan directamente a los servidores MySQL, sino a ProxySQL. La conexión con ProxySQL se realiza usando las mismas credenciales y métodos de autenticación que la base de datos.

2. Routing de Consultas:

ProxySQL evalúa las consultas recibidas y las redirige según las reglas configuradas. Esto puede incluir la distribución de consultas de lectura a nodos esclavos o consultas de escritura al nodo maestro.

3. Balanceo de Carga:

Cuando hay múltiples servidores disponibles, ProxySQL distribuye las consultas SQL de manera eficiente entre ellos. Los algoritmos de balanceo de carga pueden configurarse para distribuir el tráfico de manera uniforme, basada en la carga actual de los servidores o el número de conexiones.

4. Failover:

ProxySQL monitorea continuamente el estado de los servidores de base de datos. Si un servidor falla, redirige automáticamente las consultas a los nodos restantes disponibles.

5. Cacheo de Consultas:

Si está habilitado, ProxySQL almacena en caché el resultado de consultas SQL de lectura, lo que evita realizar consultas repetitivas a la base de datos.

6. Monitoreo y Ajustes Dinámicos:

ProxySQL proporciona información en tiempo real sobre el rendimiento de las bases de datos y las consultas. Esto te permite ajustar las configuraciones de ProxySQL dinámicamente para mejorar el rendimiento, sin reiniciar el servicio.

Arquitectura de ProxySQL

ProxySQL tiene dos capas principales:

·         Frontend: Esta es la capa que recibe las conexiones de las aplicaciones. Los clientes se conectan a esta capa como si fuera un servidor MySQL estándar.

·         Backend: Es la capa que maneja las conexiones a los servidores MySQL. ProxySQL redirige las consultas desde el frontend a los servidores MySQL backend, siguiendo las reglas de balanceo de carga y reescritura de consultas definidas.

Implementación típica de ProxySQL

1. Configuración de ProxySQL en un servidor independiente.

2. Conexión de aplicaciones: Las aplicaciones apuntan a ProxySQL en lugar de directamente a los servidores de MySQL.

3. Configuración de reglas de routing: Se definen reglas de cómo ProxySQL debe redirigir las consultas (lectura/escritura, particionamiento, etc.).

4. Monitoreo: Se habilitan métricas de monitoreo para analizar el rendimiento de las consultas y del balanceo de carga.

Casos de uso de ProxySQL

1. Escalabilidad horizontal: Permite añadir nodos de MySQL para balancear carga, mejorando el rendimiento de grandes sistemas.

2. Alta disponibilidad: En entornos de producción críticos, ProxySQL ayuda a gestionar el failover sin intervención manual.

3. Optimización de consultas: A través de reescritura y cacheo de consultas, se puede optimizar el uso de los recursos de la base de datos.

4. Entornos de múltiples bases de datos: Ideal para entornos con múltiples servidores de bases de datos o clusters, como Percona XtraDB Cluster o Galera Cluster.

Configuracion ProxySQL.

1. Instalación de ProxySQL

Para instalar ProxySQL en un servidor independiente (que actuará como proxy para el clúster), ejecuta los siguientes comandos en un servidor con Ubuntu o Debian:

sudo apt update

sudo apt install proxysql

Una vez instalado, puedes iniciar ProxySQL con:

sudo systemctl start proxysql

Verifica que esté corriendo correctamente:

sudo systemctl status proxysql

2. Acceder a la consola de administración de ProxySQL

Para acceder a la consola de administración, usa el siguiente comando:

mysql -u admin -padmin -h 127.0.0.1 -P6032

La contraseña por defecto es admin. Cambia esta contraseña después de acceder.

3. Configuración básica de ProxySQL

Dentro de la consola de administración de ProxySQL, puedes empezar a configurar los nodos de tu clúster. Suponiendo que tienes dos nodos maestros y dos nodos esclavos, la configuración sería la siguiente:

Agregar servidores MySQL (Masters y Slaves)

Primero, añade los dos nodos maestros (servidores A y B) y los dos nodos esclavos (servidores C y D) a la tabla de servidores MySQL en ProxySQL.

-- Añadir los nodos maestros

INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) VALUES

(10, 'master1_ip', 3306, 1000), -- Nodo maestro 1

(10, 'master2_ip', 3306, 1000); -- Nodo maestro 2

-- Añadir los nodos esclavos

INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) VALUES

(20, 'slave1_ip', 3306, 1000),  -- Nodo esclavo 1

(20, 'slave2_ip', 3306, 1000);  -- Nodo esclavo 2

 En este caso, estamos utilizando hostgroup_id 10 para los nodos maestros y hostgroup_id 20 para los nodos esclavos.

Configuración de usuarios

A continuación, agrega un usuario de base de datos en ProxySQL que las aplicaciones utilizarán para conectarse. Suponiendo que tu usuario es app_user:

 INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) VALUES

('app_user', 'password', 10, 1);

- default_hostgroup 10: Esto significa que el grupo predeterminado para este usuario será el hostgroup de los maestros (para escrituras).

- transaction_persistent 1: Esto garantiza que una transacción completa se ejecutará en el mismo host y no se distribuirá a varios.

Reglas de enrutamiento

Debes definir las reglas que redirijan las consultas de lectura (`SELECT`) a los nodos esclavos y las consultas de escritura (`INSERT`, UPDATE, DELETE, etc.) a los nodos maestros.

Para redirigir todas las lecturas (`SELECT`) a los esclavos, puedes crear la siguiente regla de enrutamiento:

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES

(1, 1, '^SELECT.*', 20, 1);

- Esta regla dice que cualquier consulta que comience con SELECT será enviada al hostgroup 20 (nodos esclavos).

Las escrituras (`INSERT`, UPDATE, DELETE) no necesitan reglas especiales, ya que por defecto irán al hostgroup definido para este usuario, que en este caso son los nodos maestros (hostgroup 10).

Aplicar los cambios

Después de haber configurado los servidores, usuarios y reglas de enrutamiento, es necesario aplicar los cambios:

LOAD MYSQL SERVERS TO RUNTIME;

LOAD MYSQL USERS TO RUNTIME;

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;

SAVE MYSQL USERS TO DISK;

SAVE MYSQL QUERY RULES TO DISK;

 4. Configuración avanzada (opcional)

Failback en caso de fallo de un maestro

Puedes configurar ProxySQL para que realice un failover automático si uno de los nodos maestros falla. Esto se hace monitoreando los servidores y definiendo prioridades de failover.

 -- Habilitar monitorización de los servidores MySQL

UPDATE mysql_servers SET status='ONLINE' WHERE hostname='master1_ip' OR hostname='master2_ip';

-- Configurar intervalos de monitoreo

SET mysql-monitor_ping_interval=1000; -- Intervalo de ping para monitorear

Balanceo de carga entre esclavos

Para distribuir las consultas de lectura de manera equitativa entre los esclavos, ProxySQL puede balancear las consultas de lectura entre los nodos esclavos.

-- Balancear consultas entre esclavos

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, multiplex) VALUES

(2, 1, '^SELECT.*', 20, 1, 1);

Failover en caso de fallo de un esclavo

Puedes configurar la conmutación por error de ProxySQL si un nodo esclavo falla, de modo que redirija automáticamente el tráfico de lecturas al nodo esclavo que aún esté disponible.

5. Monitoreo y optimización

ProxySQL permite obtener estadísticas en tiempo real sobre el tráfico y los servidores de MySQL. Esto es útil para asegurarse de que el clúster está funcionando como se espera.

Para ver estadísticas del tráfico:

SELECT * FROM stats_mysql_query_digest;

Para monitorear el estado de los servidores:

SELECT * FROM runtime_mysql_servers;

Si  está en línea o desconectado.

6. Configuración de la aplicación

Finalmente, configura tu aplicación para que apunte a ProxySQL en lugar de directamente a los servidores MySQL:

host = 'proxysql_ip'

port = 6033

username = 'app_user'

password = 'password'

Tu aplicación ahora estará conectada a ProxySQL, que gestionará la distribución del tráfico entre los nodos maestros y esclavos de manera eficiente.

Comandos ProxySQL.

 1. Gestión de Servidores MySQL

 - Ver todos los servidores MySQL configurados:

   SELECT * FROM mysql_servers;

- Agregar un nuevo servidor MySQL a un hostgroup:

   INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) VALUES

   (10, '192.168.1.100', 3306, 1000);

- Eliminar un servidor MySQL de un hostgroup:

   DELETE FROM mysql_servers WHERE hostname = '192.168.1.100' AND hostgroup_id = 10;

- Actualizar los parámetros de un servidor MySQL:

   UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostname = '192.168.1.100' AND hostgroup_id = 10;

- Aplicar cambios a los servidores (cargar cambios en el entorno de ejecución):

   LOAD MYSQL SERVERS TO RUNTIME;

   SAVE MYSQL SERVERS TO DISK;

 2. Gestión de Usuarios MySQL

- Ver todos los usuarios configurados:

   SELECT * FROM mysql_users;

- Agregar un nuevo usuario MySQL:

   INSERT INTO mysql_users (username, password, default_hostgroup) VALUES

   ('app_user', 'app_password', 10);

- Eliminar un usuario MySQL:

   DELETE FROM mysql_users WHERE username = 'app_user';

- Aplicar cambios a los usuarios:

   LOAD MYSQL USERS TO RUNTIME;

   SAVE MYSQL USERS TO DISK;

 3. Reglas de Enrutamiento de Consultas

- Ver todas las reglas de enrutamiento:

   SELECT * FROM mysql_query_rules;

 - Agregar una nueva regla de enrutamiento:

   INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES

   (1, 1, '^SELECT.*', 20, 1);

 - Eliminar una regla de enrutamiento:

   DELETE FROM mysql_query_rules WHERE rule_id = 1;

- Aplicar cambios a las reglas de enrutamiento:

   LOAD MYSQL QUERY RULES TO RUNTIME;

   SAVE MYSQL QUERY RULES TO DISK;

4. Monitorización de Consultas y Servidores

Monitorización de Consultas

- Ver estadísticas de las consultas más ejecutadas:

   SELECT * FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 10;

- Ver estadísticas sobre el tiempo de ejecución de consultas:

   SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 10;

- Ver estadísticas por tipo de consulta (lecturas/escrituras):

   SELECT hostgroup, digest_text, count_star, sum_time, min_time, max_time

   FROM stats_mysql_query_digest

   WHERE digest_text LIKE 'SELECT%' OR digest_text LIKE 'UPDATE%' OR digest_text LIKE 'DELETE%'

   ORDER BY sum_time DESC;

Monitorización de Servidores MySQL

- Ver estado en tiempo real de los servidores MySQL:

   SELECT * FROM runtime_mysql_servers;

Esto te muestra información como el estado actual (ONLINE/OFFLINE), latencia, tiempos de respuesta, etc.

- Ver estadísticas de conexiones de los servidores MySQL:

   SELECT * FROM stats_mysql_connection_pool;

Este comando proporciona información sobre las conexiones activas, cerradas, fallidas, y el uso del pool de conexiones para cada servidor en tiempo real.

- Ver estadísticas de tráfico de los servidores MySQL:

   SELECT * FROM stats_mysql_global;

Monitorización de Errores y Fallos

- Ver el número de consultas fallidas:

   SELECT * FROM stats_mysql_errors;

- Ver errores específicos de los servidores MySQL:

   SELECT * FROM stats_mysql_errors_by_host;

- Ver errores específicos por tipo de consulta:

   SELECT * FROM stats_mysql_errors_by_query;

 5. Monitorización de ProxySQL

- Ver estadísticas globales del proxy:

   SELECT * FROM stats_mysql_global;

Esto incluye métricas generales como el tráfico total, número de consultas ejecutadas, tiempos de procesamiento, etc.

- Ver el uso del pool de conexiones:

   SELECT * FROM stats_mysql_connection_pool;

- Ver el estado de las conexiones:

   SELECT * FROM stats_mysql_processlist;

- Ver métricas de latencia y tiempos de respuesta:

   SELECT * FROM stats_mysql_latency;

6. Gestión de la Monitorización

ProxySQL tiene un conjunto de configuraciones de monitorización que se pueden ajustar para detectar el estado de los servidores y realizar failover si es necesario.

- Configurar intervalo de ping para los servidores MySQL:

   SET mysql-monitor_ping_interval = 1000;  -- en milisegundos

- Configurar el intervalo para verificar los tiempos de respuesta:

   SET mysql-monitor_read_only_interval = 2000;  -- en milisegundos

- Configurar el intervalo de latencia:

   SET mysql-monitor_connect_interval = 1000;

7. Reinicio y Reinicialización

- Reiniciar ProxySQL:

   sudo systemctl restart proxysql

- Reiniciar la configuración (limpiar y recargar todo):

   LOAD MYSQL SERVERS TO RUNTIME;

   LOAD MYSQL USERS TO RUNTIME;

   LOAD MYSQL QUERY RULES TO RUNTIME;

- Guardar toda la configuración en disco:

   SAVE MYSQL SERVERS TO DISK;

   SAVE MYSQL USERS TO DISK;

   SAVE MYSQL QUERY RULES TO DISK;

8. Tareas Administrativas Avanzadas

- Hacer un backup de la configuración de ProxySQL:

   proxysql --backup-to-file=/ruta/al/archivo.sql

- Restaurar la configuración de ProxySQL desde un archivo:

   proxysql --restore-from-file=/ruta/al/archivo.sql