table - shrink db mysql




Como encolher/limpar o arquivo ibdata1 no MySQL (6)

Eu estou usando o MySQL em localhost como uma "ferramenta de consulta" para executar estatísticas em R, ou seja, toda vez que eu executar um script R, criar um novo banco de dados (A), criar uma nova tabela (B), importar os dados em B , envie uma consulta para obter o que eu preciso e, em seguida, 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?


Se você usar o mecanismo de armazenamento do InnoDB para algumas de suas tabelas do 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 transações) da instância do MySQL e pode ficar bastante grande. Por padrão, esse arquivo tem um tamanho inicial de 10Mb e é estendido 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/


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 excluir 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.


Em uma nova versão do servidor mysql, as receitas acima irão esmagar o banco de dados "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 é despejar 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 banco de dados /root/.my.cnf no diretório /var/lib/mysql padrão.

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

PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;

Se o seu objetivo é 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


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, este é ativado 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 banco de dados para análise, as tabelas estarão localizadas em arquivos ibd* separados, não em ibdata1 . Como geralmente você solta o banco de dados 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 <tablename> ENGINE=innodb ou OPTIMIZE TABLE <tablename> é 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 leitura, não de tabelas. E não há arquivos associados a eles, nem mesmo um diretório de banco de dados. O informations_schema está usando o mecanismo de memória db e é descartado e regenerado após a parada / reinicialização do mysqld. Veja https://dev.mysql.com/doc/refman/5.7/en/information-schema.html .







innodb