En entornos de alta demanda, donde el rendimiento y la disponibilidad de las bases de datos son cruciales, la gestión eficiente de las conexiones es clave para evitar cuellos de botella y caídas en el servicio. ProxySQL es una herramienta poderosa que se presenta como un proxy avanzado de base de datos SQL para mejorar el rendimiento, escalabilidad y seguridad de bases de datos MySQL, MariaDB y Percona. En este artículo, exploraremos qué es ProxySQL, sus características principales y cómo instalarlo para sacarle el máximo provecho.
¿Qué es ProxySQL?
ProxySQL es un proxy de base de datos que actúa como intermediario entre las aplicaciones y los servidores de bases de datos. Fue diseñado específicamente para ambientes de alta concurrencia y ofrece un conjunto de funcionalidades que mejoran tanto el rendimiento como la flexibilidad de la infraestructura de base de datos. Entre sus principales beneficios se encuentran:
- Balanceo de carga: Redistribuye las consultas SQL entre varios servidores de bases de datos para distribuir la carga de trabajo de manera equitativa.
- Enrutamiento avanzado de consultas: Puedes definir reglas para dirigir diferentes tipos de consultas (lecturas, escrituras, etc.) a diferentes nodos o clusters.
- Cache de consultas: ProxySQL permite almacenar en caché consultas frecuentes, reduciendo la carga en la base de datos.
- Alta disponibilidad: Ayuda a mitigar las interrupciones de servicio al gestionar de forma dinámica las conexiones cuando uno o varios nodos fallan.
- Gestión de conexiones: Puede manejar miles de conexiones simultáneas y reducir el número de conexiones activas contra el servidor backend, lo que optimiza los recursos de la base de datos.
Instalación de ProxySQL
La instalación de ProxySQL es un proceso relativamente sencillo que varía según el sistema operativo. Aquí veremos cómo instalarlo en Ubuntu, uno de los sistemas más utilizados en entornos de servidores.
Paso 1: Actualizar el sistema
Es recomendable comenzar por actualizar la lista de paquetes disponibles en el sistema y asegurar que todo está al día:
sudo apt update && sudo apt upgrade -y
Paso 2: Agregar el repositorio de ProxySQL
ProxySQL no está disponible de manera predeterminada en los repositorios de Ubuntu, por lo que debes agregarlo manualmente:
apt-get install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates gnupg wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/repo_pub_key' | apt-key add - echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list
Esto agrega el repositorio oficial de ProxySQL.
Paso 3: Instalar ProxySQL
Luego de agregar el repositorio, actualiza la lista de paquetes e instala ProxySQL:
sudo apt update sudo apt install proxysql
Paso 4: Configurar ProxySQL
Una vez instalado, ProxySQL viene con un archivo de configuración por defecto que puedes ajustar según tus necesidades.
- Edita el archivo de configuración principal:
sudo nano /etc/proxysql.cnf
- Aquí puedes definir los ajustes relacionados con las conexiones, usuarios y nodos de la base de datos que se conectarán a través de ProxySQL. Más adelante en este post explico cómo configurar estos ajustes desde la terminal del sistema.
Paso 5: Iniciar el servicio
Una vez configurado, puedes iniciar ProxySQL utilizando el siguiente comando:
sudo systemctl start proxysql
También puedes habilitarlo para que se inicie automáticamente con el sistema:
sudo systemctl enable proxysql
Paso 6: Acceder al CLI de ProxySQL
ProxySQL cuenta con una interfaz de línea de comandos que te permite gestionar y modificar configuraciones en tiempo real. Para acceder a ella, utiliza el siguiente comando:
mysql -u admin -p -h 127.0.0.1 -P6032
La contraseña por defecto es «admin», aunque se recomienda cambiarla lo antes posible por razones de seguridad.
Configurar ProxySQL
Vamos a suponer que disponemos de dos instancias de MySQL, y que queremos tener una de las instancias solo para escritura y la otra solo para lecturas. También damos por hecho que ambas instancias tienen los mismos datos y que están sincronizadas, una como instancia maestra o fuente (la de escritura) y otra como esclava o réplica.
Si no sabes aun cómo crear réplicas en MySQL, o no sabes cómo configurar una réplica para que sea de solo lectura este artículo te ayudará en su creación y configuración: Cómo crear y configurar una replica de base de datos MySQL 8
ProxySQL utiliza una base de datos SQLite para su conifguración, con lo que al acceder al CLI verás que la mayoría de consultas en SQL funcionarán.
Informar de las instancias de MySQL a ProxySQL
Lo primero que hay que hacer es indicarle a ProxySQL dónde se encuentran las instancias de MySQL. Para ello utilizaremos la siguiente consulta para insertar los registros de cada instancia:
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.0.0.1',3306); INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'10.0.0.2',3306); load mysql servers on runtime; save mysql servers to disk;
Le indicamos a ProxySQL el identificador del hostgroup, la IP de la instancia de MySQL y el puerto de conexión.
Actualmente disponemos de dos «hostgroups»: uno para lecturas y otro para escrituras. Es importante recordar el identificador que tiene cada uno, pues se utilizará en la creación de usuarios (por ejemplo) para indicar el hostgroup por defecto:
- Hostgroup de escritura: 1
- Hostgroup de lectura: 2
Se podrían añadir más instancias al hostgroup de lectura. Más raro es añadir instancias al hostgroup de escritura, pero también sería posible.
No se indican IPs de las instancias pues un hostgroup puede tener varias instancias asignadas.
Cada vez que se realiza alguna modificación hay que cargar los cambios a memoria (LOAD xxxx TO RUNTIME); y además hacer un guardado en disco para cuando se reinicie el servicio no se pierda la configuración (SAVE XXX TO DISK).
Crear el usuario de monitorización
ProxySQL necesita monitorizar las instancias de MySQL para detectar si alguna está caída, o la carga de trabajo que tienen, y así decidir dónde enviar las consultas.
Para ello, en cada instancia de MySQL crea el usuario «monitor»:
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor'; GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
A continuación, en ProxySQL tendrás que configurar el usuario de monitorización en las variables de configuración:
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;
Ahora puedes configurar lo que necesites sobre la monitorización de las instancias:
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.00 sec)
ProxySQL Admin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
+----------------------------------------+---------------------------------------------------+
| variable_name | variable_value |
+----------------------------------------+---------------------------------------------------+
| mysql-monitor_history | 600000 |
| mysql-monitor_connect_interval | 2000 |
| mysql-monitor_connect_timeout | 200 |
| mysql-monitor_ping_interval | 2000 |
| mysql-monitor_ping_timeout | 100 |
| mysql-monitor_read_only_interval | 2000 |
| mysql-monitor_read_only_timeout | 100 |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_username | monitor |
| mysql-monitor_password | monitor |
| mysql-monitor_query_variables | SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES |
| mysql-monitor_query_status | SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_timer_cached | true |
| mysql-monitor_writer_is_also_reader | true |
+----------------------------------------+---------------------------------------------------+
17 rows in set (0.00 sec)Creación de usuarios en ProxySQL y MySQL
Cuando sea necesario crear usuarios en MySQL, es imprescindible crearlos tanto en MySQL como en ProxySQL.
En MySQL su creación es la habitual: se crea el usuario, se vincula con una base de datos y se le dan privilegios.
ProxySQL necesita conocer al usuario para poder hacer la conexión en MySQL, por tanto hay que acceder a ProxySQL y ejecutar las siguientes consultas:
INSERT INTO mysql_users (username,password,default_hostgroup,default_schema) VALUES ('nombre_usuario', 'contraseña_mysql',1,'NOMBRE_BASE_DATOS');
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;Reglas para la creación de usuarios en ProxySQL
- Hay que indicar un «default_hostgroup» siempre. Y siempre será el hostgroup de escritura.
- Hay que indicar un «default_schema» siempre, sobre todo si el usuario se puede conectar a múltiples bases de datos. Si no da problemas para visualizar las bases de datos.
- «transaction_persistent» debe estar siempre a «1» (es el valor por defecto del campo). Esto obliga a que en una transacción siempre realice las consultas en el mismo hostgroup.
Copiar usuarios de MySQL a ProxySQL
La siguiente consulta genera las queries necesarias para crear los usuarios en ProxySQL copiando las mismas:
select DISTINCT
CONCAT(
'REPLACE INTO mysql_users (username, password, active, default_hostgroup, default_schema, transaction_persistent, backend, frontend) VALUES ("',
SUBSTRING_INDEX(REPLACE(s.GRANTEE,"'",''),'@',1), '","', u.authentication_string, '",1,1,"', s.TABLE_SCHEMA, '",1,1,1);') from information_schema.schema_privileges s
inner join mysql.user u on u.User = SUBSTRING_INDEX(REPLACE(s.GRANTEE,"'",''),'@',1) AND u.Host = SUBSTRING_INDEX(REPLACE(s.GRANTEE,"'",''),'@',-1)
WHERE s.TABLE_SCHEMA NOT IN ('sys','mysql') GROUP BY s.GRANTEE;Una vez que has ejecutado esta consulta hay que copiar las queries que se generan y pegarlas en ProxySQL. Una vez ejecutadas las consultas hay que guardar los datos con:
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;
Crear reglas
La gran ventaja de ProxySQL es que permite repartir las consultas en base a unas reglas que podemos definir.
Para definir estas reglas solo hay que insertar los registros en la tabla «mysql_query_rules». Por ejemplo vamos a indicar que para las consultas que empiecen por «SELECT» (consultas de lectura) se ejecuten en el hostgroup de lectura (el 2). Pero existe la cláusula «FOR UPDATE» que permite preparar un conjunto de filas para una actualización en una consulta posterior, lo cual tenemos que tener en cuenta, ya que las consultas con «FOR UPDATE» deben ejecutarse en el hostgroup de escritura (el 1).
Por tanto deberíamos crear dos reglas tal que:
insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (10000,1,"^SELECT.*FOR UPDATE",1,1), (10001,1,"^SELECT",1,1);
Con estas reglas le estamos indicando que compruebe primero si la consulta coincide con una consulta que empiece por «SELECT» y tenga la cláusula «FOR UPDATE», si no pasará a la siguiente, que comprobará simplemente si la consulta comienza por «SELECT».
Hay que tener en cuenta que las reglas tienen prioridad, y que ProxySQL las aplicará en base al valor indicado en el campo «rule_id» de menor a mayor. Es decir, cuanto menor sea el valor, más prioritaria será la regla. Por ejemplo, si quieres aplicar una regla para un usuario en concreto, porque sus consultas deben dirigirse a un hostgroup diferente al genérico, esta regla debe estar antes que la regla genérica, y por supuesto, en esa regla se debe indicar el nombre del usuario:
insert into mysql_query_rules(rule_id,active,username,match_digest,destination_hostgroup,apply) values (50,1,"userspecial","^SELECT.*FOR UPDATE",1,1), (51,1,"userspecial","^SELECT",3,1);
Con estas reglas podemos redirigir las consultas «SELECT» del usuario «userspecial» a un hipotético hostgroup 3. Fíjate como ha sido necesario volver a indicar la regla para la cláusula «FOR UPDATE». Y es que si no lo hicieramos así todas las consultas con «SELECT * FOR UPDATE» se ejecutarían en el hostgroup 3, ya que la regla para el «SELECT» del usuario «userspecial» es más prioritaria que la genérica para la clausula «FOR UPDATE».
Una vez creadas las reglas debemos actualizarlas en memoria y guardarlas en disco:
load mysql query rules to runtime; save mysql query rules to disk;
ProxySQL nos ofrece diferentes formas de crear reglas:
- https://proxysql.com/documentation/main-runtime/#mysql_query_rules
- https://www.percona.com/blog/proxysql-applying-and-chaining-the-rules/
Consultas comunes
Listar las variables de configuración
select * from global_variables;
Listar las instancias de MySQL vinculadas con ProxySQL
select * from mysql_servers;
Listar los clusters y hostgroups de lectura y escritura
select * from mysql_replication_hostgroups;
Listar reglas
select * from mysql_query_rules;
Comprobar las instancias de solo lectura
select * from monitor.mysql_server_read_only_log order by time_start_us desc limit 3;
Activar los logs de consultas
update global_variables set variable_value = 'queries.log' where variable_name = 'mysql-eventslog_filename'; update global_variables set variable_value = 1 where variable_name = 'mysql-eventslog_default_log'; update global_variables set variable_value = 2 where variable_name = 'mysql-eventslog_format'; load mysql variables to runtime; save mysql variables to disk;
Los ficheros creados se guardarán en /var/lib/proxysql.
A continuación hay que activar los logs en las reglas de consultas. Puedes activarlo en todas las reglas, en solo algunas, o crear una regla específica en la que se activen los logs:
update mysql_query_rules set log = 1 where rule_id = 20;
Corrección de errores
Al conectar da un error con una variable que está deprecada en MySQL 8
Las variables ‘query_cache_*’ son las que pueden dar el problema. Para solucionarlo hay que actualizar la versión de mysql en las variables de configuración de ProxySQL:
update global_variables set variable_value='8.x' where variable_name='mysql-server_version';
No se monitoriza la salud de alguna instancia de MySQL desde ProxySQL
Comprueba que en la instancia de MySQL existe el usuario «monitor». Si no existe crealo:
CREATE USER 'monitor'@'%' IDENTIFIED BY 'password'; GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
Al momento de escribir este post, la contraseña del usuario «monitor» se puede consultar en ProxySQL:
select * from mysql_users where username = 'monitor';
Comprueba también que las variables de configuración en ProxySQL «mysql-monitor_username» y «mysql-monitor_password» tiene los datos del nombre del usuario y contraseña del usuario «monitor». Si no están correctamente modificalos:
update global_variables set variable_value='nombreusuario' where variable_name='mysql-monitor_username'; update global_variables set variable_value='password' where variable_name='mysql-monitor_password'; load mysql variables to runtime;
Se escriben cambios en el hostgroup de lectura aunque está indicado para que solo sea de lectura
Esto puede ocurre en ciertos casos:
- No se ha indicado en la configuración de MySQL de la instancia de lectura la opción «read-only=ON». Compruébalo, añádela si es necesario y reinicia el servicio.
- Si la configuración del servidor está correcta probablemente falte cargar los cambios de los servidores a la memoria de ProxySQL y guardarlos en disco:
load mysql servers on runtime; save mysql servers to disk;
Comprueba que ProxySQL ha detectado correctamente la instancia de solo lectura:
Admin> select * from monitor.mysql_server_read_only_log order by time_start_us desc limit 3; +-----------+------+------------------+-----------------+-----------+-------+ | hostname | port | time_start_us | success_time_us | read_only | error | +-----------+------+------------------+-----------------+-----------+-------+ | 10.0.0.01 | 3306 | 1701453016161246 | 354 | 0 | NULL | | 10.0.0.02 | 3306 | 1701453016161221 | 420 | 1 | NULL | | 10.0.0.02 | 3306 | 1701453014161130 | 412 | 1 | NULL | +-----------+------+------------------+-----------------+-----------+-------+
En el log de proxysql aparece el error «MySQL_Monitor.cpp:7679:monitor_replication_lag_process_ready_tasks(): [ERROR] Replication lag on server is NULL»
Este error se debe a que el servidor tiene configurada la opción «max_replication_lag» a un valor superior a cero. Si el servidor actúa como fuente este valor debería estar a cero.
Comprueba los servidores con:
select * from mysql_servers;
Te devolverá el listado de servidores. Fijate en la columna «max_replication_lag», si alguno tiene un valor superior a cero y es un servidor que actúa de fuente o no es una réplica no debería tener un valor mayor a cero.
Para corregirlo solo hay que actualizar el valor:
update mysql_servers set max_replication_lag = 0 where hostgroup_id = 1; load mysql servers to runtime; save mysql servers to disk;
Si el servidor actúa solo como réplica y da este error, posiblemente esté parada por alguna otra razón, tendrás que revisar su estado y/o el mensaje de error que esté dando.
Un usuario da error de acceso denegado en ProxySQL
ProxySQL necesita conocer el usuario y las credenciales de acceso de los usuarios de MySQL. No necesita los permisos, solo el usuario, la contraseña y la base de datos por defecto a la que tiene acceso.
Para poder modificar o crear un usuario en ProxySQL hay que usar la consulta que se muestra en el apartado de creación de usuarios de este mismo post:
select DISTINCT
CONCAT(
'REPLACE INTO mysql_users (username, password, active, default_hostgroup, default_schema, transaction_persistent, backend, frontend) VALUES ("',
SUBSTRING_INDEX(REPLACE(s.GRANTEE,"'",''),'@',1), '","', u.authentication_string, '",1,1,"', s.TABLE_SCHEMA, '",1,1,1);') from information_schema.schema_privileges s
inner join mysql.user u on u.User = SUBSTRING_INDEX(REPLACE(s.GRANTEE,"'",''),'@',1) AND u.Host = SUBSTRING_INDEX(REPLACE(s.GRANTEE,"'",''),'@',-1)
WHERE s.TABLE_SCHEMA NOT IN ('sys','mysql') GROUP BY s.GRANTEE;Esta consulta generará un registro por usuario, cada registro tendrá una consulta «REPLACE INTO» para que pueda ser usada tanto si el usuario existe como si no.
Busca el usuario que quieras insertar y copia el «REPLACE INTO». Por ejemplo:
REPLACE INTO mysql_users (username, password, active, default_hostgroup, default_schema, transaction_persistent, backend, frontend) VALUES ("myuserdb","*estoesunpasswordcifrado",1,1,"MYDB",1,1,1);Logueate en ProxySQL como administrador y pega la consulta. Una vez ejecutada guarda los cambios con:
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;
Valida que el usuario ahora sí puede acceder.
Conclusión
ProxySQL es una solución robusta y flexible para mejorar el rendimiento y la disponibilidad de bases de datos MySQL, especialmente en entornos de alta demanda. Su capacidad para balancear la carga, gestionar conexiones y enrutar consultas de forma avanzada lo convierte en una herramienta esencial para arquitecturas escalables. Su instalación es relativamente sencilla, y una vez implementado, puedes configurarlo para ajustarse a las necesidades específicas de tu infraestructura. Si trabajas con bases de datos MySQL, MariaDB o Percona, y estás buscando una solución para optimizar su rendimiento, ProxySQL es definitivamente una opción a considerar.
