在我們MySQL數據庫維護工作中極力要避免的突發狀況里面,表數據損壞無疑是一種非常糟糕的情況。
然而在實際情況中,有時候這些突發狀況可能因為各種各樣的因素還是殘酷地需要我們直面處理。當這些突發狀況發生時,也不要太過沮喪,如果我們能夠冷靜專業的面對,也許轉機就在前方。
#/dev/null > innodb_index_stats.frm
#ll innodb_index_stats.frm
-rw-r----- 1 mysql mysql 0 Mar 24 08:41 innodb_index_stats.frm
2. 重啟動數據庫,無法正常啟動
# sh shutdown.sh
Enter password:
# sh startup.sh
#sh login.sh
Enter password:
ERROR 2002 (HY000): Cant connect to local MySQL server through socket /data/mysql/db_order/mysql.sock (2)
3. 出現錯誤日志
2021-03-24T08:43:05.378517-05:00 0 [ERROR] InnoDB: The size of tablespace file ./mysql/innodb_index_stats.ibd is only 49674, should be at least 65536!
2021-03-24 08:43:05 0x7fc7430a4740 InnoDB: Assertion failure in thread 140493799966528 in file fil0fil.cc line 793
InnoDB: We intentionally generate a memory trap.
4. 修改參數文件,添加innodb_force_recovery = 6,強制啟動數據庫,存在丟失少量數據風險
# sh startup.sh
#sh login.sh #此時,正常啟動
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.7.25-log Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type help; or h for help. Type c to clear the current input statement.
5. 修改參數文件,還原innodb_force_recovery = 0,再重新啟動
#sh shutdown.sh
#sh startup.sh
mysql> use mysql;
6. 刪除損壞表
mysql> drop table innodb_index_stats;
ERROR 1051 (42S02): Unknown table mysql.innodb_index_stats
mysql> drop table if exists innodb_index_stats;
Query OK, 0 rows affected, 1 warning (1.07 sec)
# rm innodb_index_stats.frm
# rm innodb_index_stats.ibd
7. 進入系統目錄,查看創建系統表腳本mysql_system_tables.sql
# cd /usr/local/mysql/share/
# ls *.sql
fill_help_tables.sql install_rewriter.sql mysql_sys_schema.sql mysql_system_tables.sql uninstall_rewriter.sql
innodb_memcached_config.sql mysql_security_commands.sql mysql_system_tables_data.sql mysql_test_data_timezone.sql
# cat *.sql|grep innodb_index_stats
SET @create_innodb_index_stats="CREATE TABLE IF NOT EXISTS innodb_index_stats (
SET @str=IF(@have_innodb <> 0, @create_innodb_index_stats, "SET @dummy = 0");
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with –A
mysql> select count(*) from innodb_index_stats;
+----------+
| count(*) |
+----------+
| 13 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from tt;
+----------+
| count(*) |
+----------+
| 407 | #損壞前數據筆數407
+----------+
1 row in set (0.00 sec)
#cd ./data/htest
#vi tt.ibd #打開刪除任一字符
#sh shutdown.sh
#sh startup.sh
3. 無法登錄數據庫,log出現錯誤
#sh login.sh
Enter password:
ERROR 2002 (HY000): Cant connect to local MySQL server
through socket /data/mysql/db_order/mysql.sock (2)
#cat mysql.err|more
2021-03-25T08:34:42.683624-05:00 0 [ERROR] InnoDB: Database
page corruption on disk or a failed file read of page [page
id: space=124, page number=5]. You may have to recover from
a backup.
…
# mysqldump -uroot -psystem -S
/data/mysql/db_order/mysql.sock --single-transaction --
default-character-set=utf8 --set-gtid-purged=off --add-drop-
table --triggers --events --routines htest tt>tt.sql
mysqldump: [Warning] Using a password on the command line
interface can be insecure.
mysqldump: Got error: 2002: Cant connect to local MySQL
server through socket /data/mysql/db_order/mysql.sock (2)
when trying to connect
#sh login.sh
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.7.25-log Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type help; or h for help. Type c to clear the current input statement.
mysql> use htest;
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> desc tt;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| TABLE_SCHEMA | varchar(64) | YES | | NULL | |
| TABLE_NAME | varchar(64) | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#mysqldump -uroot -psystem -S /data/mysql/db_order/mysql.sock --single-transaction --default-character-set=utf8 --set-gtid-purged=off --add-drop-table --triggers --events --routines htest tt>tt.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `tt` at row: 339
[mysql@mysqltest1 bin]$ ll
total 32
-rw-r--r-- 1 mysql mysql 19304 Mar 25 08:51 tt.sql
7. 刪除損壞表
mysql> drop table tt;
Query OK, 0 rows affected (1.39 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> source tt.sql;
Query OK, 0 rows affected (0.00 sec)
...
Query OK, 339 rows affected (0.08 sec)
Records: 339 Duplicates: 0 Warnings: 0
mysql> select count(*) from tt;
+----------+
| count(*) |
+----------+
| 339 | #相較407筆減少68筆數據,存在少量數據丟失風險
+----------+
1 row in set (0.00 sec)
需要說明的是:
innodb_force_recovery = 4代表相對比較安全,只有一些在損壞的多帶帶頁面上的數據會丟失。
如果是innodb_force_recovery =6 ,數據庫頁將被留在一個陳舊的狀態,這個狀態反過來可以引發對B 樹和其它數據庫結構的更多破壞。
文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。
轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/129369.html
摘要:問題原因非正常關機導致沒有把數據及時的寫入硬盤。丟失的臨時表臨時表和基于語句的復制方式不相容。如果備庫崩潰或者正常關閉,任何復制線程擁有的臨時表都會丟失。臨時表的特性只對創建臨時表的連接可見。 主備復制過程中有很大可能會出現各種問題,接下來我們就討論一些比較普遍的問題,以及當遇到這些問題時,如何解決或者預防問題發生。 1 數據損壞或丟失 問題描述:服務器崩潰、斷電、磁盤損壞、內存或網絡...
摘要:問題原因非正常關機導致沒有把數據及時的寫入硬盤。丟失的臨時表臨時表和基于語句的復制方式不相容。如果備庫崩潰或者正常關閉,任何復制線程擁有的臨時表都會丟失。臨時表的特性只對創建臨時表的連接可見。 主備復制過程中有很大可能會出現各種問題,接下來我們就討論一些比較普遍的問題,以及當遇到這些問題時,如何解決或者預防問題發生。 1 數據損壞或丟失 問題描述:服務器崩潰、斷電、磁盤損壞、內存或網絡...
閱讀 1347·2023-01-11 13:20
閱讀 1685·2023-01-11 13:20
閱讀 1133·2023-01-11 13:20
閱讀 1860·2023-01-11 13:20
閱讀 4101·2023-01-11 13:20
閱讀 2705·2023-01-11 13:20
閱讀 1386·2023-01-11 13:20
閱讀 3598·2023-01-11 13:20