MySQL Table Replication

If we need to fully replicate a MySQL table, including its structure, indexes, default values, etc., simply using the CREATE TABLE ... SELECT command will not suffice.

This section will guide you through the steps to fully replicate a MySQL table:

• Use the SHOW CREATE TABLE command to obtain the CREATE TABLE statement, which includes the structure and indexes of the original table.

• Copy the displayed SQL statement, modify the table name, and execute the SQL statement to fully replicate the table structure.

• If you want to copy the table's content, you can use the INSERT INTO ... SELECT statement.

Example

Try the following example to replicate the table tutorialpro_tbl.

Step 1:

Get the full structure of the table.

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

ERROR:
No query specified

Step 2:

Modify the table name in the SQL statement and execute it.

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

Step 3:

After completing step 2, you will have created a new cloned table clone_tbl in the database. If you want to copy the table's data, you can use the INSERT INTO... SELECT statement.

mysql> INSERT INTO clone_tbl (tutorialpro_id,
    ->                        tutorialpro_title,
    ->                        tutorialpro_author,
    ->                        submission_date)
    -> SELECT tutorialpro_id,tutorialpro_title,
    ->        tutorialpro_author,submission_date
    -> FROM tutorialpro_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

After following these steps, you will have fully replicated the table, including its structure and data.