Home / server / Questions / 479135
In Process
Boolean
Asked: 2013-02-16 00:35:40 +0800 CST

Mysql: Archiving innodb tables

  • 772

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?

mysql

3 Answers

  1. 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
    • 4

  2. 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.

    • 2

  3. 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;
    • 0