Uno de mis trabajos más recientes ha consistido en dar soporte a un cliente para migrar un desarrollo en local WordPress hacía el servidor de producción, al que posteriormente se necesitaba realizar una serie de mejoras, principalmente cosméticas, e integrar unos formularios de reservas de una aplicación de gestión para su sector, ya en uso en la web anterior que se va a sustituir.

En principio, la migración debiera resultar el paso más sencillo de los 3 hitos a alcanzar, pero como suele ser habitual también en estos casos, pronto empezaron a surgir algunos problemas, haciéndolo finalmente el más complejo.

El problema

Resulta que tras desinstalar y reinstalar el pack de desarrollo WAMP, el cliente había perdido el acceso a la base de datos de WordPress sobre la cual se habían dado de alta una buena cantidad de contenidos, realizado todas las traducciones e instalado y configurado varios plugins.

Después de identificar el problema y realizar las consultas necesarias para intentar resolverlo, pensé que era un tópico interesante para cubrir en el blog, ya que es un escenario al parecer bastante habitual y los pasos a realizar en ocasiones no son triviales y la documentación en Español es escasa.

En nuestro caso la historia terminó bien, y espero que la tuya o la de tu cliente también pueda hacerlo. El prerrequisito será al menos contar con los ficheros mínimos (.frm y .ibd) que conforman la base de datos.

¿Listo?

Localizando la base de datos

En este caso, el desarrollo por parte del cliente se había realizado en Windows a través del pack WAMP que permite desplegar rápidamente las herramientas necesarias: MySQL, el servidor web Apache y el motor de PHP. Sin embargo el tutorial es extensible a otros entornos, pues al fin y al cabo de lo que se trata es de rescatar una base de datos MySQL (y por extensión, MariaDB).

Volviendo a nuestro caso, el cliente tenía dos posibles rutas en donde localizar la base de datos, ya que había instalado el pack de WAMP en dos ocasiones, la primera con los binarios para 32 bits y la segunda para 64 bits. La primera usaba MySQL 5.5.24 y para la segunda se había instalado la versión 5.6.17, de lo cual se intuye que se pretendía cambiar para salvar algún inconveniente que seguramente aparecía y/o para acomodarse a los hostings actuales.

Rutas WAMP de 32 y 64 bits con diferentes versiones de MySQL:

c:\wamp\bin\mysql\mysql5.5.24
c:\wamp64\bin\mysql\mysql5.6.17

Conocía el nombre de la base de datos (‘wordpress’) porque se encontraba claramente definido en el fichero wp-config.php subido a su FTP, y en primera instancia aún no era conocedor de la problemática, así que el paso inicial más lógico es intentar exportar la base de datos, supuestamente existente, con el comando mysqldump.exe (o interfaz phpMyAdmin si lo preferís).

Así pues, desde la consola de comandos (en este caso de Windows) lanzamos algo así:

c:\wamp64\bin\myslql5.6.17\bin\mysqldump.exe -u root -p wordpress > c:\temp\wordpress.sql

… Y en este caso el comando retorna error indicando que la base de datos no se encuentra. Así es como probablemente nos demos cuenta en primer lugar.

Acto seguido podemos entrar en la consola de mysql para consultar a ver qué hay ahí:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.03 sec)

El número de tablas listadas puede variar según la versión, pero si todas son tablas iniciales de MySQL y ninguna es la tuya, es la verificación de que nuestra base de datos no se encuentra ahí.

Como comentaba más arriba, el cliente había instalado el pack en dos ocasiones y tras ver que los binarios de la 5.5.24 ya no se encontraban en la carpeta, lo único que queda es navegar hacia la carpeta de datos de esa versión en concreto para ver si queda algo y verificar que, en efecto, la base de datos que estamos buscando se quedó ahí, nunca fue migrada a la versión más reciente (que es la que se pone en marcha al iniciar el panel de WAMP).

Al navegar hacía c:\wamp\bin\mysql\mysql5.6.17\data encuentro lo siguiente, que comprimo en .zip y me mando a mi máquina de desarrollo para liberar al cliente y seguir con mi trabajo ‘forense’:

Posibles escenarios

Dependiendo de qué motor usara la base de datos y del nivel de ficheros eliminados, podemos encontrar varios escenarios, de más sencillo a más complejo:

Base de datos con motor MyISAM

Si la base de datos usa el motor MyISAM y puedes localizar los diferentes archivos por tabla en la carpeta data, recuperarla debería ser tan sencillo como trasladar los ficheros necesarios hacia una nueva instalación del motor (en nuestro caso a la carpeta de la versión 5.6.17) ya que estos contienen todos los datos necesarios de datos, metadatos e índices.

