Mysql

MySQL 5.6 伺服器啟動失敗：InnoDB 作業系統錯誤號 23

  • February 16, 2016

我在 Windows 7 機器上使用本地 MySQL 5.6 伺服器 (InnoDB) 來儲存研究項目的多個模式。直到昨天，系統執行沒有問題。在查詢期間，MySQL Workbench 顯示錯誤1036 - Table data is read only.之前我在該表中寫入資訊沒有問題。

在那之前，我從未儲存過 root 密碼。當我重新啟動 Workbench 時，我決定將 root 密碼儲存在“密碼儲存庫”中。然後我重新啟動了電腦。之後，我無法重新連接到伺服器並收到以下消息：Could not connect, server my not be running. - Can’t connect to MySQL Server on ‘127.0.0.1’(10061)

如果我嘗試在 cmd 中使用 啟動伺服器mysqld，我會收到以下警告：

$$ Warning $$不推薦使用帶有隱式 DEFAULT 值的 TIMESTAMP。請使用 –explicit_defaults_for_timestamp 伺服器選項（有關詳細資訊，請參閱文件）。

似乎文件“ibdata1”是問題的原因。當我嘗試複製 MySQL 數據目錄時，系統無法訪問此文件。

以下是錯誤日誌的摘錄：

2016-02-11 12:14:46 2164 [Note] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: Normal shutdown

2016-02-11 12:14:46 2164 [Note] Giving 1 client threads a chance to die gracefully
2016-02-11 12:14:46 2164 [Note] Event Scheduler: Purging the queue. 0 events
2016-02-11 12:14:46 2164 [Note] Shutting down slave threads
2016-02-11 12:14:48 2164 [Note] Forcefully disconnecting 0 remaining clients
2016-02-11 12:14:48 2164 [Note] Binlog end
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'partition'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_METRICS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_CMPMEM'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_CMP'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_LOCKS'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'INNODB_TRX'
2016-02-11 12:14:48 2164 [Note] Shutting down plugin 'InnoDB'
2016-02-11 12:14:48 2164 [Note] InnoDB: FTS optimize thread exiting.
2016-02-11 12:14:48 2164 [Note] InnoDB: Starting shutdown...
2016-02-11 12:14:49 2164 [Note] InnoDB: Shutdown completed; log sequence number 241353715815
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'BLACKHOLE'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'ARCHIVE'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'MRG_MYISAM'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'MyISAM'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'MEMORY'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'CSV'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'sha256_password'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'mysql_old_password'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'mysql_native_password'
2016-02-11 12:14:49 2164 [Note] Shutting down plugin 'binlog'
2016-02-11 12:14:49 2164 [Note] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: Shutdown complete

2016-02-11 12:15:19 2144 [Note] Plugin 'FEDERATED' is disabled.
2016-02-11 12:15:20 87c 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.
2016-02-11 12:15:20 2144 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-02-11 12:15:20 2144 [Note] InnoDB: The InnoDB memory heap is disabled
2016-02-11 12:15:20 2144 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2016-02-11 12:15:20 2144 [Note] InnoDB: Memory barrier is not used
2016-02-11 12:15:20 2144 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-02-11 12:15:20 2144 [Note] InnoDB: Not using CPU crc32 instructions
2016-02-11 12:15:20 2144 [Note] InnoDB: Initializing buffer pool, size = 6.0G
2016-02-11 12:15:20 2144 [Note] InnoDB: Completed initialization of buffer pool
2016-02-11 12:15:31 87c  InnoDB: Operating system error number 23 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
2016-02-11 12:15:31 2144 [ERROR] InnoDB: File (unknown): 'read' returned OS error 123. Cannot continue operation
2016-02-11 12:16:51 2156 [Note] Plugin 'FEDERATED' is disabled.
2016-02-11 12:16:51 888 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.
2016-02-11 12:16:51 2156 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-02-11 12:16:51 2156 [Note] InnoDB: The InnoDB memory heap is disabled
2016-02-11 12:16:51 2156 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2016-02-11 12:16:51 2156 [Note] InnoDB: Memory barrier is not used
2016-02-11 12:16:51 2156 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-02-11 12:16:51 2156 [Note] InnoDB: Not using CPU crc32 instructions
2016-02-11 12:16:51 2156 [Note] InnoDB: Initializing buffer pool, size = 6.0G
2016-02-11 12:16:51 2156 [Note] InnoDB: Completed initialization of buffer pool
2016-02-11 12:16:58 888  InnoDB: Operating system error number 23 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
2016-02-11 12:16:58 2156 [ERROR] InnoDB: File (unknown): 'read' returned OS error 123. Cannot continue operation
2016-02-11 12:17:56 5004 [Note] Plugin 'FEDERATED' is disabled.
2016-02-11 12:17:56 13a4 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.
2016-02-11 12:17:56 5004 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-02-11 12:17:56 5004 [Note] InnoDB: The InnoDB memory heap is disabled
2016-02-11 12:17:56 5004 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2016-02-11 12:17:56 5004 [Note] InnoDB: Memory barrier is not used
2016-02-11 12:17:56 5004 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-02-11 12:17:56 5004 [Note] InnoDB: Not using CPU crc32 instructions
2016-02-11 12:17:56 5004 [Note] InnoDB: Initializing buffer pool, size = 6.0G
2016-02-11 12:17:56 5004 [Note] InnoDB: Completed initialization of buffer pool
2016-02-11 12:18:01 13a4  InnoDB: Operating system error number 23 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
2016-02-11 12:18:01 5004 [ERROR] InnoDB: File (unknown): 'read' returned OS error 123. Cannot continue operation
2016-02-11 12:20:19 1416 [Note] Plugin 'FEDERATED' is disabled.
2016-02-11 12:20:19 e7c 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.
2016-02-11 12:20:19 1416 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-02-11 12:20:19 1416 [Note] InnoDB: The InnoDB memory heap is disabled
2016-02-11 12:20:19 1416 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2016-02-11 12:20:19 1416 [Note] InnoDB: Memory barrier is not used
2016-02-11 12:20:19 1416 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-02-11 12:20:19 1416 [Note] InnoDB: Not using CPU crc32 instructions
2016-02-11 12:20:19 1416 [Note] InnoDB: Initializing buffer pool, size = 6.0G
2016-02-11 12:20:19 1416 [Note] InnoDB: Completed initialization of buffer pool
2016-02-11 12:20:29 e7c  InnoDB: Operating system error number 23 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
2016-02-11 12:20:29 1416 [ERROR] InnoDB: File (unknown): 'read' returned OS error 123. Cannot continue operation

