I have a mysql server and its default database is called mysql. Suddenly it stopped and I did not start it and it gives me this error.
2024-02-05T22:10:06.810728Z 1 [ERROR] [MY-012179] [InnoDB] Could not find any file associated with the tablespace ID: 4294967294
2024-02-05T22:10:06.811213Z 1 [ERROR] [MY-012964] [InnoDB] Use --innodb-directories to find the tablespace files. If that fails then use --innodb-force-recovery=1 to ignore this and to permanently lose all changes to the missing tablespace(s)
2024-02-05T22:10:06.921309Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2024-02-05T22:10:06.984345Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
I lost mysql.ibd file
I changed the my.ini and set innodb_force_recovery =1 up to innodb_force_recovery=6, but the error still does not go away. But the problem is that I found a file called mysql.ibd with an area of 0KB, even though I found in Windows what was done a few days ago that there is a file mysql.ibd with an area of 9.2GB, but I do not want to use it as restore, it says to delete.
Most of the database in other ibd files (220GB)
I have 36000 Ibd files, each one has a table and their table structure is the same and I know it.
I also have about 1000 ibd files, but each one of them is a TABLESPACE, and it contains about a thousand tables, meaning 1000 files * 1000 tables in each file. I do not know the names of the tables in each IBD file, but I know their structure.
Right now I delete the new mysql.ibd file and This is the error I get when I try to run MYSQL80 windows service.
2024-02-08T23:26:52.956838Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2024-02-08T23:26:52.958414Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.30) starting as process 16512
2024-02-08T23:26:53.028601Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=100663296. Please use innodb_redo_log_capacity instead.
2024-02-08T23:26:53.035562Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2024-02-08T23:26:53.036292Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2024-02-08T23:26:53.036848Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-02-08T23:26:53.037690Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.30) MySQL Community Server - GPL.
This is the Folder structure of my backed up data. I removed IBD files for simplifications and added example ibd files. in the root data folder, the IBD files are general tablespaces, and inside mysql folder the each ibd file has one table.
\---Data
| TableSpace_with_1000_tables_inside.ibd <---- This is what I want to recover
| ibdata1
| ibtmp1
| ib_buffer_pool
| ib_logfile0
| ib_logfile1
| private_key.pem
| public_key.pem
| server-cert.pem
| server-key.pem
| undo_001
| undo_002
+---mysql
| One_Table_inside_each_file.ibd <----- This is what I want to recover
| general_log.CSM
| general_log.CSV
| general_log_213.sdi
+---performance_schema
| accounts_145.sdi
| binary_log_trans_189.sdi
| cond_instances_82.sdi
| data_locks_160.sdi
| data_lock_waits_161.sdi
| error_log_83.sdi
| events_errors_su_143.sdi
| events_stages_cu_111.sdi
| events_stages_hi_112.sdi
| events_stages_su_117.sdi
| events_statement_125.sdi
| events_transacti_138.sdi
| events_waits_cur_84.sdi
| events_waits_his_86.sdi
| events_waits_sum_92.sdi
| file_instances_93.sdi
| file_summary_by__94.sdi
| file_summary_by__95.sdi
| global_status_181.sdi
| global_variables_184.sdi
| hosts_146.sdi
| host_cache_96.sdi
| keyring_componen_191.sdi
| keyring_keys_152.sdi
| log_status_174.sdi
| memory_summary_b_157.sdi
| memory_summary_g_153.sdi
| metadata_locks_159.sdi
| mutex_instances_97.sdi
| objects_summary__98.sdi
| performance_time_99.sdi
| persisted_variab_187.sdi
| prepared_stateme_175.sdi
| processlist_100.sdi
| replication_appl_165.sdi
| replication_appl_171.sdi
| replication_asyn_172.sdi
| replication_asyn_173.sdi
| replication_conn_164.sdi
| replication_grou_163.sdi
| rwlock_instances_101.sdi
| session_account__151.sdi
| session_connect__150.sdi
| session_status_182.sdi
| session_variable_185.sdi
| setup_actors_102.sdi
| setup_consumers_103.sdi
| setup_instrument_104.sdi
| setup_objects_105.sdi
| setup_threads_106.sdi
| socket_instances_147.sdi
| socket_summary_b_148.sdi
| status_by_accoun_177.sdi
| status_by_host_178.sdi
| status_by_thread_179.sdi
| status_by_user_180.sdi
| table_handles_158.sdi
| table_io_waits_s_107.sdi
| table_lock_waits_109.sdi
| threads_110.sdi
| tls_channel_stat_190.sdi
| users_144.sdi
| user_defined_fun_188.sdi
| user_variables_b_176.sdi
| variables_by_thr_183.sdi
| variables_info_186.sdi
|
\---sys
sys_config.ibd
and here is my my.ini config file
[client]
port=3306
[mysql]
no-beep
[mysqld]
innodb_force_recovery=1
port=3306
datadir="C:\Users\Omar\Desktop\Data"
authentication_policy=caching_sha2_password,,
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="DESKTOP-kkk.log"
slow-query-log=1
slow_query_log_file="DESKTOP-kkk-slow.log"
long_query_time=10
log-error="DESKTOP-kkk.err"
log-bin="DESKTOP-kkk-bin"
server-id=1
lower_case_table_names=1
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
max_connections=50000
table_open_cache=4000
tmp_table_size=732M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=2G
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=128M
innodb_log_file_size=48M
innodb_thread_concurrency=65
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=20000
innodb_old_blocks_time=1000
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=64M
max_connect_errors=100
open_files_limit=8161
sort_buffer_size=256K
table_definition_cache=2000
binlog_row_event_max_size=8K
sync_source_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
loose_mysqlx_port=33060
