MySQL não inicia – Corrupção e Recuperação da InnoDB

Bancos de dados podem corromper por diversos motivos. Em nosso caso, a bateria do nosso nobreak estava com problemas e nosso Hyper-V Server desligou durante operações de escrita ao nosso banco de dados. Corrupção na InnoDB pode tornar todos os bancos de dados executando em um servidor inacessíveis. Sem entrar em detalhes técnicos do por quê disso, você descobrirá que seu banco de dados estará inacessível e o servidor MySQL simplesmente não inicia.

Eu tenho a dizer o que todo mundo diz, “Backup, Backup, e Backup”. Certifique-se que você mantém uma boa rotina de backup executando um dump para restaurar seu banco de dados caso ocorra corrupção ou perda de dados (o que é inevitável).

MYSQL NÃO INICIA: COMO RESTAURAR UM BANCO DE DADOS INNODB?

Nosso servidor é um Debian 8.x e MySQL 5.5. Distribuições baseadas no Red Hat são diferentes e um bom conhecimento de linha de comando são úteis aqui. Você não conseguirá realizar nenhum dos passos abaixo sem acesso ao shell do seu servidor via SSH. Você também não conseguirá reparar ou checar suas tabelas via phpmyadmin, WHM or cPanel.

Primeiro, nós precisamos acessar nosso servidor e esta é a parte que a experiência com a linha de comando é importante. Você deve ter acesso root ao seu servidor executando MySQL. Vamos dar uma olhada no log de erro do MySQL:

ssh user@my.server.com 
tail -500 /var/log/mysql/error.log
130306 22:02:18 mysqld_safe Number of processes running now: 0
130306 22:02:18 mysqld_safe mysqld restarted
130306 22:02:18 [Note] Plugin 'FEDERATED' is disabled.
130306 22:02:18 InnoDB: The InnoDB memory heap is disabled
130306 22:02:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130306 22:02:18 InnoDB: Compressed tables use zlib 1.2.3
130306 22:02:18 InnoDB: Using Linux native AIO
130306 22:02:18 InnoDB: Initializing buffer pool, size = 128.0M
130306 22:02:18 InnoDB: Completed initialization of buffer pool
130306 22:02:18 InnoDB: highest supported file format is Barracuda.
130306 22:02:18 InnoDB: 5.5.30 started; log sequence number 1629186928
130306 22:02:18 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130306 22:02:18 [Note] - '0.0.0.0' resolves to '0.0.0.0';
130306 22:02:18 [Note] Server socket created on IP: '0.0.0.0'.
130306 22:02:18 [Note] Event Scheduler: Loaded 0 events
130306 22:02:18 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.30-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
130306 22:02:19 InnoDB: Error: page 393457 log sequence number 111 561,760,232
InnoDB: is in the future! Current system log sequence number 70 3,946,969,851.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: for more information.

Nós vamos tentar nosso melhor para encontrar alguma informação que possa nos ajudar a diagnosticar o problema, mas algo está definitivamente errado e o procedimento abaixo pode falhar.

Passos para restaurar o serviço.

0. Com base no arquivo de log, é recomendável realizar uma verificação de disco (fsck), já que é possível que erros no disco rígido possam ter causado a corrupção do banco de dados.

1. Pare o serviço mysql

/etc/init.d/mysql stop

2. Realize backup dos arquivos /var/lib/mysql/ib*

mkdir /backup
rsync -a -P /var/lib/mysql/ib* /backup

3. Adicione a seguinte linha a seção [mysqld] do arquivo /etc/mysql/my.cnf

nano /etc/mysql/my.cnf
[mysqld]
innodb_force_recovery = 6

4. Reinicie o serviço mysql

/etc/init.d/mysql start
[ ok ] Starting mysql (via systemctl): mysql.service

5. Verifique o arquivo de log do MySQL.

160915 12:50:53 [ERROR] Cannot find or open table wordpressikdata/wp_rfr2b_options from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

160915 12:50:53 [ERROR] Cannot find or open table wordpressikdata/wp_rfr2b_target from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

6. Realize backup de todas as tabelas.

mysqldump -A > dump.sql

7. Delete todas as tabelas que necessitam recuperação.

Ao executar o comando mysqldump para realizar uma cópia de segurança do banco de dados, você pode receber o erro:

mysqldump: Got error: 1146: Table ‘wordpressikdata.wp_rfr2b_options’ doesn’t exist when using LOCK TABLES

Mas ao olhar em /var/lib/mysql/wordpressikdata, os arquivos para estas tabelas estavam lá, e o show tables também exibe as tabelas.

mysql -u root -p
mysql> show databases;

mysql> use wordpressikdata

mysql> show tables;
+----------------------------+
| Tables_in_wordpressikdata  |
+----------------------------+
| wp_commentmeta             |
| wp_comments                |
| wp_gallery_galleries       |
| wp_gallery_galleriesslides |
| wp_gallery_slides          |
| wp_links                   |
| wp_options                 |
| wp_postmeta                |
| wp_posts                   |
| wp_rfr2b_options           |
| wp_rfr2b_target            |
| wp_term_relationships      |
| wp_term_taxonomy           |
| wp_terms                   |
| wp_usermeta                |
| wp_users                   |
+----------------------------+
16 rows in set (0.00 sec)

Para nos certificarmos que a tabela existe e não há problemas, você pode executar o mysqlcheck:

