MySQL資料庫當機，啟動不起來，教你一招搞定！

技术颜良發表於2024-10-15
MySql資料庫

檢視MySQL error日誌

檢視 MySQL error日誌，排查哪個表（表空間）檔案破壞或者丟失，線索就是[page id: space=xxx, page number=xxx]。

2024-09-09T10:12:39.111413+08:00 0 [ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=73, page number=3]. You may have to recover from a backup.
.......................................
InnoDB: End of page dump
InnoDB: Page may be an index page where index id is 89
2024-09-09T10:12:39.907855+08:00 0 [ERROR] [FATAL] InnoDB: Aborting because of a corrupt database page in the system tablespace. Or,  there was a failure in tagging the tablespace  as corrupt.
2024-09-09 10:12:39 0x7f7fe37fe700  InnoDB: Assertion failure in thread 140187254384384 in file ut0ut.cc line 921
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/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
02:12:39 UTC - 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.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

從日誌內容來看，"page id: space=73, page number=3"，MySQL表空間page id 73 損壞，導致無法正常啟動，讀取不到需要的資料。

新增強制恢復引數

往配置檔案中新增強制恢復引數，先將資料庫忽略錯誤啟動(強制啟動資料庫服務)。innodb_force_recovery值最高支援設定到 6，但是值為 4 或更大可能會永久損壞資料檔案。因此當強制 InnoDB 恢復時，應始終以innodb_force_recovery=1開頭，並僅在必要時遞增該值。

[mysqld]
innodb_force_recovery = 1

  • MySQL 有個一個特性：Forcing InnoDB Recovery，啟用這個特性需要設定 innodb_force_recovery 大於 0。

  • innodb_force_recovery 可以設定為 1-6，大的值包含前面所有小於它的值的影響。

  • 建議從最小的開始嘗試，1到6依次的依次啟動。

innodb_force_recovery相關值說明：

1 (SRV_FORCE_IGNORE_CORRUPT): 忽略檢查到的 corrupt 頁。儘管檢測到了損壞的 page 仍強制服務執行。一般設定為該值即可，然後 dump 出庫表進行重建。

2 (SRV_FORCE_NO_BACKGROUND): 阻止主執行緒的執行，如主執行緒需要執行 full purge 操作，會導致 crash。阻止 master thread 和任何 purge thread 執行。若 crash 發生在 purge 環節則使用該值。

3 (SRV_FORCE_NO_TRX_UNDO): 不執行事務回滾操作。

4 (SRV_FORCE_NO_IBUF_MERGE): 不執行插入緩衝的合併操作。如果可能導致崩潰則不要做這些操作。不要進行統計操作。該值可能永久損壞資料檔案。若使用了該值，則將來要刪除和重建輔助索引。

5 (SRV_FORCE_NO_UNDO_LOG_SCAN): 不檢視重做日誌，InnoDB 儲存引擎會將未提交的事務視為已提交。此時 InnoDB 甚至把未完成的事務按照提交處理。該值可能永久性的損壞資料檔案。

6 (SRV_FORCE_NO_LOG_REDO): 不執行前滾的操作。恢復時不做 redo log roll-forward。使資料庫頁處於廢止狀態，繼而可能引起 B 樹或者其他資料庫結構更多的損壞。

另外從 MySQL 5.7.18 開始， DROP TABLE不允許使用 innodb_force_recovery大於 4 的值。

定位損壞的表

MYSQL服務啟動之後的報錯日誌提示，定位有問題的表相關資訊。我們需要進入information_schma 資料庫，檢視相關檢視，獲取資訊：

MySQL5.7：
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from INNODB_SYS_TABLES where SPACE=73;
+----------+------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME             | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+------------------+------+--------+-------+-------------+------------+---------------+------------+
|       88 | dbtest/t_corrupt |   33 |      4 |    73 | Barracuda   | Dynamic    |             0 | Single     |
+----------+------------------+------+--------+-------+-------------+------------+---------------+------------+
1 row in set (0.00 sec)

mysql> select * from INNODB_SYS_TABLESPACES where SPACE=73;
+-------+------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME             | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
|    73 | dbtest/t_corrupt |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |          4096 |     98304 |          98304 |
+-------+------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
1 row in set (0.00 sec)

如果是MySQL8.0，則使用以下方式進行檢視：
select * from INNODB_TABLES where SPACE=73
或
select * from INNODB_TABLESPACES where SPACE=73

INNODB_SYS_TABLESPACES該表提供查詢有關InnoDB獨立表空間和普通表空間的後設資料資訊（也包含了全文索引表空間），等同於InnoDB資料字典中SYS_TABLESPACES表中的資訊。

  • 該表為memory引擎臨時表，查詢該表的使用者需要有process許可權

  • INFORMATION_SCHEMA.FILES表提供查詢的資訊中包含所有InnoDB表空間型別的後設資料資訊，包括獨立表空間、普通表空間、系統表空間、臨時表空間和undo表空間（如果有）

  • 因為對於所有Antelope檔案格式的表空間檔案（注意與表的FLAG不同），表空間FLAG資訊始終為零，所以如果表空間行格式為 Redundant 或 Compact，則無法使用該FLAG資訊確定一個十進位制的整數(也就是說在Antelope檔案格式的表空間檔案中，無法透過表空間檔案的FLAG資訊判斷行格式是Compact、 Redundant、Compressed、Dynamic中的哪一種)

  • 普通表空間引入之後，系統表空間的後設資料資訊也在INNODB_SYS_TABLESPACES表暴露出來提供了查詢

