Como posso monitorar o andamento de uma importação de um arquivo .sql grande?

Se você está apenas importando de um arquivo de despejo da CLI em *nix, por exemplo

mysql -uxxx -pxxx dbname < /sqlfile.sql

em seguida, primeiro instale o visualizador de tubos no seu sistema operacional e tente algo assim:

pv sqlfile.sql | mysql -uxxx -pxxxx dbname

que mostrará uma barra de progresso à medida que o programa é executado.

É muito útil e você também pode usá-lo para obter uma estimativa do progresso do mysqldump.

pv despeja sqlfile.sqle passa para o mysql (por causa do operador pipe). Enquanto está despejando, mostra o progresso. O legal é que o mysql pega os dados tão rápido quanto pode progredir, então pv pode mostrar o progresso da importação. Eu não tenho nenhuma prova. Mas parece que sim. Acho que há algum buffer usado, mas em algum momento acho mysqlque não lê mais dados quando ainda está ocupado processando.

Se você já iniciou a importação, você pode executar este comando em outra janela para ver o tamanho atual de seus bancos de dados. Isso pode ser útil se você souber o tamanho total do arquivo .sql que está importando.

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MiB" 
FROM information_schema.TABLES GROUP BY table_schema;  

Crédito para: http://forums.mysql.com/read.php?108,201578,201578

A Referência do MySQL 8.0 afirma o seguinte sobre a precisão:

DATA_LENGTH

Para MyISAM, DATA_LENGTH é o comprimento do arquivo de dados, em bytes.

Para InnoDB, DATA_LENGTH é a quantidade aproximada de memória alocada para o índice clusterizado, em bytes. Especificamente, é o tamanho do índice clusterizado, em páginas, multiplicado pelo tamanho da página do InnoDB.

INDEX_LENGTH

Para MyISAM, INDEX_LENGTH é o comprimento do arquivo de índice, em bytes.

Para InnoDB, INDEX_LENGTH é a quantidade aproximada de memória alocada para índices não clusterizados, em bytes. Especificamente, é a soma dos tamanhos de índice não clusterizados, em páginas, multiplicado pelo tamanho da página do InnoDB.

Quando você executa um mysqldump de um único banco de dados, todas as tabelas são despejadas em ordem alfabética.

Naturalmente, o recarregamento do mysqldump em um banco de dados também estaria em ordem alfabética.

Você poderia apenas fazer um SHOW PROCESSLIST; e descubra a conexão do banco de dados executando o mysqldump. Quando o dump for recarregado, a DB Connection desaparecerá.

Se você quiser saber quais tabelas estão no arquivo de despejo, execute isso em foobar.sql

cat foobar.sql | grep "^CREATE TABLE" | awk '{print $3}'

ATUALIZAÇÃO 2012-05-02 13:53 EDT

Desculpe por não perceber que há apenas uma mesa.

Se a tabela for MyISAM, a única maneira de monitorar é do ponto de vista do SO. A razão? A tabela é bloqueada para gravação durante todo o recarregamento. O que procura? O tamanho dos arquivos .MYDe . .MYIClaro, você precisa comparar isso com o tamanho da tabela antes no outro servidor de banco de dados do qual você importou.

Se a tabela for InnoDB e você tiver innodb_file_per_table habilitado, a única maneira de monitorar é do ponto de vista do SO. A razão? A tabela é bloqueada para gravação durante todo o recarregamento. O que procura? O tamanho do .ibdarquivo. Claro, você precisa comparar isso com o tamanho da tabela antes no outro servidor de banco de dados do qual você importou.

Se a tabela for InnoDB e você tiver innodb_file_per_table desabilitado, nem mesmo o ponto de vista do SO pode ajudar.

ATUALIZAÇÃO 2012-05-02 13:56 EDT

Eu abordei algo assim no ano passado: Como faço para obter % de progresso para "type db.sql | mysql"

ATUALIZAÇÃO 2012-05-02 14:09 EDT

Como um mysqldump padrão bloqueia a tabela assim:

LOCK TABLES `a` WRITE;
/*!40000 ALTER TABLE `a` DISABLE KEYS */;
INSERT INTO `a` VALUES (123),(451),(199),(0),(23);
/*!40000 ALTER TABLE `a` ENABLE KEYS */;
UNLOCK TABLES;

então, não há como obter um progresso com o mysql até que o bloqueio da tabela seja liberado.

Se você puder obter LOCK TABLESe UNLOCK TABLEScomentar fora do arquivo de despejo ...

• se a tabela for MyISAM, SELECT COUNT(*) funcionaria
• se a tabela for InnoDB, SELECT COUNT(*) provavelmente diminuiria/interromperia a carga até que a contagem fosse concluída

A cada 2 segundos você verá os processos em execução.

watch 'echo "show processlist;" | mysql -uuser -ppassword';

Se você quiser menos frequente, adicione -n xonde x é o número de segundos. 5 segundos seria:

watch -n 5 'echo "show processlist;" | mysql -uuser -ppassword';

Se você quiser apenas verificar se está parado, pode consultar

show processlist; 

e veja o que está sendo executado.

Como solução para alguém que não consegue fazer pv funcionar ou para quem o pv conta mentiras. Você pode monitorar o tamanho do arquivo ibdata1 em /var/lib/mysql que contém os dados. Isso terminará com o mesmo tamanho (ou mais ou menos) do tamanho do arquivo em seu servidor de origem.

