domingo, 13 de dezembro de 2009

Resolvendo os problemas do tamanho do ibdata1 no MySQL

Atualização: Depois de quase 10 anos as coisas mudam um pouco. Veja a versão atualizada em:  https://www.adilson.net.br/2019/06/resolvendo-os-problemas-do-tamanho-do.html

Um problema que enfrentava sempre que colocava um aplicativo utilizando o MySQL é o tamanho sempre crescente do arquivo /var/lib/mysql/ibdata1. Este arquivo armazena as tableas do tipo InnoDB e, mesmo que os dados sejam removidos ou elimine tabelas e, até mesmo, banco de dados, o tamanho do ibdata1 não diminui, o que pode ocasionar problemas de espaço em disco mais para frente.

Fazendo uma pesquisa na rede descobri uma boa solução para esta complicação que é definida como padrão em várias instalações do MySQL.

A primeira coisa a fazer é criar um backup de todos os bancos de dados do MySQL que utiliza as tabelas InnoDB. Vamos imaginar que temos apenas um banco de dados deste tipo no servidor MySQL. Então será feito o seguinte:

mysqldump -u"usuario" --password="senha" --routines bancodedados > bancodedados.sql

Remove o banco de dados propriamente dito no console do MySQL:

DROP DATABASE bancodedados;

Desative o serviço do MySQL:

/etc/init.d/mysql stop

remove o arquivo ibdata1

rm -rf /var/lib/mysql/ibdata1

Edita o /etc/mysql/my.cf e adiciona a seguinte linha aonde está o InnoDB:

innodb_file_per_table

Esta linha faz com que as informações das tabelas fiquem em arquivos em separado.

/etc/init.d/mysql start

Recria o banco de dados no console do MySQL

CREATE DATABASE bancodedados;

Restaura o backup dos banco de dados removido:

mysql -u root -p bancodedados < bancodedados.sql

Depois desta configuração, os dados não ficam mais no ibdata1, e sim nos arquivos *.ibd dentro da pasta do banco de dados relacionado. Em algumas tabelas, este arquivo também cresce e, as vezes, não diminui o tamanho mesmo eliminando dados. Mas, para este problema, existe uma maneira de obter mais espaço no servidor após remover os dados.

Dentro do banco de dados, no console do MySQL, rode o comando

OPTIMIZE TABLE tabela;

Este comando faz com que todo espaço vazio dos dados removidos seja liberado no servidor.

13 comentários:

  1. Este comentário foi removido pelo autor.

    ResponderExcluir
  2. Seu post me resolveu um problemão. Parabens pelo artigo

    ResponderExcluir
  3. Esse post, mais uma vez me salvando. \o/

    ResponderExcluir
  4. Oi Adilson.. valeu pela ajuda! No meu caso, precisamos remover, também os arquivos ib_logfile0 e ib_logfile1.

    ResponderExcluir
  5. Boa tarde Adilson.
    Muito bom seu post.
    Más mesmo inserindo a linha "innodb_file_per_table" e realizando os procedimentos descritos acima, o MySQL continua criando os arquivos ib_logfile0, ib_logfile1 e ibdata1, além de um diretório para cada database (acho que este seria o resultado esperarado com a adição da linha "innodb_file_per_table").
    Isso é normal?

    Obrigado.

    ResponderExcluir
    Respostas
    1. Boa tarde!

      Esses arquivos ainda são criados e é uma situação normal, mas não corre o risco de crescer já que os dados ficam nos arquivos dentro da pasta do banco de dados.

      Excluir
  6. Muito importante o que o luis fernando falou ali em cima

    ResponderExcluir
  7. Ola Adilson,

    Fiz com sucesso no meu localhost. No entanto, quero enviar para o servidor um site e não tenho acesso ao ibdata1 do servidor.

    Gostaria de direcionar a pasta do ibdata1 e a pasta das minhas tabelas para um local próprio, mas o servidor guarda o banco de dados em um diretório plesk que não tenho acesso e não posso redirecioná-los ao meu desejo.

    O que percebo é que se eu fizer um backup sem possuir o ibdata1, tal backup não tem nenhum valor, visto que referencias das minhas tabelas existem no tal ibdata1. Ou seja, um backup só pode ser restaurado se com ele estiver o ibdata1 do mesmo momento.

    Exemplo: Se eu fizer um backup agora e em seguida lançar qualquer dado em uma das tabelas do servidor e depois tentar restaurar o backup que fiz antes de lançar tal dado, já era! O ibdata1 rejeita meu backup porque seus índices já possuem dados de um momento posterior. A única forma seria: deletar o ibdata1 para que seja recriado novamente mas isso não é possível em um servidor compartilhado.

    Não sei se isto é normal nos outros servidores, mas no meu caso, não posso definir nada em my.ini. Nem sequer o tempo de execução de um script que está limitado a 30 segundos pelo meu servidor e tenho dificuldades até de atualizar meus dados.

    Enfim, seria possível ter um ibdata1 direcionado para pastas próprias via php bem como as minhas tabelas?

    Existe alguma solução para backup e restauração de dados de tabelas innoDB?

    Agradeço.

    ResponderExcluir
  8. Bom dia Adilson,
    Depois de utilizar o comando de remoção:
    m -rf /var/lib/mysql/ibdata1

    não consegui mais acessar o mysql, da o seguinte erro:
    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
    O que devo fazer?
    Muito obrigado

    ResponderExcluir
  9. E com o comando:
    /etc/init.d/mysql start
    Da a seguinte msg:
    Upstart job, you may also use the start(8) utility, e.g. start mysql
    start: Job failed to start

    ResponderExcluir