2013-11-13 14:52:59 +0000 2013-11-13 14:52:59 +0000
33
33

MySQL InnoDB perdió tablas pero los archivos existen

Tengo un MySQL InnoDB que tiene todos los archivos de tablas de la base de datos, pero MySQL no los ve, y no los carga.

El problema ocurrió porque borré estos tres archivos: ibdata1, ib_logfile0 y ib_logfile1

porque estaba teniendo problemas con el arranque de mysql, y lo que leí fue que los eliminara porque MySQL simplemente los regeneraría (sé que debería haber hecho una copia de seguridad pero no lo hice).

¿Qué puedo hacer para que MySQL vuelva a ver las tablas? 

about_member.frm site_stories.frm
about_member.ibd site_stories.ibd
db.opt stories.frm
FTS_00000000000000bb_BEING_DELETED_CACHE.ibd stories.ibd
FTS_00000000000000bb_BEING_DELETED.ibd story_comments.frm
FTS_00000000000000bb_CONFIG.ibd story_comments.ibd
FTS_00000000000000bb_DELETED_CACHE.ibd story_likes.frm
FTS_00000000000000bb_DELETED.ibd story_likes.ibd
FTS_00000000000000f5_BEING_DELETED_CACHE.ibd story_tags.frm
FTS_00000000000000f5_BEING_DELETED.ibd story_tags.ibd
FTS_00000000000000f5_CONFIG.ibd story_views.frm
FTS_00000000000000f5_DELETED_CACHE.ibd story_views.ibd
FTS_00000000000000f5_DELETED.ibd story_view_totals.frm
member_favorites.frm story_view_totals.ibd
member_favorites.ibd tags.frm
members.frm tags.ibd
members.ibd
Fuente

Respuestas (3)

36
36
36
2013-11-14 15:34:22 +0000

He aquí por qué MySQL no puede ver esos archivos: El tablespace del sistema (ibdata1) tiene un diccionario de datos específico de Storage-Engine que permite a InnoDB mapear el uso potencial de las tablas: 

ALTER TABLE tblname DISCARD TABLESPACE;
ALTER TABLE tblname IMPORT TABLESPACE;

Mover tablas InnoDB de un lugar a otro requiere comandos como 

ALTER TABLE mydb.tags DISCARD TABLESPACE;

Aquí hay una parte de la Documentación de MySQL 5.5 que explica lo que hay que tener en cuenta

Consideraciones sobre la portabilidad de los archivos .ibd

No puede mover libremente los archivos .ibd entre directorios de bases de datos como puede hacer con los archivos de tablas MyISAM. La definición de la tabla almacenada en el espacio de tablas compartido InnoDB incluye el nombre de la base de datos. Los ID de transacción y los números de secuencia de registro almacenados en los archivos del tablespace también difieren entre las bases de datos.

Para mover un archivo .ibd y la tabla asociada de una base de datos a otra, utilice una sentencia RENAME TABLE:

RENAME TABLE db1.tbl_name TO db2.tbl_name; Si tiene una copia de seguridad “limpia” de un archivo .ibd, puede restaurarlo a la instalación MySQL de la que procede de la siguiente manera:

La tabla no debe haber sido eliminada o truncada desde que copió el archivo .ibd, ya que al hacerlo cambia el ID de la tabla almacenado dentro del tablespace.

Emita esta sentencia ALTER TABLE para eliminar el archivo .ibd actual:

ALTER TABLE tbl_name DISCARD TABLESPACE; Copie el archivo .ibd de copia de seguridad en el directorio adecuado de la base de datos.

Emita esta sentencia ALTER TABLE para indicar a InnoDB que utilice el nuevo archivo .ibd para la tabla:

ALTER TABLE tbl_name IMPORT TABLESPACE; En este contexto, una copia de seguridad “limpia” del archivo .ibd es aquella que cumple los siguientes requisitos:

No hay modificaciones no comprometidas por transacciones en el archivo .ibd.

No hay entradas de búfer de inserción no comprometidas en el archivo .ibd.