Se houver muitas tabelas, você também pode vê-las aparecer uma a uma em /var/lib/mysql/<nome do banco de dados>.

Eu usei esse fato recentemente quando um banco de dados de longo prazo construiu um arquivo de log de cerca de 20G durante um período de três ou quatro anos. Percebi que a transferência estava demorando muito e usei essa técnica para monitorar o progresso.

Eu acho que é altamente improvável que amanheça o dia em que um banco de dados não envolva um arquivo em algum lugar ou outro. Enquanto isso, você pode monitorar o arquivo para ver como uma transferência está progredindo. O método que sugeri foi algo que você poderia fazer de uma forma ou de outra desde que o primeiro banco de dados sql foi escrito. Nunca tive a intenção de sugerir que fosse algum tipo de técnica "oficial" que um jóquei manual pudesse recorrer. Ele assume um nível geral de proficiência com computadores em geral e unix em particular.

A resposta de Rob é ótima para a maioria das situações, mas o Pipe Viewer não funciona bem em casos de uso em que um tty não está disponível, como ao monitorar a saída de inicialização de um contêiner do docker mysql ou quando você deseja registrar o progresso em um arquivo.

Pipe Monitor ( github ) é uma alternativa projetada para gerar atualizações para um fluxo de log via STDERR. Disclaimer: Eu sou o autor.

Sua funcionalidade básica é muito semelhante: Ler de STDIN ou um arquivo. Canalize o conteúdo para STDOUT. Mostrar progresso. No entanto, enquanto o Pipe View usa sequências de controle de terminal para atualizar uma barra de progresso visual em uma única linha, o Pipe Monitor produz atualizações de texto apropriadas para aplicativos não terminais.

O Pipe Monitor suporta as seguintes opções básicas. A saída é personalizável através da opção --format:

Usage: pm [--size SIZE] [--name NAME] [--format FORMAT] INPUT_FILE

Positional arguments:
  INPUT_FILE             Optional input file. If not provided input will be read from STDIN

Options:
  --size SIZE, -s SIZE   Size of input from STDIN. Ignored if using INPUT_FILE
  --name NAME, -n NAME   A NAME tag for this output. Will be pre-pended to default FORMAT string
  --format FORMAT, -f FORMAT
                         Output format string. Allowed keys: %name, %size, %time, %eta, %percent, %written, %buffered
  --help, -h             display this help and exit

Aqui está uma comparação da saída de cada um em um ambiente não terminal.

Visualizador de tubulação (não terminal):

$ pv -nf testin > testout
40
70
77
84
90
96
100

Monitor de tubulação:

$ pm testin > testout
Processed 0 bytes of 2456678400 (0% complete). 0 bytes buffered. Running 0s, eta: <unknown>
Processed 1750794240 bytes of 2456678400 (71% complete). 327680 bytes buffered. Running 2s, eta: 1s
Processed 2106937344 bytes of 2456678400 (85% complete). 700416 bytes buffered. Running 4s, eta: 1s
Processed 2419339264 bytes of 2456678400 (98% complete). 2871296 bytes buffered. Running 6s, eta: 0s
Processed 2456678400 bytes of 2456678400 (100% complete). 0 bytes buffered. Running 6s, eta: 0s

Eu tinha um arquivo SQL de 500 MB para importar. Demorei cerca de 2 horas. O uso da CPU do mysqld estava próximo de 100% no início do processo de importação. Mas depois de alguns minutos o uso da CPU caiu para 15%.

Eu tentei muitos ajustes, mas apenas este me ajudou: innodb_flush_log_at_trx_commit = 0

Depois de aplicar esta configuração e reiniciar o mysql, a importação levou apenas 3 minutos! A utilização da CPU foi de 100% o tempo todo.

Se você gosta de usar esta configuração, você precisará editar o arquivo "/etc/mysql/my.cnf" e reiniciar o servidor mysql usando "sudo service mysql restart".

Aqui estão as configurações do meu arquivo "my.conf":

    [mysqld]
    innodb_log_buffer_size = 256M
    innodb_fast_shutdown = 0
    innodb-doublewrite = OFF
    innodb_io_capacity = 1000
    innodb_flush_log_at_trx_commit = 0

Observe: O "innodb_flush_log_at_trx_commit = 0" fará um commit apenas a cada segundo. Portanto, não é compatível com ACID, mas para uma importação em massa aceitável. Após a importação, você pode definir o valor de "innodb_flush_log_at_trx_commit" de volta para 1 e reiniciar seu banco de dados. Link para a documentação do mySQL

Para alguém que está procurando o exemplo do visualizador de tubos usando mysqldumpvocê, faria algo assim:

mysqldump -hxxx -uxxx -p dbname | pv -W > dump.sql

O -Wsinalizador apenas diz ao pv para esperar o primeiro byte antes de mostrar o progresso (após o prompt)

Se o seu banco de dados estiver quieto (ou seja, não houver outros usuários ativos) e você quiser apenas ver a atividade de leitura/gravação, por que não fazer algo como:

mysqladmin -h<host>-uroot -p<yourpass> extended -r -i 10 |grep 'row'

Você verá o número de leituras/gravações/inserções/esperas/atualizações.

Se você estiver inserindo, por exemplo, verá algo como:

Innodb_rows_inserted                          | 28958 

Onde 28958 é o número de linhas inseridas para o seu intervalo (10 segundos no meu caso).