Resolvendo os problemas do tamanho do ibdata1 no MySQL

Baseado em: http://www.adilson.net.br/2009/12/resolvendo-os-problemas-do-tamanho.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.

Obs: Este mesmo procedimento funciona no Windows. Vale atentar que a localização do arquivo em questão fica em: c:\ibdata\ibdata1 de acordo com o link: http://dev.mysql.com/doc/refman/5.5/en/windows-server-first-start.html