Many junior Linux administrators wonder why we don't simply use cp to move MySQL tables between databases. At first glance, copying .frm, .ibd, and .MYD files seems like a straightforward solution:

# Dangerous approach!
cp /var/lib/mysql/db1/mytable.* /var/lib/mysql/db2/
chown -R mysql:mysql /var/lib/mysql/db2

The MySQL storage engine architecture makes direct file copying unreliable:

• InnoDB: Tablespace files contain internal pointers to the system tablespace
• MyISAM: While more file-based, the index files require proper synchronization
• Metadata: The .frm files alone don't contain all schema information

For reliable table transfers between databases, consider these approaches:

1. Using mysqldump (Most Reliable)

mysqldump -u root -p db1 mytable > mytable_dump.sql
mysql -u root -p db2 < mytable_dump.sql

2. CREATE TABLE ... SELECT (For Simple Cases)

CREATE TABLE db2.mytable LIKE db1.mytable;
INSERT INTO db2.mytable SELECT * FROM db1.mytable;

3. Transportable Tablespaces (InnoDB Specific)

-- On source:
FLUSH TABLES mytable FOR EXPORT;
-- Now you can safely copy the .ibd file
-- On target:
ALTER TABLE mytable IMPORT TABLESPACE;

In very specific circumstances with MyISAM tables when the MySQL server is completely stopped, you might get away with:

service mysql stop
cp /var/lib/mysql/db1/mytable.{frm,MYD,MYI} /var/lib/mysql/db2/
chown mysql:mysql /var/lib/mysql/db2/mytable.*
service mysql start

But even then, you risk corruption if any buffers weren't properly flushed.

While cp seems faster, proper methods offer advantages:

While the cp command might seem like a quick solution for transferring MySQL tables between databases, this approach comes with significant risks:

# Dangerous approach (not recommended):
cp /var/lib/mysql/db1/mytable.frm /var/lib/mysql/db2/mytable.frm
cp /var/lib/mysql/db1/mytable.ibd /var/lib/mysql/db2/mytable.ibd

MySQL maintains internal data dictionaries and caches that won't be updated by manual file operations. The proper methods ensure:

• Consistent table definitions in the data dictionary
• Proper synchronization with InnoDB's transaction logs (if using InnoDB)
• Correct permission handling

For MyISAM tables (temporary workaround):

# Only if you MUST use file operations
FLUSH TABLES db1.mytable WITH READ LOCK;
cp /var/lib/mysql/db1/mytable.{frm,MYD,MYI} /var/lib/mysql/db2/
UNLOCK TABLES;

For InnoDB (proper method):

# Using mysqldump (most reliable)
mysqldump -u username -p db1 mytable > mytable_dump.sql
mysql -u username -p db2 < mytable_dump.sql

# Using CREATE TABLE ... SELECT
CREATE TABLE db2.mytable LIKE db1.mytable;
INSERT INTO db2.mytable SELECT * FROM db1.mytable;

In very specific cases with MyISAM tables, you might get away with manual copying if:

1. The MySQL server is completely stopped
2. You copy ALL related files (.frm, .MYD, .MYI)
3. You verify file permissions match existing tables
4. You run REPAIR TABLE afterward

For tables over 50GB, consider this efficient approach:

# On source server:
mysqldump -u username -p --single-transaction \
--quick db1 bigtable | gzip > bigtable.sql.gz

# On destination server:
gunzip < bigtable.sql.gz | mysql -u username -p db2

# For InnoDB tablespaces (MySQL 5.6+):
ALTER TABLE db1.bigtable DISCARD TABLESPACE;
# Copy .ibd file
ALTER TABLE db1.bigtable IMPORT TABLESPACE;

Remember that manual file operations should only be considered when standard methods won't work, and always with proper backups.