MariaDB / MariaBackup : the best way to export/migrate a 700GB table to another server, but with only 200GB of free space at the origin

The biggest thing most people forget about TRUNCATE TABLE is that TRUNCATE TABLE is DDL and not DML. In InnoDB, the metadata within ibdata1 contains a numbered list of InnoDB tables. Using TRUNCATE TABLE causes the internal metadata id of the InnoDB table to shift. This happens because TRUNCATE TABLE effectively does the following:

Example: To truncate an InnoDB Table called mydb.mytb

USE mydb
CREATE TABLE newtb LIKE mytb;
ALTER TABLE mytb RENAME oldtb;
ALTER TABLE newtb RENAME mytb;
DROP TABLE oldtb;

The new mytb would thus have a different internal metadata id.

When you copied the .ibd file to some other place, the .ibd contains within it the original internal metadata id. Simply putting the .ibd file back does not cause a reconciliation of the internal metadata id with that of the one in ibdata1.

What you should have done is this:

Copy the .ibd file of the InnoDB table. Then, run this

ALTER TABLE tablename DISCARD TABLESPACE;

To bring it back later on, copy the .ibd file back into datadir and then run

ALTER TABLE tablename IMPORT TABLESPACE;

This would have preserved the internal metadata id.

Make sure .frm is always present.

I once helped a client restore 30 InnoDB tables he hosed in the same manner. I had to use another DB server and play some games with adding and dropping InnoDB tables to hunt down the correct internal metadata id.

The client found this article : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file . We used it and it helped a great deal. I hope it helps you.

Empty frm files won't work because they have a specific syntax which is being checked by mysql.

[root@server test]# touch test_one.frm
[root@server test]# chown mysql test_one.frm
[root@server test]# mysql test

Although it is showing up in show table because it only checks for the existence of the file ...

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
....
| test_one       |
...
+----------------+

... any operations on it will fail.

mysql> show create table test_one;
ERROR 1033 (HY000): Incorrect information in file: './test/test_one.frm'

mysql> drop table test_one;
ERROR 1051 (42S02): Unknown table 'test.test_one'

The documentation says:

Create a matching .frm file in some other database directory and copy it to the database directory where the orphan table is located.

Now, matching (same structure) is actually not mandatory if you only want to drop it but you need to have a valid frm file.

If you do this you should be able to drop the table afterwards:

mysql> create table test_recoverer (id int unsigned primary key);
Query OK, 0 rows affected (0.31 sec)

[root@server test]# cat test_recoverer.frm > test_one.frm

mysql> drop table test_one;
Query OK, 0 rows affected (0.11 sec)