Recuperar una tabla de MySQL desde los ficheros .frm e .ibd

Recuperar una tabla de MySQL desde los ficheros .frm e .ibd


Juanmi Taboada
Juanmi Taboada
Recuperar una tabla de MySQL desde los...

En primer lugar debemos conocer que para que exista el fichero .ibd debemos tener activado el sistema de almacenamiento Barracuda mediante la separación de ficheros por cada tabla de InnoDB, esto se hace con la opción “innodb_file_per_table=1” en el fichero “my.cnf” (/etc/mysql/my.cnf):

[mysqld]
innodb_file_per_table=1

También de forma dinámica mediante:

SET GLOBAL innodb_file_per_table=1;

En primer lugar y lo más importante es que debemos usar una versión de MySQL igual o superior a las versión 5.6, ya que de otro modo tendremos problemas al importar y será realmente complicado llegar a un punto estable en la base de datos. Para poder hacer todo lo que necesitamos vamos a realizar 2 pasos:

  1. Asegurarnos que tenemos MySQL Server 5.6 o superior (mysql-server)
  2. Nos aseguramos de tener instaladas las utilidades de MySQL (mysql-utilities), esto nos dará acceso al comando “mysqlfrm”.
  3. Obtendremos la estructura de la base de datos del fichero .frm
  4. Crearemos una base de datos y una tabla destino de todo lo que vamos a recuperar
  5. Importamos los datos

Vamos a ello:

1 – En primer lugar comprobamos que versión tenemos instalada:

$ mysqld --version
mysqld Ver 5.5.46-0+deb8u1 for debian-linux-gnu on x86_64 ((Debian))

En mi caso y como buen usuario de Debian estable tenía la versión 5.5 en una Debian Jessie, por lo que me vi obligado a actualizar MySQL a una versión mas reciente, para ello:

  1. Configuramos APT:
    • Debian 8: wget http://dev.mysql.com/get/mysql-apt-config_0.3.5-1debian8_all.deb
    • Debian 7: wget http://dev.mysql.com/get/mysql-apt-config_0.3.5-1debian7_all.deb
    • Instalamos el paquete: dpkg -i mysql-apt-config_0.3.5-1debian7_all.deb
    • Referencia: http://dev.mysql.com/downloads/repo/apt
  2. Actualizamos APT:
    • apt-get update
  3. Instalamos el nuevo mysql: (nos debería decir que va a eliminar el MySQL actual del sistema operativo)
    • apt-get install mysql-community-server
  4. Actualiza las bases de datos: recuerda usar “mysql_upgrade -u root -p” para actualizar el estado de tu base de datos a la nueva versión de MySQL.
  5. Problema con las contraseñas: si tuvieras problemas con las contraseñas (por ser demasiado antigua), resetea tu contraseña.

Una ves hecho esto, comprobamos de nuevo la versión y veremos que tenemos una versión 5.6 o superior:

$ mysqld --version
mysqld Ver 5.6.28 for Linux on x86_64 (MySQL Community Server (GPL))

2 – Nos aseguramos de tener instaladas las utilidades de MySQL