Los ficheros que interesan son 3 por tabla. Suponiendo que sea la tabla wp_users, sería:

\bin\mysql\mysql5.x.y\data\wordpress\wp_users.frm
\bin\mysql\mysql5.x.y\data\wordpress\wp_users.MYD
\bin\mysql\mysql5.x.y\data\wordpress\wp_users.MYI

Base de datos con motor InnoDB

Seguramente este sea tu caso, ya que la mayoría de aplicaciones hoy en día usan el motor InnoDB con soporte para bloqueo a nivel de tabla y mayor integridad de datos para inserciones concurrentes. Entonces podemos identificar dos casuísticas básicas:

Se encuentra el fichero Ibdata1 en la raíz de \bin\mysql\mysql5.x.y\data

El fichero Ibdata1 contiene gran cantidad de datos del espacio de tablas de la base de datos, como datos temporales que aún no se han persistido, metadatos, índices, transacciones, y por defectos los datos en sí, aunque estos pueden guardarse separadamente en ficheros independientes, como veremos más abajo.

En cualquier caso, seguramente puedas recuperar la base de datos si dispones de éste y del resto de ficheros. Debería bastar con traspasar dicho fichero junto con los ficheros de log (si dispones de ellos, no son imprescindibles pero queremos ahorrar el máximo de problemas) y la carpeta en concreto de la base de datos objetivo.

Si era la única base de datos valiosa, entonces puedes proceder directamente a sobrescribir la carpeta \data con todos sus contenidos. En caso contrario, mejor da un vistazo más abajo para ver el escenario más complejo.

Los pasos en este escenario quedarían:

Si todo va bien, deberías poder conectar con la base de datos y tras lanzar unas consultas como select * from wp_users te debería volver las filas esperadas.

Recuperar base de datos MySQL tras perder el fichero Ibdata1

Este es el caso en el que me encontré y probablemente tú también te encuentres en esta situación, es la más compleja. Puede darse por varios motivos, en el caso del cliente porque al desinstalar WAMP eliminó el fichero ibdata1 junto con los logs, o en otros casos la gente suele eliminarlo como consejo general en los foros, cuando el motor no arranca por cualquier motivo y como una posible solución a probar se sugiere eliminarlo y reiniciar, sin entender demasiado la causa del problema.

Sea como fuere, si aún dispones de la subcarpeta de la base de datos con los ficheros .frm e .ibd, tal y como se muestra en la figura 1, seguramente puedas recuperarla.

Paso 1. Regenerando la estructura de las tablas

De los ficheros enumerados anteriormente, .frm e .ibd, los primeros contienen información sobre la estructura de la tabla, y los siguientes los índices y datos en sí, así que partiendo de la pérdida de ibdata1 que mantiene el catálogo íntegro y le sirve al motor InnoDB como nexo común para gestionar el espacio de tablas, deberemos recrearlo a través de varios pasos.

El primero será reconstruir el esquema de la base de datos con las tablas vacías. Esto es posible gracias a los ficheros .frm. Aunque quizá podríamos recrear la base de datos pasando un scrip .sql del que dispongamos, para que el proceso tenga éxito es importante que la estructura de las tablas sea respetada al máximo, debe ser exacta. Es por ello que vamos a usar los ficheros .frm para generar un fichero de consultas con los create table necesarios.

Hay dos maneras:

1. Instalar el kit de herramientas ‘MySQL Utilities’ (enlace aquí) y ejecutar el comando mysqlfrm como se indica en esta entrada.
2. Comprimir los ficheros .frm en un zip y subirlo a la herramienta online de TwinDB.

Personalmente optaría por el método 2, ya que estamos hablando solamente de la estructura de datos (no los datos contenidos) por lo cual no debería ser información especialmente sensible, y además esta funcionalidad es gratuita. En cualquier caso, si haces esto en nombre de tu cliente, consúltale u opta por la ruta uno.

Recuperando las tablas con MySQL Data Recovery Portal de TwinDB

En la siguiente imagen vemos la sección de la herramienta de TwinDB que nos interesa ‘from .frm file’. Una vez subido el zip con los ficheros, analizará los contenidos y nos devolverá una ventana emergente o popup con las consultas SQL. Cópialas y pégalas en un nuevo fichero .sql a ejecutar posteriormente o directamente en tu editor de consultas favorito (phpMyAdmin, Workbench, etc).

Paso 2. Crear una base de datos nueva y recrear la estructura previa

Es el paso lógico después del paso 1. Será necesario haber creado previamente una base de datos vacía en el nuevo motor al que tenemos acceso sin mayores problemas, y acto seguido importar el script SQL anterior con nuestra herramienta favorita o a través de la línea de comandos:

mysql -uroot -p wordpress < c:\temp\script-generado-desde-frm.sql

