Como encolher / limpar o arquivo ibdata1 no MySQL

Eu estou usando o MySQL em localhost como uma “ferramenta de consulta” para a realização de statistics em R, ou seja, toda vez que eu executar um script R, criar um novo database (A), criar uma nova tabela (B), importar os dados em B , envie uma consulta para obter o que eu preciso, e depois solto B e solto A.

Está funcionando bem para mim, mas percebo que o tamanho do arquivo ibdata está aumentando rapidamente, não armazenei nada no MySQL, mas o arquivo ibdata1 já excedeu 100 MB.

Eu estou usando mais ou menos a configuração padrão do MySQL para a configuração, existe uma maneira para eu posso automaticamente reduzir / limpar o arquivo ibdata1 após um período fixo de tempo?

Esse ibdata1 não está encolhendo é uma característica particularmente irritante do MySQL. O arquivo ibdata1 na verdade não pode ser reduzido a menos que você exclua todos os bancos de dados, remova os arquivos e recarregue um dump.

Mas você pode configurar o MySQL para que cada tabela, incluindo seus índices, seja armazenada como um arquivo separado. Dessa forma, ibdata1 não crescerá tão grande. De acordo com o comentário de Bill Karwin, isso está habilitado por padrão a partir da versão 5.6.6 do MySQL.

Foi há um tempo atrás que fiz isso. No entanto, para configurar seu servidor para usar arquivos separados para cada tabela, você precisa alterar my.cnf para habilitar isso:

 [mysqld] innodb_file_per_table=1 

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

Como você deseja recuperar o espaço do ibdata1 você realmente precisa excluir o arquivo:

  1. Faça um mysqldump de todos os bancos de dados, procedimentos, gatilhos etc, exceto os bancos de dados mysql e performance_schema
  2. Solte todos os bancos de dados, exceto os 2 bancos de dados acima
  3. Pare o mysql
  4. Exclua os arquivos ibdata1 e ib_log
  5. Inicie o mysql
  6. Restaurar do despejo

Quando você iniciar o MySQL na etapa 5, os arquivos ibdata1 e ib_log serão recriados.

Agora você está pronto para ir. Quando você cria um novo database para análise, as tabelas estarão localizadas em arquivos ibd* separados, não em ibdata1 . Como geralmente você solta o database logo depois, os arquivos ibd* serão excluídos.

http://dev.mysql.com/doc/refman/5.1/en/drop-database.html

Você provavelmente já viu isso:
http://bugs.mysql.com/bug.php?id=1341

Usando o comando ALTER TABLE ENGINE=innodb ou OPTIMIZE TABLE é possível extrair dados e páginas de índice do ibdata1 para separar arquivos. No entanto, ibdata1 não diminuirá a menos que você execute as etapas acima.

Em relação ao information_schema , isso não é necessário nem possível. Na verdade, é apenas um monte de visualizações somente de leitura, não de tabelas. E não há arquivos associados a eles, nem mesmo um diretório de database. O informations_schema está usando o mecanismo de memory db e é descartado e regenerado na parada / reboot do mysqld. Veja https://dev.mysql.com/doc/refman/5.7/en/information-schema.html .

Quando você exclui tabelas innodb, o MySQL não libera o espaço dentro do arquivo ibdata, é por isso que continua crescendo. Esses arquivos quase nunca encolhem.

Como reduzir um arquivo ibdata existente:

http://dev.mysql.com/doc/refman/5.5/en/innodb-resize-system-tablespace.html

Você pode fazer o script e agendar o script para ser executado após um período de tempo fixo, mas para a configuração descrita acima, parece que vários espaços de tabela são uma solução mais fácil.

Se você usar a opção de configuração innodb_file_per_table , criará vários espaços de tabela. Ou seja, o MySQL cria arquivos separados para cada tabela, em vez de um arquivo compartilhado. Esses arquivos separados são armazenados no diretório do database e são excluídos quando você exclui esse database. Isso deve remover a necessidade de reduzir / limpar arquivos ibdata no seu caso.

Mais informações sobre vários espaços de tabela:

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

Adicionando a resposta de John P ,

