Mysql – Recover Mysql data

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.

If your slave does not also act as a master to another slave, then you should not have any issues from deleting binary logs. The relay logs are important to the slave.

You need to further investigate on why mysql schema disappeared. Is mysql schema still present on the disk, even though MySQL is not showing it? If you have not stopped the slave instance yet, then you could run under same user as MySQL is running:

lsof | grep '/path_to_mysql'

You might see mysql schema tables marked as deleted in there:

(deleted)

Another possibility is that you are connecting with a user that has limited privileges and just does not see mysql schema. Run SHOW GRANTS; to see what privileges you currently have.

As Rolando pointed out, use PURGE BINARY LOGS as best practice for cleaning up binary logs. If MySQL is down, you could delete the files manually, but then you have to also delete the same file names from the index file. Be careful on master servers, as binary logs might still be needed by slaves.

And if you do need to copy mysql schema, you can do it with just these steps on the slave. Although, I would recommend executing FLUSH TABLES; on master before doing these steps.

/etc/init.d/mysql stop
scp -rp master_server:/var/lib/mysql/mysql /var/lib/mysql/
/etc/init.d/mysql start