Tutorial Recover Innodb Database Dengan .Frm Dan .Ibd File
Restore atau recovery file database mysql xampp, mungkin gampang apabila sudah backup folder data di mysql dengan path berikut :
Sumber https://fahmiffa.blogspot.com/
E:\\xampp\mysql\data
 |
| Tutorial reover innodb database dengan file .frm dan .ibd |
Tapi menjadi lebih sulit jikalau ternyata di dalam folder data ada beberapa file yang corrupt atau rusak, menyerupai ibdata1, log_file0 atau log_file1.
walaupun kita sudah install xampp fresh maka akan terjadi erorr di xampp dengan info 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 jikalau data tersebut data penting, sudah mencapai ribuan data.
Oke, eksklusif saja untuk memperbaiki problem tersebut. kita harus melaksanakan restore atau recovery innodb database dengan memakai file .frm dan .ibd saja yang ada di folder database.
 |
| struktur folder data mysql |
Disini aku akan melaksanakan recovery database apotek, dimana semua file .frm dan .ibd di dalam foldernya. ada beberapa langkah untuk melaksanakan recovery innodb database mysql adalah :
- backup dahulu folder apotek di E:\\xampp\mysql\data, ambil folder apotek saja.
- Install xampp fresh, lalu 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 gres di phpmyadmin sebagai daerah untuk recovery, di sini aku menciptakan 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 mendapat sekema table, disini aku akan merecovery table tbapotek.
- Silahkan upload file tbapotek.frm, lalu akan mendapat sekema table.
- Silahkan masuk mysql dengan cmd dan masukan query menyerupai 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.