mysql> select * from information_schema.INNODB_SYS_TABLESPACES where name like '%country%';
+-------+-----------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME                  | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+-----------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
|    27 | sakila/country        |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |         65536 |     98304 |          98304 |
|    51 | world/country         |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |         65536 |    180224 |         180224 |
|    52 | world/countrylanguage |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |         65536 |    229376 |         229376 |
+-------+-----------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
3 rows in set (0.10 sec)

欄位含義如下：

  • SPACE：表空間檔案ID

  • NAME：資料庫和表名組合字串，例如：test/t1

  • FLAG：有關表空間檔案儲存格式和儲存特性的bit位級資料

  • FILE_FORMAT：表空間檔案儲存格式。例如：Antelope、Barracuda或普通表空間支援的任何行格式。該欄位中的資料是根據駐留在.ibd檔案中的表空間FLAG資訊進行解釋的。

  • ROW_FORMAT：表空間的行格式（Compact、 Redundant、Compressed、Dynamic），該欄位中的資料是根據駐留在.ibd檔案中的表空間FLAG資訊進行解釋的

  • PAGE_SIZE：表空間中的頁大小。該欄位中的資料是根據駐留在.ibd檔案中的表空間FLAG資訊進行解釋的

  • ZIP_PAGE_SIZE：表空間zip頁大小。該欄位中的資料是根據駐留在.ibd檔案中的表空間FLAG資訊進行解釋的

  • SPACE_TYPE：表空間檔案的型別。可能的值包括：General (普通表空間)和Single (獨立表空間檔案)

  • FS_BLOCK_SIZE：檔案系統中的塊大小，它是用於hole punching技術的單位大小。該欄位是在InnoDB透明頁壓縮功能被引入之後新增的

  • FILE_SIZE：檔案表面上的大小(即表示檔案未壓縮時的最大大小）。該欄位是在InnoDB透明頁壓縮功能被引入之後新增的

  • ALLOCATED_SIZE：檔案的實際大小，即在磁碟上分配的空間大小。該欄位是在InnoDB透明頁壓縮功能被引入之後新增的

INNODB_SYS_TABLES該表提供查詢有關InnoDB表的後設資料，等同於InnoDB資料字典中SYS_TABLES表的資訊，該表為memory引擎臨時表，查詢該表的使用者需要有process許可權。

mysql> select * from information_schema.INNODB_SYS_TABLES where NAME like '%country%';
+----------+-----------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME                  | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+-----------------------+------+--------+-------+-------------+------------+---------------+------------+
|       44 | sakila/country        |   33 |      6 |    27 | Barracuda   | Dynamic    |             0 | Single     |
|       68 | world/country         |   33 |     18 |    51 | Barracuda   | Dynamic    |             0 | Single     |
|       69 | world/countrylanguage |   33 |      7 |    52 | Barracuda   | Dynamic    |             0 | Single     |
+----------+-----------------------+------+--------+-------+-------------+------------+---------------+------------+
3 rows in set (0.00 sec)

欄位含義如下：

  • TABLE_ID：Innodb表ID，在整個例項中唯一

  • NAME：表名稱。該字串包含db_name+tb_name，例如"test/t1"，該字串值可能受lower_case_table_names系統引數設定的影響

  • FLAG：有關表格式和儲存特性的位級資訊資料，包括行格式，壓縮頁大小（如果適用）以及DATA DIRECTORY子句是否與CREATE TABLE或ALTER TABLE一起使用等

  • N_COLS：表中的列數量。該欄位值包含了Innodb表的三個隱藏列（DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR），另外，如果存在虛擬生成列，則該欄位值還包含虛擬生成列

  • SPACE：表所在表空間ID。0表示InnoDB系統表空間。任何其他非0數字獨立表空間或普通表空間。該ID值在執行過TRUNCATE TABLE語句後保持不變。對於每個表的表空間ID記錄，在此表中的ID值是唯一的

  • FILE_FORMAT：表空間檔案的儲存格式（有效值為：Antelope、Barracuda）

  • ROW_FORMAT：表的資料行儲存格式（有效值為：Compact,、Redundant,、Dynamic、Compressed）

  • ZIP_PAGE_SIZE：壓縮頁大小。僅適用於使用壓縮行格式的表

  • SPACE_TYPE：表所屬的表空間型別。可能的值包括：System(系統表空間)、General(普通表空間)、Single(獨立表空間)、使用CREATE TABLE或ALTER TABLE 語句時使用TABLESPACE建表選項指定表空間名稱，例如：TABLESPACE = innodb_system，表示分配該表到系統表空間，如果需要指定到一個普通表空間(針對NDB儲存引擎適用)

處理有問題的表

檢視page number內容：

mysql> select * from information_schema.INNODB_BUFFER_PAGE where  SPACE=73 and PAGE_NUMBER=3 ;
+---------+----------+-------+-------------+-----------+------------+-----------+-----------+---------------------+---------------------+-------------+----------------------+-----------------+----------------+-----------+-----------------+------------+---------+--------+-----------------+
| POOL_ID | BLOCK_ID | SPACE | PAGE_NUMBER | PAGE_TYPE | FLUSH_TYPE | FIX_COUNT | IS_HASHED | NEWEST_MODIFICATION | OLDEST_MODIFICATION | ACCESS_TIME | TABLE_NAME           | INDEX_NAME      | NUMBER_RECORDS | DATA_SIZE | COMPRESSED_SIZE | PAGE_STATE | IO_FIX  | IS_OLD | FREE_PAGE_CLOCK |
+---------+----------+-------+-------------+-----------+------------+-----------+-----------+---------------------+---------------------+-------------+----------------------+-----------------+----------------+-----------+-----------------+------------+---------+--------+-----------------+
|       1 |      158 |    73 |           3 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |     6180293 | `dbtest`.`t_corrupt` | GEN_CLUST_INDEX |            100 |      2900 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
+---------+----------+-------+-------------+-----------+------------+-----------+-----------+---------------------+---------------------+-------------+----------------------+-----------------+----------------+-----------+-----------------+------------+---------+--------+-----------------+
1 row in set (0.11 sec)

注意：查詢 INNODB_BUFFER_PAGE 系統表[1] 會對效能有影響，因此不建議隨意在生產環境執行。

如果錯誤日誌中有提示space id和index id相關資訊，則也可以透過如下方式進行查詢：

mysql>  select b.INDEX_ID, a.NAME as TableName, a.SPACE as Space,b.NAME as IndexName from INNODB_SYS_TABLES a,INNODB_SYS_INDEXES b where a.SPACE =b.SPACE and a.SPACE=73 and b.INDEX_ID=89;
+----------+------------------+-------+-----------------+
| INDEX_ID | TableName        | Space | IndexName       |
+----------+------------------+-------+-----------------+
|       89 | dbtest/t_corrupt |    73 | GEN_CLUST_INDEX |
+----------+------------------+-------+-----------------+
1 row in set (0.00 sec)

根據上面的查詢結果，確定損壞的頁是屬於主鍵還是輔助索引，如果屬於主鍵索引，因為在 MySQL 中索引即資料，則可能會導致資料丟失，如果是輔助索引，刪除索引重建即可。

從上面可以查出dbtest庫下的t_corrupt這張表是主鍵索引存在問題，資料可能會有丟失。如果我們想要完整的資料，使用SELECT * FROM t1 就會發生如下錯誤：

mysql> select * from t_corrupt;
ERROR 2013 (HY000): Lost connection to MySQL server during query

嘗試先讀取部分資料，看看會不會報錯，我們可以採用二分查詢判斷資料頁損壞的位置。

select * from t_corrupt limit 100;

用 mysqldump 或者 SELECT … INTO OUTFILE 把表資料匯出。

使用mysqldump匯出資料
mysqldump -uroot -proot dbtest t_corrupt --where=" true limit 100"  > t_corrupt.sql

或者使用SELECT … INTO OUTFILE 把表資料匯出
--要設定secure_file_priv引數，才能使用 into outfile 引數把表中資料匯出
mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| /tmp/              |
+--------------------+
1 row in set (0.00 sec)

select * 
INTO OUTFILE '/tmp/t_corrupt.sql'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM t_corrupt
limit 100;

匯出完成後，我們就需要把之前舊的資料表刪除掉

mysql> drop table t_corrupt;
Query OK, 0 rows affected (0.00 sec)

去掉 innodb_force_recovery 或者設定為 0，重新啟動生產資料庫。

[mysqld]
innodb_force_recovery=0  #配置成0
然後重新建立表，把資料匯入。
CREATE TABLE t_corrupt (id int(11));

mysql -uroot -proot dbtest < t_corrupt.sql

或者
LOAD DATA LOCAL INFILE '/tmp/t_corrupt.sql' 
INTO TABLE t_corrupt
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

檢查恢復後的資料

mysql> select * from t_corrupt;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
...........
|   98 |
|   99 |
|  100 |
+------+
100 rows in set (0.00 sec)

最後說明，這個方法僅僅是緊急情況下的一種補救，不能依賴於這個辦法。對於DBA來說，日常要做好資料備份工作，包括全備份和日誌備份。及時備份是非常有必要的措施，同時我們還需要定時驗證備份檔案的有效性，保證備份檔案可以正常使用。確定要使用該方案是要確保有原始損壞資料的備份，謹慎在生產環境使用。

關於oracle，如何強制開庫，可以參考oracle無法open，教你用“六脈神劍”來開庫這篇文章。

相關文章