Copy MySQL table using backup

Question

I want to copy the contents of a hot table to another table on the same database. I don't want to rename the original or delete it.

One option is to create a new table LIKE the current one and then INSERT INTO... SELECT.

However, I think it might be fastest to create a backup of my table using mysqlhotcopy and then load this into the second table. I'm not sure how I can accomplish the second part of this - loading into an arbitrary table Foo from a backup. I want to do this using PHP.

Any ideas?

Answer 1

I was going to suggest using php myadmin and click on the operations tab. There is a table copy procedure there. If this is going to be an ongoing thing, creating a new table and inserting the contents should do.

Answer 2

This is an Overview:

Your answer really depends on how big your table is and how much read-lock time you can stand. A lot of records can be copied in 2 seconds.

No matter which way you cut it, you are going to need a read lock of some sort on the table while it is being read (to get a consistent read anyway). InnoDB transactions may provide a way to get a consistent snapshot without holding up other writers. MyISAM or InnoDB will have very different backup strategies.

With MyISAM

A very fast way to do what you are referring to is:

CREATE TABLE x_backup LIKE x;
ALTER TABLE x_backup DISABLE KEYS;
INSERT INTO x_backup SELECT * from x;
ALTER TABLE x_backup ENABLE KEYS;

A real example:

8.68 seconds for 333,935 decently sized records.

           Name: sds_profile
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 333935
 Avg_row_length: 129
    Data_length: 43114720
   Index_length: 11572224


mysql> create table x LIKE sds_profile;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table x disable keys;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into x select * from sds_profile;
Query OK, 333935 rows affected (8.68 sec)
Records: 333935  Duplicates: 0  Warnings: 0

mysql> alter table x enable keys;
Query OK, 0 rows affected (0.78 sec)

With InnoDB

Consider doing similar, but in a transaction with the correct isolation level.

hotcopy

If you are doing this from PHP, it would be good to do it via the normal MySQL authentication mechanisms. Otherwise, you need to get direct access to the data dir, with read privs on the mysql files. This can open up further security issues that you may not want to contend with.

Answer 3

I would use SELECT...INTO OUTFILE and LOAD DATA INFILE to accomplish this.

You have a lot of flexibility and it will perform much faster.