Menu fechado

ProxySQL Tutorial – Master e Slave

Seguindo a série de blog posts sobre ProxySQL. Hoje vamos falar de como podemos colocar o proxysql em frente ao seu conjunto de master e slave(s) e distribuir escritas para os slaves sem ter que alterar o código da aplicação. Você pode entender mais sobre como instalar o ProxySQL e seus conceitos básicos aqui.

Neste tutorial, vamos utilizar 4 servidores:

  1. ProxySQL (192.168.112.60)
  2. Node1 – Master (192.168.112.61)
  3. Node2 – Slave-1 (192.168.112.62)
  4. Node3 – Slave-3 (192.168.112.63)

Servidores

Com o proxysql devidamente instalado vamos nos conectar na interface de administração e vamos adicionar nossos três servidores e configurar o Replication Hostgroup. Basicamente vamos dizer ao ProxySQL qual vai ser o HG do master e do slave. Lembrando que o ProxySQL diferencia master e slave baseado na variável read_only:

$ mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='proxysql> '

#proxysql> 
INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (20, '192.168.112.61');
INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (20, '192.168.112.62');
INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (20, '192.168.112.63');
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment) VALUES (10, 20, 'Master / Slave App 1');
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

Quando verificamos a nossa lista de servidores, todos ainda estão no HG 20, inclusive o master:

proxysql> SELECT hostgroup_id, hostname, status FROM mysql_servers;
+--------------+----------------+--------+
| hostgroup_id | hostname | status |
+--------------+----------------+--------+
| 20 | 192.168.112.61 | ONLINE |
| 20 | 192.168.112.63 | ONLINE |
| 20 | 192.168.112.62 | ONLINE |
+--------------+----------------+--------+
3 rows in set (0.00 sec)

Existe uma thread no ProxySQL que é responsável por ser conectar em cada servidor listado na tabela mysql_servers e checkar o valor da variável read_only. Na tabela mysql_server_read_only_log podemos verificar o log destes checks:

proxysql> SELECT * FROM mysql_server_read_only_log LIMIT 3;
+----------------+------+------------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+----------------+------+------------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------+
| 192.168.112.61 | 3306 | 1529175123875168 | 0 | NULL | timeout on creating new connection: Access denied for user 'monitor'@'192.168.112.60' (using password: YES) |
| 192.168.112.62 | 3306 | 1529175123876409 | 0 | NULL | timeout on creating new connection: Access denied for user 'monitor'@'192.168.112.60' (using password: YES) |
| 192.168.112.63 | 3306 | 1529175123877369 | 0 | NULL | timeout on creating new connection: Access denied for user 'monitor'@'192.168.112.60' (using password: YES) |
+----------------+------+------------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Como podemos ver acima, o Proxy não está conseguindo conectar aos nossos servidores. Por questões de segurança, vamos alterar o usuário que o proxy utiliza para realizar este check:

UPDATE global_variables SET variable_value='p_monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='M0n170Rpwd!' WHERE variable_name='mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

Agora temos que criar este usuário no nosso master:

CREATE USER p_monitor@192.168.112.60 IDENTIFIED BY 'M0n170Rpwd!';

Com o usuário devidamente criado no MySQL, podemos verificar na interface admin do ProxySQL que agora temos 4 servidores:

proxysql> SELECT hostgroup_id, hostname, status FROM mysql_servers ORDER BY hostgroup_id;
+--------------+----------------+--------+
| hostgroup_id | hostname | status |
+--------------+----------------+--------+
| 10 | 192.168.112.61 | ONLINE |
| 20 | 192.168.112.61 | ONLINE |
| 20 | 192.168.112.62 | ONLINE |
| 20 | 192.168.112.63 | ONLINE |
+--------------+----------------+--------+
4 rows in set (0.01 sec)

O nosso Master está no tanto no HG 10 (escrita) e no HG20 (leitura). Podemos remover o master do HG de leitura configurando a variável mysql-monitor_writer_is_also_reader para falso (ela vem ativada por padrão).

Usuários

Próximo passo é criar no ProxySQL o usuário que a aplicação utiliza para conectar no banco de dados. Podemos criar ele manualmente ou importar todos os usuário do MySQL para o ProxySQL como descrito neste post. Neste tutorial, vou criar o usuário manualmente. Lembrando que o usuário deve ser/estar criado também no MySQL com os devidos GRANTS aplicados :

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('marcelo', 'marcelo', 10);
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;

Reparem que se rodarmos um select na tabela mysql_users vamos ver o password do usuário em plaintext:

proxysql> SELECT username, password FROM mysql_users;
+----------+----------+
| username | password |
+----------+----------+
| marcelo | marcelo |
+----------+----------+
1 row in set (0.00 sec)