Después de correr la consulta, si listas las tablas con USE WORDPRES; SHOW TABLES; o con tu interfaz gráfica preferida, deberías ver la estructura de tablas recreada (pero vacía) con éxito.

Hemos conseguido el primer hito.

Paso 3. Volcar los datos previos de los ficheros .ibd sobre la nueva estructura

Este es sin duda el paso que conlleva mayor complejidad y el que motiva realmente la creación de la entrada. El objetivo está claro: debemos conseguir trasladar los datos de los ficheros .ibd a la nueva base de datos, de esta manera rellenaremos la estructura que acabamos de crear con los contenidos de la base de datos original.

Sin embargo, a diferencia del motor MyISAM, el motor InnoDB no permite simplemente sustituir o copiar/pegar los ficheros .ibd originales sobre el destino cuando falta el fichero Ibdata1 ya que faltaría información necesaria para la integridad del motor.

Afortunadamente, hay un método para importar el contenido previo de los ficheros .ibd a la nueva BD con el comando SQL IMPORT TABLESPACE.

Ahora mismo disponemos de una base de datos nueva (ejemplo ‘wordpress’), que si navegamos a su respectiva carpeta \data\wordpress con nuevos ficheros .frm.

Siguiendo con el ejemplo de la tabla wp_users, los siguientes pasos que deberemos realizar, grosso modo, son los siguientes:

Después de este paso, si ahora hacemos una consulta SELECT sobre la tabla wp_users, deberíamos ver los contenidos previos (por ejemplo una fila con el usuario por defecto admin y tantos usuarios como dispusiéramos en el momento previo a perder la BD).

Si es tu caso, felicidades! Puedes continuar con el siguiente punto para ver cómo recrear todas las tablas de golpe de manera rápida. Pero como la ley de Murphy está al orden del día, es probable que los pasos previos no hayan funcionado, con lo cual salta a Errores.

Descartando e importando todas las tablas de golpe

Una vez verificado que hemos recuperado la tabla wp_users, quedaría realizar el mismo procedimiento para cada una de las tablas. Como puede resultar una tarea tediosa, podemos obtener un listado de tablas con un SELECT del esquema principal de wordpres y usarlo para concatenar el texto para generar el comando deseado.

Para el ALTER TABLE … DISCARD TABLESPACE:

SELECT concat('ALTER TABLE ', table_name, ' DISCARD TABLESPACE;')
FROM information_schema.tables
WHERE table_schema = 'wordpress';

En este caso, se obtiene una salida tipo así:

ALTER TABLE wp_cf_form_entries DISCARD TABLESPACE;
ALTER TABLE wp_cf_form_entry_meta DISCARD TABLESPACE;
ALTER TABLE wp_cf_form_entry_values DISCARD TABLESPACE;
ALTER TABLE wp_commentmeta DISCARD TABLESPACE;
ALTER TABLE wp_comments DISCARD TABLESPACE;
ALTER TABLE wp_layerslider DISCARD TABLESPACE;
ALTER TABLE wp_links DISCARD TABLESPACE;
ALTER TABLE wp_options DISCARD TABLESPACE;
ALTER TABLE wp_postmeta DISCARD TABLESPACE;
ALTER TABLE wp_posts DISCARD TABLESPACE;
...

…Que podemos copiar/pegar y ejecutar de golpe. Para el IMPORT lo mismo, pero cambiando la palabra en la función concat().

Errores

Hay diversos casos que pueden fallar, vamos a resolver uno por uno.

Verificar que MySQL está configurado para funcionar en modo ‘un fichero por tabla’

Como ya anunciaba indirectamente más arriba, MySQL puede funcionar en modo almacenaje de todo los contenidos en la tabla Ibdata1, o bien mantener un fichero .ibd por tabla (tu caso previo era el segundo si pudiste localizar dichos ficheros para ejecutar estos pasos).

El caso es que la base de datos de destino también va a tener que funcionar en este modo para poder completar la importación, y no siempre lo hace por defecto.

Si no es el caso, lo habrás notado en que al realizar el primer DISCARD TABLESPACE has obtenido un error -1 del motor. ¡No es muy descriptivo!

Para detectar realmente que el problema está en el modo de funcionamiento, hace falta localizar el fichero de logs (en WAMP hay un acceso directo en la consola de control, en Linux suele estar en /var/log/mysql) y ver cual era el error real.

En este caso vemos lo siguiente, que es algo más informativo:

161011 14:29:25 InnoDB: Error: table `wordpress`.`wp_users`
InnoDB: is in the system tablespace 0 which cannot be discarded

Esto significa que la tabla reside en el fichero Ibdata1 y no en ficheros .ibd separados. Para solucionarlo, deberemos poner el flag ‘innodb_file_per_table’ de configuración del motor a 1.

