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:
- ProxySQL (192.168.112.60)
- Node1 – Master (192.168.112.61)
- Node2 – Slave-1 (192.168.112.62)
- 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).