磁碟上有足夠的可用空間。MySQL 在 C:\ 上執行，數據目錄在 D: 上。執行 MySQL 的使用者帳戶對數據目錄具有完全的權限和特權。我真的不知道該怎麼辦。現在我正在備份我的數據。然後我會嘗試一個chkdsk。但我真的擔心這不會有幫助。您還有其他建議嗎？

更新：

由於讀取錯誤，備份“ibdata1”文件和一個表文件“xyz.MYD”失敗。我做了一個chkdsk檢查發現並修復了一些壞扇區。但是無論如何 MySQL 伺服器都沒有啟動。然後我使用了能夠修復壞扇區的“HDD Regenerator 2011”。之後我可以讀取和備份這兩個文件（ibdata1 & xyz.MYD）。但是 MySQL 伺服器仍然沒有啟動。接下來我能做什麼？

問題的原因很可能是 HDD 錯誤，而 MySQL 正在表“xyz”中寫入。最後，我能夠恢復系統。這是我所做的：

  1. 包括 MySQL datadir 的數據備份（表“xyz”和我的“ibdata”可以讀取/複製，因此無法進行完整備份） Ran chkdsk —> 找到但沒有修復壞扇區
  2. 執行 HDD Regenerator v2011 —> 找到並修復壞扇區
  3. 完成備份
  4. 測試 MySQL 是否會在重新生成後執行 -> 沒有工作
  5. 添加innodb_force_recovery=1 … 6到$$ mysqld $$我的配置文件中的部分（MySQL 僅以最大值“6”開始）
  6. 用於mysqlcheck db_name -u root -p檢查哪些表已損壞
  7. 用於mysqluc > mysqlfrm獲取損壞表的表結構（僅與--diagnostic選項一起使用）
  8. 用於myisamchk -r -q table_name修復損壞的表（此表使用 myisam 引擎）
  9. 看到這個：http ://dev.mysql.com/doc/refman/5.7/en/myisam-repair.html
  10. 我不得不使用這個技巧來讓它工作FOR %G IN (dir \b c:\mysql\data\mydb\*.myi) DO myisamchk -r -f %G：（https://iandunn.name/myisamchk-error-22-on-windows/）
  11. 最初該表有 55721190 行，在 myisamchk 修復後它有 55721166 行（對我來說沒問題，因為我可以辨識和恢復失去的行）
  12. 備份
  13. mysqldump -u root -p my_schema > .../recovery_dump.sql
  14. mysql drop all_databases, 停止伺服器, 將 ibdata1 和 iblogs 移動到 tmp 文件夾,innodb_force_recovery=6在配置文件中刪除, 啟動伺服器
  15. mysql -u root -p my_schema < .../recovery_dump.sql

這個“InnoDB Corruption Repair Guide”是一個很大的幫助。（https://forums.cpanel.net/threads/innodb-corruption-repair-guide.418722/）

引用自：https://dba.stackexchange.com/questions/129097

相關問答

Mysql

MySql 5.6：忽略 my.ini 中的 innodb_page_size 設置

  • August 22, 2018
檢視問答
Mysql

MySQL - Windows 10 上的 InnoDB 寫入速度慢

  • January 18, 2016
檢視問答
Mysql

無法打開表 mysql/innodb_index_stats

  • December 2, 2015
檢視問答
Mysql

如何刷新 innodb 表，複製文件，然後從 Windows bat 文件中解鎖表？

  • October 10, 2015
檢視問答
Mysql

Mysql 8.0.21 long living Prepared statements 有記憶體洩漏？還是我們在做一些奇怪的事情

  • March 3, 2022
檢視問答
Mysql

Windows 上的 MySQL 升級

  • January 29, 2022
檢視問答