I had a customer's computer shutdown/crash due to power going out and when mysql tried to boot they got. After I looked in data folder for database and there was just .frm files and no .idb files.
2016-05-18 18:22:57 108 [Note] InnoDB: Database was not shutdown normally!
2016-05-18 18:22:57 108 [Note] InnoDB: Starting crash recovery.
2016-05-18 18:22:57 108 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-05-18 18:23:07 108 [Note] InnoDB: Restoring possible half-written data pages
2016-05-18 18:23:07 108 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 172727488
2016-05-18 18:23:08 108 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
2016-05-18 18:23:08 108 [Note] InnoDB: 128 rollback segment(s) are active.
2016-05-18 18:23:08 108 [Note] InnoDB: Waiting for purge to start
2016-05-18 18:23:09 108 [Note] InnoDB: 5.6.23 started; log sequence number 172727488
2016-05-18 18:23:09 108 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2016-05-18 18:23:09 108 [Note] - '127.0.0.1' resolves to '127.0.0.1';
2016-05-18 18:23:09 108 [Note] Server socket created on IP: '127.0.0.1'.
2016-05-18 18:23:13 108 [Note] Event Scheduler: Loaded 0 events
2016-05-18 18:23:13 108 [Note] C:\PHPPOS\mysql\bin\mysqld.exe: ready for connections.
Version: '5.6.23' socket: '' port: 3306 MySQL Community Server (GPL)
160518 19:46:36 [Note] Plugin 'FEDERATED' is disabled.
160518 19:46:37 InnoDB: Initializing buffer pool, size = 8.0M
160518 19:46:37 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file .\ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
160518 19:46:37 [ERROR] Plugin 'InnoDB' init function returned error.
160518 19:46:37 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160518 19:46:37 [ERROR] Do you already have another mysqld server running on port: 3306 ?
160518 19:46:37 [ERROR] Aborting
I copied their data directory to an identical server and am trying to recover.
The files ib_logfile0,ib_logfile1 are exactly 48M each so I tried the following to recover.
Add my.ini value to the mysqld section: (size of log file)
innodb_log_file_size=48M
Results on restart.
2016-05-19 14:16:45 7116 [Note] InnoDB: The log sequence numbers 170685510 and 170685510 in ibdata files do not match the log sequence number 172823340 in the ib_logfiles!
2016-05-19 14:16:45 7116 [Note] InnoDB: Database was not shutdown normally!
2016-05-19 14:16:45 7116 [Note] InnoDB: Starting crash recovery.
2016-05-19 14:16:45 7116 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-05-19 14:16:45 7116 [Note] InnoDB: Restoring possible half-written data pages
2016-05-19 14:16:45 7116 [Note] InnoDB: from the doublewrite buffer...
2016-05-19 14:16:45 7116 [ERROR] InnoDB: Table bitnami_phppos/FTS_0000000000000064_0000000000000141_INDEX_1 in the InnoDB data dictionary has tablespace id 87, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-05-19 14:16:45 7116 [ERROR] InnoDB: Table bitnami_phppos/FTS_0000000000000064_0000000000000141_INDEX_2 in the InnoDB data dictionary has tablespace id 88, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
It seems like it is looking for the .ibd files; but as I said they are not at ALL in the database anymore.
Here is settings for innodb:
+------------------------------------------+------------------------+
| Variable_name | Value |
+------------------------------------------+------------------------+
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | innodb |
| innodb_checksums | ON |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_flush_neighbors | 1 |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
| innodb_large_prefix | OFF |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | .\ |
| innodb_lru_scan_depth | 1024 |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_dirty_pages_pct_lwm | 0 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 2000 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_size | 16384 |
| innodb_print_all_deadlocks | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_undo_directory | . |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | ON |
| innodb_version | 5.6.23 |
| innodb_write_io_threads | 4 |
+------------------------------------------+------------------------+
119 rows in set (0.00 sec)
Any other steps I can try to recover? (I have tried many things including changing innodb_force_recovery to 6 and also trying innodb_fast_shutdown to 0. Binlogs are not available.
Is this customer out of luck?
Best Answer
Community Wiki answer generated from comments on the question by akuzminsky
User data are in *.ibd files. So, from just ibdata1 and logfiles - not possible. What you need is take an image from the disk, parse it with stream_parser and if it finds InnoDB pages fetch records from them with c_parser (search for undrop-for-innodb).
For the future, but make sure MySQL runs in durable mode + NTFS + write cache protected by a battery (if enabled) + backups.
Durable mode:
Defaults of all of these are set to make InnoDB durable. Just make sure you don't change them.