Autenticação externa via PAM no MySQL Enterprise e Oracle Linux 7.4
O MySQL Enterprise Edition suporta um método de autenticação que permite que o MySQL Server use o PAM (Pluggable Authentication Modules) para autenticar usuários do MySQL.
O PAM pode ser visto como um subsistema do Linux e macOS que provê uma interface padronizada para autenticação. Ou seja, os aplicativos podem usar a interface do PAM para confirmar usuários e senhas. Pode-se configurar o PAM para vários métodos de autenticação, incluindo usuários e senhas armazenados localmente no próprio SO ou buscá-los remotamente em um diretório LDAP.
Com o plugin authentication_pam habilitado, o MySQL server vai delegar a autenticação dos usuários para o PAM. Você pode, por exemplo, usar os usuários/senhas do Sistema Operacional para fazer login no MySQL. Outro exemplo, se o SO e o PAM estiverem integrados a um serviço de identidade e autenticação, tais como Open LDAP ou Active Directory, o MySQL pode usar os usuários/senhas a partir destes serviços.
O plugin authentication_pam também tem suporte a usuários 'proxy'. Esta funcionalidade permite que o DBA mapeie as permissões de um usuário MySQL para um grupo definido externamente. Por exemplo, supondo que exista no diretório LDAP um grupo 'developers' contendo vários usuários, o DBA pode criar um único usuário 'developer' no MySQL com as devidas permissões e mapeá-lo com o grupo 'developers' do diretório LDAP (1 para n). Desta forma, o time de segurança que mantém o diretório LDAP pode adicionar, remover, alterar senhas dos desenvolvedores que vão acessar o MySQL sem que o DBA precise tocar no banco de dados.
Nota: se você quiser configurar o MySQL para conectar-se diretamente a um serviço de identidade e autenticação LDAP sem configurar o PAM e o Sistema Operacional, pode usar o plugin authentication_ldap, também disponível no MySQL Enterprise Edition. O MySQL Enterprise é um software comercial e sua licença para uso em produção deve ser adquirida junto à Oracle. Porém, você pode utilizá-lo em ambiente não-produtivo por 30 dias.
Abaixo veremos como configurar o plugin authentication_pam no Oracle Linux 7.4. Os passos são bastante similares para outras distribuições Linux e macOS. Consulte a documentação para mais detalhes.
Passo 1: habilitar o plugin authentication_pam no MySQL
Acidione ao arquivo my.cnf:
Confirme se o plugin está ativo:
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%pam%';
Passo 2: criar um módulo mysql no PAM
Crie um novo serviço mysql no PAM. Basta criar um arquivo com nome mysql no diretório de configuração do PAM (normalmente /etc/pam.d):
vi /etc/pam.d/mysql
# PAM-1.0 configuration file for the 'mysql' service
auth       include   password-auth
account    include   password-auth
O que fizemos ao usar o include foi repassar autenticação para outro serviço password-auth já definido no PAM. Se quiser ver em detalhes como está configurado, basta visualizar o conteúdo do arquivo /etc/pam.d/password-auth.
Note que este arquivo foi gerado automaticamente pela ferramenta authconfig. Uma maneira usual de integrar o Sistema Operacional a diferentes serviços de identidade e autenticação (LDAP, Activer Directory, etc.) é usar o System Security Services Daemon (SSSD).
Passo 3: garantir permissões do processo mysqld ao arquivo de usuários do SO
Em alguns Sistemas Operacionais, tal como o Oracle Linux 7.4, é usado o arquivo /etc/shadow para manter informações de acesso os usuários. O processo mysqld vai precisar de permissões de acesso a este arquivo para funcionar corretamente com o PAM.
Uma maneira de dar tais permissões é:
criar um grupo shadow;
dar permissões ao grupo para acessar /etc/shadow;
adicionar o usuário que vai rodar o processo mysqld (por padrão, o usuário mysql);
reiniciar o processo mysqld.
groupadd shadow
gpasswd -a mysql shadow
Neste ponto, a autenticação externa via PAM já deve estar funcionando.
Passo 4: mapear usuários do MySQL para autenticação externa
Opcão 1: mapeamento 1 para 1
Você pode mapear um usuário MySQL para um usuário externo. O usuário do MySQL vai definir os privilégios enquanto que a senha para login estará definida fora do MySQL.
Teste 1 para 1 Para testar a autenticação externa, criaremos um usuário myuser no Sistema Operacional e o mapearemos para um usuário no MySQL. O usuário do SO vai definir a senha enquanto que o usuário no MySQL vai definir quais dados terá acesso.
Crie o usuário myuser no Sistema Operacional e dê uma senha:
(opcional) Verifique se foi criado com sucesso com:
# su - myuser
uid=1001(myuser) gid=1001(myuser) groups=1001(myuser) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
No MySQL, crie um usuário mapeando as permissões para o usuário myuser:
CREATE USER 'myuser'@'localhost' IDENTIFIED WITH authentication_pam AS 'mysql';
GRANT SELECT ON *.* TO 'myuser'@'localhost';
Neste exemplo demos apenas permissões de SELECT ao usuário myuser.
Teste o acesso ao MySQL usando a senha que você definiu para o usuário myuser no SO:
# mysql -umyuser -p --enable-cleartext-plugin
Server version: 5.7.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Você pode verificar as informações do usuário autenticado com:
SELECT USER(), CURRENT_USER(), @@proxy_user;
+------------------+------------------+--------------+
| USER()           | CURRENT_USER()   | @@proxy_user |
| myuser@localhost | myuser@localhost | NULL         |
Ao final dos testes, remova os usuários do MySQL e do SO:
DROP USER 'myuser'@'localhost';
Opção 2: mapeamento 1 para n (grupos de usuários)
Você pode mapear um usuário MySQL para um grupo de usuários. O usuário do MySQL vai definir os privilégios e estará ligado a um grupo de usuários. Desta forma, os dados de quem serão os usuários pertencentes ao grupo e suas respectivas senhas estarão definidas e mantidos fora do MySQL.
Teste 1 para n Para testar a autenticação externa, criaremos no Sistema Operacional um grupo myusers contendo os usuários myuser1 e myuser2. Depois mapearemos o grupo myusers para um usuário MySQL priv_myusers.
No Sistema Operacional, crie o grupo myusers com os usuários myuser1 e myuser2:
groupadd myusers
passwd myuser1
gpasswd -a myuser1 myusers
adduser myuser2
passwd myuser2
gpasswd -a myuser2 myusers
No MySQL, garanta que não existe um usuário anônimo:
SELECT user, host FROM mysql.user WHERE user='';
No MySQL, crie um usuário proxy padrão ''@'' que irá mapear os usuários externos (PAM) para usuários internos (MySQL):
CREATE USER ''@'' IDENTIFIED WITH authentication_pam AS 'mysql, myusers=priv_myusers';
No MySQL, crie um usuário priv_myusers, mapeando as permissões para o grupo myusers:
CREATE USER 'priv_myusers'@'localhost' IDENTIFIED BY 'SecretPass1!';
GRANT PROXY ON 'priv_myusers'@'localhost' TO ''@'';
GRANT SELECT ON *.* TO 'priv_myusers'@'localhost';
Neste exemplo demos apenas permissões de SELECT no MySQL.
Teste o acesso ao MySQL usando a senha que você definiu para o usuário myuser1 no SO:
# mysql -umyuser1 -p --enable-cleartext-plugin
+-------------------+------------------------+--------------+
| USER()            | CURRENT_USER()         | @@proxy_user |
| myuser1@localhost | priv_myusers@localhost | ''@''        |
Teste também o acesso ao MySQL com o usuário myuser2:
# mysql -umyuser2 -p --enable-cleartext-plugin
| myuser2@localhost | priv_myusers@localhost | ''@''        |
DROP USER 'priv_myusers'@'localhost';
DROP USER ''@'';
userdel -r myuser1
userdel -r myuser2
O recurso de Autenticação Externa do MySQL Enterprise Edition permite gerenciar usuários e senhas fora do MySQL. É um recurso que pode facilitar a implantação de políticas de segurança, principalmente as que exigem gerenciamento de identidades por administradores que não sejam os DBAs.
Manual de referência do MySQL 5.7 - Authentication Plugins
Livro: Pro MySQL NDB Cluster
Adquiri recentemente o excelente livro Pro MySQL NDB Cluster na versão eBook. Faltava um guia definitivo, agora o temos.
O livro Pro MySQL NDB Cluster é escrito por dois engenheiros de suporte do time Oracle MySQL, Jesper W. Krogh e Mikiya Okuno. É um mergulho profundo de quase 700 páginas no produto, publicado pela Apress (em Inglês).
É importante enfatizar que há várias formas de trabalhar com o MySQL em Cluster. Há a Replicação nativa do MySQL, há também o MySQL InnoDB Cluster e o MySQL (NDB) Cluster. O livro é dedicado inteiramente a este último.
De forma sucinta, o MySQL NDB Cluster é uma solução que vai atender muito bem alguns requisitos, como: disponibilidade de 99,999%, workloads transacionais (OLTP), replicação geográfica ativo-ativo, tempos de respostas consistentes da ordem de milésimos de segundo, escalabilidade praticamente linear (inclusive de escritas) e capacidade de atender dezenas de milhares de transações por segundo. Contudo, o MySQL Cluster não vai ser uma boa escolha para executar consultas analíticas (OLAP) e, por se tratar de um sistema distribuído, vai adicionar maior complexidade na implantação e administração. Para entender os motivos, é necessário conhecer um pouco da sua origem e arquitetura, tópicos muito bem cobertos no livro.
O livro é dividido em cinco partes e 20 capítulos.
A primeira parte fornece algumas informações básicas sobre os vários componentes do MySQL NDB Cluster e como funcionam. Os capítulos são:
Capítulo 1: Arquitetura e conceitos básicos
Capítulo 2: Data Nodes
Parte II - Instalação e Configuração
A segunda parte foca os tópicos relacionados à instalação e configuração, incluindo a replicação entre clusters. São os seguintes capítulos:
Capítulo 4: Configuração
Capítulo 5: Instalação
Capítulo 6: Replicação
Parte III - Tarefas diárias e manutenção
Na terceira parte, os tópicos incluem tarefas que fazem parte da rotina diária do DBA. Os capítulos são:
Capítulo 7: NDB Management e outros utilitários do NDB
Capítulo 8: Backups e Restores
Capítulo 9: Manutenção de tabelas
Capítulo 10: Reinicializações
Capítulo 11: Upgrades e Downgrades
Capítulo 12: Segurança
Capítulo 13: MySQL Cluster Manager
Capítulo IV - Monitoramento e Troubleshoting
A quarta parte continua com dois tópicos que também fazem parte da rotina diária do DBA: monitoramento e investigação e solução de problemas. Os capítulos são:
Capítulo 14: Soluções de Monitoramento e o Sistema Operacional
Capítulo 15: Dados de monitoramento
Capítulo 16: Monitorando o MySQL NDB Cluster
Capítulo 17: Problemas típicos e soluções
Capítulo V - Performance Tuning
A parte final abrange os tópicos relacionados ao ajuste de desempenho do cluster. Os capítulos são:
Capítulo 18: Desenvolvimento de aplicações para MySQL NDB Cluster com SQL
Capítulo 19: MySQL NDB Cluster como um banco de dados NoSQL
Capítulo 20: Tuning da aplicação para MySQL NDB Cluster
Para quem usa o MySQL NDB Cluster, pretende usar ou mesmo se interessa por Sistemas Distribuídos de alta performance é leitura obrigatória. Recomendo!
Erro 'Got an error reading communication packets' no MySQL
Se uma aplicação cliente se conecta com êxito no MySQL, mas depois desconecta indevidamente ou a conexão é encerrada de maneira inesperada você receberá uma mensagem de erro 'Got an error reading communication packets'. Para solucionar o problema, primeiro tentamos identificar a causa. Porém, uma dificuldade aqui é que se trata de um erro de comunicação entre cliente e servidor e são várias as possíveis causas.
O MySQL Server vai registrar uma mensagem de conexão abortada no log de erros, algo como '[Warning] Aborted connection 9999 to db: 'xxxxxx' user: 'yyyyyy' host:'zzzzzz' (Got an error reading communication packets)'. No MySQL 5.6 você deve configurar log_warnings para 2 ou mais para que essas mensagens sejam registradas. No MySQL 5.7 não é necessário alterar a configuração padrão (log_error_verbosity) para ter estes detalhes. Do lado da aplicação, também é comum aparecer erros 'Lost connection to MySQL server'.
Outra maneira de verificar o problema é monitorar o valor da variável de status do MySQL Aborted_clients. Ela incrementa toda vez que um erro deste tipo ocorre. Outra variável de status relacionada a problemas de comunicação é Aborted_connects, mas ela será incrementada somente no caso da aplicação cliente falhar ao estabelecer a conexão inicial com o MySQL Server. Uma forma de acompanhar os valores destas variáveis é com o comando SHOW GLOBAL STATUS LIKE 'Aborted%';.
Se você estiver usando o MySQL Enterprise Monitor também receberá alertas relacionados a este problema e poderá acompanhar as conexões abortadas no respectivo gráfico.
Algumas das principais causas do erro são:
A aplicação cliente tem algum bug e não fecha adequadamente as conexões ou não encerra transações corretamente com COMMIT.
A aplicação está configurada com algum limite máximo do tempo de conexão e o excede. Por exemplo, no caso do PHP há a opção max_execution_time. Outras linguagens e ambientes têm opções similares.
A aplicação cliente foi encerrada abruptamente (crash) no meio de uma transferência de dados.
O MySQL está com o valor para a configuração max_allowed_packet muito pequeno. Neste caso, a aplicação cliente também deve receber um erro do tipo 'packet too large'.
O MySQL está configurado com limites muito baixos para wait_timeout ou interactive_timeout (em segundos). Neste caso, a conexão que permanecer no estado SLEEP por um tempo superior ao configurado será interrompida. Você poderá verificar o tempo e estado das conexões com o comando SHOW PROCESSLIST;.
A rede é ruim e o MySQL está configurado com limites muito baixos para net_write_timeout e net_read_timeout. Neste caso, se o cliente interromper o recebimento ou envio de resultados de queries acima destes limites, o MySQL Server pode pensar que o cliente está morto e fechar a conexão.
Algumas de suas queries podem precisar de mais memória do que a configurada no MySQL Server e/ou não puderam ser concluídas em tempo adequado. Tente identificar se o problema ocorre para queries específicas.
Firewalls, proxies, TCP/IP mal configurado,MTU auto discovery sem funcionar, DNS, hubs, switches, cabos defeituosos e outros problemas de rede/ethernet.
No Linux, alguns drivers apresentam problemas quando as interfaces de rede estão configuradas em Half Duplex em uma ponto e Full Duplex em outra.
Algum problema com a biblioteca de threads que causa interrupções nas leituras.
Se você conseguir identificar e isolar a causa, a solução será trivial. Porém, nem sempre será fácil. Você pode recorrer aos seguintes procedimentos:
Revise as seguintes configurações do MySQL: skip_name_resolve, max_allowed_packet, wait_timeout, interactive_timeout, net_write_timeout e net_read_timeout. Monitore o log de erros e as variáveis de status Aborted% para certificar-se de que o problema diminuiu ou desapareceu. Modifique uma configuração de cada vez e gradualmente.
Esse erro usualmente está relacionado às condições da sua rede. Comece certificando-se que não há erros reportados pelas interfaces de rede (ifconfig -a) e depois com ferramentas como tcpdump e netstat. Gere um pacote maior que 1GB com ping enquanto procura por anomalias com tcpdump e netstat.
Relacione as mensagens de erro tanto do lado da aplicação cliente quanto do lado do MySQL Server. Prefira usar os Id's das conexões com o MySQL para fazer esta correlação ao invés do timestamp dos logs. Se puder, faça com que sua aplicação coloque esta informação (CONNECTION_ID()) no log de debug. No MySQL Server veja os Id's das conexões problemáticas no log de erros. Se estiver trabalhando com versão mais antiga que 5.7, configure log_warnings para maior ou igual a 2.
Se for necessário, colete e analise os detalhes das conexões problemáticas. Se você estiver usando o MySQL Enterprise Edition, habilite o Audit Log. Se estiver usando o MySQL Community, use o General Log, mas esteja ciente de que isto pode gerar impacto indesejado na performance, portanto use com cuidado. Com a lista dos Id's de conexões problemáticas verifique se queries específicas estão relacionadas ao problema. Se for possível isolar tais queries será mais fácil reproduzir e corrigir o problema.
Se você tiver acesso ao Suporte da Oracle para MySQL, poderá abrir um chamado e solicitar ajuda para identificar a causa-raíz.
https://www.percona.com/blog/2016/05/16/mysql-got-an-error-reading-communication-packet-errors
Migrando Legados para MySQL 5.7 com Query Rewrite Plugin - Parte 2
No post anterior, vimos o que são os Query Rewrite Plugins, tanto preparse, quanto postparse. Agora é o momento de ver como este recurso pode viabilizar a migração de uma aplicação legada real. Para exemplificar, usaremos uma versão antiga do Moodle que é incompatível com MySQL 5.7. Veremos como implementar um preparse QR plugin.
O legado: Moodle 2.3
Moodle é uma plataforma de aprendizado extremamente popular criada com o LAMP Stack. A versão 2.3 já é bastante antiga e não recebe mais atualizações desde janeiro de 2014.
Ao tentar instalar o Moodle 2.3 com MySQL 5.7, ocorre o erro:
PHP Warning:  mysqli::mysqli(): Headers and client library minor version mismatch. Headers:50550 Library:50631 in /var/www/html/moodle/lib/dml/mysqli_native_moodle_database.php on line 377
Debug info: Unknown system variable 'storage_engine'
SELECT @@storage_engine
line 407 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 184 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 121 of /lib/ddl/mysql_sql_generator.php: call to mysqli_native_moodle_database->get_dbengine()
line 248 of /lib/ddl/sql_generator.php: call to mysql_sql_generator->getCreateTableSQL()
line 401 of /lib/ddl/database_manager.php: call to sql_generator->getCreateStructureSQL()
line 356 of /lib/ddl/database_manager.php: call to database_manager->install_from_xmldb_structure()
line 1422 of /lib/upgradelib.php: call to database_manager->install_from_xmldb_file()
line 184 of /admin/index.php: call to install_core()
Na base de bugs do Moodle é possível encontrar um bug com a análise do motivo do problema (MDL-50633). Resumidamente o problema ocorre porque o Moodle tenta ler qual o Storage Engine padrão usando a variável de sistema do MySQL @@storage_engine. Esta variável foi renomeada no MySql 5.7 ou superior para @@default_storage_engine.
Uma saída seria atualizar o Moodle, mas isto nem sempre é possível em alguns cenários. Outra seria corrigir esta consulta específica na aplicação. No maravilhoso mundo open source quase sempre podemos contar com esta opção, porém não é o caso de outros softwares de código fechado. Vamos supor que não seja possível mexer na aplicação.
Resta a opção de interceptar os comandos enviados pela aplicação e modificá-los antes que o banco de dados execute-os. Há algumas formas de fazer isso, como por exemplo o MySQL Proxy, porém na maioria dos casos é indesejável adicionar mais um componente na infra-estrutura, principalmente algo que seja um ponto único de falha entre aplicação e banco de dados. E se fosse possível fazer a modificação de comandos online, diretamente no servidor de banco de dados? É exatamente isto que faz um Query Rewrite Plugin!
Tentativa 1: usar um preparse Query Rewrite Plugin
O caminho mais fácil seria usar o plugin Rewriter, apresentado no post anterior, para interceptar a query SELECT @@storage_engine e reescrevê-la para SELECT @@default_storage_engine.
O passo-a-passo é o seguinte:
Passo 1: Instalar o Rewriter plugin:
mysql -uroot -p < install_rewriter.sql
Passo 2: Criar a regra de reescrita:
mysql> INSERT INTO query_rewrite.rewrite_rules ( pattern, pattern_database, replacement ) VALUES ('SELECT @@storage_engine', 'moodle', 'SELECT @@default_storage_engine');
Passo 3: Carregar a regra na memória:
Houve algo errado! Vamos fazer um debug verificando o que o plugin retornou na coluna message da tabela query_rewrite.rewrite_rules:
mysql> SELECT * FROM query_rewrite.rewrite_rules \G
pattern: SELECT @@storage_engine
pattern_database: moodle
replacement: SELECT @@default_storage_engine
message: Parse error in pattern: >>Unknown system variable 'storage_e
ngine'<<
É possível concluir que as variáveis de ambiente são verificadas pelo Banco de Dados antes da etapa de parsing, pois o Rewriter é um postparse QR plugin. Desta forma, teremos que usar um preparse QR plugin.
Tentativa 2: criar um postparse Query Rewrite Plugin
Podemos criar postparse QR plugins customizados, que funcionarão de forma parecida com o rewrite_example mostrado no post anterior.
Para nossa necessidade específica, precisamos apenas de um novo plugin que intercepte a query SELECT @@storage_engine e a reescreva para SELECT @@default_storage_engine antes que ela chegue ao parser.
O rewrite_example é justamente um exemplo de implementação de preparse QR plugin. O seu código-fonte está disponível junto com o código do MySQL 5.7 no GitHub.
Com pequenas modificações, criei um novo plugin para resolver nosso problema, o Rewrite Status Variable Storage Engine, disponível no GitHub. Se usar Linux 64-bit, você pode baixar o plugin já compilado e não precisa preocupar-se com os detalhes de codificação ou processo de compilação.
Desenvolver um plugin para MySQL requer conhecimento em C++, o que pode não ser trivial para alguns leitores. Não trataremos dos detalhes sobre o código-fonte de um novo plugin neste post, talvez em um artigo futuro. Se quiser aprofundar-se no tema, recomendo o ótimo livro Expert MySQL.
Uma vez que você tenha o plugin compilado em forma de biblioteca (arquivos .so no Linux ou .dll no Windows), basta instalá-lo normalmente no MySQL. Se estiver usando Linux 64-bit pode testar usando o seguinte procedimento:
Baixe o plugin compilado rewrite_status_storage_engine.so
Verifique qual seu diretório de plugins no MySQL com o comando SELECT @@plugin_dir e mova o arquivo rewrite_status_storage_engine.so para lá
Certifique-se que o arquivo possui as permissões corretas com chmod 755 rewrite_status_storage_engine.so
Instale o novo plugin normalmente no MySQL com INSTALL PLUGIN rewrite_status_storage_engine SONAME 'rewrite_status_storage_engine.so';
Verifique se o plugin foi instalado com sucesso e está ativo:
Name: rewrite_status_storage_engine
Library: rewrite_status_storage_engine.so
Agora é só confirmar que o erro não ocorre mais:
mysql>  SELECT @@storage_engine;
| InnoDB                   |
Message: Query 'SELECT @@storage_engine' rewritten to 'SELECT @@default_storage_
engine' by a query rewrite plugin
Seguindo com a instalação do Moodle 2.3
Agora você deve conseguir instalar o Moodle 2.3 com MySQL 5.7 sem erros. Siga os passos do manual do Moodle mantendo o rewrite_status_storage_engine ativo.
Query Rewrite Plugins são bastante poderosos e podem ajudar em migrações evitando alterações na aplicação. Podem ser desenvolvidos plugins cutomizados em C++ ou usar o plugin Rewriter, dependendo da necessidade específica do projeto.
Envie seus comentários e sugestões.
https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin-installation.html
Bell, Charles Andrew, and Sven Sandberg. Expert MySQL. Vol. 3. Apress, 2012.
Slideshare Martin Hansson FOSSDEM 2016 presentation
MySQL Server Team Blog, posts by Martin Hansson
Manual do Moodle 2.3
Migrando Legados para MySQL 5.7 com Query Rewrite Plugin - Parte 1
Uma funcionalidade que pode fazer a diferença e viabilizar projetos de migração de legado para MySQL é a Query Rewrite Plugin. Com ela é possível interceptar comandos que são enviados ao MySQL e modificá-los online, sem a necessidade de alterar a aplicação.
Neste post, vamos entender como utilizar este recurso, com um exemplo real usando Moodle 2.3 no MySQL 5.7.
O que é o Query Rewrite Plugin
A partir do MySQL 5.7.6 há suporte para os Rewrite Plugins, que podem examinar e seletivamente reescrever comandos recebidos pelo servidor antes que sejam executados.
Quando uma instrução é recebido pelo MySQL Server, por exemplo um SELECT * FROM tabela where id = 1, ele passa por um parser que vai validar sintaxe e semântica, entre outros passos. Depois disso, o Otimizador de Consultas entra em ação, gerando e selecionando o melhor plano de execução para a query. Finalmente, o melhor plano é executado e o resultado retornado para o cliente que enviou a instrução.
Plugins Query Rewrite podem ser de dois subtipos:
- preparse: interceptam instruções antes do parser.
- postparse: agem logo depois do parser realizar seu trabalho.
Query Rewrite Plugins disponíveis
No pacote do MySQL Server 5.7 há dois Query Rewrite Plugins já disponíveis:
- rewrite_example: um exemplo de como funciona e como pode ser implementado um preparse Query Rewrite Plugin
- rewriter: um postparse plugin de uso mais genérico, para reescrever queries com SELECT, como veremos a seguir.
Como usar o plugin Rewriter (postparse)
A maneira mais fácil de enteder o funcionamento do Rewriter plugin é testando-o.
Passo 1 - Instale e configure o Rewriter plugin com:
mysql -uroot -p <install_rewriter.sql
Passo 2 - Como um teste simples, crie uma regra para transformar automaticamente SELECT ? em SELECT ? + 1:
mysql> INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) VALUES ('SELECT ?', 'SELECT ? + 1');
Passo 3 - Carregue a regra recém criada na memória:
Passo 4 - Execute um SELECT:
Message: Query 'SELECT 1' rewritten to 'SELECT 1 + 1' by a query rewrite plugin
Você pode desabilitar apenas regras específicas com:
Veja que o Rewriter tem a capacidade de reescrever SELECTs. Ele examina instruções SELECT e pode reescrevê-los, com base nas regras de reescrita definidas na tabela query_rewrite.rewrite_rules. Os DBAs usam o plugin manipulando as regras armazenadas nesta tabela. Para efetivar novas regras (alterações na tabela rewrite_rules), o DBA deve chamar a procedure flush_rewrite_rules(), que carrega as regras na memória. Se houver algum erro durante a operação, o plugin o informará através da coluna message.
O plugin adiciona algumas variáveis ​de sistema para configuração:
SHOW GLOBAL VARIABLES LIKE 'rewriter%';
| rewriter_verbose | 1     |
Também são adicionadas variáveis de status com informações sobre sua operação:
SHOW GLOBAL STATUS LIKE 'rewriter%';
| Rewriter_number_loaded_rules      | 1     |
| Rewriter_number_reloads           | 1     |
| Rewriter_number_rewritten_queries | 1     |
| Rewriter_reload_error             | OFF   |
O Rewriter plugin atuará em instruções SELECT e em Prepared Statements, mas não fará reescritas para SELECTs dentro de Stored Programs ou UDFs.
Testando outro Query Rewrite Plugin (preparse)
O Rewriter é um QR plugin postparse. Isto significa que vai atuar depois do trabalho do parser. Vejamos agora como funciona um QR plugin preparse, que vai modificar a query antes de passar pelo parser:
mysql> INSTALL PLUGIN rewrite_example SONAME 'rewrite_example.so';
Message: Query 'SELECT @@DEFAULT_STORAGE_ENGINE' rewritten to 'select @@default_storage_engine' by a query rewrite plugin
Message: Query 'SHOW WARNINGS' rewritten to 'show warnings' by a query rewrite plugin
Note que a query foi reescrita pelo plugin para letras minúsculas. Este segundo plugin é apenas um exemplo, talvez sem muita aplicação prática. Porém, talvez você necessite realmente criar um preparse QR plugin em algumas situações, como no estudo de caso que veremos na parte 2.
Query Rewrite Plugins podem ter vários usos e são aliados poderosos quando não é possível alterar o código da aplicação. O Rewriter é uma implementação mais genérica, que pode ajudar se for necessário interceptar e reescrever SELECTs.
https://dev.mysql.com/doc/refman/5.7/en/plugin-types.html#query-rewrite-plugin-type
https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin.html
http://www.slideshare.net/MartinHanssonOracle/the-query-rewrite-plugin-interface-writing-your-own-plugin