Failure Analysis | What to do after the database tablespace is rm

Author: Xiao Asia

A member of the Aikesheng DBA team, responsible for database failure and platform problem solving in the project, and a special liking for database high availability and distributed technology.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

Background introduction

Due to the misoperation of the operation and maintenance personnel in the customer's production environment, a certain business table ibd file in the MySQL database was dropped to rm. There is no slave library available for this environment due to historical reasons. In response to this situation, how do we deal with it?

Scenario recurrence

In a single instance, after deleting the data file rm of table sbtest1, observe the running status of the database [Do not restart mysql before file recovery].

Prerequisites

Prepare database and test table data

• Create a MySQL-5.7.28 single-instance database, and use sysbench to prepare a 100w test table.

[root@localhost ~]# sysbench --mysql-host=10.186.65.84 --mysql-port=8484 --mysql-user=xiao --mysql-password=xiao --mysql-db=test --db-driver=mysql   --mysql_storage_engine=innodb --db-ps-mode=disable  --test=/usr/local/sysbench1.0/share/sysbench/oltp_insert.lua --table_size=1000000 --threads=1 --tables=1 --db-ps-mode=disable --percentile=95 --report-interval=1 --time=1 prepare
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
[root@localhost ~]#

• View the data in the database:

[root@localhost ~]# mysql -uroot -p -S /data/mysql/data/8484/mysqld.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.28-log MySQL Community Server (GPL)
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 test;
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 count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.21 sec)

• View the ibd file of the sbtest1 table:

[root@localhost test]# ll -h /data/mysql/data/8484/test/
total 241M
-rw-r----- 1 actiontech-mysql actiontech-mysql  60   Jun  7 07:55 db.opt
-rw-r----- 1 actiontech-mysql actiontech-mysql  8.5K  Jun  7 07:57 sbtest1.frm
-rw-r----- 1 actiontech-mysql actiontech-mysql  240M Jun  7 07:57 sbtest1.ibd

Carry out the test

Perform a read and write test on the database through sysbench for 5 minutes, during which rm operation is performed on the sbtest1.ibd file to observe the running status of the database:

down

[root@localhost ~]# sysbench --mysql-host=10.186.65.84 --mysql-port=8484 --mysql-user=xiao --mysql-password=xiao --mysql-db=test --db-driver=mysql   --mysql_storage_engine=innodb --db-ps-mode=disable  --test=/usr/local/sysbench1.0/share/sysbench/oltp_read_write.lua --table_size=1000000 --threads=1 --tables=1 --db-ps-mode=disable --percentile=95 --report-interval=20 --time=300 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Report intermediate results every 20 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 20s ] thds: 1 tps: 79.34 qps: 1587.51 (r/w/o: 1111.43/317.35/158.73) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 1 tps: 80.35 qps: 1607.27 (r/w/o: 1124.95/321.61/160.71) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 1 tps: 80.35 qps: 1606.98 (r/w/o: 1124.92/321.36/160.70) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 1 tps: 77.90 qps: 1557.65 (r/w/o: 1090.40/311.45/155.80) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 1 tps: 79.75 qps: 1595.13 (r/w/o: 1116.64/319.00/159.50) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 1 tps: 80.90 qps: 1617.36 (r/w/o: 1131.96/323.60/161.80) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 1 tps: 81.40 qps: 1628.40 (r/w/o: 1140.00/325.60/162.80) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 1 tps: 80.80 qps: 1615.68 (r/w/o: 1130.89/323.20/161.60) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00

delete

[root@localhost test]# ll -h /data/mysql/data/8484/test/
total 241M
-rw-r----- 1 actiontech-mysql actiontech-mysql   60 Jun  7 07:55 db.opt
-rw-r----- 1 actiontech-mysql actiontech-mysql 8.5K Jun  7 07:57 sbtest1.frm
-rw-r----- 1 actiontech-mysql actiontech-mysql 240M Jun  7 07:57 sbtest1.ibd
[root@localhost test]# 
[root@localhost test]# 
[root@localhost test]# 
[root@localhost test]# rm -r sbtest1.ibd 
rm: remove regular file ‘sbtest1.ibd’? y
[root@localhost test]# ll -h /data/mysql/data/8484/test/
total 16K
-rw-r----- 1 actiontech-mysql actiontech-mysql   60 Jun  7 07:55 db.opt
-rw-r----- 1 actiontech-mysql actiontech-mysql 8.5K Jun  7 07:57 sbtest1.frm
[root@localhost test]#

View mysql-error.log and sysbench pressure test results

View sysbench results:

view mysql-error.log:

It can be seen that when the sbtest1.ibd file is removed by rm, the addition, deletion, and modification of the table can still be performed normally, and there is no error in the mysql-error.log.

The next test is to copy the deleted file from /proc/'pidof mysqld'/fd to the original location, and check that MySQL can be restarted normally.

File recovery

View the status of the

