一、問題描述

某次一線反應，某主庫表全部丟失，查看為空，登陸主機查看mysqld.log后報錯：Operating system error number 2 in a file operation數據目錄OS重裝后修改過，但只是指向方式不同，目錄還是同一目錄，另懷疑被入侵遭刪除，但也沒發現相關異常；

關聯資源：superuser、mariadb-kb、mysql5.7

二、分析如下

1、手動執行mysql_safe啟動，查看日志報錯如下：

2024-07-08T06:33:43.608829Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-07-08T06:33:43.608961Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2024-07-08T06:33:43.609008Z 0 [Note] ./bin/mysqld-debug (mysqld 5.7.44-debug-log) starting as process 2575072 ...
2024-07-08T06:33:43.616778Z 0 [Note] InnoDB: PUNCH HOLE support available
2024-07-08T06:33:43.616839Z 0 [Note] InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!!
2024-07-08T06:33:43.616850Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2024-07-08T06:33:43.616857Z 0 [Note] InnoDB: Uses event mutexes
2024-07-08T06:33:43.616865Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2024-07-08T06:33:43.616873Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.13
2024-07-08T06:33:43.616880Z 0 [Note] InnoDB: Using Linux native AIO
2024-07-08T06:33:43.619317Z 0 [Note] InnoDB: Number of pools: 1
2024-07-08T06:33:43.619521Z 0 [Note] InnoDB: Using CPU crc32 instructions
2024-07-08T06:33:43.621668Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2024-07-08T06:33:43.683417Z 0 [Note] InnoDB: Completed initialization of buffer pool
2024-07-08T06:33:43.686321Z 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2024-07-08T06:33:43.700000Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2024-07-08T06:33:43.766525Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2024-07-08T06:33:43.766588Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2024-07-08T06:33:43.766599Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not creat
e them.
2024-07-08T06:33:43.766608Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/engine_cost.ibd' OS error: 71
2024-07-08T06:33:43.766639Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2024-07-08T06:33:43.766648Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2024-07-08T06:33:43.766655Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not creat
e them.
2024-07-08T06:33:43.766665Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `mysql/engine_cost`. Please refer to http://dev.mysql.com/do
c/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2024-07-08T06:33:43.766675Z 0 [Warning] InnoDB: Ignoring tablespace `mysql/engine_cost` because it could not be opened.
2024-07-08T06:33:43.766785Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2024-07-08T06:33:43.766803Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2024-07-08T06:33:43.766811Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not creat
e them.
2024-07-08T06:33:43.766819Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/gtid_executed.ibd' OS error: 71
2024-07-08T06:33:43.766827Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
…………
mysqld: Table 'mysql.plugin' doesn't exist
2024-07-08T06:34:15.781770Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2024-07-08T06:34:15.781880Z 0 [ERROR] Too many arguments (first extra is 'start').
2024-07-08T06:34:15.781889Z 0 [Note] Use --verbose --help to get a list of available options!
2024-07-08T06:34:15.781893Z 0 [ERROR] Aborting

如上所示，找不到innodb 數據字典文件*.ibd的，關于此類報錯，官網解釋如下：

With innodb_file_per_table enabled (the default), the following messages may appear at startup if a file-per-table tablespace file (.ibd file) is missing:

[ERROR] InnoDB: Operating system error number 2 in a file operation.
[ERROR] InnoDB: The error means the system cannot find the path specified.
[ERROR] InnoDB: Cannot open datafile for read-only: './test/t1.ibd' OS error: 71
[Warning] InnoDB: Ignoring tablespace `test/t1` because it could not be opened.

另外，ibdata1文件（660）作為記錄InnoDB的共有表空間；數據庫突然崩潰導致的該文件損壞也會導致如上報錯；對應的獨享表空間[mysqld]增加innodb_file_per_table；

2、強制innodb恢復，用以備份表

[mysqld]
innodb_force_recovery = 1  #值從0-6，默認0，安全值建議為1即可，不大于3，

3、mysql普通用戶啟動，參見Run MySQL as a Normal User

4、恢復

因現場環境被別人重裝，損壞的數據刪除，未能進一步排查恢復；但可參考如下：

1、備份原有的數據文件data為data_old
2、重啟數據庫，重新初始化重建，mysql表空間，
3、恢復源數據庫數據目錄到新的data下面，重啟數據庫驗證

5、附錄：
另外如果是多主環境，可配置如下，避免多主寫入，帶有自增列的記錄導致自增列沖突的問題

auto_increment_offset = 2  #自增列偏移量，默認為1，即起始值
auto_increment_increment = 2  #自增量
#另Innodb參數
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data/
innodb_log_arch_dir = /usr/local/mysql/data/
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 512M