High Availability

MySQL Load Balancing com 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).

ProxySQL – Sincronizar / Importar usuários

No meu último post expliquei alguns fundamentos básicos do ProxySQL. Hoje eu vou falar como podemos importar os usuários do MySQL para o ProxySQL.

Para realizar esta tarefa, vamos usar o proxysql-admin uma ferramenta criada pela Percona para auxiliar na administração do proxy. Se vocês está utilizando o ProxySQL da percona a ferramenta já vem instalada por padrão, caso contrário, pode baixar diretamente do repositório no Github.

Vamos utilizar a opção --syncusers que vai se conectar no servidor MySQL indicado, vai buscar todos os usuários na tabela mysql.user e vai importar eles para o ProxySQL:

proxysql-admin --cluster-username=[mysql-user] --cluster-password=[mysql-password] --cluster-port=[mysql-port] --cluster-hostname=[mysql-host] --syncusers

A ferramenta foi criada originalmente para aumentar a compatibilidade entre o ProxySQL e o Percona XtraDB Cluster, então a terminologia dosparâmetros e mensagens vão sempre mencionar o cluster, porém a funcionalidade descrita deste post também é 100% compatível com Master Slave.

Aqui vai um exemplo do comando em prática:

$ proxysql-admin --cluster-username=root --cluster-password=sekret --cluster-port=3306 --cluster-hostname=192.168.112.61 --syncusers

Syncing user accounts from Percona XtraDB Cluster to ProxySQL

Synced Percona XtraDB Cluster users to the ProxySQL database!

Vale lembrar que toda a vez que um usuário é adicionado ou troca a senha, temos que também atualizar este usuário no proxysql. Com o proxysql-admin basta rodar o --syncusers e a ferramenta se encarrega do resto.

Desmistificando ProxySQL

Fala galera. Neste post vou falar um pouco sobre o ProxySQL . O ProxySQL é um proxysql para que implementa o protocolo do MySQL o que lhe permite fazer coisas que outros proxies não fazem. Ele vem ganhando muita atenção nos últimos tempos e é capaz de integrar com vários produtos do ecossistema MySQL, como Replicação (master – slave), Percona XtraDB Cluster e Group Replication. Uma  das funcionalidades mais úteis (que faz o produto se destacar perante os demais) é a habilidade de escalar escritas para os slaves , ele é capaz de identificar que tipo de query está sendo recebida (Escrita ou Leitura) e enviar a query para o master ou para o(s) slave(s). Isso tudo sem ter que alterar uma linha de código na aplicação.

Neste artigo vou abordar alguns pontos importantes para entendermos como o proxy funciona.

Instalação

A Instalação pode ser feita das seguintes maneiras:

  1. Baixando a os pacotes para a sua versão de sistema operacional (rpm / deb) do repositório oficial no github https://github.com/sysown/proxysql/releases :
    sudo yum install https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql-1.4.9-1-centos7.x86_64.rpm
    
  2. Baixando a os pacotes para a sua versão de sistema operacional (rpm / deb) do site da Percona https://www.percona.com/downloads/proxysql/ :
    sudo yum install https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql-1.4.9-1-centos7.x86_64.rpm
  3. Instalando o repositório da Percona para a sua versão de sistema operacional (preferido):
    sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
    sudo yum install proxysql

 

 

sudo yum install https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql-1.4.9-1-centos7.x86_64.rpm

Agora só precisamos iniciar o serviço

sudo service proxysql start 

Interfaces

ProxySQL faz uma divisão da área de administração do proxy com a área em que as aplicações conectam e executam queries. A área de administração escuta na porta 6032 e a área  para as aplicações escuta na 6033 (3306 ao contrário).

Camadas

Outra parte importante para entender como o proxy funciona são as camadas que ele utiliza, vou utilizar parte do diagrama oficial (disponível aqui) para explicar:

+-------------------------+
|      1. RUNTIME         |   
+-------------------------+
       /|\          |
        |           |
        |           |
        |          \|/
+-------------------------+
|     2.  MEMORY          |   
+-------------------------+ 
       /|\          |      
        |           |      
        |           |       
        |          \|/      
+-------------------------+  
|     3.   DISK           |  
+-------------------------+  

ProxySQL sempre vai ler as informações da camada 1.Runtime que reside em memória.
Quando conectamos na porta 6032 sempre vamos estar conectados a camada 2.Memory, que como o nome diz, reside em memória.
E Por final, temos a camada 3.Disk, como as demais camadas residem em memória, precisamos de uma camada para persistir os dados em disco.

Qual o benefício deste layout? Ele nos permite realizar várias alterações e aplicar elas somente no momento em que terminamos  todas elas, podemos fazer uma analogia a transações, aonde depois de executar várias queries, executamos um commit.
Quando o proxysql iniciar, ele lê os dados do disco para a memória e lê os dados da memória para runtime. Quando realizarmos uma alteração, vamos estar manipulando a memória, vamos daí executar um comando LOAD MYSQL [SERVERS | USERS | QUERY RULES] TO RUNTIME para ler os dados da memória para runtime, e conseqüentemente, vamos executar um SAVE MYSQL [SERVERS | USERS | QUERY RULES] TO DISK para salvar os dados da memória para o disco.

Hostgroups

ProxySQL agrupa servidores em algo chamado hostgroup. Em uma topologia em que temos um master e dois slaves, vamos criar o HostGroup(HG) 1 e especificar que o master faz parte dele, e vamos criar o HG2 e especificar que ambos os slaves fazem parte dele. A criação de HG é feita juntamente com a inserção de servidores na tabela mysql_servers e não existe uma numeração fixa. Você pode atribuir a número/ID que quizer. Posteriormente, vamos configurar usuários e regras para queries que vão identificar se a query é uma leitura, ela deve ser direcionada para o HG 2 (leitura / aonde estão nossos slaves) e o proxysql vai enviar a query para um dos servidores presentes neste HG. Caso a query não seja uma leitura, ela vai ser direcionada para o HG de escrita.

Autenticação

ProxySQL possui funcionalidades como firewall por exemplo, em que você pode bloquear uma query de ser executada. Uma vez que o proxy identifica esta query, ele retorna uma mensagem de erro, sem mesmo ter enviado ela para algum hostgroup. Para que funcionalidades como esta sejam possíveis o proxy requer uma autenticação além da autenticação no servidor do MySQL. Para que um usuário do MySQL possa conectar via ProxySQL temos que criar o mesmo no proxy.

 

 

Fica aqui um link de uma apresentação que fiz a um tempo atrás referente a algumas features do ProxySQL – https://www.slideshare.net/MarceloAltmann/proxy-sql-para-mysql

Agora que você entendeu um pouco de como funciona o ProxySQL, já pode começar a utilizar / testar em suas aplicações.