MySQL: use o mysqlcheck para fazer manutenção das suas tabelas

O programa cliente mysqlcheck oferece uma maneira eficiente para executar a manutenção das tabelas em seu banco de dados — ele verifica, conserta, otimiza ou apenas analisa as tabelas dentro do banco de dados.
O mysqlcheck deve ser usado quando o servidor mysqld está rodando — uma de suas primeiras vantagens é essa: a de não precisar indisponibilizar o servidor para fazer uma manutenção no seu sistema de banco de dados.
O mysqlcheck é um “frontend”. Ele se autentica no MySQL e execute as declarações CHECK TABLE, REPAIR TABLE, ANALYZE TABLE e OPTIMIZE TABLE do modo mais conveniente pro usuário. Ele determina a forma mais adequada para cada declaração de acordo com a operação pedida, na linha de comando, e a envia ao servidor para ser executada.
É o indicado para realizar as tarefas de manutenção em tabelas MyISAM. Outros mecanismos de armazenamento (storage engines) podem não suportar todas as operações, nestes casos, mensagens de erro irão surgir para te informar do fato:

note    : The storage engine for the table doesn't support check
PRECAUÇÕES

Você deve fazer backup dos dados nas tabelas antes de inciar uma operação de restauração (repair) nestas tabelas. Sob certas circunstâncias, a operação pode causar perda de dados.
Algumas causas de perda de dados podem estar ligadas a sistemas de arquivos com erros.

De acordo com o manual oficial do MySQL, há 3 sintaxes possíveis pro comando mysqlcheck:

mysqlcheck [opcoes] nome_do_banco [nome_da_tabela1 nome_da_tabela2 ...]
mysqlcheck [opcoes] --databases nome_do_banco1 nome_do_banco2 ...
mysqlcheck [opcoes] --all-databases

Daqui pra frente, vamos desenvolver melhor o assunto e algumas das opções mais comuns de uso do comando.

Como verificar uma tabela dentro do banco de dados

Se seu aplicativo retornou uma mensagem de erro, informando que uma determinada tabela está corrupta, execute o mysqlcheck assim:

mysqlcheck -c nome_do_banco nome_da_tabela -u root -p

forneça a senha, assim que lhe for pedido e aguarde o resultado:

nome_do_banco.nome_da_tabela      OK

Se a senha e/ou nome de usuário estiver errada, o sistema emitirá uma mensagem de erro semelhante a esta:

mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

A opção -c é a que indica que a operação a ser realizada é de checagem (verificatória).

Como verificar todas as tabelas em um banco de dados

Se quiser verificar todas as tabelas dentro de um banco de dados, com o mysqlcheck, omita seus nomes. Forneça apenas o nome do banco de dados que as contém (no meu caso, é clientes):

mysqlcheck -c clientes -u root -p

O meu resultado foi este:
clientes.CLPJ OK
clientes.CLCD OK
clientes.CLPD OK
clientes.CLCT OK[/plain]

Como checar todas as tabelas e bancos de dados

Para realizar uma checagem desta amplitude, o comando executado é bem curto:

mysqlcheck c -u root -p --all-databases

Você pode executar o mysqlcheck em mais de um banco de dados (sem ser todos), assim, com a opção --databases. No exemplo, que segue, o comando será executado em todas as tabelas dentro dos bancos de dados fornecedores e clientes:

mysqlcheck -c -u root -p --databases fornecedores clientes

Como analisar tabelas usando o mysqlcheck

O exemplo que segue, usa o comando mysqlcheck, com a opção -a para analisar a tabela cadastros, dentro do banco de dados clientes:

mysqlcheck -a clientes cadastros -u root -p

Internamente, o mysqlcheck roda a declaração ANALYZE TABLE em relação à clientes. Enquanto trabalha, trava a tabela, permitindo apenas a sua leitura – motivo, pelo qual, você não deve fazer estas tarefas em horários de pico.

Use o mysqlcheck para otimizar tabelas