Lo haremos a nivel del fichero de configuración mysql.cnf o mysql.ini según se llame dependiendo del sistema.

Localizar la sección [mysqld] y añadir la siguiente línea a continuación:

[mysqld]innodb_file_per_table=1

Y también se puede ejecutar dinámicamente en la consola MySQL para que tenga efecto inmediato sin necesidad de reiniciar:

SET GLOBAL innodb_file_per_table=1;

Sin embargo, al hacer el cambio, es probable que debas borrar las tablas generadas previamente con un DROP e importar el script de nuevo para que tenga efecto.

De hecho, si lo tiene, verás que en la carpeta de la nueva base de datos se han creado también desde el instante cero los ficheros .ibd, que son los que intentamos eliminar con el comando ALTER … DISCARD TABLESPACE en primer lugar, para sustituir por los anteriores e importar con ALTER … IMPORT TABLESPACE.

Ahora ya debería funcionar, a menos que…

Problemas en el momento de importar los ficheros .ibd

Si has podido eliminar los .ibd por defecto pero el proceso falla al importar los nuevos con un error de que el fichero .ibd no se ha podido encontrar, y estás seguro que está ahí, lo más probable es que haya un problema de permisos.

De nuevo revisando el log, puedes encontrar algo así:

161011 15:07:13 InnoDB: cannot calculate statistics for table wordpress/wp_users
InnoDB: because the .ibd file is missing. For help, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.x/en/innodb-troubleshooting.html
161011 15:07:13 [ERROR] MySQL is trying to open a table handle but the .ibd file for
table wordpress/wp_users does not exist

Para que el comando ALTER IMPORT TABLESPACE funcione, el usuario del servicio MySQL debe poder acceder correctamente, de otra manera para él es como si el fichero no existiera, y de ahí el error.

Debes cambiar el propietario de los ficheros al usuario de MySQL. Dependiendo del sistema será de una manera un otra. Yo hice la restauración en una máquina Virtual con Vagrant con linux, y al hacer un ls -la en la carpeta data vi que los ficheros copiados a mano eran propiedad de root, mientras que deben ser propiedad del usuario mysql.

Solución en este caso, elevarnos como superusuario con su y ejecutar:

chown mysql:mysql *.ibd

Ahora el motor ya debería poder leer los ficheros y el comando ALTER TABLE … IMPORT TABLESPACE debería terminar con éxito. Si recibes algún warning, es normal. Simplemente verifica que al hacer la SELECT de la tabla objetivo, existen los datos esperados.

Otros problemas esotéricos

La importación de datos podría no funcionar si pruebas a realizarla en una versión antigua del motor, como las 5.5.x. Parece que el motor no era lo suficientemente flexible como para importar las tablas sin la presencia del fichero Ibdata1. Esto ha sido corregido en posteriores versiones.

La solución en este caso sería asegurarse que se realiza la importación en una versión reciente del motor (5.6 en adelante) o parece que MariaDB es un entorno ideal para realizar el proceso. De hecho, mi máquina Vagrant viene con MariaDB y pude realizar el proceso sin problemas.

¿Y si no dispongo de ninguno de los datos?

Si la carpeta \data original está totalmente vacía o no existe, o no hay constancia de la subcarpeta con los ficheros .frm e .ibd , puede que aún no esté todo perdido.

Si el problema ha sucedido recientemente, la base de datos era pequeña y el disco duro que la contenía relativamente grande en comparación, la probabilidad de que los datos hayan sido sobreescritos en la ubicación física del disco son bajas, es probable que puedas recuperar los ficheros para posteriormente ejecutar los pasos previamente descritos.

Puedes probar, en Windows, con alguna utilidad de recuperación de datos gratuita tipo Recuva o bien puedes probar a revertir a un punto de copia de seguridad de Windows previo, en caso de que no hayas hecho cambios relevantes en el sistema desde entonces.

Para terminar

En esta entrada hemos visto un buen número de casuísticas que pueden darse al perder el acceso a una base de datos MySQL y planteado posibles soluciones para la mayoría de ellas.

Si todo ha ido bien, probablemente ya dispones de la base de datos accesible. ¡Felicidades!

La mejor manera de exportarla al servidor de producción, staging, hacer backup o lo que sea, es lanzar de nuevo el comando mysqldump o sacar un export .sql de la base de datos entera con phpMyAdmin o similar, ahora que hemos reganado el acceso 😉

Si sigues teniendo problemas y crees que seguramente aún hay una solución, puedes dejarme una nota en el formulario de contacto para requerir soporte online y ver qué podemos hacer. De cualquier modo, si la entrada te ha sido útil, te animo a difundirla por las redes 😉