Porém a tabela runtime_mysql_users vai ter computado o hash para o password. Por questões de segurança, toda a vez que criarmos um usuário manualmente no ProxySQL e inserirmos o password em plaintext, devemos rodar um comando extra pra que a versão da tabela de runtime, seja salva também no banco de dados main, e consequentemente, vamos salvar da main para o disco novamente para que o a versão com hash do password persista caso o serviço do ProxySQL seja reiniciado:

proxysql> SAVE MYSQL USERS FROM RUNTIME; SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

proxysql> SELECT username, password FROM mysql_users;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| marcelo | *8E36BAA4C91256FAEF957292B1C224C102754D25 |
+----------+-------------------------------------------+
1 row in set (0.00 sec)

Regras

Pronto? Quase lá. Agora temos que criar a mágica!!!

Toda a vez que o usuário marcelo se conectar ao ProxySQL, ele vai mandar todas as queries para o HG 10 (configurado no campo default_hostgroup da tabela mysql_users). O HG 10 é um mapeamento para o nosso servidor master. Agora temos que instruir o ProxySQL a redirecionar todos os nossos comandos SELECT para o HG 20 que está configurado a fazer um balanceamento em todos os nossos servidores, incluindo os slaves. Vamos fazer esta configuração na tabela mysql_query_rules . Existe uma pequena excessão que temos que levar em consideração que são os comandos SELECT . . . FOR UPDATE , pois este comando é executado quando queremos colocar um lock na linha que estamos lendo, com o intuito de alterar esta linha, então temos que tomar o cuidado para que este select seja enviado também ao HG 10:

INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,match_digest,apply) VALUES(1,'marcelo',10,1,'^SELECT.*FOR UPDATE',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,match_digest,apply) VALUES(2,'marcelo',20,1,'^SELECT ',1);
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

Pronto, agora o ProxySQL já está configurador para distribuir os selects que são seguros para os slaves. Tudo que temos que fazer agora é configurar nossas aplicações para se conectar no proxy ao invés de conectar diretamente no servidor. Lembrando que por padrão a porta do ProxySQL para aplicação é a 6033.

Abaixo segue um gráfico coletado com o PMM mostrando a diferença de somente o master servindo o tráfico contra o momento em que o proxy começa a distribuir as queries:

Como podemos ver, node1(Master) vinha com um tráfego bem alto quando comparado aos outros dois servidores, a partir do momento que que o ProxySQL iniciou a distribuir o tráfego, a distribuição de tráfego ficou homogênea nos 3 servidores, o mesmo pode se observar no gráfico de load dos servidores.

Failover

ProxySQL será capaz de identificar quando um failover acontece. Assim que o master estiver indisponível e algum slave for promovido para master, o proxy irá identificar que o slave não está mais com a variável read_only configurada e irá mover o slave para o HG 10.

Importante: O ProxySQL é somente um proxy responsável por redirecionar o tráfico e capaz de identificar que um failover aconteceu, ele não irá fazer o failover automático como executar um CHANGE MASTER TO nos demais slaves.

 

Slaves atrasados

ProxySQL pode parar de enviar trafego para um slave se ele estiver atrasado por mais de X segundos. Para que o proxy consiga verificar o status do slave, o usuário utilizado pela thread de monitor precisa do grant  REPLICATION CLIENT.  Podemos adicionar este grant com o comando abaixo:

GRANT REPLICATION CLIENT ON *.* TO p_monitor@192.168.112.60

Agora, vamos configurar o ProxySQL para não enviar mais trafego para servidores atrasados por mais de 10 segundos:

UPDATE mysql_servers SET max_replication_lag = 10 WHERE hostgroup_id=20;
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

Pronto, agora toda vez que o slave atrasar, ele terá seu status alterado para SHUNNED na tabela runtime_mysql_servers:

proxysql> SELECT * FROM runtime_mysql_servers;
+--------------+----------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 192.168.112.61 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.168.112.61 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 20 | 192.168.112.63 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 20 | 192.168.112.62 | 3306 | SHUNNED | 1 | 0 | 1000 | 10 | 0 | 0 | |
+--------------+----------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

Podemos verificar o atraso que o ProxySQL recebeu do slave consultado a tabela mysql_server_replication_lag_log:

proxysql> SELECT * FROM mysql_server_replication_lag_log WHERE hostname = '192.168.112.62' ORDER BY time_start_us DESC LIMIT 1;
+----------------+------+------------------+-----------------+----------+-------+
| hostname | port | time_start_us | success_time_us | repl_lag | error |
+----------------+------+------------------+-----------------+----------+-------+
| 192.168.112.62 | 3306 | 1529190811923215 | 953 | 604 | NULL |
+----------------+------+------------------+-----------------+----------+-------+
1 row in set (0.00 sec)

O atraso é monitorado a cada  mysql-monitor_replication_lag_interval milisegundos (10 segundos por padrão).

Gostou ? Ajude e Compartilhe!