No exemplo que segue, o mysqlcheck é usado para otimizar a tabela projetos, dentro do banco de dados clientes.

mysqlcheck -o clientes projetos -u root -p
Enter password: 
clientes.projetos                                  Table is already up to date

Como já disse, o comando mysqlcheck executa um comando MySQL internamente. Neste caso, o OPTIMIZE TABLE.
À medida em que você vai removendo registros das suas tabelas, espaços sem uso vão ficando no meio. Este comando funciona semelhante ao desfragmentador de alguns sistemas operacionais, reorganizando os espaços, o que melhora a performance em tabelas que já tenham passado por grandes quantidades de alterações.

Restaure, conserte tabelas com o comando mysqlcheck

Aqui, o mysqlcheck vai usar internamente o comando REPAIR TABLE, que repara (conserta) uma tabela MyISAM corrompida.
Veja como:

mysqlcheck -r clientes projetos -u root -p

Combine diversas tarefas em uma só declaração mysqlcheck

É claro que, para a sua comodidade, é possível combinar diversas tarefas para serem executadas pelo mysqlcheck, em apenas uma linha de comando. veja como combinar CHECK, OPTIMIZE e REPAIR e mais a opção --auto-repair, dentro do banco de dados clientes (adeque o comando à sua realidade):

mysqlcheck -u root -p --auto-repair -o clientes

Ou em todas as tabelas, em todos os bancos de dados:

