Resolvendo os problemas do tamanho do ibdata1 no MySQL - versão 2019

Esta é uma atualização da publicação https://www.adilson.net.br/2009/12/resolvendo-os-problemas-do-tamanho.html , que tem quase 10 anos. Só que vou adicionar alguns detalhes novos que mudaram de 2009 para hoje, em 2019.

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 e, também, no MariaDB.

Como o MariaDB é um fork do MySQL, vou tratar tudo como MySQL. Então o que for para o MySQL pode valer também para o MariaDB.

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:


service mysql stop ou systemctl stop mysql


remove o arquivo ibdata1


rm -rf /var/lib/mysql/ibdata1


Dependendo da versão deve-se, também remover mais dois arquivos senão o MySQL não inicializa.

rm -rf /var/lib/mysql/ib_logfile0  /var/lib/mysql/ib_logfile1

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


innodb_file_per_table ou innodb_file_per_table=ON ou innodb_file_per_table=1


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


service mysql start ou systemctl start mysql


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.

Tenham uma boa semana.