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

# 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)

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.

# 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.

#sh shutdown.sh
#sh startup.sh
mysql> use mysql;

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

# 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

#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

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)