Para ello es tan sencillo como comprobar que tenemos disponible el comando “mysqlfrm”, en caso negativo: sudo apt-get install mysql-utilities (referencia: http://dev.mysql.com/downloads/utilities)

3 – Ahora vamos a localizar la estructura de la tabla que queremos recuperar

Para ello debemos tener bien localizado (en cualquier sitio) una copia del fichero .frm y ejecutaremos el comando como sigue:

  • mysqlfrm –server=<usuario>:<clave>@localhost –port 3307 –diagnostic <fichero.frm>

si el resultado es positivo (vemos un CREATE TABLE), guardamos el resultado en un fichero:

  • mysqlfrm –server=<usuario>:<clave>@localhost –port 3307 –diagnostic <fichero.frm> > tabla.sql

4 – Ahora creamos la base de datos destino:

Usamos el mismo nombre que la base de datos en la que estaba almacenada esa tabla. Una vez seleccionada crearemos la tabla según hemos obtenido en el comando anterior, de tal modo que crearemos una tabla con la misma estructura que los datos de la tabla que vamos a recuperar.

Ejecutamos: ALTER TABLE <tabla> DISCARD TABLESPACE
que borrará el fichero .ibd correspondiente a la tabla recién creada.

5 – Importamos los datos:

Copiamos ahora el fichero .ibd de la tabla que queremos recuperar dentro de la carpeta de MySQL (en Debian la ruta sería /var/lib/mysql/<base de datos>/<tabla>.ibd), es importante que el nombre del fichero.ibd sea el mismo que el .frm existen pero con extensión .ibd.

Volvemos ahora a la consola de MySQL y ejecutamos: ALTER TABLE <tabla> IMPORT TABLESPACE

En mi caso y con la versión de MySQL 5.5 recibía contínuamente el error “warning” (1) error-type statement“, y no conseguí hacerlo funcionar de ningún modo, incluso probé la edición hexadecimal del fichero .ibd para alinear los IDs de MySQL con los del ficherro .ibd, en ningún caso conseguí un resultado favorable (probé también con la opción “innodb_force_recovery=1” (2, 3, 4, 5 y 6), al usar 4 y 5 conseguí información parcial del fichero, unos 5Mb de 400Mb, lo cual no sería para nada. Si necesitas detalle sobre las diferentes opeciones del innodb_force_recovery, acude a: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

Con la versión de MySQL 5.6 conseguí una importación limpia y sin problemas, MySQL 5.6 corrigió los IDs internos y sincronizó el estado de la base de datos con mi sistema. Así que finalmente y como primera tarea debemos ejecutar un mysql-dump sobre la base de datos para no perder el trabajo realizado. En mi caso el resultado del MySQL dejó esto en 250Mb (esperaba unos 400Mb), esto se debe a que en la tabla se usaba como un “log” y la tabla original contenía mucha basura, por lo que necesitaba una limpieza y ser optimizada, esto es normal y no debe alarmarnos.

NOTA 1: si durante la importación surgen problemas, prueba a activar la opción "innodb_force_recovery=1" en el fichero my.cnf de la configuración de MySQL (en Debian esto se encuentra en /etc/mysql/my.cnf), es importante desactivar esta opción tan pronto tengamos importada la base de datos correctamente.

NOTA 2: si tuvieras problemas durante el proceso y te diera problemas al borrar la tabla o acceder a ella y estás un un punto muerto, prueba a borrar a mano (desde el sistema operativo) el fichero .ibd de la carpeta de la base de datos y después desde la consola de MySQL (sin ejecutar USE <base de datos>) lanza un DROP TABLE <base de datos>repetidas veces, si aún diera problemas, revisa que no haya pendiente de borrar ficheros "sobrantes" en la carpeta de la base de datos. En mi caso (durante las primeras pruebas) encontré ficheros temporales del editor hexadecimal.

Si todo fue bien, tendrás tu tabla recuperada. ¡Felicidades!

 

Referencia 1 (Stackexchange): Restoring MySQL Tables from .ibd, .frm and mysqllogbin files

Referencia 2 /Chris Calender): Recovering an InnoDB table from only an .ibd file

Comments

  • Sergio
    Sergio

    Es posible recuperar los registros de una tabla?
    El problema ha sido que se han perdido unos registros pero no puedo recuperar todo.
    El backup que me han dado es una copia entera de la carpeta Data del mysql dnd estan los frm y un fichero .idb

    Es posible?

    Gracias

    • Article Author
    • Reply
    • Juanmi Taboada
      Juanmi Taboada

      Hola:

      Sí es posible recuperar los registros de una tabla. Para ello usa el procedimiento indicado en este artículo. Recupera la Base de Datos en otro equipo y después extrae esos registros al formato que necesites.

      No obstante, una copia de seguridad de la carpeta mysql probablemente esté corrupta dado que esos ficheros han podido ser modificados durante la copia. Excepto que la base de datos estuviese parada o la copia se haya realizado adecuadamente, es probable que los ficheros .frm e .ibd pen problemas.

      Si pasan la copia completa de la carpeta de Mysql, recuperarlo debería ser algo trivial. Por cierto, recuerda usar una versión compatible de MySQL si no la misma.

      Suerte,

      • Article Author
      • Reply
  • Axel Ruiz
    Axel Ruiz

    Saludos, he hecho tu procedimiento y me ha funcionado, pero resulta que al tratar de importar un archivo ibd que pesa 50mb , el servidor pierde la conexion y despues comienzo a tener problemas con mysql. Que puedo hacer para que me importe el archivo de ese tamaño ? Gracias.

    • Article Author
    • Reply
    • jmtaboada
      jmtaboada

      50Mb es algo ridículo, ¿has mirado los mensajes que te devuelve en los logs? Gracias

      • Article Author
      • Reply

Related Articles

Sistemas

Clouds are beautiful until comes the rain!

During the last weeks, I’ve received interesting news about Cloud computing. Most show that many users are not ready to upload their personal documents online. Are you? What...

Posted on by Juanmi Taboada
Sistemas

Get your hardware for Arduino Underwater ROV

In my last post, “Finishing the frame for an Underwater ROV”, I gave all details about the design I used to build the frame for Alioli Underwater ROV. In this post, I...

Posted on by Juanmi Taboada