@AdamDickins

Как восстановить базу данных mysql, если служба mysql не запускается на windows?

Здравствуйте. Все началось с внезапной перезагрузки windows

Служба mysql перестала запускаться. Логи ошибки
2024-04-18T15:45:33.695183Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\Mysql80\bin\mysqld.exe (mysqld 8.0.36) starting as process 5728
2024-04-18T15:45:33.707629Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-04-18T15:45:33.775503Z 1 [ERROR] [MY-012562] [InnoDB] We scanned the log up to 365732577584. A checkpoint was at 365732577634 and the maximum LSN on a database page was 0. It is possible that the database is now corrupt!
2024-04-18T15:45:33.777504Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: log0recv.cc:3888 thread 8104
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2024-04-18T15:45:33Z UTC - mysqld got exception 0x16 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x246a273d180
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff67d7ea488    mysqld.exe!?my_print_stacktrace@@YAXPEBEK@Z()
7ff67c95e5c1    mysqld.exe!?print_fatal_signal@@YAXH@Z()
7ff67c95e303    mysqld.exe!?my_server_abort@@YAXXZ()
7ff67d7ceaaa    mysqld.exe!?my_abort@@YAXXZ()
7ff67d99f479    mysqld.exe!?do_reset@Zstd_dec@compression@transaction@binary_log@@EEAAXXZ()
7ff67d95590f    mysqld.exe!?do_reset@Zstd_dec@compression@transaction@binary_log@@EEAAXXZ()
7ff67d937b97    mysqld.exe!?do_reset@Zstd_dec@compression@transaction@binary_log@@EEAAXXZ()
7ff67d834254    mysqld.exe!?do_reset@Zstd_dec@compression@transaction@binary_log@@EEAAXXZ()
7ff67d82eb9a    mysqld.exe!?do_reset@Zstd_dec@compression@transaction@binary_log@@EEAAXXZ()
7ff67d7527b9    mysqld.exe!?init@Rpl_info_values@@QEAA_NXZ()
7ff67d68076b    mysqld.exe!?init@Rpl_info_values@@QEAA_NXZ()
7ff67c7d6abe    mysqld.exe!?run_bootstrap_thread@bootstrap@@YA_NPEBDPEAUMYSQL_FILE@@P6A_NPEAVTHD@@@ZW4enum_thread_type@@@Z()
7ff67dd6d119    mysqld.exe!?my_init_dynamic_array@@YA_NPEAUDYNAMIC_ARRAY@@IIPEAXII@Z()
7ff67d7dac7c    mysqld.exe!?my_thread_self_setname@@YAXPEBD@Z()
7ffb6a321bb2    ucrtbase.dll!_configthreadlocale()
7ffb6b077034    KERNEL32.DLL!BaseThreadInitThunk()
7ffb6c6a2651    ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): 
Connection ID (thread ID): 1
Status: NOT_KILLED


Иду на сайт, как указано в ошибке
dev.mysql.com/doc/refman/8.0/en/forcing-innodb-rec...

Следую инструкции
[mysqld]
innodb_force_recovery = 1


Warning

Only set innodb_force_recovery to a value greater than 0 in an emergency situation, so that you can start InnoDB and dump your tables. Before doing so, ensure that you have a backup copy of your database in case you need to recreate it. Values of 4 or greater can permanently corrupt data files. Only use an innodb_force_recovery setting of 4 or greater on a production server instance after you have successfully tested the setting on a separate physical copy of your database. When forcing InnoDB recovery, you should always start with innodb_force_recovery=1 and only increase the value incrementally, as necessary.


В интрукции написано что нужно постепенно увеличивать
innodb_force_recovery, от 1 до 6.

Изменения параметра innodb_force_recovery также не запускает mysql
При
innodb_force_recovery = 6

Выдает следующий лог
2024-04-18T16:41:13.566399Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\Mysql80\bin\mysqld.exe (mysqld 8.0.36) starting as process 7736
2024-04-18T16:41:13.576362Z 0 [Warning] [MY-013991] [InnoDB] Ignored the innodb-redo-log-capacity option in the Innodb read-only mode. Server is using active redo log files.
2024-04-18T16:41:13.578812Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-04-18T16:41:13.697453Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-04-18T16:41:13.813841Z 1 [Warning] [MY-011018] [InnoDB] Skip updating information_schema metadata in InnoDB read-only mode.
2024-04-18T16:41:14.027627Z 1 [Warning] [MY-010005] [Server] Skip re-populating collations and character sets tables in InnoDB read-only mode.
2024-04-18T16:41:14.028895Z 1 [ERROR] [MY-014063] [Server] Failed to set the data dictionary property MYSQLD_VERSION_HI.
2024-04-18T16:41:14.032045Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2024-04-18T16:41:14.032400Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-04-18T16:41:14.103526Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\Mysql80\bin\mysqld.exe: Shutdown complete (mysqld 8.0.36)  MySQL Community Server - GPL.


Как можно выдернуть таблицы из базы ?
Может как то можно скопировать .ibd в другую базу ?
Заранее спасибо за помощь.
  • Вопрос задан
  • 152 просмотра
Пригласить эксперта
Ответы на вопрос 1
@psiklop
mysqldump попробуй
mysqldump -u user -p'pass' db > export.sql
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы