O MySQL e a Resolução de Nomes

O MySQL e o DNS (servidor de nomes – domain name server) são dois caras que não se entendem muito bem não.  E, para reduzir esta briga ao máximo é preciso compreender como o MySQL interpreta e trata a resolução de nomes.

Primeiro, é importante dizer que a maioria das aplicações que rodam hoje em dia são do tipo servidor, ou seja, o cliente (usuário) executa a aplicação em um servidor de aplicação e não, diretamente, em seu desktop.

É razoável assumir que servidores de aplicação tenham IP fixo, ao contrário de desktops que, normalmente, em grandes redes tem IP dinâmico fornecido por um servidor DHCP.

Quando uma nova sessão (ou thread) é iniciada a partir de uma requisição de um cliente (normalmente, um servidor de aplicação) o MySQL precisa tomar várias providências, a que mais no interessa neste momento é a autenticação, validação do usuário.

O MySQL, então, verifica se o usuário existe, é válido. Um usuário válido é tão, somente, que ele exista na tabela mysql.user. E aí sim, começam os problemas com DNS. O MySQL então irá solicitar ao DNS que retorne o IP do nome do host, e, também o nome do host do IP de conexão, usando-se as funções: gethostbyaddr() e gethostbyname.

Até aí, e eu com isso? O que eu, DBA, tenho a ver com DNS? Ah, meu amigo. O DBA tem que se preocupar sim, pois, veja o tamanho do problema que podemos enfrentar.

O MySQL quer por que quer, saber de onde partiu a conexão, a requisição, para poder criar uma sessão (thread) para este usuário. Portanto, ele acha razoável pegar o hostname e o IP do computador de origem, e, solicitar ao DNS mais próximo que resolve estes nomes, fornecendo: hostname, IP, e Reverso (que normalmente é um FQDN – Full Qualified Domain Name). Não deixa de ser uma medida de segurança adicional.

Servidores DNS foram feitos para serem rápidos, mas, falham aos montes. Isto é notório e sabido. Rede maior ou menor, pouco ou muita requisição na porta 53 (porta do DNS), eles falham. Servidores DNS atendem a muitas requisições, e, não têem qualquer controle sobre a entrega do resultado. Falham! Não vamos aqui discutir os motivos e os pontos de falha, afinal, não é minha e talvez nem a sua praia. Mas, que falham, ah sim, eles falham. E mais, podem demorar a nos responder.

Comportamento típico de um servidor MySQL que está sofrendo com DNS: a) demora para autenticar usuário (pode-se notar várias threads com o status UNAUTHENTICATED USER (visível através do comando SHOW PROCESSLIST no console, ou, qualquer outra ferramenta de monitoração); ou, b) Usuários não conseguem se conectar.

“Usuários não conseguem se conectar”. O MySQL no intuito de evitar questionar o servidor DNS a toda nova conexão, ele mantém um registro dos últimos 128 IP’s & Hostnames em um CACHE chamado HOST CACHE. Este HOST CACHE também registra falhas de conexão, tentativas de conexão com erro (um destes erros pode ser senha inválida). Caso ultrapasse o número de conexões com erro estipulado no arquivo my.cnf, senhas erradas, etc, o IP & Hostname é marcado como RUIM (blacklist). E partir deste momento, não será permitido nenhuma conexão a partir daquela fonte. De duas uma: ou reinicializamos o servidor MySQL (que mau!), ou, executamos o comando FLUSH HOSTS, que irá “zerar” o HOST CACHE. Vida nova! O ruim disto é que o MySQL, agora, terá de se socorrer do DNS, novamente, e… segura peão!

Más notícias! Não existe forma como verificar quais são os IP’s & Hostnames gravados no HOST CACHE, tão pouco, sua situação: blacklist ou não. Seria tão simples ter uma comandinho SHOW HOST CACHE, SHOW CACHE HOST, ou até quem sabe um comandinho: MOSTRA AÍ PÔ… Seria legal ver os gringos usar estes acentos!

Agora, para os mais corajosos. Existe sim um plugin ou, um patch, que poderá lhe trazer esta funcionalidade. É lógico que eu não recomendo, a não ser que voce assuma que nunca mais irá usar os binários oficiais. Para quem quiser interesse, segue o link do plugin-patch-mágicoso:

http://jcole.us/blog/archives/2006/09/26/followup-on-ips-hostnames-and-mysql/

Eu usei só de farra, e, confesso que ficou bacana. Mas, ainda, sou fã do bom e velho binário fornecido pela Oracle/Sun/Mysql. É importante frisar que está documentando a solicitação de um comando SHOW HOST CACHE dentro da comunidade do MySQL, é questão de tempo termos esta funcionalidade. Faria muito mais sentido, em minha opinião que isto fosse disponibilizado na forma de uma tabela no catálogo do MySQL: INFORMATION_SCHEMA.

