Início / dba / Perguntas / 174957
Accepted
Matija Nalis
Asked: 2017-05-31 13:15:53 +0800 CST

como parar o mysqldump com segurança?

  • 772

Eu (bem, meu script cron) tentei killall mysqldumpe isso não terminou muito bem - o servidor mysql parou de aceitar conexões depois de um tempo.

Era a máquina Debian Jessie com mysql 5.5.55-0+deb8u1.

O cenário de uso foi:

  • havia uma SELECTconsulta de longa duração (algumas horas) que era muito lenta ou o cliente que a enviou estava com problemas (o estado da consulta era Sending data), mas todas as outras consultas estavam indo e vindo (apenas a carga talvez fosse um pouco maior) .

  • no backup noturno estava sendo executado com mysqldump --max_allowed_packet=2147483648 --hex-blob --single-transaction --master-data --routines --order-by-primary --databases db1 db2 db3... | pigz -p8 > backup.sql.gz. Ele nunca terminou, provavelmente porque estava esperando que o SELECTacima terminasse primeiro (adivinhando aqui - era a única coisa que parecia fora do comum, e a mesma configuração funcionou bem por meses).

  • cron job executado pela manhã, que killall -q mysqldumpdeveria encerrar o backup com segurança caso não fosse concluído no horário definido (notificando o administrador para examinar e corrigir o problema mais tarde), permitindo que as pessoas continuassem trabalhando com o servidor mysql normalmente.

  • o resultado, no entanto, foi a tabela de conexão completa e, portanto, nenhum usuário conseguiu fazer login no servidor mysql. Houve FLUSH /*!40101 LOCAL */ TABLESconsulta travada Waiting for table flushe centenas de SELECTconsultas travadas no mesmo Waiting for table flushestado.

  • além disso, admin matando LOCK TABLESa consulta mysql não ajudou, pois outras consultas SELECT permaneceram Waiting for table flush(o que parece ser o comportamento pretendido? )

Reiniciar o servidor mysql finalmente "consertou" o problema. No entanto, querendo evitar que essa situação (e intervenções administrativas de emergência) se repitam, gostaria de encerrar com segurança o backup do mysqldump no Debian Jessie mysql-5.5.55 (ou no próximo Debian Stretch mariadb-10.1.23-8). Há algum jeito?

Se não, quais são outras opções para realizar o backup do mysql e evitar a carga do servidor pela manhã (o que é - neste caso - quase tão ruim quanto o servidor completamente travado)?

(Eu gostaria de ficar com os pacotes Debian Stable, se possível)

mysql mysql-5.5

3 respostas

  1. Como você está usando --master_datapara obter um valor consistente do status do mestre.

    Os internos do mysqldump emitirão os comandos abaixo para o servidor mysql.

    2017-05-31T04:39:05.843130Z    48 Query /*!40100 SET @@SQL_MODE='' */
2017-05-31T04:39:05.843273Z    48 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-05-31T04:39:05.843411Z    48 Query FLUSH /*!40101 LOCAL */ TABLES
2017-05-31T04:39:05.846031Z    48 Query FLUSH TABLES WITH READ LOCK
2017-05-31T04:39:05.846166Z    48 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-05-31T04:39:05.846279Z    48 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2017-05-31T04:39:05.846413Z    48 Query SHOW MASTER STATUS
2017-05-31T04:39:05.846539Z    48 Query UNLOCK TABLES
..
..... Here it continues to take backup of data and structures .

    O que tinha acontecido?:

    Seu backup teria acabado de começar e havia uma consulta que estava sendo executada em uma tabela específica por um longo período de tempo antes mesmo do comando FLUSH TABLES e não liberou o bloqueio na tabela e FLUSH TABLESdeve estar aguardando esse thread para seja concluído ou continue tentando liberar até que a versão_revisão dessa tabela seja igual a todas as tabelas.

    Assim, você obtém outros encadeamentos bloqueados para outras tabelas também. Como este é todo o bloqueio de nível de DBs*.Tables* enquanto as tabelas de liberação estavam acontecendo. Finalmente, ele teria acumulado todas as novas conexões na lista de processos e empilhado até max_connectionse não permitindo que ninguém fizesse login.

    Digamos que se você conseguiu fazer login no terminal e tentou matar as tabelas de descarga, não acho que haja uma maneira de recuperar ou reverter as tabelas de descarga que foram feitas e liberar sua própria conexão de thread. Então, pode KILLED STATEser por mais tempo. E assim você pode ter chegado à última opção que é reiniciar o servidor.

    Como corrigi-lo?:

    No momento do problema, quando o administrador conseguiu fazer login no prompt do mysql.

    Em vez de emitir o comando kill no thread FLUSH TABLES, se kill foi dado ao thread em execução por muito tempo SELECT .Há chances de que SELECT fosse descartado e a tabela fosse deixada FLUSH TABLES para adquirir e atualizar revision_version e liberar o bloqueio para novas consultas. E o backup teria continuado. Como acho que ninguém espera uma resposta do outro lado aguardando o resultado da consulta em execução por longas horas.

    Qual é a solução a longo prazo?:

    • Você precisa garantir que não haja consultas tão longas em execução no momento do backup.

    • Parece que isso pode ser uma nova implantação ou alguém acionou uma consulta incorreta e não se preocupou em fechar a sessão.

    • Tente matar a consulta se uma consulta estiver sendo executada por mais de Xsecs (depende de sua necessidade). Ou

    • Faça um ajuste com as equipes para ajustar as consultas ou envie para o Admin para fornecer os dados do resultado ou fornecer um escravo separado todos juntos.
    • 1
  2. Best Answer

    Conforme sugerido por @Mannoj, substituí o simples "killall -q mysqldump" por uma versão mais inteligente que deve resolver o problema. Ele procura todas as consultas com mais de 4 horas no estado "Aguardando liberação da tabela" e as elimina (e produz a lista de processos para que o problema possa ser depurado no dia seguinte).

    Eu chamo de cron(8) em duas vezes:

    • às 23h - logo antes de iniciar o backup (para que o backup realmente comece mesmo se algum SELECT de longa duração ainda estivesse em execução no momento)
    • às 7 da manhã com argumento "difícil" - logo antes das pessoas começarem a trabalhar (para reiniciar o servidor mysql caso ninguém possa se conectar a ele)
    #!/bin/sh
killall -q mysqldump
dormir 3
mysql -BNe 'mostrar lista de processos' | \
  perl -nE '($id,$u,$h,$db,$cmd,$time,$state,$info) = split /\t/;
  if (($time > 4*3600) e ($cmd =~ /Query/)) {
          $encontrado++; diga "mostrar lista de processos";
          if ($encontrado==1); diga "matar $id;"
  };' | mysql -v

if [ "$1" = "difícil" ]
então
  dormir 30
  mysql -BNe 'mostrar lista de processos' | grep -q 'Query.*show processlist' || /etc/init.d/mysql reinicie
fi
    • 1

  3. Como o limite de reputação para comentários, acho que existem 2 opções para testar, e acho que parar o mysqldump com segurança não é uma boa maneira de resolver esse problema.

    max_allowed_packet

    Seu banco de dados de repente ganhou mais grande hoje em dia?

    Mostra 2G permitido.

    Se seus dados mais de 2G vão travar o processo.

    Nenhuma dica de tabela sem bloqueio

    Você pode tentar --lock-tables=falseou desativar a tabela bloqueada skip-add-locks.my.cnf

    • 0

relate perguntas