MySQL Copy Table

If we need to completely copy a MySQL data table, including the table structure, indexes, default values, etc.

If only usingCREATE TABLE ... SELECTthe command, it cannot be achieved.

This chapter will introduce how to completely copy a MySQL data table. The steps are as follows:

• UseSHOW CREATE TABLEcommand to get the CREATE TABLE (CREATE TABLE) statement, which contains the original table's structure, indexes, etc.
• Copy the SQL statement shown by the following command, modify the data table name, and execute the SQL statement. Through the above command, you will completely copy the data table structure.
• If you want to copy the contents of the table, you can use INSERT INTO ... SELECT statement to achieve it.

Example

Try the following example to copy the table example_tbl.

Step 1:

Get the complete structure of the data table.

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

ERROR:
No query specified

Step 2:

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

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

Step 3:

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

mysql> INSERT INTO clone_tbl (example_id,
    ->                        example_title,
    ->                        example_author,
    ->                        submission_date)
    -> SELECT example_id,example_title,
    ->        example_author,submission_date
    -> FROM example_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

After performing the above steps, the contents of the table will be completely copied, including the table structure and table data.

Using the mysqldump command

The mysqldump command can be used to back up and restore MySQL databases.

If you only want to copy a single table, you can use mysqldump to export the structure and data of that table, and then import it into a new database or a new table.

The following is a simple example.

Backup table data:

mysqldump -u username -p dbname old_table > old_table_dump.sql

This will export the structure and data of the table named old_table to a SQL file named old_table_dump.sql.

You need to provide the MySQL username and password, and replace username, dbname, and old_table with actual values.

Restore to a new database:

mysql -u username -p new_dbname < old_table_dump.sql

This will create a table named old_table in the new database (new_dbname), and import the previously exported structure and data into the new table.