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

MySQL InnoDB ha perso le tabelle ma i file esistono

Ho un MySQL InnoDB che ha tutti i file delle tabelle del database, ma MySQL non li vede e non li carica.

Il problema è successo perché ho cancellato questi tre file: ibdata1, ib_logfile0 e ib_logfile1

perché stavo avendo problemi con l'avvio di mysql, e quello che ho letto era di rimuoverli perché MySQL li rigenererà (so che avrei dovuto farne il backup ma non l'ho fatto).

Cosa posso fare per far sì che MySQL veda di nuovo le tabelle?

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

Risposte (3)

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

Ecco perché MySQL non può vedere quei file: Il tablespace di sistema (ibdata1) ha un dizionario dati specifico dello Storage-Engine che permette a InnoDB di mappare il potenziale utilizzo delle tabelle:

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

Spostare le tabelle InnoDB da un posto all'altro richiede comandi come

ALTER TABLE mydb.tags DISCARD TABLESPACE;

Qui c'è una parte della MySQL 5.5 Documentation che spiega cosa bisogna considerare

Considerazioni sulla portabilità dei file .ibd

Non si possono spostare liberamente i file .ibd tra le directory del database come si fa con i file delle tabelle MyISAM. La definizione della tabella memorizzata nel tablespace condiviso InnoDB include il nome del database. Anche gli ID delle transazioni e i numeri di sequenza dei log memorizzati nei file del tablespace differiscono tra i database.

Per spostare un file .ibd e la tabella associata da un database a un altro, usare l'istruzione RENAME TABLE:

RENAME TABLE db1.tbl\nometabella a db2.tbl\nometabella; Se hai un backup “pulito” di un file .ibd, puoi ripristinarlo nell'installazione MySQL da cui è partito come segue:

La tabella non deve essere stata eliminata o troncata da quando hai copiato il file .ibd, perché così facendo cambi l'ID della tabella memorizzato all'interno del tablespace.

Emetti questa istruzione ALTER TABLE per cancellare il file .ibd corrente:

ALTER TABLE tbl\nome DISCARD TABLESPACE; Copia il file .ibd di backup nella corretta directory del database.

Emetti questa istruzione ALTER TABLE per dire a InnoDB di usare il nuovo file .ibd per la tabella:

ALTER TABLE tbl\nome IMPORT TABLESPACE; In questo contesto, un backup “pulito” del file .ibd è quello per cui sono soddisfatti i seguenti requisiti:

Non ci sono modifiche non impegnate da transazioni nel file .ibd.

Non ci sono voci di insert buffer unmerged nel file .ibd.

Lo spurgo ha rimosso tutti i record di indice marcati come cancellati dal file .ibd.

mysqld ha lavato tutte le pagine modificate del file .ibd dal buffer pool al file.

Dati questi avvertimenti e protocolli, ecco una linea d'azione suggerita

Per questo esempio, proviamo a ripristinare la tabella tags nel database mydb

STEP #1

Assicurati di avere dei backup di quei file .frm e .ibd in /tmp/innodb_data

STEP #2

Prendi lo statement CREATE TABLE tags ed eseguilo come CREATE TABLE mydb.tags .... Assicurati che sia esattamente la stessa struttura dell'originale tags.frm

STEP #3

Cancella lo tags.ibd vuoto usando MySQL

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

STEP #4

Porta la copia di backup di tags.ibd

ALTER TABLE mydb.tags IMPORT TABLESPACE;

STEP #5

Aggiungi la tabella tags al dizionario dei dati InnoDB

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

STEP 6

Test di accessibilità della tabella

Se ottieni risultati normali, complimenti per aver importato una tabella InnoDB.

STEP 7

In futuro, per favore non cancellare ibdata1 e i suoi log

Provaci!

Ho già discusso cose come questa

CAVEAT

E se non conosci la struttura delle tabelle della tabella tags ?

Ci sono degli strumenti per ottenere l'istruzione CREATE TABLE solo usando il file .frm. Ho scritto un post anche su questo: Come estrarre lo schema della tabella dal solo file .frm? . In quel post, ho copiato un file .frm su una macchina Windows da una scatola Linux, ho eseguito lo strumento Windows e ho ottenuto la dichiarazione CREATE TABLE.

10
10
10
2014-01-28 02:08:22 +0000

Ho la stessa situazione, non riesco a eliminare o creare tblname specifici. La mia procedura di correzione è:

  1. Fermare MySQL.

  2. Rimuovere ib_logfile0 e ib_logfile1.

  3. Rimuovi i file tblname. Attenzione: questo cancellerà definitivamente i tuoi dati

  4. Avviare MySQL.

2
2
2
2019-03-27 11:19:13 +0000

Anche io ho avuto questo problema. Ho cancellato ibdata1 accidentalmente e tutti i miei dati erano persi.

Dopo 1-2 giorni di ricerca in google e SO, finalmente ho trovato una soluzione che mi ha salvato la vita (avevo tanti database e tabelle con record enormi).

  1. fare un backup da /var/lib/mysql

  2. recuperare lo schema della tabella dal file .frm con dbsake (c'era un'altra opzione! mysqlfrm. ma non ha funzionato per me)

dbsake frmdump --type-codes /var/lib/mysql/database-name/tbl.frm
  1. creare una nuova tabella (con nuovo nome) con lo schema esportato.

  2. scartare i dati della nuova tabella con questo comando:

ALTER TABLE `tbl-new` DISCARD TABLESPACE;
  1. copiare i dati della vecchia tabella e incollarli al posto di quella nuova e impostare i giusti permessi per essa.
cp tbl.ibd tbl@002dnew.ibd && chown mysql:mysql tbl@002dnew.ibd
  1. importa i dati nella nuova tabella.
ALTER TABLE `tbl-new` IMPORT TABLESPACE;
  1. bene! abbiamo i dati nella nuova tabella e possiamo eliminare la vecchia.
DROP TABLE `tbl`;
  1. controlla /var/lib/mysql/database-name e se ci sono dati (file .ibd) per la vecchia tabella, cancellali.
rm tbl.ibd
  1. e infine rinominare la nuova tabella al nome originale
ALTER TABLE `tbl-new` RENAME `tbl`;