Reciclando o espaço utilizado pelo arquivo ibdata1 do Mysql.

Por padrão o Mysql, armazena os metadados de todos os bancos de dados no arquivo ibdata1, o qual pode “crescer” ao ponto de ocupar todo o espaço livre em disco. Além disto, quando removemos algumas tabelas ou algumas linhas de determinadas tabelas os dados não são fisicamente removidos do arquivo ibdata1.

Desta forma, o objetivo deste artigo é descrever os passos necessários para: (i) ajustar as configurações do Mysql, de forma que salve os dados de cada tabela em um arquivo separado; e (ii) recriar os bancos e suas tabelas.

Mysql Log
Mysql Log

Começamos o processo ajustando as configurações do Mysql; devemos adicionar as linhas abaixo na seção [mysqld] do arquivo “/etc/mysql/my.cnf”

innodb_buffer_pool_size = 1028M
innodb_file_per_table=1

A primeira linha define o limite do buffer associado as operações realizadas nos bancos do tipo innodb e a segunda informa que para cada tabela será criado um arquivo de metadados separado. Para ativar estas configurações e prosseguir com o processo precisamos reiniciar o mysql.

sudo service mysql restart

Com os ajustes devidamente aplicados ao Mysql podemos iniciar a extração dos dados contidos em todos os bancos. O processo é relativamente simples mas pode ser um pouco demorado.

Para realizar o backup da estrutura e dos dados de todos os bancos de dados podemos utilizar o comando abaixo; o qual supõe que estamos conectados na maquina que hospeda o banco de dados, por isto utilizamos o valor “127.0.0.1”

time sudo nice --10 mysqldump --host=127.0.0.1 \
--user=USER  --password=PASS \
--all-databases > full-databses.sql

No comando acima, é necessário informar o nome de um usuário valido no banco de dados (USER) e a sua senha (PASS).

O comando “time” fou utilizado de forma que findo o processo saibamos quanto tempo foi gasto; já o comando “nice –10” foi utilizado para aumentar a prioridade do processo de backup, tornando-o, teoricamente, mais rápido

Depois de realizar o backup dos dados podemos iniciar o processo de exclusão dos bancos; devemos apagar tocos os bancos exceto: information_schema, mysql e performance_schema.

Supondo que o mysql esteja instalado na maquina que estamos conectados, podemos utilizar o comando abaixo para nos conectarmos ao gerenciador de banco de dados:

mysql --host=127.0.0.1 --user=USER  --password=PASS

Assim como no comando anterior, é necessário informar o nome de um usuário valido no banco de dados (USER) e a sua senha (PASS).

Podemos utilizar o comando a seguir para listar todos os bancos existentes:

SHOW DATABASES;

Supondo que a máquina possui os três bancos listados anteriormente (information_schema, mysql e performance_schema) mais os bancos: Syslog e phpmyadmin; podemos remover estes dois últimos utilizando:

DROP DATABASE Syslog;
DROP DATABASE phpmyadmin;

Ou seja para casa banco cadastrado precisamos utilizar um comando “DROP DATABASE NOME_DO_BANCO”.

Depois de remover os bancos, podemos verificar se ainda há algum que ficou faltando

SHOW DATABASES;

O comando acima deve retornar algo semelhante à:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

Com todos os bancos removidos, podemos parar o serviço do Mysql

sudo service mysql stop

E apagar os arquivos “ibdata1” e “ib_log”

cd /var/lib/mysql
rm ibdata1 ib_logfile*

Depois de remover os arquivos podemos reiniciar o mysql

sudo service mysql restart

Podemos observar se o serviço está rodando verificando a sua porta associada ao Mysql está em estado de escuta (LISTEM)

lsof  -i:3306

Caso o serviço esteja ativo, devemos obter uma saída semelhante à:

COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  3793 mysql   10u  IPv4  32204      0t0  TCP localhost:mysql (LISTEN)

Já que o serviço está ativo, podemos verificar quais bancos ainda estão disponíveis. Primeiro devemos nos conectar ao Mysql

mysql --host=127.0.0.1 --user=USERS  --password=passos

E executar o comando:

SHOW DATABASES;

Mesmo depois de remover todos os arquivos ainda deve haver os três bancos utilizados pelo Mysql

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

Com o Mysql “Limpo”, podemos retornar o dump/backup dos bancos que existiam

mysql -u USER -p < full-databses.sql

Findo o processo de Restores o Mysql e os bancos poderão ser utilizados normalmente