Recover orphaned InnoDB partition tablespaces in MySQL

October 14, 2014

Author

Jervin Real

MySQL

Share this Post:

A few months back, Michael wrote about reconnecting orphaned *.ibd files using MySQL 5.6. I will show you the same procedure, this time for partitioned tables. An InnoDB partition is also a self-contained tablespace in itself so you can use the same method described in the previous post.

To begin with, I have an example table with a few orphaned partitions and we will reconnect each partition one by one to the original table.

1

mysql [localhost] {msandbox} (recovery) > SHOW CREATE TABLE t1 G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( [...] KEY `h_date` (`h_date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (year(h_date)) (PARTITION p0 VALUES LESS THAN (2006) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2010) ENGINE = InnoDB, PARTITION px VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (recovery) > SELECT COUNT(*) FROM t1; +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) -rw-rw----. 1 revin revin 176M Oct 8 08:41 t1#P#p0.ibd -rw-rw----. 1 revin revin 612M Oct 8 08:41 t1#P#p1.ibd -rw-rw----. 1 revin revin 932M Oct 8 08:42 t1#P#px.ibd

The first step is to create a dummy table and remove partitioning so that we can reattach individual partitions to this table.

1

mysql [localhost] {msandbox} (recovery) > CREATE TABLE t1_t LIKE t1; Query OK, 0 rows affected (0.02 sec) mysql [localhost] {msandbox} (recovery) > ALTER TABLE t1_t REMOVE PARTITIONING; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (recovery) > ALTER TABLE t1_t DISCARD TABLESPACE; Query OK, 0 rows affected (0.05 sec)

Once the tablespace for our dummy table has been discarded, we copy one of the partitions to take the place of the dummy table’s tablespace. For example, we copy t1#P#p0.ibd as t1_t.ibd into the MySQL data directory, of course taking into account the permissions afterward. The next step is to import the tablespace to the dummy table.

1	mysql [localhost] {msandbox} (recovery) > ALTER TABLE t1_t IMPORT TABLESPACE;<br>Query OK, 0 rows affected, 1 warning (7.34 sec)

And for the secret sauce, we will exchange our dummy table recently imported tablespace to replace the target partition in our original table.

1

mysql [localhost] {msandbox} (recovery) > ALTER TABLE t1 EXCHANGE PARTITION px WITH TABLE t1_t; Query OK, 0 rows affected (6.42 sec) mysql [localhost] {msandbox} (recovery) > SELECT COUNT(*) FROM t1; +----------+ | COUNT(*) | +----------+ | 8523686 | +----------+ 1 row in set (2.50 sec)

You can do the same with subpartitions, too! Here’s my slightly different table with subpartitions where I reconnect one of the orphaned tablespaces t2#P#px#SP#pxsp1.ibd .

1

mysql [localhost] {msandbox} (recovery) > SHOW CREATE TABLE t2 G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( [...] KEY `h_date` (`h_date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (year(h_date)) SUBPARTITION BY HASH (u_id) SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN (2006) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2010) ENGINE = InnoDB, PARTITION px VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (recovery) > SELECT COUNT(*) FROM t2; +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.94 sec) -rw-rw----. 1 revin revin 92M Oct 8 08:44 t2#P#p0#SP#p0sp0.ibd -rw-rw----. 1 revin revin 92M Oct 8 08:44 t2#P#p0#SP#p0sp1.ibd -rw-rw----. 1 revin revin 304M Oct 8 08:44 t2#P#p1#SP#p1sp0.ibd -rw-rw----. 1 revin revin 316M Oct 8 08:44 t2#P#p1#SP#p1sp1.ibd -rw-rw----. 1 revin revin 480M Oct 8 08:45 t2#P#px#SP#pxsp0.ibd -rw-rw----. 1 revin revin 460M Oct 8 08:45 t2#P#px#SP#pxsp1.ibd mysql [localhost] {msandbox} (recovery) > CREATE TABLE t2_t LIKE t2; Query OK, 0 rows affected (0.02 sec) mysql [localhost] {msandbox} (recovery) > ALTER TABLE t2_t REMOVE PARTITIONING; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (recovery) > ALTER TABLE t2_t DISCARD TABLESPACE; Query OK, 0 rows affected (0.04 sec)

Once again, after copying t2#P#px#SP#pxsp1.ibd to replace t2_t.ibd in the MySQL data directory and setting the correct permissions, we can import it into our dummy table and exchange the same to our original table. In this case, on a table with subpartitions, a tablespace is a combined unit of partition and subpartition, hence in our partition name above P#px is our main partition and SP#pxsp1 the subpartition name. For our exchange command below, we will only need the later portion.

1

mysql [localhost] {msandbox} (recovery) > ALTER TABLE t2_t IMPORT TABLESPACE; Query OK, 0 rows affected, 1 warning (2.49 sec) mysql [localhost] {msandbox} (recovery) > ALTER TABLE t2 EXCHANGE PARTITION pxsp1 WITH TABLE t2_t; Query OK, 0 rows affected (3.11 sec) mysql [localhost] {msandbox} (recovery) > SELECT COUNT(*) FROM t2; +----------+ | COUNT(*) | +----------+ | 4546036 | +----------+ 1 row in set (0.94 sec)

But wait there’s more: Do you know that in MySQL 5.7, you can take the full shortcut? Directly import all partitions back to the original table, sweet! A quick example below on MySQL 5.7.5, I created the same t2 table above, DISCARD TABLESPACE , copy the partition tablespaces from my test 5.6 instance and IMPORT TABLESPACE And done! 🙂

1

mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t2; +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > ALTER TABLE t2 DISCARD TABLESPACE; Query OK, 0 rows affected (0.02 sec) mysql [localhost] {msandbox} (test) > ! cp -v /sbx/msb/msb_5_6_210/data/test/t2#P#* /sbx/msb/msb_5_7_5/data/test/ `/sbx/msb/msb_5_6_210/data/test/t2#P#p0#SP#p0sp0.ibd' -> `/sbx/msb/msb_5_7_5/data/test/t2#P#p0#SP#p0sp0.ibd' `/sbx/msb/msb_5_6_210/data/test/t2#P#p0#SP#p0sp1.ibd' -> `/sbx/msb/msb_5_7_5/data/test/t2#P#p0#SP#p0sp1.ibd' `/sbx/msb/msb_5_6_210/data/test/t2#P#p1#SP#p1sp0.ibd' -> `/sbx/msb/msb_5_7_5/data/test/t2#P#p1#SP#p1sp0.ibd' `/sbx/msb/msb_5_6_210/data/test/t2#P#p1#SP#p1sp1.ibd' -> `/sbx/msb/msb_5_7_5/data/test/t2#P#p1#SP#p1sp1.ibd' `/sbx/msb/msb_5_6_210/data/test/t2#P#px#SP#pxsp0.ibd' -> `/sbx/msb/msb_5_7_5/data/test/t2#P#px#SP#pxsp0.ibd' `/sbx/msb/msb_5_6_210/data/test/t2#P#px#SP#pxsp1.ibd' -> `/sbx/msb/msb_5_7_5/data/test/t2#P#px#SP#pxsp1.ibd' mysql [localhost] {msandbox} (test) > ALTER TABLE t2 IMPORT TABLESPACE; Query OK, 0 rows affected, 6 warnings (11.36 sec) mysql [localhost] {msandbox} (test) > SHOW WARNINGS G *************************** 1. row *************************** Level: Warning Code: 1810 Message: InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t2#P#p0#SP#p0sp0.cfg', will attempt to import without schema verification [...]