Hier is waarom MySQL die bestanden niet kan zien: De systeem tablespace (ibdata1) heeft een Storage-Engine specifieke data dictionary die InnoDB potentieel tabelgebruik in kaart laat brengen:

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

Het verplaatsen van InnoDB tabellen van de ene plaats naar de andere vereist commando’s zoals

ALTER TABLE mydb.tags DISCARD TABLESPACE;

Hier is een deel van de MySQL 5.5 Documentatie dat uitlegt waar je rekening mee moet houden

Portabiliteit Overwegingen voor .ibd Bestanden

Je kunt .ibd bestanden niet vrijelijk verplaatsen tussen database directories zoals je dat kunt met MyISAM tabel bestanden. De tabeldefinitie die is opgeslagen in de gedeelde InnoDB-ruimte bevat de databasenaam. De transactie-ID’s en logboekvolgordenummers die in de tablespace-bestanden zijn opgeslagen, verschillen ook tussen databases.

Om een .ibd bestand en de bijbehorende tabel van de ene database naar de andere te verplaatsen, gebruik je een RENAME TABLE statement:

RENAME TABLE db1.tblname TO db2.tblname; Als je een “schone” backup hebt van een .ibd-bestand, kun je die als volgt terugzetten naar de MySQL-installatie waar hij vandaan komt:

De tabel mag niet gedrop of getruncated zijn sinds je het .ibd bestand gekopieerd hebt, omdat dit het tabel ID in de tablespace verandert.

Geef deze ALTER TABLE opdracht om het huidige .ibd bestand te verwijderen:

ALTER TABLE tbl_name DISCARD TABLESPACE; Kopieer het backup .ibd bestand naar de juiste database directory.

Geef deze ALTER TABLE opdracht om InnoDB te vertellen het nieuwe .ibd bestand voor de tabel te gebruiken:

ALTER TABLE tbl_name IMPORT TABLESPACE; In deze context is een “schone” .ibd-bestandsback-up er een waarvoor aan de volgende eisen is voldaan:

Er zijn geen ongecommitteerde wijzigingen door transacties in het .ibd-bestand.

Er zijn geen unmerged insert buffer entries in het .ibd bestand.

Purge heeft alle met delete gemarkeerde index records uit het .ibd bestand verwijderd.

mysqld heeft alle gewijzigde pagina’s van het .ibd bestand uit de buffer pool naar het bestand gespoeld.

Gegeven deze voorbehouden en protocollen, is hier een voorgestelde handelwijze

Laten we voor dit voorbeeld proberen om de tags tabel te herstellen naar de mydb database

STAP #1

Zorg ervoor dat je back-ups hebt van die .frm en .ibd bestanden in /tmp/innodb_data

STAP #2

Haal het CREATE TABLE tags statement en voer het uit als CREATE TABLE mydb.tags .... Zorg ervoor dat het exact dezelfde structuur heeft als de originele tags.frm

STAP #3

Verwijder de lege tags.ibd met MySQL

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

STAP #4

Breng de reservekopie van tags.ibd

ALTER TABLE mydb.tags IMPORT TABLESPACE;

STAP #5

Voeg tabel tags toe aan de InnoDB Data Dictionary

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

STAP 6

Test de toegankelijkheid van de tabel

Als je normale resultaten krijgt, gefeliciteerd dat je een InnoDB tabel importeert.

STAP 7

Verwijder in de toekomst alsjeblieft geen ibdata1 en zijn logs

Probeer het eens !!!

Ik heb dit soort dingen al eerder besproken

• Apr 23, 2012 : MySQL: hoe herstel ik een tabel die is opgeslagen in een .frm en een .ibd bestand?
• Sep 28, 2011 : Hoe herstel ik een InnoDB tabel waarvan de bestanden verplaatst zijn

CAVEAT

Wat als je de tabelstructuur van de tags niet kent?

Er zijn tools om het CREATE TABLE statement gewoon met het .frm bestand te krijgen. Ik heb hier ook een post over geschreven : Hoe kan ik het tabel schema uit alleen het .frm bestand halen? . In die post kopieerde ik een .frm bestand naar een Windows machine vanaf een Linux box, startte de Windows tool en kreeg het CREATE TABLE statement.

Ik heb dezelfde situatie, kan niet laten vallen of maken specifieke tblnaam. Mijn herstel procedure is:

1. Stop MySQL.

2. Verwijder iblogfile0 en iblogfile1.

3. Verwijder de tblname bestanden. WAARSCHUWING: DIT VERWIJDERT UW GEGEVENS PERMENTEEL

4. Start MySQL.

Ik had dit probleem ook. Ik verwijderde ibdata1 per ongeluk en al mijn gegevens waren verloren.

Na 1-2 dagen zoeken in google en SO, vond ik eindelijk een oplossing die mijn leven redde (ik had zo veel databases en tabellen met enorme records).

1. neem een backup van /var/lib/mysql

2. herstel tabel schema van .frm bestand met dbsake (er was een andere optie! mysqlfrm. maar het werkte niet voor mij)

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

1. maak een nieuwe tabel (met nieuwe naam) met geëxporteerde schema.

2. verwijder nieuwe tabel gegevens met dit commando:

ALTER TABLE `tbl-new` DISCARD TABLESPACE;

1. kopieer de gegevens van de oude tabel en plak ze in plaats van de nieuwe tabel en stel de juiste permissie in.

cp tbl.ibd tbl@002dnew.ibd && chown mysql:mysql tbl@002dnew.ibd

1. importeer gegevens naar nieuwe tabel.

ALTER TABLE `tbl-new` IMPORT TABLESPACE;

1. goed! we hebben gegevens in nieuwe tabel en we kunnen oude laten vallen.

DROP TABLE `tbl`;

1. controleer /var/lib/mysql/database-name en als er gegevens zijn (.ibd bestand) voor oude tabel, verwijder die dan.

rm tbl.ibd

1. en hernoem tenslotte de nieuwe tabel naar de oorspronkelijke naam

ALTER TABLE `tbl-new` RENAME `tbl`;