MariaDBをfrmとibdから復旧を試みる記録（現在進行中）

ぶっこわれたMariaDBのデータベースをfrmファイル、ibdファイルから復旧させてみる記録。ただいま絶賛作業中で備忘録として記録していく。無事復旧できるかもしれないしできないかもしれないし、試行錯誤の記録（になる予定）なので手順書としての価値は一切ないが、何かの参考になればと。

• 環境
  • FreeBSD 13.1-RELEASE-p2
  • MariaDB 10.5 （もしかすると10.3）
  • Nextcloud 24.0.3
• 状況
  1. NextcloudのWeb UI経由で大量の動画ファイルをアップロードしてたら、DBでデッドロックが発生。
  2. mysqldの再起動を試みるもいつまでたっても終了しなかったのでkill
  3. その後、MariaDBが立ち上がらなくなる
  4. innodb_force_recoveryを試してみるもコアダンプするという中々の状況

     2022-09-04 10:11:34 0 [Warning] You need to use --log-bin to make --binlog-format work.
     2022-09-04 10:11:34 0 [Warning] The parameter innodb_large_prefix is deprecated and has no effect. It may be removed in future releases. See https://mariadb.com/kb/en/library/xtradbinnodb-file-format/
     2022-09-04 10:11:34 0 [Note] InnoDB: !!! innodb_force_recovery is set to 1 !!!
     2022-09-04 10:11:34 0 [Note] InnoDB: Uses event mutexes
     2022-09-04 10:11:34 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
     2022-09-04 10:11:34 0 [Note] InnoDB: Number of pools: 1
     2022-09-04 10:11:34 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
     2022-09-04 10:11:34 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
     2022-09-04 10:11:34 0 [Note] InnoDB: Completed initialization of buffer pool
     2022-09-04 10:11:34 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=25931582135,25931582135
     2022-09-04 10:11:34 0 [ERROR] InnoDB: Not applying INSERT_HEAP_REDUNDANT due to corruption on [page id: space=0, page number=11]
     2022-09-04 10:11:34 0 [ERROR] InnoDB: Not applying INSERT_HEAP_REDUNDANT due to corruption on [page id: space=0, page number=11]
     2022-09-04 10:11:34 0 [ERROR] InnoDB: Not applying INSERT_HEAP_REDUNDANT due to corruption on [page id: space=0, page number=11]
     2022-09-04 10:11:34 0 [ERROR] InnoDB: Not applying INSERT_HEAP_REDUNDANT due to corruption on [page id: space=0, page number=11]
     2022-09-04 10:11:34 0 [ERROR] InnoDB: Not applying INSERT_HEAP_REDUNDANT due to corruption on [page id: space=0, page number=11]
     2022-09-04 10:11:34 0 [ERROR] InnoDB: Not applying INSERT_HEAP_REDUNDANT due to corruption on [page id: space=0, page number=11]
     2022-09-04 10:11:34 0 [ERROR] [FATAL] InnoDB: Trying to read 16384 bytes at 28980721369088 outside the bounds of the file: ./ibdata1
     220904 10:11:34 [ERROR] mysqld got signal 6 ;
     This could be because you hit a bug. It is also possible that this binary
     or one of the libraries it was linked against is corrupt, improperly built,
     or misconfigured. This error can also be caused by malfunctioning hardware.
     
     To report this bug, see https://mariadb.com/kb/en/reporting-bugs
     
     We will try our best to scrape up some info that will hopefully help
     diagnose the problem, but since we have already crashed,
     something is definitely wrong and this may fail.
     
     Server version: 10.5.16-MariaDB
     key_buffer_size=134217728
     read_buffer_size=131072
     max_used_connections=0
     max_threads=153
     thread_count=0
     It is possible that mysqld could use up to
     key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467795 K  bytes of memory
     Hope that's ok; if not, decrease some variables in the equation.
     
     Thread pointer: 0x0
     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...
     stack_bottom = 0x0 thread_stack 0x49000
     0x1339f0c <my_print_stacktrace+0x3c> at /usr/local/libexec/mariadbd
     0xc95910 <handle_fatal_signal+0x290> at /usr/local/libexec/mariadbd
     0x80197e580 <pthread_sigmask+0x540> at /lib/libthr.so.3
     0x80197db3f <pthread_setschedparam+0x82f> at /lib/libthr.so.3
     0x7ffffffff2d3 <???> at ???
     0x801be033a <thr_kill+0xa> at /lib/libc.so.7
     0x801b58c74 <_raise+0x34> at /lib/libc.so.7
     0x801c0a109 <abort+0x49> at /lib/libc.so.7
     0x115a8ae <wsrep_thd_set_PA_unsafe+0x941ce> at /usr/local/libexec/mariadbd
     0x1154da1 <wsrep_thd_set_PA_unsafe+0x8e6c1> at /usr/local/libexec/mariadbd
     0x11858e3 <wsrep_thd_set_PA_unsafe+0xbf203> at /usr/local/libexec/mariadbd
     0x1185e8a <wsrep_thd_set_PA_unsafe+0xbf7aa> at /usr/local/libexec/mariadbd
     0x1140cda <wsrep_thd_set_PA_unsafe+0x7a5fa> at /usr/local/libexec/mariadbd
     0x112ae05 <wsrep_thd_set_PA_unsafe+0x64725> at /usr/local/libexec/mariadbd
     0x112899b <wsrep_thd_set_PA_unsafe+0x622bb> at /usr/local/libexec/mariadbd
     0x1120e32 <wsrep_thd_set_PA_unsafe+0x5a752> at /usr/local/libexec/mariadbd
     0x1120b6f <wsrep_thd_set_PA_unsafe+0x5a48f> at /usr/local/libexec/mariadbd
     0x11b8bb6 <wsrep_thd_set_PA_unsafe+0xf24d6> at /usr/local/libexec/mariadbd
     0x12a82f9 <wsrep_thd_set_PA_unsafe+0x1e1c19> at /usr/local/libexec/mariadbd
     0x10e6899 <wsrep_thd_set_PA_unsafe+0x201b9> at /usr/local/libexec/mariadbd
     0xb7a68c <_Z24ha_initialize_handlertonP13st_plugin_int+0x6c> at /usr/local/libexec/mariadbd
     0xdf57af <_Z11plugin_initPiPPci+0x11af> at /usr/local/libexec/mariadbd
     0xdf4f91 <_Z11plugin_initPiPPci+0x991> at /usr/local/libexec/mariadbd
     0xb5bf93 <_Z20init_server_psi_keysv+0x2af3> at /usr/local/libexec/mariadbd

  5. バイナリログ消したり色々してるうちに起動できるようになった