Linux / mysql: is it safe to copy mysql db files with cp command from one db to another?
Solution 1:
Copying is very simple for MyISAM and completely 100% risky (near suicidal) with InnoDB.
From your question, you 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 you question, let's take a table called db1.mytable. In a normal installation, the table is located in /var/lib/mysql/db1. There would be 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 use the MyISAM storage engine, you can shutdown mysql and copy away. If you are simply making a copy of the table and placing it in another database, you should do that using SQL.
For example, if you want to copy db1.mytable to database db2, do 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 are two 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 is contained in 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 ibdata1. The only manifestation of any InnoDB table outside of ibdata1 is the .frm file of the InnoDB table. Copying all InnoDB data at once requires copying all of /var/lib/mysql.
Copying an individual InnoDB table is total impossible. You must mysqldump to extract a dump of the table as a logical representation of the data and its corresponding index definitions. You would then load that 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 that InnoDB table outside of ibdata1 would be:
- /var/lib/mysql/db1/mytable.frm
- /var/lib/mysql/db1/mytable.ibd
All the metadata for db1.mytable still resides in ibdata1 and there is absolutely no way 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, you are in line for world of hurting. Copying the .frm and .ibd file of an InnoDB table is only good if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file.
I wrote two posts in DBA StackExchange about this tablespace id concept
- https://dba.stackexchange.com/a/9555/877 (under the heading 'Restoring Databases')
- https://dba.stackexchange.com/a/6269/877
Here is excellent link on how to 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 be able to see why I said near suicidal.
For InnoDB you only need to this
CREATE TABLE db2.mytable LIKE db1.mytable;
INSERT INTO db2.mytable SELECT * FROM db1.mytable;
to make a copy of an InnoDB table. If you are migrating it to another DB server, use mysqldump.
Solution 2:
Copying the entire MySQL datadir is a practical technique, presuming the MySQL service is stopped and you want the entire database server copied.
This is a useful technique for shifting databases with large indexes, and a mysql dump will not include the indexes, which will need to be regenerated at import time. I've found this technique useful when setting up MySQL slaves.
Copying an individual file will depends on the table schema in use, but in most situation not a suitable solution.
Solution 3:
Use xtrabackup w/ ow w/o innobackupex wrapper and you will be fine on both myisam and innodb databases. Note, that restoring innodb databases are not just copying back the files even if you use xtrabackup. Tell if you need more info
Solution 4:
No, you should backup with mysqdump and restore with mysql cli utility, copying the frm file you are copying only the table structure and not the data inside, and if you are on innodb copy the file directly is not possible .
The best way is dump and restore the table.