Restaurando archivos ibd en tablas InnoDB

Este post es una recopilación de la información más actual para para restaurar una base de datos MySQL a partir de los archivos de datos en entornos Windows. La versión de MySQL es la 5.7.11.


¿Por qué restaurar?


En mi caso al cambiar de ordenador se me olvidó hacer una copia reciente de la base de datos con lo cual al instalarlo todo en el nuevo habría perdido un mes entero de trabajo, por fortuna disponía del disco duro anterior.

¿Qué necesitamos para restaurar la base de datos MySQL?


Necesitamos un backup de la estructura de la base de datos (actualizada) o un backup completo en el que la estructura sea la correcta (si el backup es actual no necesitaríamos restaurar los datos desde los archivos, el problema es que la que tenía era un mes antigua).

Lo siguiente son los archivos de datos, que en Windows se encuentran en la siguiente ruta: C:\ProgramData\MySQL\MySQL Server 5.7\Data\dbname. La carpeta ProgramData está oculta. En esta ubicación encontraremos varios tipos de archivos, todos con el nombre de la tabla como nombre de archivo y la extensión que indica el tipo de archivo. Estos son los tipos de archivo en mi caso:
  • .frm definición de las tablas tanto MyISAM como InnoDB
  • .MYD datos de las tablas MyISAM
  • .MYI índices de las tablas MyISAM
  • .idb el espacio de tablas InnoDB con los datos
  • .trn tablas con disparadores
De estos tipos de archivos los necesarios son los MYD e IBD. Los primeros se pueden copiar tal cual, para los segundos es este procedimiento.

Antes del procedimiento hay algo a tener en cuenta: ROW_FORMAT. Si desconocemos el formato de fila de nuestra anterior base de datos entonces seguramente nos salga el siguiente error la primera vez que intentemos importar los nuevos archivo .ibd:
Error Code: 1808
Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)
Hay varios tipos de  ROW_FORMAT que podemos leer la explicación aqui y aqui. Lo que debemos tener en cuenta es:
  1. Modificar el ROW FORMAT de una tabla sin archivo .ibd provoca un colapso del servidor MySQL con errores como el siguiente: [ERROR] InnoDB: Trying to do I/O to a tablespace which does not exist. I/O type: read
  2. En MySQL 5.7.8 y versiones anteriores, las filas se almacenan en formato compacto de forma predeterminada. A partir de MySQL 5.7.9, el formato de fila predeterminado se define por innodb_default_row_format, que tiene un valor por defecto DYNAMIC. El formato de fila predeterminado se utiliza cuando la opción de tabla ROW_FORMAT no se define explícitamente o cuando se especifica ROW_FORMAT = DEFAULT. Generalmente nunca se especifica este parámetro en los backups.
  3. Cuando la opción ROW_FORMAT no se especifica explícitamente o cuando se utiliza ROW_FORMAT = DEFAULT, cualquier operación que reconstruye la tabla de manera transparente al usuario también cambia el formato de fila de la tabla para el formato definido por defecto, entre estas operaciones se encuentra los ALTER TABLE que utilizan ALGORITHM=COPY o ALGORITM=INPLACE donde se requiere la reconstrucción de tablas.>OPTIMIZE TABLE es también una operación de reconstrucción de tablas.
En resumen que podemos tener varios tipos de formatos de filas en nuestras tablas y nos enteraremos solo al importar los archivos .idb.

Aquí os dejo algunas consultas interesantes para gestionar los formatos de fila:
SET GLOBAL innodb_default_row_format=COMPACT;

SELECT @@innodb_default_row_format;

SELECT `table_name`, `row_format`
FROM `information_schema`.`tables`
WHERE `table_schema`=DATABASE();

Restaurando los archivos .ibd


Debemos cambiar los formatos de filas de las tablas a los que tenemos en los .idb que vamos a restaurar, Esto lo debemos hacer en primera instancia:

ALTER TABLE tbname1 ROW_FORMAT=COMPACT;
ALTER TABLE tbname2 ROW_FORMAT=DYNAMIC;

Para evitar errores como este Cannot delete or update a parent row: a foreign key constraint fails debemos desactivar la comprobación de claves externas:

SET FOREIGN_KEY_CHECKS=0;

Luego eliminamos los archivos .ibd con los datos viejos (hay que hacerlo tabla por tabla)

ALTER TABLE `actores` DISCARD TABLESPACE;

Este paso es un engorro pero podemos generar todas las consultas necesarias ejecutando esta consulta que devuelve las tablas InnoDB de la base de datos actual.

SELECT CONCAT('ALTER TABLE `', table_name, '` DISCARD TABLESPACE;') AS _ddl
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND ENGINE='InnoDB';

Ahora toca el turno de importar los archivos .ibd. Pero antes si queremos evitar este tipo de errores:
[ERROR] InnoDB: Trying to access page number 327753 in space 42, space name dbname/tbname, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
[ERROR] InnoDB: Server exits.
Debemos establecer innodb_force_recovery = 0 en el archivo my.ini que se encuentra en C:\ProgramData\MySQL\MySQL Server 5.7 y reiniciar el servidor.

Según el manual de MySQL este es el valor que tiene por defecto pero en mi caso tuve que especificarlo.

Para importar el archivo .ibd basta con ejecutar esta consulta en cada tabla:

ALTER TABLE tbname IMPORT TABLESPACE

Existe la creencia que las tablas deben tener el mismo id de tabla interno pero esto es una limitación antigua, la propia documentación oficial dice:
Para MySQL 5.5 y versiones anteriores, la tabla ya debe existir y no debe haberse borrado o truncado desde la copia de seguridad. Cuando se trunca una tabla InnoDB, o se borra y se crea nuevamente, se le asigna un nuevo ID de tabla. Cualquier diferencia entre el ID de tabla en la base de datos y la tabla de la copia de seguridad puede evitar que sea restaurada. El requisito para hacer coincidir los ID de tabla es también la razón por la cual se recomienda restaurar al mismo servidor MySQL de procedencia de los datos de la copia, no en otro servidor con un conjunto similar de bases de datos y tablas. Esta restricción no se aplica a MySQL 5.6 y versiones posteriores, siempre y cuando se haga la restauración de una versión de disponibilidad general (GA) a otra en la misma serie de servidores MySQL.
Espero que con esto podáis restaurar vuestros archivos .ibd en entornos Windows sin mayor problema. 

Comentarios

Harold Arvey ha dicho que…
me sirvio mucho el aporte, gracias por compartir