MySQL InnoDB Recovery


0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Filament.io Made with Flare More Info'> 0 Flares ×

InnoDB Recovery

InnoDB Recovery

(Eu deveria ter feito backup)

Caso seu MySQL esteja configurado para utilizar o InnoDB em um único arquivo (ibdata1) e caso os próximos passos falhem você terá grandes chances de perder quase tudo ou tudo do seu banco de dados.

Se você não usa a flag innodb_file_per_table, mas chegou aqui já precisando se recuperar de um desastre, descobrimos a função deste último infelizmente após um episódio onde não houve recovery em um banco MySQL em produção.

Quase 40GB de informações foram perdidos e o sistema de backup, que não era conferido, já estava inoperante a quase 1 mês.

Isso significa que você tem pouco o que fazer caso o MySQL não suba – você pode colocar no my.cnf a flag innodb_force_recovery = [flag de 1-6].

Onde indica qual o tipo de recuperação deseja tentar, a flag 1 tenta restore através do journal do binlog – e é o restore mais recomendável caso o seu MySQL tenha rodado durante um longo período com pouca memória ram e/ou usando memória swap.

Então vamos lá:

$ su mysql
$ mysqld

Caso o resultado se refira ao checksum do ibdata1, você poderá incluir a seguinte flag na chave [mysqld] em /etc/mysql/my.cnf conforme abaixo:

[mysqld]
innodb_force_recovery = 1

Mas antes de startar o banco faça backup da base corrompida e leia a documentação diponível aqui. Porque se a flag incial não funcionar analise seu caso e tente utilizar a flag que melhor se enquadra em seu caso, por exemplo, caso não seja problema de memória poderia ser um bad block no seu HD? Você utiliza filesystem JFS ou algum outro não jornalizado?

Por isso o backup da base corrompida é essencial, você poderá testar todas as flags de recovery e caso não consiga um full innodb recovery, usar o que mais restaurar seus dados.

$ su mysql
$ mysqld

O banco irá subir com o InnoDB em read-only, caso consiga restaurar. Após realizar a checagem finalize o processo com kill mysqld, mas atenção: não use kill -9 para que o MySQL possa ser finalizado “graciosamente” ou receba um process end gracefully.

Mas se os passos do InnoDB Recovery não funcionaram, boa sorte no Google, por experiência a partir deste ponto se você não conseguir restaurar seus dados estará com grandes chances de perdê-los quase que em totalidade.

Prevenindo um InnoDB Recovery

É recomendável que você leia e entenda principalmente a função da flag innodb_file_per_table já que ela é peça chave para evitar que desastres maiores e/ou futuros ocorram.

Prevenindo um InnoDB Recovery

(Não coloque todos os seus ovos em uma única cesta)

O InnoDB é um mecanismo de armazenamento muito interessante para o MySQL, que combina um desempenho razoável com grandes recursos e  como conseqüência, um bom conjunto de ferramentas para diagnóstico e tunning. Além disso também suporta operações ACID, já que fornece relacionamento indexado e gerenciado pelo SGDB entre chaves primárias e estrangeiras – coisa que não ocorre na engine MyISAM.

Uma das desvantagens é que é ineficiente no que se refere à gestão do espaço do disco. Enquanto uma extensão de espaço em disco rígido foi alocado ao armazenamento, o InnoDB não vai liberá-lo, mesmo quando você excluir tabelas ou bases de dados. Para adicionar um pouco de flexibilidade, você deve usar a opção innodb_file_per_table.

Infelizmente, se você tem um banco de dados em produção, você não pode simplesmente ativar essa opção. Você terá que fazer um dump do banco de dados e restaurá-lo em uma nova instância do MySQL com a opção ativada desde o início. Este cenário significa que o banco de dados estará inacessível a partir do momento que você começar o mysqldump para o momento em que você terminar de restaurar os dados na nova instância.

Existe uma maneira de minimizar o tempo de inatividade ?

Sim , você pode executar o mysqldump em um backup do seu banco de dados. Mas, em seguida, você perde os dados gravados no banco de dados a partir do momento que você faça o backup no momento em que a nova instância está pronta. Outra solução seria configurar a replicação através do binlog do MySQL após subir a nova instância com a flag innodb_file_per_table, mas se você não tem a extrema necessidade de manter seu banco de produção up durante este procedimento – vamos aos passos:

 

Habilitando innodb_file_per_table

Edite o arquivo my.cnf na nova instância do seu banco de dados

vi /etc/mysql/my.cnf

Adicione o seguinte conteúdo

innodb_file_per_table = 1
innodb_file_format = barracuda

Em seguida reinicie o MySQL desta instância:

$ sudo /etc/init.d/mysql restart

 

Realizando o mysqldump

Em seu banco de dados de produção execute:

mysqldump -u root -p --routines --events --flush-privileges --all-databases > /tmp/dump.sql
Nota importante: porque --routines? Por padrão o mysqldump, não exporta as stored procedures e functions. Porque --events? Para exportar todos os schedulers programados. E por fim, porque --flush-privileges? Durante a criação de cada rotina, evento um permissionamento é baseado no criador geralmente root@localhost, mas em algumas situações isso pode mudar - o que poderá divergir da hora em que você estiver restaurando o dump (Coisa que não queremos).