[root@localhost 8484]# lsof |grep sbtest1.ibd

The red box is FD: file descriptor, and the application program recognizes the file through the file descriptor.

[root@localhost 8484]# ls -l /proc/1933/fd/78
lrwx------ 1 actiontech-mysql actiontech-mysql 64 Jun  7 08:22 /proc/1933/fd/78 -> /data/mysql/data/8484/test/sbtest1.ibd (deleted)

execute the cp command to copy the file to the original data directory

Before proceeding to cp, make sure that the table data changes have been placed on the disk and there is no new operation.

[root@localhost 8484]# cp /proc/1933/fd/78 /data/mysql/data/8484/test/sbtest1.ibd

Modify file attributes

[root@localhost test]# chown actiontech-mysql:actiontech-mysql sbtest1.ibd
[root@localhost test]# ll -h
total 497M
-rw-r----- 1 actiontech-mysql actiontech-mysql   60 Jun  7 07:55 db.opt
-rw-r----- 1 actiontech-mysql actiontech-mysql 8.5K Jun  7 07:57 sbtest1.frm
-rw-r----- 1 actiontech-mysql actiontech-mysql 248M Jun  7 08:35 sbtest1.ibd

restart mysql

[root@localhost test]# systemctl restart mysqld_8484.service
[root@localhost test]#

View mysql-error.log:

verifies whether the file is available through

[root@localhost ~]# sysbench --mysql-host=10.186.65.84 --mysql-port=8484 --mysql-user=xiao --mysql-password=xiao --mysql-db=test --db-driver=mysql   --mysql_storage_engine=innodb --db-ps-mode=disable  --test=/usr/local/sysbench1.0/share/sysbench/oltp_read_write.lua --table_size=1000000 --threads=1 --tables=1 --db-ps-mode=disable --percentile=95 --report-interval=1 --time=30 run

You can see that after the ibd file is restored, it can be read and written normally.

What operations can let us know that the table ibd file does not exist?

1. Add/delete fields to the table---can find

mysql> alter table sbtest1 add column name varchar(15);
ERROR 1030 (HY000): Got error 44 from storage engine

At this time, if you perform a select operation on the table, you can still view the results:

mysql> select count(*) from sbtest1;
+-------------+
| count(*) |
+-------------+
| 1000000 |
+-------------+
1 row in set (0.20 sec)

View mysql-error.log information:

[ERROR] InnoDB: Cannot rename './test/sbtest1.ibd' to './test/#sql-ib134-2944391225.ibd' for space ID 118 because the source file does not exist
mysql> alter table sbtest1 drop column pad;
ERROR 1030 (HY000): Got error 44 from storage engine

At this time, perform a select operation on the table, and you can still view the results:

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.19 sec)

View mysql-error.log information:

[ERROR] InnoDB: Cannot rename './test/sbtest1.ibd' to './test/#sql-ib135-2944391227.ibd' for space ID 118 because the source file does not exist.

2. Add/delete indexes to the table ---

mysql> alter table sbtest1 add index idx_pad(pad);
Query OK, 0 rows affected (6.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `idx_pad` (`pad`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 
1 row in set (0.00 sec)
mysql> alter table sbtest1 drop index idx_pad;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

3. Perform OPTIMIZE operation on the table --- can find

mysql> OPTIMIZE table sbtest1;
+-----------------+------------+---------------+--------------------------------------------------------------------------------------+
| Table      | Op     | Msg_type | Msg_text                                             |
+-----------------+-------------+---------------+--------------------------------------------------------------------------------------+
| test.sbtest1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.sbtest1 | optimize | error     | Got error 44 from storage engine                           |
| test.sbtest1 | optimize | status    | Operation failed                                         |
+-----------------+-------------+----------+---------------------------------------------------------------------------------------------+
3 rows in set, 1 warning (12.30 sec)

View mysql-error.log:

[ERROR] InnoDB: Cannot rename './test/sbtest1.ibd' to './test/#sql-ib136-2944391229.ibd' for space ID 118 because the source file does not exist.

4. Perform ALTER TABLE tbl_name ENGINE=INNODB operation on the table --- can find

mysql> ALTER TABLE sbtest1 ENGINE=INNODB;
ERROR 1030 (HY000): Got error 44 from storage engine

View mysql-error.log:

[ERROR] InnoDB: Cannot rename './test/sbtest1.ibd' to './test/#sql-ib137-2944391231.ibd' for space ID 118 because the source file does not exist.

5. Make changes to the table field attributes --- can find

mysql> alter table sbtest1 modify column pad char(70);
ERROR 1025 (HY000): Error on rename of './test/sbtest1' to './test/#sql2-5bc4-7' (errno: 197 - Tablespace cannot be accessed)

View mysql-error.log:

[ERROR] InnoDB: Cannot rename './test/sbtest1.ibd' to './test/#sql2-5bc4-7.ibd' for space ID 118 because the source file does not exist.