Para um sistema Linux, os passos 1-6 podem ser realizados com estes comandos:

  1. mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql
  2. DROP DATABASE [database_name];
  3. sudo /etc/init.d/mysqld stop
  4. sudo rm /var/lib/mysql/ibdata1
    sudo rm /var/lib/mysql/ib_logfile (e exclua qualquer outro ib_logfile que possa ser denominado ib_logfile0 , ib_logfile1 etc …)
  5. sudo /etc/init.d/mysqld start
  6. create database [database_name];
  7. mysql -u [username]-p[root_password] [database_name] < dumpfilename.sql

Aviso: estas instruções farão você perder outros bancos de dados se você tiver outros bancos de dados nesta instância mysql. Certifique-se de que as etapas 1,2 e 6,7 sejam modificadas para abranger todos os bancos de dados que você deseja manter.

Se você usa o mecanismo de armazenamento InnoDB para algumas de suas tabelas MySQL, provavelmente já se deparou com um problema com sua configuração padrão. Como você deve ter notado no diretório de dados do seu MySQL (no Debian / Ubuntu – / var / lib / mysql) está um arquivo chamado ‘ibdata1’. Ele contém quase todos os dados do InnoDB (não é um log de transactions) da instância do MySQL e pode ficar bastante grande. Por padrão, este arquivo tem um tamanho inicial de 10Mb e se estende automaticamente. Infelizmente, por design os arquivos de dados do InnoDB não podem ser reduzidos. É por isso que DELETEs, TRUNCATEs, DROPs, etc. não recuperam o espaço usado pelo arquivo.

Eu acho que você pode encontrar uma boa explicação e solução lá:

http://vdachev.net/2007/02/22/mysql-reducing-ibdata1/

Se o seu objective é monitorar o espaço livre do MySQL e você não pode parar o MySQL para encolher seu arquivo ibdata, então obtenha através dos comandos de status da tabela. Exemplo:

MySQL> 5.1.24:

 mysqlshow --status myInnodbDatabase myTable | awk '{print $20}' 

MySQL <5.1.24:

 mysqlshow --status myInnodbDatabase myTable | awk '{print $35}' 

Em seguida, compare esse valor com seu arquivo ibdata:

 du -b ibdata1 

Fonte: http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

Em uma nova versão do servidor mysql, as receitas acima irão esmagar o database “mysql”. Na versão antiga, funciona. No novo, algumas tabelas mudam para o tipo de tabela INNODB, e ao fazer isso você irá danificá-las. A maneira mais fácil é descarregar todos os seus bancos de dados, desinstalar o mysql-server, adicionar no my.cnf:

 [mysqld] innodb_file_per_table=1 erase all in /var/lib/mysql install mysql-server restore users and databases 

Escreveu rapidamente o procedimento da resposta aceita no bash:

 #!/usr/bin/env bash DATABASES="$(mysql -e 'show databases \G' | grep "^Database" | grep -v '^Database: mysql$\|^Database: binlog$\|^Database: performance_schema\|^Database: information_schema' | sed 's/^Database: //g')" mysqldump --databases $DATABASES -r alldatabases.sql && echo "$DATABASES" | while read -r DB; do mysql -e "drop database \`$DB\`" done && \ /etc/init.d/mysql stop && \ find /var/lib/mysql -maxdepth 1 -type f \( -name 'ibdata1' -or -name 'ib_logfile*' \) -delete && \ /etc/init.d/mysql start && \ mysql < alldatabases.sql && \ rm -f alldatabases.sql 

Salve como purge_binlogs.sh e execute como root .

Exclui mysql , information_schema , performance_schema (e diretório binlog ).

Supõe que você tenha /root/.my.cnf administrador em /root/.my.cnf e que seu database /root/.my.cnf no diretório /var/lib/mysql padrão.

Você também pode limpar logs binários depois de executar esse script para recuperar mais espaço em disco com:

 PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP; 

Como já foi observado, você não pode encolher ibdata1 (para fazer isso você precisa descarregar e reconstruir), mas também não há necessidade real de fazê-lo.

Usando o autoextend (provavelmente a configuração de tamanho mais comum), o ibdata1 pré-aloca armazenamento, crescendo sempre que estiver quase cheio. Isso torna as gravações mais rápidas, já que o espaço já está alocado.

Quando você exclui dados, ele não diminui, mas o espaço dentro do arquivo é marcado como não usado. Agora, quando você inserir novos dados, ele reutilizará o espaço vazio no arquivo antes de aumentar o arquivo.

Por isso, só continuará a crescer se você realmente precisar desses dados. A menos que você realmente precise de espaço para outro aplicativo, provavelmente não há razão para reduzi-lo.