11

Most guides recommend mysqldump and simple SQL for copying one table to anoter db. How about linux shell cp? Can I just simply do

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

giorgio79
  • 1,747
  • 9
  • 25
  • 36

4 Answers4

21

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

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.

RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
  • 4
    Other responses said this, so perhaps it "goes without saying," but it's good to say anyway: InnoDB keeps writing to the files even if the database is idle, so copying its files while MySQL is running is almost certain to cause corruption! You can shut down, take a filesystem snapshot, or use Percona XtraBackup to work around this. –  Mar 08 '12 at 12:25
  • 1
    Excellent answer as always, @Rolando! The only big question I have: Why would someone want to do this as opposed to doing a straight MySQL dump & import? I am assuming sheer database size, but feel that should be brought up. – Giacomo1968 Apr 12 '14 at 15:02
  • 1
    @JakeGould Loading a mysqldump script into mysql requires processing lots of SQL, inserting thousands of rows at a time, using CPUs cycles, cranking out explain plans, rebuilding indexes (BTree Insertions, Leaf Node Splits, rebalancing Keys, a table scan for each non-unique to build) just to produce the same table. In the case of MyISAM, why reinvent the wheel? Just copy of the `.frm`, `.MYD` and `.MYI`. – RolandoMySQLDBA Apr 12 '14 at 19:16
  • @JakeGould In the case of InnoDB, I had copied a live database that was using all InnoDB with rsync. After copying using rsync, I ran a shutdown of mysql, did a final rsync, and restarted mysql without problems. I wrote a post like before : http://serverfault.com/questions/288140/mysql-slave-replication-reset-with-no-master-downtime-using-myisam/288467#288467 – RolandoMySQLDBA Apr 12 '14 at 19:19
8

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.

Coops
  • 5,967
  • 1
  • 31
  • 52
  • 2
    To expound: you don't have to stop the service per se, if your filesystem is capable, you can either perform an LVM snapshot and back up off of that; or freeze the filesystem itself. – thinice Mar 07 '12 at 18:14
  • As long as a personal can eat downtime, this is the easiest way. +1 !!! – RolandoMySQLDBA Mar 08 '12 at 16:17
2

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

pQd
  • 29,561
  • 5
  • 64
  • 106
Gabor Vincze
  • 554
  • 1
  • 4
  • 11
1

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.

aleroot
  • 3,160
  • 5
  • 28
  • 37