Linux/mysql:is it safe to copy MySQL DB files with cp command from one DB to another?

Copying is very simple for MyISAM and completely 100% risky (near suicidal) with InnoDB.

From your question, your brought up

cp /db1/mytable.frm /db2/mytable.frm

MyISAM

this is OK to do. However, you cannot just move the. frm. You must move all components. From your question, let's take a table called db1.mytable. In a normal installation, the table is located in/var/lib/mysql/db1. There would is three files making up the table.

• /var/lib/mysql /db1/mytable.frm
• /var/lib/mysql/db1/mytable. MYD (Table Database)
• /var/lib/mysql/db1/mytable . MYI (Table Indexes)

You must move all three file to move the one table. If all your tables with the MyISAM storage engine, you can shutdown MySQL and copy away. If you is simply making a copy of the table and placing it in another database, you should does that using SQL.

For example, if you want to copy db1.mytable to database DB2, does this:

create TABLE db2.mytable like db1.mytable; ALTER TABLE db2.mytable DISABLE keys;insert into db2.mytable SELECT * from db1.mytable; ALTER TABLE db2.mytable ENABLE KEYS;  

now If you just moving the table from DB1 to DB2, you can do this:

ALTER TABLE db1.mytable RENAME db2.mytable;

InnoDB

Copying is very dangerous because of the infrastructure, that InnoDB works under. There is both basic infrastructures:1) innodb_file_per_table disabled and 2) innodb_file_per_table enabled

The Achilles ' Heel of InnoDB is the system tablespace file known as ibdata1 (normally located in/var/lib/mysql). What's contained in the that file?

• Table Data Pages
• Table Index Pages
• Table MetaData (tablespace ID management list)
• MVCC Data (to-support Transaction isolation and ACID Compliance)

InnoDB (innodb_file_per_table Disabled)

with innodb_file_per_table disabled, all these types of InnoDB info live within IBDA Ta1. The only manifestation of any InnoDB table outside of Ibdata1 is the. frm file of the InnoDB table. Copying all InnoDB data @ once requires Copying all of/var/lib/mysql.

Copying An individual InnoDB table was total impossible. You must mysqldump to extract a dump of the table as a logical representation of the data and its corresponding index Defi Nitions. You would then load this dump to another database on the same server or another server.

InnoDB (innodb_file_per_table enabled)

With innodb_file_per_table-enabled, table data and its indexes live in the database folder next to the. frm file. For example, for the table db1.mytable, the manifestation of this InnoDB table outside of IBDATA1 would be:

• /var/lib/mysql/db1/mytable.frm
• /var/lib/mysql/db1/mytable.ibd

All of the metadata for db1.mytable still resides in Ibdata1 and there are absolutely noto around that. Redo logs and MVCC data also still live with ibdata1.

WARNING (or DANGER as the Robot would say in Lost in Space)

If you are thinking of just copying the. frm and. ibd file, your is in line for world of hurting. Copying the. frm and. ibd file of an InnoDB table are only good if you can guarantee that the Tablespace ID of the. IBD fil E matches exactly with the Tablespace ID entry in the metdata of the Ibdata1 file.

I wrote posts in the DBA stackexchange about this tablespace ID concept

• http:// dba.stackexchange.com/a/9555/877  (under the heading ' Restoring Databases ')
• http://dba.stackexchange.com/a/6269/877

Here are excellent link on what reattach and. ibd file to Ibdata1 in the event of mismatched tablespace ids:http://www. Chriscalender.com/?tag=innodb-error-tablespace-id-in-file. After reading this, you should is able to see what I said near suicidal.

For InnoDB-Need to this

create TABLE db2.mytable like Db1.mytable;insert to db2.mytable SELECT * from db1.mytable;  

To make a copy of a InnoDB table. If you is migrating it to another DB server, use Mysqldump.

Source: >  

From for notes (Wiz)

Linux/mysql:is it safe to copy MySQL DB files with cp command from one DB to another?