mysql应对磁盘损坏_MySQL 磁盘空间满导致表空间相关数据文件损坏故障处理

近期，遇到一个mysql数据库因为磁盘空间满导致表空间相关数据文件损坏而无法启动，相关处理过程如下：

1、mysql数据库磁盘空间满报错提示

2019-09-30T01:25:10.326653+08:00 22822601 [Note] Aborted connection 22822601 to db: 'unconnected' user: 'bpuser' host: 'localhost' (Got an error reading communication packets)

2019-09-30T01:25:12.893926+08:00 21573334 [ERROR]

Disk is full writing '/data/mysql/mydata/blog/mysql-bin.002059' (Errcode: 15425568 - No space left on device). Waiting for someone to free space...

2019-09-30T01:25:12.893974+08:00 21573334 [ERROR] Retry in 60 secs. Message reprinted in 600 secs

2019-09-30T01:25:27.822078+08:00 0 [ERROR] [FATAL] InnoDB: Native Linux AIO interface. io_submit() call failed when resubmitting a partial I/O request on the file ./mydata/history_uint2019-09-30T01:25:28.811224+08:00 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled

2、mysql重启失败报错提示

2019-09-30T01:25:35.462390+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1947029007360

2019-09-30T01:25:35.683704+08:00 0 [ERROR] InnoDB:

Space ID in fsp header is 4275156, but in the page header it is 164.

2019-09-30T01:25:35.683774+08:00 0 [ERROR] InnoDB: Header page contains inconsistent data in datafile: ./mydata/history_uint#P#p201910130000.ibd, Space ID:18446744073709551615, Flags: 17039360. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2019-09-30T01:25:35.683865+08:00 0 [Note] InnoDB: Page size:1024. Pages to analyze:64

......

2019-09-30T01:25:35.689292+08:00 0 [Note] InnoDB: Chosen space:164

2019-09-30T01:25:35.689327+08:00 0 [ERROR] InnoDB: Corrupted page [page id: space=164, page number=0] of datafile './mydata/his_uint#P#p201910130000.ibd' could not be found in the doublewrite buffer.

......

2019-09-30T01:25:50.175443+08:00 0 [Note] InnoDB: Starting crash recovery.

2019-09-30T01:25:50.462341+08:00 0 [ERROR] InnoDB: Tablespace 431 was not found at ./mydata/his_uint#P#p201910130000.ibd.

2019-09-30T01:25:50.462391+08:00 0 [ERROR] InnoDB:

Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.

2019-09-30T01:25:50.481016+08:00 0 [ERROR] InnoDB: Cannot continue operation.

2019-09-30T01:28:44.666477Z mysqld_safe Logging to '/data/mysql/mydata/errlog/mysql.err'.

2019-09-30T01:28:44.736202Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/mydata/data

3、安装mysql报错日志提示

在参数文件中加入：innodb_force_recovery=1 重新启动数据库，注意此参数是让数据库引擎忽略崩溃的事务或者数据块，会丢失数据。

2019-09-30T14:59:29.161408+08:00 0 [Note] InnoDB: At LSN: 1947033396675: unable to open file ./mydata/history_uint#P#p201907310000.ibd for tablespace 357

2019-09-30T14:59:29.173311+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1947034250240

2019-09-30T14:59:29.391928+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1947039493120

2019-09-30T14:59:29.619846+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1947044736000

2019-09-30T14:59:29.883131+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1947049978880

2019-09-30T14:59:30.150950+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1947055221760

2019-09-30T14:59:30.412127+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1947060464640

2019-09-30T14:59:30.682865+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1947065707520

2019-09-30T14:59:30.946472+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1947070950400

2019-09-30T14:59:31.208800+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence nu