La purga ha eliminado todos los registros de índice marcados como borrados del archivo .ibd.

mysqld ha vaciado todas las páginas modificadas del archivo .ibd de la reserva de búferes al archivo.

Dadas estas advertencias y protocolos, aquí se sugiere un curso de acción

Para este ejemplo, vamos a intentar restaurar la tabla tags a la base de datos mydb

PASO #1

Asegúrese de tener copias de seguridad de esos archivos .frm y .ibd en /tmp/innodb_data

PASO #2

Obtenga la sentencia CREATE TABLE tags y ejecútela como CREATE TABLE mydb.tags .... Asegúrese de que tiene exactamente la misma estructura que la original tags.frm

PASO #3

Borre el tags.ibd vacío usando MySQL 

cd /var/lib/mysql/mydb
cp /tmp/innodb_data.tags.ibd .
chown mysql:mysql tags.ibd

PASO #4

Traiga la copia de seguridad de tags.ibd 

ALTER TABLE mydb.tags IMPORT TABLESPACE;

PASO #5

Añada la tabla tags al diccionario de datos InnoDB 

SHOW CREATE TABLE mydb.tags\G
SELECT * FROM mydb.tags LIMIT 10;

PASO 6

Pruebe la accesibilidad de la tabla

Si obtiene resultados normales, felicitaciones por haber importado una tabla InnoDB.

PASO 7

En el futuro, por favor, no borre ibdata1 y sus logs

¡¡¡Prueba!!!

Ya he hablado de cosas como esta antes

  • Apr 23, 2012 : [ MySQL: ¿cómo restaurar una tabla almacenada en un archivo .frm y en un archivo .ibd?

Hay herramientas para obtener la sentencia CREATE TABLE sólo con el archivo Sep 28, 2011. También escribí un post sobre esto : ¿Cómo se puede extraer el esquema de la tabla sólo del archivo .frm? . En ese post, copié un archivo .frm a una máquina Windows desde un equipo Linux, ejecuté la herramienta de Windows y obtuve la sentencia tags.
Fuente
10
10
10
2014-01-28 02:08:22 +0000

Tengo la misma situación, no puedo soltar o crear un nombre de tabla específico. Mi procedimiento para arreglarlo es:

  1. Detener MySQL.

  2. Eliminar ib_logfile0 y ib_logfile1.

  3. Eliminar los archivos tblname. AVISO: ESTO BORRARÁ PERMANTENTE SUS DATOS

  4. Inicie MySQL.
Fuente
2
2
2
2019-03-27 11:19:13 +0000

Yo también tuve este problema. Borré ibdata1 accidentalmente y todos mis datos se perdieron.

Después de 1 o 2 días de búsqueda en google y SO, finalmente encontré una solución que me salvó la vida (tenía muchas bases de datos y tablas con registros enormes).

  1. tomar una copia de seguridad de /var/lib/mysql

  2. recuperar el esquema de la tabla del archivo .frm con dbsake (¡había otra opción! mysqlfrm. pero no me funcionó) 

dbsake frmdump --type-codes /var/lib/mysql/database-name/tbl.frm

  1. crear una nueva tabla (con nuevo nombre) con el esquema exportado.

  2. descartar los datos de la nueva tabla con este comando: 

ALTER TABLE `tbl-new` DISCARD TABLESPACE;
  1. copie los datos de la tabla antigua y péguelos en lugar de la nueva y establezca el permiso correcto para ella. 
cp tbl.ibd tbl@002dnew.ibd && chown mysql:mysql tbl@002dnew.ibd
  1. importar los datos a la nueva tabla. 
ALTER TABLE `tbl-new` IMPORT TABLESPACE;
  1. ¡bien! tenemos los datos en la nueva tabla y podemos dejar la antigua. 
DROP TABLE `tbl`;
  1. comprueba /var/lib/mysql/database-name y si hay datos (archivo .ibd) para la tabla antigua, elimínalos. 
rm tbl.ibd
  1. y finalmente renombrar la nueva tabla al nombre original 
ALTER TABLE `tbl-new` RENAME `tbl`;
Fuente