$ mysqlcheck -u root -p wordpressikdata
Enter password:

Ele irá checar e reparar qualquer banco de dados e tabelas passados a ele. Porém, você pode receber algo como:

wordpressikdata.wp_commentmeta                     OK
wordpressikdata.wp_comments                        OK
wordpressikdata.wp_gallery_galleries               OK
wordpressikdata.wp_gallery_galleriesslides         OK
wordpressikdata.wp_gallery_slides                  OK
wordpressikdata.wp_links                           OK
wordpressikdata.wp_options                         OK
wordpressikdata.wp_postmeta                        OK
wordpressikdata.wp_posts                           OK
wordpressikdata.wp_rfr2b_options
Error    : Table 'wordpressikdata.wp_rfr2b_options' doesn't exist
status   : Operation failed
wordpressikdata.wp_rfr2b_target
Error    : Table 'wordpressikdata.wp_rfr2b_target' doesn't exist
status   : Operation failed
wordpressikdata.wp_term_relationships              OK
wordpressikdata.wp_term_taxonomy                   OK
wordpressikdata.wp_terms                           OK
wordpressikdata.wp_usermeta                        OK
wordpressikdata.wp_users                           OK

Não há um modo rápido de resolver este erro, já que estamos lidando com um banco de dados ou tabela corrompida, e se você não possuí um backup anterior (funcionando) você não será capaz de restaurar os dados da tabela, somente recriar sua estrutura.

Como recriamos a tabela? Os únicos arquivos que temos são estes dois:

  • wp_rfr2b_options.frm
  • wp_rfr2b_target.frm

… mas estes arquivos são binários e nós precisamos obter sua estrutura para recriar estas tabelas no nosso banco de dados MySQL

Por sorte, existe uma ferramenta do MySQL chamada mysqlfrm que pode ler os arquivos binários e e informar os comandos CREATE TABLE necessários para o MySQL automaticamente.

No Debian, você pode instalar o pacote mysql-utilities para obter esta ferramenta.

apt-get update
apt-get install mysql-utilities

Então use-a no arquivo desta forma:

mysqlfrm --diagnostic ./wp_rfr2b_target.frm

# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for ./wp_rfr2b_target.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `wp_rfr2b_target` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rss_content` text CHARACTER SET <UNKNOWN>,
  `rss_ad_campaign_name` varchar(300) CHARACTER SET <UNKNOWN> NOT NULL,
  `optin_fields` text CHARACTER SET <UNKNOWN>,
  `rss_extra` text CHARACTER SET <UNKNOWN>,
  `flag_ad_campaign` enum('0','1') CHARACTER SET <UNKNOWN> NOT NULL,
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

#...done.

O sistema teve um pequeno problema para identificar a codificação de caracteres, mas você pode simplesmente deletar estas partes se você (como eu) sabem que a codificação padrão do MySQL é o suficiente (UTF8). No final, após alguma modificação manual, restaurei a estrutura da minha tabela.

CREATE TABLE `wordpressikdata`.`wp_rfr2b_options` (
  `option_name` varchar(750),
  `option_value` text,
PRIMARY KEY `PRIMARY` (`option_name`)
) ENGINE=InnoDB;

CREATE TABLE `wordpressikdata`.`wp_rfr2b_target` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rss_content` text,
  `rss_ad_campaign_name` varchar(300),
  `optin_fields` text,
  `rss_extra` text,
  `flag_ad_campaign` enum('0','1'),
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

O que nós precisamos fazer agora é simplesmente deletar a tabela com os comandos abaixo:

mysql -u root -p
Enter password: 
mysql> use wordpressikdata

mysql> show tables;

mysql> drop table wp_rfr2b_options;
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> drop table wp_rfr2b_target;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
ERROR:
Can't connect to the server

mysql> exit
Bye

Caso o comando falhe ao deletar as tabelas, você pode removê-las manualmente:

rm -rf /var/lib/mysql/wordpressikdata/wp_rfr2b_options.frm

No meu caso, o mysql simplesmente travava ao tentar deletar a tabela usando drop table wp_rfr2b_options

8. Pare o serviço mysql
9. Remova os arquivos /var/lib/mysql/ib*

rm -rf /var/lib/mysql/ib*

10. Comente a linha innodb_force_recovery do arquivo /etc/mysql/my.cnf
11. Reinicie o mysql. Veja o arquivo de erro do mysql.
12. Restaure o banco de dados do dump:

mysql < dump.sql

Este problema levou algumas horas de investigação para encontrar uma solução, e eu espero que este post possa ajudar vocês! Lembre-se que é sempre recomendável criar uma rotina de backup do seu servidor.

Referências

MySQL Won’t Start – InnoDB Corruption and Recovery – http://chepri.com/our-blog/mysql-innodb-corruption-and-recovery/

Resolving MySQL error 1146: “table doesn’t exist” when doing backup – TechTarget – http://itknowledgeexchange.techtarget.com/security-admin/resolving-mysql-error-1146-table-doesnt-exist-when-doing-backup/

How to fix MySQL lost table description from .frm files after emergency migration of /var/lib/mysql – Network Geek Stuff – http://networkgeekstuff.com/projects/networkgeekstuff/minipost-how-to-fix-mysql-lost-table-description-from-frm-files-after-emergency-migration-of-varlibmysql/