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.
Este comentário foi removido pelo autor.
ResponderExcluirSeu post me resolveu um problemão. Parabens pelo artigo
ResponderExcluirEsse post, mais uma vez me salvando. \o/
ResponderExcluirOi Adilson.. valeu pela ajuda! No meu caso, precisamos remover, também os arquivos ib_logfile0 e ib_logfile1.
ResponderExcluirBoa tarde Adilson.
ResponderExcluirMuito 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.
Boa tarde!
ExcluirEsses 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.
Obrigado!
ResponderExcluirMuito importante o que o luis fernando falou ali em cima
ResponderExcluirOla Adilson,
ResponderExcluirFiz 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.
Bom dia Adilson,
ResponderExcluirDepois 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
Adicionou a linha no my.cf?
Excluiradicionei, ele não localiza o mysqld.sock
ExcluirE com o comando:
ResponderExcluir/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