UNAUTHENTICATED USER é um problema sério. Neste ano de 2011, vivenciei experiências ruins. As famosas “até ontem funcionava tudo normal, hoje ninguém consegue se conectar no banco”. Tudo porque o DNS ficou maluco, saturou, deixou de responder, ou por pura viadagem, resolveu sacanear o banco.

Contar com o DNS a cada conexão pode ser traumático. Lembre-se, por mais que exista um HOST CACHE para evitar excesso de requisição ao DNS, não temos qualquer gerência sobre este cache, na verdade, nem sabemos o que tem dentro desta caixa preta.

Lembre-se ainda que, a maioria das aplicações ainda não descobriram a tal da conexão persistente. Então, cada novo comando é uma nova conexão. E a cada nova conexão, em teoria é uma nova requisição ao servidor DNS.

Agora, faça as contas. Um pouco de matemática nunca é demais. Vamos esquecer por um momento dois caches: HOST CACHE e THREAD CACHE. Quantos comandos por segundo são executados contra todos os servidores MySQL sob sua jurisdição?

5.000 QPS (queries por segundo)? 10.000 QPS? Em continha de português, só o banco de dados entope o servidor DNS com esta quantidade de requisições por segundo. Fora todo o resto da rede e de tantas outras aplicações que precisam fazer uso da resolução de nome.

Aonde eu quero chegar? Um servidor DNS já tem muito trabalho, e, se o MySQL ainda entupir ele com mais milhares de requisição por segundo, teremos problemas.

Resumão: Como o MySQL usa o DNS

cliente faz nova requisição de conexão -> Host Cache (consulta se já existe) -> DNS -> Host Cache (atualiza/insere) -> Autentica usuário (usuário + senha + host)

Dicas: Como apaziguar o MySQL e o DNS

  • Evite usar DHCP para servidores de aplicação. O IP pode mudar, e, entrar em conflito com o IP e Hostname armazenado no HOST CACHE;
  • Se não consegue conectar ao MySQL com determinado usuário e senha, e, tem certeza de que está tudo correto: FLUSH HOSTS;
  • Excesso de threads com UNAUTHENTICATED USER, demora para conectar, precisa usar o comando FLUSH HOSTS com frequência, utilize a opção skip-name-resolve no arquivo de configuração do MySQL. Esta operação necessita de reinicialização do servidor MySQL (mysqld) para que tenha efeito.

Implicação do uso da opção skip-name-resolve

Reforço que após escrever skip-name-resolve no seu arquivo de configuração do MySQL (my.cnf ou my.ini) é preciso reiniciar o servidor MySQL (só o serviço) para que tenha efeito.

Esta opção/configuração irá fazer com o MySQL desconsidere o uso do DNS. Também não existirá no HOST CACHE a equivalência IP <-> Hostname. Obviamente, haverá um ganho no tempo de conexão. As conexões ao banco ocorrerão de forma mais rápida. Contudo, ao criar um usuário não se deverá mais utilizar a forma <usuário>@<hostname>. A partir do uso de skip-name-resolve, os usuários novos e antigos deverão ser/estar registrados no formato: <usuário>@<ip>.

Minha recomendação é pelo uso de skip-name-resolve. Resolve uma série de problemas não tratados neste artigo, diminui o tempo de conexão, diminui a quantidade de tarefas do servidor MySQL durante a autenticação, e, extrai um ponto de falha (DNS).

 

 

 

 

 

12 comentários em “O MySQL e a Resolução de Nomes”

  1. Alexandre, boa tarde!

    Cara tive esse tipo de problema em clientes que utilizam TS. Na ocasião como todas as conexões provinham do mesmo ip, coloquei o IP do servidor TS dentro do arquivo hosts no servidor Linux. O problema foi resolvido, nunca mais voltaram a aparecer treads como UNAUTHENTICATED USER, mas pelo que você descreve a coisa vai bem além disso.
    Muito bom o artigo a partir de agora passamos a conhecer e entender como funciona todo o processo na integra.

    Abraços,

    William Prudente

  2. Olá, eu sei que o post é bem antigo, mas eu to com um problema que eu não consigo resolver. Eu liberei o acesso remoto para um usuario, só que não conecta de jeito nenhum, eu acho que eu já fiz tudo que eu encontrei, comentar a linha bind-address, ja dei todos os privilegios e continuo com o erro:
    ERROR 1045 (28000): Access denied for user ‘xxxxxx’@’179.111.225.163’ (using password: YES)
    ja troquei senha, ja deletei o usuario e crie de novo, então eu acho que pode ser que o ip esteja bloqueado no mysql, como eu achei o seu post muito interessante sobre isso, talvez você possa me ajudar por favor.

  3. Alexandre, muito bom o seu post, tenho uma app antiga usando o mysql 4.x, sabe como faço para confugurar o skip-name-resolve? Eu o inseri no my.cnf, mas acho que na versão 4.x essa opção dentro do my.cnf não é valida.

Deixe um comentário

O seu endereço de e-mail não será publicado.

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.