MySQL Copy Table

If we need a complete copy of MySQL data tables, including the table structure, indexes, default values, etc. If you only use the CREATE TABLE ... SELECT command, can not be achieved.

This chapter will show you how to complete copy MySQL data tables, the following steps:

• Use SHOW CREATE TABLE command to obtain data to create a table (CREATE TABLE) statement that contains the original data table structures, indexes, and so on.
• Copy the following command to display the SQL statements to modify the table name, and execute SQL statements, the above command will be fully replicated data table structure.
• If you want to copy the contents of the table, you can use the INSERT INTO ... SELECT statement to achieve.

Examples

Try the following examples to copy table w3big_tbl.

step one:

Get the complete structure of the data table.

mysql> SHOW CREATE TABLE w3big_tbl \G;
*************************** 1. row ***************************
       Table: w3big_tbl
Create Table: CREATE TABLE `w3big_tbl` (
  `w3big_id` int(11) NOT NULL auto_increment,
  `w3big_title` varchar(100) NOT NULL default '',
  `w3big_author` varchar(40) NOT NULL default '',
  `submission_date` date default NULL,
  PRIMARY KEY  (`w3big_id`),
  UNIQUE KEY `AUTHOR_INDEX` (`w3big_author`)
) ENGINE=InnoDB 
1 row in set (0.00 sec)

ERROR:
No query specified

Step two:

Modify the SQL statement data table name, and execute SQL statements.

mysql> CREATE TABLE `clone_tbl` (
  -> `w3big_id` int(11) NOT NULL auto_increment,
  -> `w3big_title` varchar(100) NOT NULL default '',
  -> `w3big_author` varchar(40) NOT NULL default '',
  -> `submission_date` date default NULL,
  -> PRIMARY KEY  (`w3big_id`),
  -> UNIQUE KEY `AUTHOR_INDEX` (`w3big_author`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (1.80 sec)

Step three:

After performing the second step, you will create a new clone clone_tbl table in the database. If you want to copy the data table you can use the INSERT INTO ... SELECT statement to achieve.

mysql> INSERT INTO clone_tbl (w3big_id,
    ->                        w3big_title,
    ->                        w3big_author,
    ->                        submission_date)
    -> SELECT w3big_id,w3big_title,
    ->        w3big_author,submission_date
    -> FROM w3big_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

After performing the above steps, you will complete copy tables, including the table structure and table data.