mysqlcheck -u root -p --auto-repair -o --all-databases
LEIA MAIS:
  • Comandos (internos) de manutenção — rápida noção dos comandos REPAIR, ANALYZE, CHECK e OPTIMIZE.
  • Otimize suas consultas — use o QUERY CACHE!
  • Outras opções úteis pro mysqlcheck

    Para ter um feedback maior do que está sendo feito pelo programa, use a opção –debug-info. Ela é mais voltada para encontrar erros dentro do próprio programa, para desenvolvedores – mas é uma mão na roda para administradores de MySQL que desejam ter um maior controle do que o comando está executando. Veja, no exemplo, como a saída oferece mais informações.

    mysqlcheck -u root -p --debug-info --auto-repair clientes projetos
    Enter password: 
    clientes.projetos                                  OK
    User time 0.01, System time 0.00
    Maximum resident set size 1584, Integral resident set size 0
    Non-physical pagefaults 538, Physical pagefaults 0, Swaps 0
    Blocks in 0 out 0, Messages in 0 out 0, Signals 0
    Voluntary context switches 3, Involuntary context switches 17

    MySQL: comandos para manutenção do banco de dados

    De maneira bem breve, vou listar 4 comandos MySQL relacionados à manutenção dos seus bancos de dados. Neste texto, serão descritos de forma bem sucinta e rápida. É mais ou menos um lembrete para quem é administrador iniciante e ainda não criou os bons hábitos de verificação dos seus bancos de dados.

    Check table

    Com suporte ao MyISAM e ao InnoDB, o comando CHECK TABLE pode ser usado para verificar erros e inconsistências nas tabelas.
    Sua sintaxe é simples mas você precisa ter privilégios adequados para poder rodar este comando:

    
    CHECK TABLE projetos QUICK;
    
    +-------------------+-------+----------+----------+
    | Table             | Op    | Msg_type | Msg_text |
    +-------------------+-------+----------+----------+
    | clientes.projetos | check | status   | OK       |
    +-------------------+-------+----------+----------+
    1 row in set (0.09 sec)
    

    Repair table

    Se houver erros detectados por CHECK TABLE, será necessário usar o REPAIR na sua tabela.

    
    REPAIR TABLE projetos;
    
    +-------------------+--------+----------+----------+
    | Table             | Op     | Msg_type | Msg_text |
    +-------------------+--------+----------+----------+
    | clientes.projetos | repair | status   | OK       |
    +-------------------+--------+----------+----------+
    1 row in set (0.00 sec)
    

    Analyze table

    Este comando (exemplo abaixo) analisa e armazena a distribution key da tabela. Enquanto isto, ele a trava, impedindo que seja alterada temporariamente – ela só pode ser lida.

    O que é distribution key

    Uma distribution key ou chave de distribuição é uma coluna (ou grupo de colunas) usada para determinar a partição do banco de dados em que um registro, em particular, será armazenado.
    Você define uma distribution key em uma tabela através do comando CREATE TABLE.

    
    ANALYZE TABLE projetos;
    
    +-------------------+---------+----------+-----------------------------+
    | Table             | Op      | Msg_type | Msg_text                    |
    +-------------------+---------+----------+-----------------------------+
    | clientes.projetos | analyze | status   | Table is already up to date |
    +-------------------+---------+----------+-----------------------------+
    1 row in set (0.01 sec)
    
    

    Optimize table

    Se você executa muitas operações de remoção de registros (DELETE), provavelmente precisará da declaração OPTIMIZE TABLE para fazer uso de espaços liberados e desfragmentar o arquivo de dados.

    
    OPTIMIZE TABLE projetos;
    
    +-------------------+----------+----------+----------+
    | Table             | Op       | Msg_type | Msg_text |
    +-------------------+----------+----------+----------+
    | clientes.projetos | optimize | status   | OK       |
    +-------------------+----------+----------+----------+
    1 row in set (0.04 sec)
    

    Como verificar e consertar seu sistema de arquivos no Ubuntu

    O sistema de arquivos do seu HD pode ter problemas, pelas mais variadas razões. Este artigo é sobre como checar e, se houver erros, corrigi-los, com o comando fsck.
    Uma situação típica, em que podem ocorrer, não somente perda de dados, mas danos ao seu sistema de arquivos é a situação de falta de energia.
    Você reinicia o sistema e ele pára, pedindo para que você faça o reparo manualmente do seu sistema de arquivos.

    Como usar o fsck para reparar o sistema de arquivos

    O fsck (file system consistency check — verificação da consistência do sistema de arquivos) é um programa usada para encontrar erros no sistema de arquivos e corrigi-los.
    Enquanto ferramenta de manutenção da integridade dos seus dados, é bom usá-la com frequência – especialmente em caso de o seu sistema ser desligado de forma irregular.
    Para usar o fsck, em um ambiente seguro, tal como vou descrever aqui, você precisará ter privilégios administrativos.
    O comando pode ser executado, como root, sozinho:

    fsck

    Nestas condições, ele irá perscrutar todas as partições descritas em /etc/fstab.
    O modo certo de usar o fsck, contudo, é no Runlevel 1, o modo monousuário. Neste runlevel, o seu PC pode se tornar indisponível para o ambiente gráfico – é o equivalente ao modo de segurança no Windows.
    Para reiniciar o sistema no runlevel 1, execute o seguinte comando:

    init 1

    em, seguida, desmonte a partição em que o fsck será executado.
    Se quiser obter uma lista das partições que se encontram montadas e onde estão montadas no seu sistema, use o comando:

    cat /etc/mtab

    ou para saber exatamente onde está montada a sua partição /home:

    cat /etc/mtab | grep home
    /dev/sda3 /home ext3 rw 0 0

    (vou usar a partição /dev/sda3, neste exemplo):

    umount /dev/sda3

    ou pelo seu nome:

    umount /home

    Agora execute o fsck:

    fsck /dev/sda3

    Se preferir não ter que responder a todas as perguntas feitas pelo fsck, use o modo -y. Assim, o fsck executará a verificação e aplicará as correções necessárias, sem te importunar com questões técnicas:

    fsck -y /dev/sda3

    Se houver arquivos danificados e estes forem recuperados pelo fsck, ele os guardará no diretório /home/lost+found
    Ao terminar o processo, você pode reiniciar o computador ou apenas montar de volta a partição desmontada:

    mount /home

    E volte ao runlevel multiusuário:

    init 2

    Simples, assim.

    LEIA TAMBÉM

    MySQL: Otimize suas consultas com Query Cache

    O QUERY CACHE armazena o texto de uma declaração SELECT junto ao seu resultado, no cliente. Quando uma declaração idêntica é recebida, mais tarde, o servidor apresenta o resultado, já pronto, em vez de fazer o trabalho de pesquisa novamente.
    O query cache é compartilhado entre sessões e, portanto, pode ser aproveitado por diversos outros clientes.

    Quando o Query Cache pode ser útil?

    Imagine um ambiente em que haja múltiplas tabelas, que não sofram muitas mudanças, para as quais o servidor receba várias consultas idênticas. Em uma situação como esta, é mais rápido fornecer a resposta pronta do que pesquisar a mesma coisa várias vezes.
    O query cache não retorna dados velhos ou vencidos. Quando as tabelas são alteradas, qualquer entrada relevante no query cache é descartada.
    Até as versões atuais do MySQL, há certas limitações:

    • O query cache não funciona em ambiente onde haja múltiplos servidores mysqld atualizando tabelas MyISAM.
    • O query cache não suporta tabelas particionadas e é automaticamente desabilitado nestes casos. Você não pode habilitá-lo nestes casos.

    O query cache não é “ciência exata” e sua eficiência pode variar em função da carga de trabalho a que o servidor está submetido.

    Como funciona o Query Cache?

    Antes de serem executadas, os textos das consultas são comparados aos que já se encontram armazenados no query cache, que é sensível à caixa. Portanto:

    SELECT * FROM nome_da_tabela
    select * from nome_da_tabela

    são duas queries diferentes.
    As consultas têm que ser exatamente as mesmas (em cada byte) para serem reconhecidas como idênticas.
    Há outros casos em que queries, mesmo idênticas, serão consideradas diferentes:

    • quando usarem bancos de dados diferentes;
    • ao usarem versões divergentes de protocolo;
    • ao ter diferentes default character set.

    O cache vai armazenar as queries, inseridas nos casos acima, separadamente.
    O cache não vai aceitar queries dentro das seguintes condições:

    • se refere a uma função definida por usuário — User Defined Function ou UDF;
    • em que sejam executadas de dentro de uma stored function, uma trigger ou um evento;
    • se refere a variáveis definidas por usuário ou programas locais;
    • se refere às tabelas dentro dos bancos de dados MySQL, INFORMATION_SCHEMA ou performance_schema;
    • não usa tabelas;
    • usa tabelas temporárias;
    • gera avisos;
    • o usuário não tem privilégios suficientes para sua completa execução;

    Agora, que já terminei de explicar como o query cache não funciona, vou tentar explicar aonde ele vai funcionar – o que é bem mais simples.
    Antes de entregar o resultado de uma query, o MySQL verifica se o usuário tem privilégios de SELECT referentes aos bancos de dados e tabelas envolvidos. Se não, o resultado armazenado no cache não poderá ser usado.
    O query cache também funciona entre transações, quando você estiver usando tabelas InnoDB.
    A partir da versão 5.7 do MySQL, o resultado de uma consulta SELECT em uma VIEW será armazenada no cache.

    Como configurar

    É possível configurar o valor do query cache

    • no terminal do seu sistema, com o comando mysqld;
    • no arquivo de configuração my.cnf (permanentemente);
    • no cliente do MySQL.

    Neste texto, vou mostrar como configurar o query cache direto no cliente MySQL. Abra um terminal e autentique-se no servidor:

    mysql -u root -p

    Dentro do MySQL, verifique se o query cache está disponível:

    SHOW VARIABLES LIKE 'have_query_cache';
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | have_query_cache | YES   |
    +------------------+-------+
    1 row in set (0.00 sec)

    Para a gente pode brincar, aqui, o valor da variável have_query_cache precisará ser igual a YES, mesmo que o query cache esteja desativado.
    Dentro do MySQL ajuste o tamanho do query cache através da definição da variável de sistema query_cache_size:

    SET GLOBAL query_cache_size = 40000

    Para desabilitar a função, ajuste o valor de query_cache_size para 0. Seja cuidadoso com o tamanho total da variável. As threads precisam trancar o cache durante sua atualização — caches muito grandes ficam mais tempo trancados.

    Textos relacionados:
    • O comando SELECT — conheça várias dicas de uso do comando, no MySQL.
    • Data types — os tipos de dados possíveis para criar campos em uma tabela.
    • Storage engines — conheça os mecanismos de armazenamento do MySQL.

    Que tamanho usar pro query cache

    O manual do MySQL adverte que o tamanho mínimo possível para query_cache_size é 40Kb, para que ele possa alocar suas estruturas.
    Já o tamanho adequado depende da arquitetura do seu sistema. Como já foi dito, valores muito altos podem causar mais prejuízos do que benefícios — uma vez que aumentam o tempo em que o cache fica indisponível a cada atualização.
    Valores muito pequenos podem causar a exibição de uma mensagem de aviso e o desativamento do query cache. Veja:

    SET GLOBAL query_cache_size = 40000;
    Query OK, 0 rows affected, 2 warnings (0.01 sec)
    SHOW WARNINGS\G
    *************************** 1. row ***************************
      Level: Warning
       Code: 1292
    Message: Truncated incorrect query_cache_size value: '40000'
    *************************** 2. row ***************************
      Level: Warning
       Code: 1282
    Message: Query cache failed to set size 39936;
    new query cache size is 0
    2 rows in set (0.01 sec)

    Na última linha da mensagem, o sistema avisa que alterou o valor da variável query_cache_size para 0.
    Neste caso, você deve aumentar gradualmente o valor da variável e testar o seu sistema por alguns dias.
    O manual da versão 5.7 do MySQL recomenda um valor mínimo de 1000000, para que o query cache consiga guardar um número razoável de resultados de consultas.

    screenshot mysql set global query_cache_size
    Clique para ampliar

    Como criar um arquivo SWAP no Linux

    Normalmente as pessoas criam e configuram a sua partição swap, ao instalar o seu sistema.
    Até a versão 2.6 do kernel, o uso de uma partição separada para swap trazia melhorias sensíveis de performance.
    Atualmente, um arquivo de swap tem a mesma performance que uma partição dedicada. Ainda assim, o uso de uma partição separada é o jeito mais organizado e seguro de fazer a paginação.

    LEIA MAIS

    Você pode regular o uso do arquivo/espaço de troca (swap space) no Linux com a configuração do swapiness — o que pode trazer um ganho sensível no desempenho do seu sistema.
    Leia mais aqui.

    Por que eu deveria criar um arquivo de swap

    Se você não tem um arquivo ou partição swap, é aconselhável criar um, para que o sistema tenha para onde “transbordar” quando tiver chegado a um certo limite de uso da memória RAM — que é uma situação rara para usuários comuns do Linux.


    Por favor, leia Perguntas e respostas sobre o SWAP.
    Se você já tem um arquivo ou partição, não há razão para criar outro, a não ser que o espaço atual seja muito pequeno ou você tenha feito algum upgrade de memória no seu sistema – ao hibernar, o Linux grava no swap toda a situação atual da sua memória RAM, entre outras coisas.
    Houve um tempo em que se recomendava configurar o tamanho do swap como o dobro do tamanho da memória RAM. Isto não é mais necessário. O Linux, nas mãos de um usuário normal, vai usar o swap, predominantemente, só para hibernar o sistema. Portanto, o tamanho de swap deve acompanhar o tamanho da sua memória RAM.
    Como criar uma nova partição é um pouco complicado, criar um novo arquivo de swap pode ser uma solução mais viável. Vou mostrar como fazê-lo a seguir.

    Como criar um arquivo de swap no Linux

    O procedimento descrito abaixo vale pra qualquer distro Linux. O que se requer, aqui, é que você tenha acesso root ao sistema, ou seja, tenha privilégios administrativos.
    O procedimento não funciona em sistemas de arquivos BTRFS.
    O processo envolve o uso do comando dd para criar o arquivo swap e o comando mkswap para configurar o swap em um arquivo ou dispositivo independente.

    Autenticando-se como administrador (root)

    Abra um terminal – no Ubuntu, use a combinação de teclas Ctrl + Alt +T. Nos exemplos, vou usar o comando sudo para executar os procedimentos com privilégios administrativos. Mas, se você preferir, pode entrar como superusuário, através do comando su e suprimir o uso do sudo — é sua escolha.

    Como criar um arquivo de armazenamento com o dd

    O comando dd tem múltiplas funcionalidades. Pode ser usado, inclusive, para medir a velocidade de leitura/escrita de um dispositivo, bem como, criar um pendrive inicializável com Linux.
    Neste caso, vou criar um arquivo de armazenamento, a ser usado como swap, com o tamanho de 512 Mb.
    Multiplique o tamanho que você deseja em Megabytes por 1024, para obter o valor a ser usado pelo parâmetro count, conforme o exemplo a seguir.
    Note que 512 multiplicado por 1024 é igual a 524288 – e é este número que vou usar no meu exemplo:

    
    sudo dd if=/dev/zero of=/swapfile bs=1024 count=524288
    
    [sudo] password for justincase: 
    524288+0 registros de entrada
    524288+0 registros de saída
    536870912 bytes (537 MB) copiados, 18,417 s, 29,2 MB/s
    justincase@JustInCase-Solaris-8:~$
    

    O arquivo swap já está criado. Segue, abaixo, uma explicação do que foi feito:

    • if=/dev/zero — Lê o arquivo /dev/zero, um arquivo especial uma vez que provê todos os caracteres null necessários para construir o arquivo swapfile
    • of=/swapfile — cria o arquivo /swapfile e grava nele o conteúdo de /dev/zero
    • bs=1024 — este parâmetro indica que 1024 blocos deverão ser gravados/lidos por vez
    • count=524288 — limita a quantidade de blocos gravados a este número

    Como configurar o seu arquivo de swap

    O comando, que segue, configura o seu recém criado arquivo swapfile como sendo um arquivo de swap:

    
    sudo mkswap /swapfile
    
    mkswap: /swapfile: permissões inseguras 0644, sugere-se 0600.
    Configurando espaço de swap versão 1, tamanho = 500 MiB (524284 bytes)
    nenhum rótulo, UUID=9055d446-c93c-41e7-b500-5da7bffff888
    

    Agora, siga a recomendação de segurança, e altere as permissões do arquivo:

    
    chmod 0600 /swapfile
    

    Para que o seu arquivo swap seja ativado na próxima vez em que você ligar a máquina, é necessário indicar isto no arquivo de sistema /etc/fstab. Abra-o e acrescente a seguinte linha, no final:

    /swapfile swap swap defaults 0 0

    Grave o arquivo e feche o editor.
    Após reiniciar o seu sistema o swap já estará funcionando.

    Como verificar se o meu swap está ativado ou não

    Use o comando free para verificar a situação geral de uso da memória do sistema, o que inclui o uso do swap:

    
    sudo free -mh
    

    Normalmente, a última linha costuma mostrar informações sobre ele.
    Outra forma de verificar o status do swap é inquirir o arquivo /proc/meminfo/:

    
    cat /proc/meminfo | grep -i swap
    

    Como desativar e remover o arquivo de swap do meu sistema

    Para desativar o arquivo de swap criado aqui, use o comando:

    
    sudo swapoff /swapfile
    

    Agora remova a linha inserida ao final do arquivo /etc/fstab.
    Após reiniciar o sistema, pode apagar o arquivo /swapfile:

    
    sudo rm -vf /swapfile
    

    Se você tiver interesse em apenas reduzir o uso do swap, clique aqui, para saber como fazer isto.

    Assinar blog por e-mail

    Digite seu endereço de e-mail para assinar este blog e receber notificações de novas publicações por e-mail.