Tutorial Recover innodb database dengan .frm dan .ibd file

Restore atau recovery file database mysql xampp, mungkin mudah apabila sudah backup folder data di mysql dengan path berikut :

E:\\xampp\mysql\data

Tutorial reover innodb database dengan file .frm dan .ibd

Tapi menjadi lebih sulit jika ternyata di dalam folder data ada beberapa file yang corrupt atau rusak, seperti ibdata1, log_file0 atau log_file1.

walaupun kita sudah install xampp fresh maka akan terjadi erorr di xampp dengan informasi berikut :

2018-10-09 01:01:59 13f0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.

2018-10-09  1:01:59 5104 [Note] InnoDB: Using mutexes to ref count buffer pool pages

2018-10-09  1:01:59 5104 [Note] InnoDB: The InnoDB memory heap is disabled

2018-10-09  1:01:59 5104 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions

2018-10-09  1:01:59 5104 [Note] InnoDB: Memory barrier is not used

2018-10-09  1:01:59 5104 [Note] InnoDB: Compressed tables use zlib 1.2.3

2018-10-09  1:01:59 5104 [Note] InnoDB: Not using CPU crc32 instructions

2018-10-09  1:01:59 5104 [Note] InnoDB: Initializing buffer pool, size = 16.0M

2018-10-09  1:01:59 5104 [Note] InnoDB: Completed initialization of buffer pool

2018-10-09  1:01:59 5104 [Note] InnoDB: Restoring page 0 of tablespace 0

2018-10-09  1:01:59 5104 [Warning] InnoDB: Doublewrite does not have page_no=0 of space: 0

2018-10-09  1:01:59 5104 [ERROR] InnoDB: space header page consists of zero bytes in data file E:\xampp\mysql\data\ibdata1

2018-10-09  1:01:59 5104 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!

2018-10-09  1:01:59 5104 [ERROR] Plugin 'InnoDB' init function returned error.

2018-10-09  1:01:59 5104 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

2018-10-09  1:01:59 5104 [Note] Plugin 'FEEDBACK' is disabled.

2018-10-09  1:01:59 5104 [ERROR] Unknown/unsupported storage engine: InnoDB

2018-10-09  1:01:59 5104 [ERROR] Aborting

Alhasil data yang sudah di inputkan di database mungkin rusak. Tentu menjadi sangat merepotkan jika data tersebut data penting, sudah mencapai ribuan data.

Oke, langsung saja untuk memperbaiki masalah tersebut. kita harus melakukan restore atau recovery innodb database dengan menggunakan file .frm dan .ibd saja yang ada di folder database.

struktur folder data mysql 

Disini saya akan melakukan recovery database apotek, dimana semua file .frm dan .ibd di dalam foldernya. ada beberapa langkah untuk melakukan recovery innodb database mysql yaitu :

• backup dahulu folder apotek di E:\\xampp\mysql\data, ambil folder apotek saja.
• Install xampp fresh, kemudian copy folder apotek tadi ke path folder xampp\mysql\data yang terbaru

Apabila kita run xampp dan menuju phpmyadmin akan ada database apotek dengan beberapa tablenya. tetapi akan erorr table doesn't exist in engine. Ok next step

• Buat database baru di phpmyadmin sebagai tempat untuk recovery, di sini saya membuat database dengan nama "dbapotek"
• Tambahkan di konfigurasi my.ini di path mysql => xampp\mysql\bin

[mysqld]
innodb_file_per_table=1

• Buka situs https://recovery.twindb.com/ untuk mendapatkan sekema table, disini saya akan merecovery table tbapotek.
• Silahkan upload file tbapotek.frm, kemudian akan mendapatkan sekema table.
• Silahkan masuk mysql dengan cmd dan masukan query seperti berikut :

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

E:\xampp\mysql\bin>mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.8-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use dbapotek;
Database changed
MariaDB [dbapotek]> CREATE TABLE `tbapotek` (
    ->   `id` varchar(15) NOT NULL,
    ->   `kategori` varchar(5) NOT NULL,
    ->   `nama` varchar(25) NOT NULL,
    ->   `jenis` varchar(5) NOT NULL,
    ->   `umur` varchar(15) NOT NULL,
    ->   `alamat` varchar(15) NOT NULL,
    ->   `nohp` varchar(15) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.42 sec)

MariaDB [dbapotek]> ALTER TABLE tbapotek DISCARD TABLESPACE;
Query OK, 0 rows affected (0.22 sec)

• Silahkan copy kan file tbapotek.ibd di folder apotek ke folder dbapotek, dan masukan query berikut :

MariaDB [dbapotek]> ALTER TABLE tbapotek IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.44 sec)

Sekarang buka di phpmyadmin ke dbapotek > tbapotek, data file tersebut akan kembali terbaca. 

Okeh begitulah tutorial restore atau recovery databsse innodb dari file .frm dan .ibd tanp ibdata1 dan log_file.

terima kasih semoga membantu, untuk tutorial video sebagai berikut :
youtube tutorial => d isini

Share