Caso esteja em outro servidor utilize o scp ou o meio de transferir arquivos que preferir para copiar o dump.sql.

[Na nova instância do MySQL]

$ cd ~
$ scp user@hostname-db-producao:/tmp/dump.sql .
$ mysql -u root -p < dump.sql

Force o mysql_upgrade (para recriar o performance_schema)

# mysql_upgrade --force

Pronto. Verá que será criados vários arquivos *.idb no seu datadir.

Prós e contras

Vou discorrer sobre as vantagens e desvantagens das duas opções, e vou me esforçar para convencer de que a opção innodb_file_per_table é preferível.

Caso de ibdata1 (uma única tabela)

Tendo tudo em um grande arquivo significa que todas as tabelas e índices, a partir de todos os sistemas, estão “misturados”, ou seja, juntos nesse grande arquivo.

Isso permite que a seguinte propriedade seja favorável: espaço livre que pode ser compartilhado entre diferentes tabelas e esquemas diferentes. Assim, se eu limpar muitos registros de uma tabela de log, por exemplo, o espaço agora não utilizado pode ser ocupado por novos registros de qualquer outra tabela.

Esta mesma propriedade favorável também se traduz em uma não tão boa: os dados podem ser muito fragmentado em todo o espaço de tabela.

Uma propriedade irritante da storage engine InnoDB é que ele nunca desaloca espaço. Então, depois de expurgar os registros da tabela de log, o arquivo da tabela (geralmente ibdata1) ainda mantém o mesmo armazenamento. Ele não libera o espaço livre do file system, jamais.

Já vi mais de uma vez como certas tabelas são deixados sem monitoramento, crescendo até que o espaço em disco atinge 90% ou até estourar o espaço livre da partição e o banco de dados cair ou ficar em read-only.

Há pouco a se fazer neste caso. Mas bem, sempre se pode limpar registros. Claro, o espaço seria reutilizado pelo InnoDB. Mas ter um arquivo que consome cerca de 80 a 90% de espaço em disco é uma catástrofe de desempenho. Isso significa que a agulha do disco precisa mover grandes distâncias. O desempenho geral do disco funciona muito baixo.

Uma maneira de resolver isso é dumpar os dados com a flag recriação e re-importá-los. Mas… Novamente e o tempo pra realizar isso? O banco ficaria off a menos que você fizesse isso utilizando um banco slave (O que as vezes não está disponível).  Supondo que você esteja usando apenas InnoDB, um dump com -single-transaction irá fazer o trabalho sujo. Ou você pode utilizar mk-paralell-dump para acelerar as coisas (dependendo do seu método de dump e de acessibilidade necessidades, importante para bloquear nos registros durante o restore em produção) .

innodb_file_per_table

Com este parâmetro, um conjunto de arquivos é criado por tabela. O que temos é isto:

  • Tablespace não é compartilhado entre diferentes tabelas, e certamente não entre esquemas diferentes.
  • Cada arquivo é considerado um espaço de tabela própria.
  • Mais uma vez, nunca a tabela reduz de tamanho.
  • É possível recuperar o espaço por espaço de tabela.

Espere . Os dois últimos parecem conflitantes, não é? Vamos explicar .

No nosso exemplo tabela de log, onde expurgamos muitas linhas (até 90GB de dados são removidos ) . O arquivo ibd  não reduziu seu tamanho. Mas o que podemos fazer:

ALTER TABLE log ENGINE = InnoDB

O que vai acontecer é que um novo arquivo temporário é criado, enquanto a tabela é reconstruída. Apenas dados existentes são adicionados à nova tabela. Uma vez completado, a tabela original é removida, e a nova tabela renomeada como o nome da tabela original.

Claro, isso leva muito tempo, durante o qual a tabela é completamente bloqueado: escrita, nem leitura são permitidos. Mas ainda assim o que nos permite recuperar o espaço em disco.

Com o novo plugin InnoDB, espaço em disco também é recuperada quando executamos uma declaração TRUNCATE TABLE log.

A fragmentação não é tão ruim quando em uma única tabela: os dados são restringidos dentro dos limites de um arquivo menor.

Monitoração

Uma outra coisa favorável sobre innodb_file_per_table é que é possível monitorar o tamanho da tabela no nível de sistema de arquivos. Você não precisa de acesso ao MySQL para usar SHOW TABLE STATUS ou para consultar o INFORMATION_SCHEMA. Você pode apenas olhar para cima os 10 maiores arquivos do diretório de dados do MySQL (e subdiretórios) e monitorar o seu tamanho. Você pode ver qual tabela cresce mais rápido, por exemplo.

Backup

Ainda não é possível fazer backup da tabelas InnoDB copiando somente os arquivos *.idb. Mas espero que o trabalho seja feito para possibilitar o que é disponível para o storage engine MyISAM nas versões futuras.

Sumário
Nome do Artigo
MySQL InnoDB Recovery
Descrição
Howto de MySQL InnoDB Recovery Disaster - Resultado quando o mysqld exibe a mensagem: "failed to start process" e aponta checksum fail
Autor
0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Filament.io Made with Flare More Info'> 0 Flares ×