Para este laboratorio realizaremos una configuración para conseguir un escensario como el siguiente
Es importante realizar los pasos para la configuración de cada servidor que se describen a continuación
5.1 Instalación
Ubuntu / Debian
Añadir el repositorio
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.5.x/repo_pub_key' | apt-key add -
echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list
apt-get update
apt-get install proxysql
Iniciamos el servicio
La configuración predeterminada inicial se puede encontrar en estos archivos
root@ip-172-31-51-244:~# systemctl status proxysql | grep cnf
Process: 8000 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
├─8002 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
└─8003 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
Jan 22 12:21:42 ip-172-31-51-244 proxysql[8000]: 2024-01-22 12:21:42 [INFO] Using config file /etc/proxysql.cnf
root@ip-172-31-51-244:~#
Por ejemplo, con las credenciales de la consola de administración, donde puedes cambiar la contraseña
ProxySQL Admin> SET admin-admin_credentials="test_admin:admin123;radminuser:radminpass";
Después de configurar las credenciales de administrador, aplique los cambios a la configuración del tiempo de ejecución
ProxySQL Admin> LOAD ADMIN VARIABLES TO RUNTIME;
ProxySQL Admin> SAVE ADMIN VARIABLES FROM RUNTIME;
Y podemos ver que está activo y escuchando en dos puertos TCP y dos sockets
# netstat -lpn | grep proxysql
root@ip-172-31-51-244:~# netstat -lpn | grep 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
root@ip-172-31-51-244:~#
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 usando un cliente mysql estándar; también necesita instalar el cliente MySQL
root@ip-172-31-51-244:~# apt install mysql-client-core-8.0
# mysql --protocol=tcp -P 6032 -uadmin -padmin --prompt='Admin >'
(...)
Server version: 5.5.30 (ProxySQL Admin Module)proxysql>
Admin >show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
Admin >select * from mysql_servers;
Empty set (0.00 sec)
5.2 Configuración
Agregue nodos del clúster a ProxySQL. Para configurar los nodos backend del clúster Percona XtraDB en ProxySQL, inserte los registros correspondientes en la tabla mysql_servers.
Este ejemplo agrega tres nodos del clúster Percona XtraDB al grupo de hosts predeterminado 11 y 12, 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 (3, '192.168.12.3', 3306, 'ONLINE', 1000,10000);
INSERT INTO mysql_servers (hostgroup_id, hostname, port,status, weight,max_connections) VALUES (3, '192.168.12.4', 3306, 'ONLINE', 1000,10000);
INSERT INTO mysql_servers (hostgroup_id, hostname, port,status, weight,max_connections) VALUES (3, '192.168.12.5', 3306, 'ONLINE', 1000,10000);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
5.3 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.
El siguiente ejemplo muestra cómo agregar un usuario de monitoreo en el Nodo 2
CREATE USER 'proxysql_monitor'@'%' IDENTIFIED WITH mysql_native_password by 'ProxySQLPa55';
GRANT USAGE ON *.* TO 'proxysql_monitor'@'%';
El siguiente ejemplo muestra cómo configurar 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';
Para cargar esta configuración en tiempo de ejecución, emita un comando LOAD. Para guardar estos cambios en el disco (asegurándose de que persistan después de que se cierre ProxySQL), emita un comando GUARDAR.
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
Para asegurarse de que la supervisión esté habilitada, consulte los registros de supervisión
ProxySQLAdmin> select * from runtime_mysql_servers order by 1,5;
+--------------+--------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2 | 192.168.12.5 | 3306 | 0 | ONLINE | 1000 | 0 | 10000 | 0 | 0 | 0 | |
| 2 | 192.168.12.3 | 3306 | 0 | SHUNNED | 1000 | 0 | 10000 | 0 | 0 | 0 | |
| 2 | 192.168.12.4 | 3306 | 0 | SHUNNED | 1000 | 0 | 10000 | 0 | 0 | 0 | |
| 3 | 192.168.12.3 | 3306 | 0 | ONLINE | 1000 | 0 | 10000 | 0 | 0 | 0 | |
| 3 | 192.168.12.4 | 3306 | 0 | ONLINE | 1000 | 0 | 10000 | 0 | 0 | 0 | |
| 3 | 192.168.12.5 | 3306 | 0 | ONLINE | 1000 | 0 | 10000 | 0 | 0 | 0 | |
| 4 | 192.168.12.3 | 3306 | 0 | ONLINE | 1000 | 0 | 10000 | 0 | 0 | 0 | |
| 4 | 192.168.12.4 | 3306 | 0 | ONLINE | 1000 | 0 | 10000 | 0 | 0 | 0 | |
+--------------+--------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
8 rows in set (0.02 sec)
5.4 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 (writer_hostgroup,reader_hostgroup,backup_writer_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind)
VALUES (2,4,3,1,1,1,1,100);
ProxySQLAdmin> 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 | 4 | 3 | 1 | 1 | 1 | 1 | 100 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)
5.5 Definir las reglas de consulta para la división R/W
Ahora es el momento de seguir adelante y 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 11, 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=12;
Segunda regla: las consultas con un patrón ^SELECT. FOR UPDATE siempre deben entregarse desde el grupo de hosts del escritor, por lo que configuramos el destino_hostgroup=11;
INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^SELECT.*',2, 0);
INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^SELECT.* FOR UPDATE',3, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
ProxySQLAdmin> SELECT rule_id, match_digest,destination_hostgroup FROM mysql_query_rules;
+---------+---------------------+-----------------------+
| rule_id | match_digest | destination_hostgroup |
+---------+---------------------+-----------------------+
| 100 | ^SELECT.*FOR UPDATE | 2 |
| 101 | ^SELECT | 3 |
+---------+---------------------+-----------------------+
2 rows in set (0.00 sec)
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);
Inserte los comandos creados en el paso anterior en proxysql en uno de los nodos
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
5.7 MySQL Configurar interfaces mysql
De forma predeterminada, las interfaces mysql están configuradas a continuación
show variables like 'mysql-interfaces';
+------------------+--------------+
| Variable_name | Value |
+------------------+--------------+
| mysql-interfaces | 0.0.0.0:6033 |
En caso de que desee configurar las interfaces mysql en 0.0.0.0:3306 o cualquier otro puerto, puede hacerlo como se muestra a continuación
proxysql> show variables like 'mysql-interfaces';
+------------------+--------------+
| Variable_name | Value |
+------------------+--------------+
| mysql-interfaces | 0.0.0.0:6033 |
+------------------+--------------+
SET mysql-interfaces='0.0.0.0:3306';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
PROXYSQL RESTART;
Ahora, cuando nos conectamos como cliente de aplicación a la instancia de ProxySQL
root@ip-172-31-51-244:~# mysql -h127.0.0.1 -P 6033 -umysqladmin -pthe_secure_password -e "select user,host from mysql.user where user='mysqladmin'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+------+
| user | host |
+------------+------+
| mysqladmin | % |
+------------+------+
Vuelva a verificar si nuestro equilibrio de carga funciona como se esperaba
root@Proxy:~# for seq in `seq 1 100`; do mysql --host=192.168.12.2 -u apps -pIt_is_so_Secure1 -P 3306 -BNe"select @@hostname;" 2> /dev/null ; done | sort | uniq -c
31 Node-1
31 Node-2
38 Node-3
root@Proxy:~#
Cómo conectar la aplicación a la base de datos a través de proxysql
Hostname: 167.172.249.178
Port: 3306
Estado de la replica slave
La configuración de la replicación es simple, puede seguir el siguiente enlace para seguir cómo configuré la replicación Streaming MySQL Backups with Percona XtraBackup – Another Alternative