Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I use innodb_file_per_table configuration parameter. I want to archive some old tables. If I shutdown the database and move the ibd and frm to archive directory, innodb shows warning that table.ibd is not available. Is there a way to move a table away from database?

share|improve this question
2  
Don't forget to ask how to restore the tables you have backed up. Different solutions have different restore procedures and not all restore procedures are equal. Also, test and document the procedure yourself before relying on it. –  Ladadadada Feb 15 '13 at 10:00

3 Answers 3

Are you familiar with mysqldump? It's a tool that creates a file full of SQL statements that will recreate the tables you asked it to dump and import the data they contained. It's a part of the standard MySQL install.

You can use it like this:

mysqldump --defaults-extra-file=/home/username/.my.cnf database table > table.sql

By default the dump includes CREATE TABLE statements.

You can restore like this:

mysql database < table.sql
share|improve this answer

Do not try to do this kind of thing at the filesystem level, especially not with innodb tables. That's a sure road to disappointment. Do as Ladadadada suggests and dump the table, save the dump file as your archive and then drop the table. That way it is also easily restorable, not just to the original server but to any server.

share|improve this answer

What you can do is to rename your table using below command

In the MySQL client:

SHOW CREATE TABLE table1\G

RENAME TABLE table1 TO table2;

CREATE table table1 <table spec>; 

Where <table spec> is the result of the SHOW CREATE TABLE command.

Then in your shell:

cp /path/to/datadir/table2/*.{ibd,frm} /path/to/archivedir/

Back in the MySQL client:

DROP TABLE table2;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.