Recuperar Banco de Dados MySQL InnoDB

 

É claro que a melhor solução é ter um backup atualizado. Mas nem sempre isso acontece, e problemas, falhas e erros podem acontecer. Em uma última situação que enfrentei, tinha o backup mas estava desatualizado de alguns bancos. A maior parte dos dados foram recuperados pelo dump do backup, mas nem todos puderam ser feitos. Tínhamos acesso ainda à pasta data do mysql com os arquivos, mas quando ia iniciar o serviço não iniciava.

Nessa situação a primeira coisa a se fazer é verificar o arquivo de log do mysql, que se encontra na pasta data, com o nome da maquina.err

No caso do linux e similares, uma possível pasta é /usr/local/var/mysql e também pode usar o comando locate .err para localizar arquivos que possuem a extensão.

Localizado o arquivo, tente iniciar o serviço do mysql e depois veja o conteúdo do arquivo de erro.

Alguns passos que podem ser feitos para tentar corrigir o problema.

1.  Mova os arquivos  ib_logfile0 e ib_logfile1 para outro diretório e tente iniciar o serviço novamente.
2. Se for linux, verifique as permissões dos arquivos da pasta data.

Caso ainda não inicie, continue analisando o arquivo de log do mysql. Nesse caso específico o problema foi mais sério, pois o arquivo ibdata1 estava realmente corrompido.

InnoDB: stored checksum 3103110567, prior-to-4.0.14-form stored checksum 438929502
InnoDB: Page lsn 66 2543532480, low 4 bytes of lsn at page end 2544351964
InnoDB: Page number (if stored to page already) 131080,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 0 381
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 131080.

Tive que partir para medidas mais sérias. Existe um parâmetro do mysql para forçar ou ignorar erros no arquivo do innodb, que é innodb_force_recovery = X onde o x varia de 1 até 6.

• 1 (SRV_FORCE_IGNORE_CORRUPT)
  Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.
• 2 (SRV_FORCE_NO_BACKGROUND)
  Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it.
• 3 (SRV_FORCE_NO_TRX_UNDO)
  Does not run transaction rollbacks after crash recovery.
• 4 (SRV_FORCE_NO_IBUF_MERGE)
  Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate tablestatistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes.
• 5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
  Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed. This value can permanently corrupt data files.
• 6 (SRV_FORCE_NO_LOG_REDO)
  Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt data files. Leaves database pages in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

A próxima pergunta é qual número usar. Recomendo paciência nesse caso e testar valor por valor. Quando menor o valor utilizado menos prejuízo você terá. Altere o arquivo my.cnf ou my.ini do mysql e coloque na [mysqld]

[mysqld]
innodb_force_recovery=1

Existem outros parâmetros que também podem ajudar, listados abaixo. mas não tente colocar todos de uma vez. Tenha paciência, e se somente o innodb_force_recovery não resolver, adicione os parâmetros abaixo e tente iniciar o serviço para cada alteração.

innodb_max_dirty_pages_pct = 0
innodb_purge_threads=1
skip-grant-tables

Na maioria das situações esses passos resolvem o problema. Caso tenha resolvido, recomendo fazer um dump das bases de dados, de todas, e restaurar em uma nova instalação do mysql, pois esses medidas são temporárias com o objetivo de extrair os dados.


Mas nem sempre essas medidas funcionam. Foi justamente o que aconteceu comigo dessa vez.

A próxima opção é recuperar os dados a partir dos arquivos .FRM e .IBD .

Independente de qual Sistema Operacional e versão do Mysql deve funcionar. Somente é necessário fazer as adaptações de acordo com cada situação.

Para os próximos passos, mova a pasta data para um outro local seguro, se possível, copie para uma mídia externa e continue mexendo em uma cópia local na máquina. Feito a cópia, faça uma nova instalação do mysql sem utilizar a pasta antiga data. Feito a nova instalação e tendo acesso aos arquivos da pasta data da instalação anterior e uma copia em um local seguro, siga para os próximos passos.

 1- Ativar INNODB_FILE_PER_TABLE

Primeiro devemos entrar novamente no arquivo my.cnf ou my.ini e na cláulsual [mysqld] acrescentar a seguinte linha. Se você tentou os passos anteriores, não se esqueca de retirar ou comentar todas as linhas adicionadas, como a innodb_force_recovery e as demais . Para comentar pode colocar # no início da linha.

[mysqld]
innodb_file_per_table=1 


Salve e feche o arquivo.

2- INSTALar MYSQL UTILITIES 

Faça o download e instale o mysql Utilities em https://dev.mysql.com/downloads/utilities/

3- Acesso ao MYSQL COMMAND

Entre no mysql pela linha de comando. Se não estiver no path do sistema, procure na pasta bin do mysql

mysql -u root -p

É pra conseguir entrar no mysql sem problemas. As senhas padrões normalmente são root e vazia. Caso não acesse pelas senhas padrões use o usuário e senha que foram definidos na nova instalação do mysql.

4- CRiar e selecionar uma nova base de dados

O próximo passo é criar um novo banco de dados para importar as tabelas que queres recuperar. Pode ser com o mesmo nome do banco anterior.

create database test;

use test;

Até agora não começamos o processo de recuperação, somente de preparação para os dados.


5- CRiar os arquivos MYSQL

O mais importante agora, é ter a estrutura das tabelas que queremos recuperar. Caso tenhas um backup antigo delas com a mesma estrutura das atuais é o recomendável utilizar. Mas caso não tenha, ainda não se desespere, temos duas opções ainda para recuperar a estrutura.

1. Utilizar o backup antigo que contenha a mesma estrutura do banco atual.
2. Utilizar o Myslq Utilities que instalamos nos passos anteriores, que ele busca a estrutura no arquivo .FRM e gera o script sql.
3. Utilizar o site  https://recovery.twindb.com/ que, a partir do teu arquivo .FRM ele gera gratuitamente o script sql.

• Na coluna da esquerda, clique em Recover Struture, e após clique em From .frm File
• Clique em Browse e procure o arquivo .frm da pasta data original que contém o banco corrompido. Aquela pasta que pedi para salvar anteriormente. 
• Clique em Upload.
• Um popup irá aparecer com a estrutura da tabela, em sql. Copie esses comandos e cole na linha de comando do mysql para criar a tabela. Isso irá criar a tabela com os arquivos .frm e .ibd correspondentes.

Deverá ser feito isso para cada tabela do banco. No meu caso utilizei o método 1.

6- remover arquivos IBD Novos

O próximo passo é remover os arquivos .ibd NOVOS , atenção, os novos criados limpos, para substituirmos com os antigos que possuem os dados que queremos recuperar.

Volte ao prompt de comando do mysql e digite o seguinte comando para cada tabela.

ALTER TABLE filename DISCARD TABLESPACE

Como mencionei, esse comando apaga os arquivos ibd das tabelas novas e deverá ser executado para cada tabela que se queira recuperar. Troque o filename pelo nome da tabela.

7- copiar arquivos .ibd originais

É necessário copiar agora os arquivos .idb do banco original, salvo anteriormente, para a nova instalação do mysql, na pasta data da nova instalação.

cp /backup/mysql/data/database_original/tabela.idb /var/local/mysql/data/database_novo/.

onde database_original é o nome do banco original, que havia sido corrompido e o database_novo e o nome do novo banco de dados criado na nova instalação do mysql.

8- IMPORTar DAdos para a novas tabelas

O último passo é associar os arquivos .ibd à nova tabela criada novamente. No prompt do mysql use o seguinte comando.

ALTER TABLE finename IMPORT TABLESPACE;

Onde filename é o nome da tabela. Lembre-se de repetir esses passos para todas as tabelas do banco e para cada banco de dados.



Parabéns, seguindo esses passos você deve ter conseguido recuperar os seus dados. Deixe um comentário e se inscreva no blog para receber novas dicas.